主從複製 + 分庫分表要講主從複製，首先來看看MySQL自帶的日誌文件。日誌錯誤日誌錯誤日誌是 MySQL 中最重要的日誌之一，它記錄了當 mysqld 啟動和停止時，以及伺服器在運行過程中發生任何嚴重錯誤時的相關信息。當資料庫出現任何故障導致無法正常使用時，建議首先查看此日誌文件。該日誌 ...

主從複製 + 分庫分表

要講主從複製，首先來看看MySQL自帶的日誌文件。

日誌

錯誤日誌

錯誤日誌是 MySQL 中最重要的日誌之一，它記錄了當 mysqld 啟動和停止時，以及伺服器在運行過程中發生任何嚴重錯誤時的相關信息。當資料庫出現任何故障導致無法正常使用時，建議首先查看此日誌文件。

該日誌是預設開啟的，預設存放目錄 /var/log/，預設的日誌文件名為 mysqld.log 。查看日誌位置：

show variables like '%log_error%';

通過tail指令查看日誌文件的尾部記錄的日誌：

tail -50 /var/log/mysqld.log

實時查看文件尾部記錄的日誌：

tail -f /var/log/mysqld.log

二進位日誌

基本概述

二進位日誌（BINLOG）記錄了所有的 DDL（數據定義語言）語句和 DML（數據操縱語言）語句，但不包括數據查詢（SELECT、SHOW）語句。在MySQL8版本中，預設二進位日誌是開啟著的。

DDL：例如創建資料庫、創建表、修改表等操作；

DML：增刪改操作；

有什麼用？

資料庫災難時的數據恢復，一旦資料庫崩了，可通過二進位日誌進行數據恢復；
用於 MySQL 的主從複製；

查看二進位日誌相關信息：

show variables like '%log_bin%';

log_bin_basename：當前資料庫伺服器的binlog日誌的基礎名稱(首碼)，具體的binlog文件名需要在該basename的基礎上加上編號(編號從000001開始)。

log_bin_index：binlog的索引文件，裡面記錄了當前伺服器關聯的 binlog 文件有哪些。

格式

MySQL伺服器中提供了多種格式來記錄二進位日誌，具體格式及特點如下：

日誌格式	含義
STATEMENT	基於SQL語句的日誌記錄，記錄的是SQL語句，對數據進行修改的SQL都會記錄在日誌文件中
ROW	基於行的日誌記錄，記錄的是每一行的數據變更之前和之後的樣子。（預設）
MIXED	混合了STATEMENT和ROW兩種格式，預設採用STATEMENT，在某些特殊情況下會自動切換為ROW進行記錄

預設的日誌記錄格式採用的是“ROW”，可以通過命令 show variables like '%binlog_format%'; 查看；
mysql> show variables like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set, 1 warning (0.11 sec)
如果我們需要自定義配置二進位日誌的格式，只需要在 /etc/my.cnf 中配置 binlog_format 參數即可。

查看二進位日誌

由於日誌是以二進位方式存儲的，不能直接讀取，需要通過二進位日誌查詢工具 mysqlbinlog 來查看，具體語法：

mysqlbinlog [ 參數選項 ] logfilename 

參數選項： 
	-d 指定資料庫名稱，只列出指定的資料庫相關操作。 
	-o 忽略掉日誌中的前n行命令。 
	-v 將行事件(數據變更)重構為SQL語句 （如果是ROW格式的，需要加上該參數）
	-vv 將行事件(數據變更)重構為SQL語句，並輸出註釋信息

刪除二進位日誌

對於比較繁忙的業務系統，每天生成的binlog數據巨大，如果長時間不清除，將會占用大量磁碟空間。可以通過以下幾種方式清理日誌：

指令	含義
reset master	刪除全部 binlog 日誌，刪除之後，日誌編號，將從 binlog.000001 重新開始
purge master logs to 'binlog.*'	刪除 * 編號之前的所有日誌
purge master logs before 'yyyy-mm-dd hh24:mi:ss'	刪除日誌為 "yyyy-mm-dd hh24:mi:ss" 之前產生的所有日誌

