本文分享自華為雲社群《結合實戰，我為MySQL主從複製總結了幾張圖！》，作者： 冰 河。
MySQL官方文件
MySQL 主從複製官方文件連結地址如下所示：
http://dev.mysql.com/doc/refman/8.0/en/replication.html
MySQL 主從複製方式
MySQL5.6 開始主從複製有兩種方式：基於日誌（binlog）、基於 GTID（全域性事務標示符）。 這裡，我們主要講基於日誌（binlog）的複製。 關於GTID的主從複製，我們後面再詳細討論。
MySQL主從複製原理
MySQL主從複製原理，也稱為A/B原理。
(1) Master 將資料改變記錄到二進位制日誌(binary log)中，也就是配置檔案 log-bin 指定的檔案， 這些記錄叫做二進位制日誌事件(binary log events)；
(2) Slave 透過 I/O 執行緒讀取 Master 中的 binary log events 並寫入到它的中繼日誌(relay log)；
(3) Slave 重做中繼日誌中的事件，把中繼日誌中的事件資訊一條一條的在本地執行一次，完 成資料在本地的儲存，從而實現將改變反映到它自己的資料(資料重放)。
主從配置注意事項
(1)主從伺服器作業系統版本和位數一致；
(2) Master 和 Slave 資料庫的版本要一致；
(3) Master 和 Slave 資料庫中的資料要一致；
(4) Master 開啟二進位制日誌，Master 和 Slave 的 server_id 在區域網內必須唯一；
主從配置的簡要步驟
1、Master 上的配置
(1) 安裝資料庫；
(2) 修改資料庫配置檔案，指明 server_id，開啟二進位制日誌(log-bin)；
(3) 啟動資料庫，檢視當前是哪個日誌，position 號是多少；
(4) 登入資料庫，授權資料複製使用者（IP 地址為從機 IP 地址，如果是雙向主從，這裡的 還需要授權本機的 IP 地址，此時自己的 IP 地址就是從 IP 地址)；
(5) 備份資料庫（記得加鎖和解鎖）；
(6) 傳送備份資料到 Slave 上；
(7) 啟動資料庫；
以下步驟，為單向主從搭建成功，想搭建雙向主從需要的步驟：
(1) 登入資料庫，指定 Master 的地址、使用者、密碼等資訊（此步僅雙向主從時需要）；
(2) 開啟同步，檢視狀態；
2、Slave 上的配置
(1) 安裝資料庫；
(2) 修改資料庫配置檔案，指明 server_id（如果是搭建雙向主從的話，也要開啟二進位制 日誌 log-bin）；
(3) 啟動資料庫，還原備份；
(4) 檢視當前是哪個日誌，position 號是多少（單向主從此步不需要，雙向主從需要）；
(5) 指定 Master 的地址、使用者、密碼等資訊；
(6) 開啟同步，檢視狀態。
單向主從環境搭建
安裝資料庫
參考《MySQL之——原始碼編譯MySQL8.x+升級gcc+升級cmake（親測完整版）》。
配置Master的my.cnf
[root@liuyazhuang131 ~]# vi /etc/my.cnf
# 在 [mysqld] 中增加以下配置項 # 設定 server_id，一般設定為 IP server_id=131 # 複製過濾：需要備份的資料庫，輸出 binlog #binlog-do-db=liuyazhuang # 複製過濾：不需要備份的資料庫，不輸出（mysql 庫一般不同步） binlog-ignore-db=mysql # 開啟二進位制日誌功能，可以隨便取，最好有含義 log-bin=lyz-mysql-bin ## 為每個 session 分配的記憶體，在事務過程中用來儲存二進位制日誌的快取 binlog_cache_size=1M ## 主從複製的格式（mixed,statement,row，預設格式是 statement） binlog_format=mixed # 二進位制日誌自動刪除/過期的天數。預設值為 0，表示不自動刪除。 expire_logs_days=7 # 跳過主從複製中遇到的所有錯誤或指定型別的錯誤，避免 slave 端複製中斷。 # 如：1062 錯誤是指一些主鍵重複，1032 錯誤是因為主從資料庫資料不一致 slave_skip_errors=1062
複製過濾可以讓你只複製伺服器中的一部分資料，有兩種複製過濾：
(1) 在 Master 上過濾二進位制日誌中的事件；
(2) 在 Slave 上過濾中繼日誌中的事件。如下：
MySQL 對於二進位制日誌 (binlog)的複製型別
(1) 基於語句的複製：在 Master 上執行的 SQL 語句，在 Slave 上執行同樣的語句。MySQL 默 認採用基於語句的複製，效率比較高。一旦發現沒法精確複製時，會自動選著基於行的複製。
(2) 基於行的複製：把改變的內容複製到 Slave，而不是把命令在 Slave 上執行一遍。從MySQL5.0 開始支援。
(3) 混合型別的複製：預設採用基於語句的複製，一旦發現基於語句的無法精確的複製時，就會採用基於行的複製。
重啟Master庫
啟動/重啟 Master 資料庫服務，登入資料庫，建立資料同步使用者，並授予相應的許可權
[root@liuyazhuang131 ~]# service mysql restart
[root@liuyazhuang131 ~]# mysql -uroot -proot
##建立資料同步使用者，並授予相應的許可權 mysql> grant replication slave, replication client on *.* to 'repl'@'192.168.209.132' identified by '123456'; Query OK, 0 rows affected (0.00 sec) ## 重新整理授權表資訊 mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) ## 檢視 position 號，記下 position 號（從機上需要用到這個 position 號和現在的日誌檔案) mysql> show master status; +----------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +----------------------+----------+--------------+------------------+-------------------+ | lyz-mysql-bin.000001 | 1312 | | mysql | | +----------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
模擬業務資料庫
建立 lyz 庫、表，並寫入一定量的資料，用於模擬現有的業務系統資料庫
create database if not exists lyz default charset utf8 collate utf8_general_ci; use lyz; DROP TABLE IF EXISTS `lyz_user`; CREATE TABLE `lyz_user` ( `Id` int(11) NOT NULL AUTO_INCREMENT, `userName` varchar(255) NOT NULL DEFAULT '' COMMENT '使用者名稱', `pwd` varchar(255) NOT NULL DEFAULT '' COMMENT '密碼', PRIMARY KEY (`Id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT='使用者資訊表'; INSERT INTO `lyz_user` VALUES (1,'yixiaoqun','123456');
實現初始資料一致
為保證 Master 和 Slave 的資料一致，我們採用主備份，從還原來實現初始資料一致
## 先臨時鎖表 mysql> flush tables with read lock; Query OK, 0 rows affected (0.00 sec) ## 這裡我們實行全庫備份，在實際中，我們可能只同步某一個庫，那也可以只備份一個庫 [root@liuyazhuang131 mysql]# mysqldump -u root -proot lyz > /tmp/lyz.sql [root@liuyazhuang131 mysql]# cd /tmp [root@liuyazhuang131 tmp]# ll | grep lyz.sql -rw-r--r-- 1 root root 2031 Apr 25 01:18 lyz.sql # 注意：實際生產環境中大資料量（超 2G 資料）的備份，建議不要使用 mysqldump 進行 比分，因為會非常慢。此時推薦使用 XtraBackup 進行備份。 # 解鎖表 mysql> unlock tables; Query OK, 0 rows affected (0.00 sec)
將 Master 上備份的資料遠端傳送到 Slave 上，以用於 Slave 配置時恢復資料
[root@liuyazhuang131 tmp]# scp /tmp/lyz.sql root@192.168.209.132:/tmp/lyz.sql The authenticity of host '192.168.209.132 (192.168.209.132)' can't be established. RSA key fingerprint is da:70:7b:d5:0c:16:b3:1a:53:b7:3d:9f:20:01:26:3e. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '192.168.209.132' (RSA) to the list of known hosts. root@192.168.209.132's password: lyz.sql
配置Slave庫
接下來處理 Slave（192.168.209.132），配置檔案只需修改一項，其餘配置用命令來操作
[root@liuyazhuang132 ]# vi /etc/my.cnf # 在 [mysqld] 中增加以下配置項 # 設定 server_id，一般設定為 IP server_id=132 # 複製過濾：需要備份的資料庫，輸出 binlog #binlog-do-db=lyz # 複製過濾：不需要備份的資料庫，不輸出（mysql 庫一般不同步） binlog-ignore-db=mysql # 開啟二進位制日誌，以備 Slave 作為其它 Slave 的 Master 時使用 log-bin=lyz-mysql-slave1-bin ## 為每個 session 分配的記憶體，在事務過程中用來儲存二進位制日誌的快取 binlog_cache_size = 1M # 主從複製的格式（mixed,statement,row，預設格式是 statement） binlog_format=mixed # 二進位制日誌自動刪除/過期的天數。預設值為 0，表示不自動刪除。 expire_logs_days=7 # 跳過主從複製中遇到的所有錯誤或指定型別的錯誤，避免 slave 端複製中斷。 # 如：1062 錯誤是指一些主鍵重複，1032 錯誤是因為主從資料庫資料不一致 slave_skip_errors=1062 ## relay_log 配置中繼日誌 relay_log=lyz-mysql-relay-bin ## log_slave_updates 表示 slave 將複製事件寫進自己的二進位制日誌 log_slave_updates=1 ##防止改變資料(除了特殊的執行緒) read_only=1
如果Slave為其它的Slave的Master時，必須設定bin_log,在這裡，我開啟了二進位制日誌，而且顯式的命名(預設名稱為hostname),但是如果hostname改變則會出現問題。
relay_log配置中繼日誌，log_slave_updates表示slave將複製事件 寫進自己的二進位制日誌.當設定log_slave_updates時，你可以讓slave扮演其它slave的master.此時，slave把sql執行緒執行的事件寫進自己的二進位制日誌(binary log)然後，它的slave可以獲取這些事件並執行它。如下圖所示(傳送複製事件到其它的Slave):
還原備份資料
儲存後重啟MySQL服務，還原備份資料
[root@liuyazhuang132 ~]# service mysql restart Shutting down MySQL. SUCCESS! Starting MySQL.. SUCCESS!
Slave上建立相同庫
[root@liuyazhuang132 ~]# mysql -uroot -proot
mysql> use lyz;
Database changed
匯入資料
[root@liuyazhuang132 ~]# mysql -uroot -proot lyz < /tmp/lyz.sql [root@liuyazhuang132 ~]# mysql -uroot -proot mysql> use lyz; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from lyz_user; +----+-----------+--------+ | Id | userName | pwd | +----+-----------+--------+ | 1 | yixiaoqun | 123456 | +----+-----------+--------+ 1 row in set (0.00 sec)
Slave庫新增引數
登入Slave資料庫,新增相關引數：Master的IP、埠、同步使用者、密碼、position號、讀取哪個日誌檔案
change master to master_host='192.168.209.131',master_user='repl',master_password='123456',master_port=3306, master_log_file='lyz-mysql-bin.000001',master_log_pos=1312,master_connect_retry=30;
上面執行的命令的解釋:
- master_host=‘192.168.209.131’ ##Master的IP地址
- master_user=‘repl’ ##用於同步資料的使用者(在Master中授權的使用者)
- master_password=‘123456’ ##同步資料使用者的密碼
- master_port=3306 ##master資料庫服務的埠
- master_log_file=‘lyz-mysql-bin.000001’ ##指定Slave從哪個日誌檔案開始讀取複製檔案(可在Master上使用show master status檢視到日誌檔名)
- master_log_pos=429 ##從哪個POSITION號開始讀
- master_connect_retry=30 #當重新建立主從連線時，如果連線建立失敗，間隔多久後重試,單位為秒，預設設定為60秒，同步延遲調優引數。
檢視主從同步狀態
show slave status\G;
可看到Slave_IO_State為空，Slave_IO_Runngin和Slave_SQL_Running是No,表時Slave還是沒有開始複製過程。
開啟主從同步
mysql> start slave;
再次檢視同步狀態
#show slave status\G;
主要看以下兩個引數,這兩個引數如果是Yes，就表示資料同步正常
Slave_IO_Running:Yes
Slave_SQL_Running:Yes
可檢視master和slave上執行緒的狀態,在master上，可以看到slave的I/O執行緒建立的連線
Master:mysql>show processlist\G;
1.row為處理slave的I/O執行緒的連線。
2.row為處理MySQL客戶連線執行緒。
3.row為處理本地命令列的執行緒
Slave:mysql>show processlist\G;
1.row為處理slave的I/O執行緒的連線。
2.row為處理MySQL客戶連線執行緒。
3.row為處理本地命令列的執行緒
主從資料複製同步測試
Master: mysql> insert into lyz_user values(2,'test1','123456'); Slave: mysql> start slave;
經過以上配置，在192.168.209.131上對資料庫/表進行增刪改查，建立/刪除資料庫/表都會同步到192.168.209.132資料庫上了。
至此，整個配置過程結束。
點選關注，第一時間瞭解華為雲新鮮技術~