也可以在 mysql 的配置文件中配置二進位日誌的過期時間，設置了之後，二進位日誌過期會自動刪除。預設二進位日誌只存放30天，即2592000s，30天後自動刪除。

# 查看過期時間
show variables like '%binlog_expire_logs_seconds%';

查詢日誌

查詢日誌中記錄了客戶端的所有操作語句（所有的增刪改查以及DDL語句都會記錄），而二進位日誌不包含查詢數據的SQL語句。預設情況下，查詢日誌是未開啟的。

show variables like '%general%';

如果需要開啟查詢日誌，可以修改MySQL的配置文件 /etc/my.cnf 文件，添加如下內容：

# 該選項用來開啟查詢日誌 ， 可選值 ： 0 或者 1 ； 0 代表關閉， 1 代表開啟
general_log=1
# 設置日誌的文件名 ， 如果沒有指定， 預設的文件名為 
host_name.log general_log_file=mysql_query.log

開啟了查詢日誌之後，在MySQL的數據存放目錄，也就是 /var/lib/mysql/ 目錄下就會出現 mysql_query.log 文件。之後所有的客戶端的增刪改查操作都會記錄在該日誌文件之中，長時間運行後，該日誌文件將會非常大。

慢查詢日誌

顧名思義，記錄的就是執行效率比較低，速度較慢的sql日誌。慢查詢日誌記錄了所有執行時間超過參數 long_query_time 設置值並且掃描記錄數不小於min_examined_row_limit 的所有的SQL語句的日誌，預設未開啟。long_query_time 預設為10 秒，最小為 0，精度可以到微秒。

如果需要開啟慢查詢日誌，需要在MySQL的配置文件 /etc/my.cnf 中配置如下參數：

# 開啟慢查詢日誌
slow_query_log=1
# 設置慢查詢日誌的時間參數為2，代表超過2s，就算慢查詢
long_query_time=2

主從複製

概述

主從複製是指將主資料庫的 DDL 和 DML 操作通過二進位日誌傳到從庫伺服器中，然後在從庫上對這些日誌重新執行（也叫重做），從而使得從庫和主庫的數據保持同步。

MySQL支持一臺主庫同時向多台從庫進行複製，從庫同時也可以作為其他從伺服器的主庫，實現鏈狀複製。

主從複製的優點：

主庫出現問題（宕機或重啟），可以快速切換到從庫提供服務；
實現讀寫分離，降低主庫的訪問壓力，主庫執行寫操作（增刪改），從庫執行讀操作（查）；
可以在從庫中執行備份，以避免備份期間影響主庫服務；

第三點解釋：

進行數據備份時要加上全局鎖，避免數據不一致的情況發生，當前資料庫就處於只讀狀態，其他的客戶端不能執行增刪改操作。有了主從複製後，可以在從庫中加全局鎖進行備份，主庫中依然可以進行增刪改等相關操作，而從庫加了全局鎖，查詢是沒有問題的。

主從複製原理

MySQL主從複製的核心就是 二進位日誌，具體的過程如下：

從庫中有兩組線程：

IOthread：發起請求連接 master 資料庫，讀取 master 資料庫中的 binlog 日誌文件，並寫入到 slave 自身的中繼日誌 Relay log 中；

SQLthread：負責讀取中繼日誌中的日誌，重新執行日誌中記錄的操作，保證主從數據的一致性。

主從複製主要分為以下三步操作：

Master 主庫在事務提交時，會把數據變更記錄在二進位日誌文件 Binlog 中；
從庫讀取主庫的二進位日誌文件 Binlog ，寫入到從庫的中繼日誌 Relay Log；
slave 重做（重新執行）中繼日誌中的事件，將改變反映它自己的數據；

搭建一主一從

準備

準備兩台伺服器，都關閉防火牆：

systemctl stop firewalld;	# 關閉防火牆
systemctl disable firewalld;	# 關閉防火牆的開機自啟

在兩台伺服器中分別安裝好 MySQL，並檢查 MySQL 的運行狀態：

systemctl status mysql;

主庫搭建

修改主庫的配置文件 /etc/my.cnf：

vim /etc/my.cnf

# mysql 服務ID，保證整個集群環境中唯一，取值範圍：1 – 2^32-1，預設為1
server-id=1
# 是否只讀,1 代表只讀, 0 代表讀寫（主庫既可讀又可寫，設置為0）
read-only=0
# 忽略的數據, 指不需要同步的資料庫
# binlog-ignore-db=mysql
# 指定同步的資料庫，如果不指定某個具體的資料庫，那表明所有資料庫都需要同步
# binlog-do-db=db01

重啟主庫：

systemctl restart mysqld

登錄mysql，創建遠程連接的賬號，並授予主從複製許可權：

mysql -uroot -p123

# 創建 ypf 用戶，並設置密碼123123，該用戶可在任意主機連接該MySQL服務，@'%'表示這個用戶可以在任意主機上來訪問當前伺服器
CREATE USER 'ypf'@'%' IDENTIFIED WITH mysql_native_password BY 'Root@123123';

# 為 'ypf'@'%' 用戶分配主從複製許可權
GRANT REPLICATION SLAVE ON *.* TO 'ypf'@'%';

通過指令，查看二進位日誌坐標：

show master status;

輸出欄位解釋：

file : 寫入到哪個 binlog 日誌文件；

position ：從哪個位置開始推送日誌；

binlog_ignore_db : 指定不需要同步的資料庫；

從庫配置

修改從庫的配置文件 /etc/my.cnf：

vim /etc/my.cnf

# mysql 服務ID，保證整個集群環境中唯一，取值範圍：1 – 2^32-1，和主庫不一樣即可
server-id=2
# 是否只讀,1 代表只讀, 0 代表讀寫（從庫只讀，設置為1）
read-only=1

重啟主庫：

systemctl restart mysqld

登錄mysql，設置主庫配置：

mysql -uroot -p123

CHANGE REPLICATION SOURCE TO SOURCE_HOST='192.168.200.200', SOURCE_USER='ypf', SOURCE_PASSWORD='Root@123123', SOURCE_LOG_FILE='binlog.000004', SOURCE_LOG_POS=663;

# 如果是 8.0.23 之前的MySQL版本，執行如下SQL：
CHANGE MASTER TO MASTER_HOST='192.168.200.200', MASTER_USER='ypf', MASTER_PASSWORD='Root@123123', MASTER_LOG_FILE='binlog.000004', MASTER_LOG_POS=663;

參數名	含義	8.0.23之前
SOURCE_HOST	主庫IP地址	MASTER_HOST
SOURCE_USER	連接主庫的用戶名	MASTER_USER
SOURCE_PASSWORD	連接主庫的密碼	MASTER_PASSWORD
SOURCE_LOG_FILE	binlog日誌文件名	MASTER_LOG_FILE
SOURCE_LOG_POS	binlog日誌文件位置	MASTER_LOG_POS

最後兩個參數在主庫中執行 show master status;可查看參數值

開啟主從複製：

start replica; 	#8.0.22之後
start slave; 	#8.0.22之前

查看主從同步狀態：

show replica status; 	#8.0.22之後	如果表比較大，展示數據比較亂，可以在命令後面加上\G
show slave status; 		#8.0.22之前

測試主從是否同步

在主庫 192.168.200.200 上創建資料庫、表，並插入數據：

create database db01;
use db01;

create table tb_user( 
    id int(11) primary key not null auto_increment, 
    name varchar(50) not null, 
    sex varchar(1) 
)engine=innodb default charset=utf8mb4; 

insert into tb_user(id,name,sex) values(null,'Tom', '1'),(null,'Trigger','0'), (null,'Dawn','1');

在從庫 192.168.200.201 中查詢數據，驗證主從是否同步。

上面配置的是從當前二進位日誌的指定位置（SOURCE_LOG_POS參數設定）往後進行主從複製，如果需要把之前主庫的數據同步到從庫，那可以先把主庫的數據導出到一個sql腳本中，然後在從庫中執行sql腳本，這樣保證了主從的初始數據是一致的。

分庫分表

隨著互聯網及移動互聯網的發展，應用系統的數據量也是成指數式增長，若採用單資料庫進行數據存儲，存在以下性能瓶頸：

IO瓶頸：熱點數據太多，資料庫緩存不足，產生大量磁碟IO，效率較低。請求數據太多，帶寬不夠，網路IO瓶頸。
CPU瓶頸：排序、分組、連接查詢、聚合統計等SQL會耗費大量的CPU資源，請求數太多，CPU出現瓶頸。

因此，就需要將存儲在一個資料庫中的數據分散存儲在多台資料庫中，緩解單台資料庫的磁碟存儲及訪問的壓力。

什麼是分庫分表？

分庫：就是一個資料庫分成多個資料庫，部署到不同機器上。

分表：就是一個資料庫表分成多個表。

分庫分表的中心思想都是將數據分散存儲，使得單一資料庫/表的數據量變小來緩解單一資料庫的性能問題，從而提升資料庫性能。

為什麼要分庫？

業務量劇增，MySQL單機磁碟容量會撐爆，拆成多個資料庫，磁碟使用率大大降低。
我們知道資料庫連接是有限的。在高併發的場景下，大量請求訪問資料庫，MySQL單機是扛不住的！當前非常火的微服務架構出現，就是為了應對高併發。它把訂單、用戶、商品等不同模塊，拆分成多個應用，並且把單個資料庫也拆分成多個不同功能模塊的資料庫（訂單庫、用戶庫、商品庫），以分擔讀寫壓力。

為什麼要分表？

單表數據量太大的話，SQL的查詢就會變慢。如果一個查詢SQL沒命中索引，千百萬數據量級別的表可能會拖垮整個資料庫。
即使SQL命中了索引，如果表的數據量超過一千萬的話，查詢也是會明顯變慢的。這是因為索引一般是B+樹結構，數據千萬級別的話，B+樹的高度會增高，查詢就會變慢。

拓展：

MySQL預設的存儲引擎是 InnoDB，使用的索引結構是 B+樹，InnoDB存儲引擎最小儲存單元是頁，一頁大小就是16k。對於葉子節點，假設一行數據的大小為1k，那一頁中可以存儲16行這樣的數據。InnoDB的指針占用6個位元組的空間，主鍵即使為bigint，占用位元組數為8。

所以，對於非葉子節點，設每頁能存的鍵值為n，則 n * 8 + (n + 1) * 6 = 16*1024，可求得n約為1170，那每頁可以存儲的指針數為1171。因此，一棵高度為2的B+樹，能存放1171 * 16 = 18736條這樣的數據行。同理一棵高度為3的B+樹，能存放1171 *1171 *16 =21939856，接近2200W的數據行。

B+樹的高度一般為1~3層，如果B+樹到了4層，查詢的時候會多查磁碟的次數（磁碟IO次數），SQL就會變慢。

講一下水平/垂直、分庫/分表？

水平分庫

以欄位為依據，按照一定策略，將一個庫的數據拆分到多個庫中。

特點：

拆分後，每台機器中，每個庫的表結構一樣，每個庫的數據都不一樣，所有庫的並集是全量數據。（相當於這個資料庫的每張表橫向分割成多個部分分別保存到不同的機器的資料庫中）

水平分表

以欄位為依據，按照一定策略，將一個表的數據拆分到多個表中。

特點：

拆分後，每台機器中，每張表的表結構都一樣，每個表的數據都不一樣，所有表的並集是全量數據。

垂直分庫

以表為依據，根據業務將不同表拆分到不同庫中。

特點：

拆分後，每台機器中，每張表的表結構都不一樣，每個表的數據也不一樣，所有庫的並集是全量數據。（相當於這個資料庫的每張表縱向分割成多個部分分別保存到不同的機器的資料庫中）

應用場景：

在業務發展初期，業務功能模塊比較少，為了快速上線和迭代，往往採用單個資料庫來保存數據。但是隨著業務蒸蒸日上，系統功能逐漸完善。這時候，可以按照系統中的不同業務進行拆分，比如拆分成用戶庫、訂單庫、積分庫、商品庫，把它們部署在不同的資料庫伺服器。

垂直分表

以欄位為依據，根據欄位屬性將不同欄位拆分到不同表中。

特點：

拆分後，每台機器中，每張表的表結構都不一樣，每個表的數據也不一樣，一般通過一列（主鍵/外鍵）關聯。所有表的並集是全量數據。

應用場景：

如果一個單表包含了幾十列甚至上百列，管理起來很混亂，每次都select *的話，還占用IO資源。這時候，我們可以將一些不常用的、數據較大或者長度較長的列拆分到另外一張表。

分庫分表有哪些策略？

範圍分片
取模分片
一致性hash分片

範圍分片

根據指定的欄位及其配置的範圍與數據節點的對應情況，來決定該數據屬於哪一個分片。比如在訂單表中，我們可以利用表的主鍵，0-500萬之間的值，存儲在0號數據節點(數據節點的索引從0開始) ； 500萬-1000萬之間的數據存儲在1號數據節點； 1000萬-1500萬的數據節點存儲在2號節點，依此類推。

優點：

這種方案有利於擴容，不需要數據遷移。假設數據量增加到2千萬，我們只需要水平增加一張表就好了，之前0~1500萬的數據，不需要遷移。

缺點：

這種方案會有熱點問題，因為訂單id是一直在增大的，也就是說最近一段時間都是匯聚在一張表裡面的。比如最近一個月的訂單都在1000萬~2000萬之間，平時用戶一般都查最近一個月的訂單比較多，請求都打到order_1表啦，這就導致數據熱點問題。

該分片規則，主要是針對於數字類型的欄位適用。

取模分片

根據指定的欄位值與節點數量（機器數）進行求模運算，根據運算結果，來決定該數據屬於哪一個分片。

優點：

不會存在明顯的熱點問題。

缺點：

如果一開始按照取模分成3個表了，未來某個時候，表數據量又到瓶頸了，需要擴容，這就比較棘手了。比如你從3張表，又擴容成6張表，那之前id=5的數據是在（5%3=2），現在應該放到（5%6=5），也就是說歷史數據要做遷移了。
不適用於字元串類型。如果主鍵是UUID（字元串），那就不適用了。

一致性hash分片

所謂一致性哈希，相同的哈希因數計算值總是被劃分到相同的分區表中，不會因為分區節點的增加而改變原來數據的分區位置，有效的解決了分散式數據的擴容問題。

什麼時候需要考慮分庫分表？

什麼時候分庫？

當你的業務發展很快，用戶急劇上漲，並且還是多個服務共用一個單體資料庫，資料庫成為了性能瓶頸，就需要考慮分庫了。比如用戶、商品、支付、訂單等，都可以抽取出來，整成一個獨立的服務（微服務），並且拆分對應的資料庫（用戶庫、商品庫、訂單庫）。

什麼時候分表？

如果你的系統處於快速發展時期，如果每天的訂單流水都新增幾十萬，並且，訂單表的查詢效率明變慢時，就需要規劃分表了。一般B+樹索引的高度是2~3層最佳（3層的B+樹都可容納超2000W的數據行），如果數據量千萬級別，可能高度就變4層了，數據量就會明顯變慢了。

分庫分表後存在什麼問題？

分散式ID

我們往往直接使用資料庫自增特性來生成主鍵ID，這樣確實比較簡單。而在分庫分表的環境中，數據分佈在不同的分片上，不能再藉助資料庫自增長特性直接生成，否則會造成不同分片上的數據表主鍵會重覆。

最簡單可以考慮UUID，或者使用雪花演算法生成分散式ID。

分頁問題

將不同分片上查到的結果進行彙總，再分頁；
把分頁交給前端，前端傳來pageSize和pageNo，在各個資料庫節點都執行分頁，然後匯聚總數量前端。這種方法，缺點就是會造成空查。

數據遷移，容量規劃，擴容等問題

很少有項目會在初期就開始考慮分片設計的，一般都是在業務高速發展面臨性能和存儲的瓶頸時才會提前準備。使用一致性Hash演算法可以避免後期分片集群擴容起來需要遷移舊的數據這一問題。

參考

黑馬程式員：https://www.bilibili.com/video/BV1Kr4y1i7ru

公眾號：撿田螺的小男孩 -《我們為什麼要分庫分表？》

MySQL的主從複製和分庫分表初探