點擊上方“朱小廝的博客”，選擇“設為星標”

后臺回復"1024"領取公眾號專屬資料

來源：rrd.me/gDJDK

MySQL主從同步原理

MySQL主從同步是在MySQL主從復制(Master-Slave Replication)基礎上實現的，通過設置在Master MySQL上的binlog(使其處于打開狀態)，Slave MySQL上通過一個I/O線程從Master MySQL上讀取binlog，然后傳輸到Slave MySQL的中繼日志中，然后Slave MySQL的SQL線程從中繼日志中讀取中繼日志，然后應用到Slave MySQL的數據庫中。這樣實現了主從數據同步功能。

MySQL中主從復制的優點

• 橫向擴展解決方案

在多個從庫之間擴展負載以提高性能。在這種環境中，所有寫入和更新在主庫上進行。但是，讀取可能發生在一個或多個從庫上。該模型可以提高寫入的性能（由于主庫專用于更新），同時在多個從庫上讀取，可以大大提高讀取速度。

• 數據安全性

由于主庫數據被復制到從庫，從庫可以暫停復制過程，可以在從庫上運行備份服務，而不會破壞對應的主庫數據。

• 分析

可以在主庫上創建實時數據，而信息分析可以在從庫上進行，而不會影響主服務器的性能。

GTID概念

從 MySQL 5.6.5 開始新增了一種基于 GTID 的復制方式。通過 GTID保證了每個在主庫上提交的事務在集群中有一個唯一的ID。這種方式強化了數據庫的主備一致性，故障恢復以及容錯能力。

在原來基于二進制日志的復制中，從庫需要告知主庫要從哪個偏移量進行增量同步，如果指定錯誤會造成數據的遺漏，從而造成數據的不一致。借助GTID，在發生主備切換的情況下，MySQL的其它從庫可以自動在新主庫上找到正確的復制位置，這大大簡化了復雜復制拓撲下集群的維護，也減少了人為設置復制位置發生誤操作的風險。另外，基于GTID的復制可以忽略已經執行過的事務，減少了數據發生不一致的風險。

什么是GTID

GTID (Global Transaction ID) 是對于一個已提交事務的編號，并且是一個全局唯一的編號。GTID 實際上 是由UUID+TID 組成的。其中 UUID 是一個 MySQL 實例的唯一標識。TID代表了該實例上已經提交的事務數量，并且隨著事務提交單調遞增。

下面是一個GTID的具體形式：3E11FA47-71CA-11E1-9E33-C80AA9429562:23，冒號分割前邊為uuid，后邊為TID。

GTID 集合可以包含來自多個 MySQL 實例的事務，它們之間用逗號分隔。

如果來自同一MySQL實例的事務序號有多個范圍區間，各組范圍之間用冒號分隔。例如：e6954592-8dba-11e6-af0e-fa163e1cf111:1-5:11-18,e6954592-8dba-11e6-af0e-fa163e1cf3f2:1-27 可以使用show master status實時查看當前事務執行數

Gtid的作用

Gtid采用了新的復制協議，舊協議是，首先從服務器上在一個特定的偏移量位置連接到主服務器上一個給定的二進制日志文件，然后主服務器再從給定的連接點開始發送所有的事件。

新協議有所不同，支持以全局統一事務ID (GTID)為基礎的復制。當在主庫上提交事務或者被從庫應用時，可以定位和追蹤每一個事務。GTID復制是全部以事務為基礎，使得檢查主從一致性變得非常簡單。如果所有主庫上提交的事務也同樣提交到從庫上，一致性就得到了保證。

Gtid的工作原理

①當一個事務在主庫端執行并提交時，產生GTID，一同記錄到binlog日志中。

②binlog傳輸到slave,并存儲到slave的relaylog后，讀取這個GTID的這個值設置gtid_next變量，即告訴Slave，下一個要執行的GTID值。

③sql線程從relay log中獲取GTID，然后對比slave端的binlog是否有該GTID。

④如果有記錄，說明該GTID的事務已經執行，slave會忽略。

⑤如果沒有記錄，slave就會執行該GTID事務，并記錄該GTID到自身的binlog， 在讀取執行事務前會先檢查其他session持有該GTID，確保不被重復執行。

⑥在解析過程中會判斷是否有主鍵，如果沒有就用二級索引，如果沒有就用全部掃描。

配置步驟

操作環境

系統：CentOS 7 數據庫：Percona MySQL 5.7 主庫：192.168.11.31 從庫：192.168.11.32

主庫配置

[mysqld] datadir=/data/mysql/3306 socket=/tmp/mysql.sock symbolic-links=0server_id=31 #服務器ID log-bin=master-bin #二進制日志文件名 binlog_format = row #強烈建議，其他格式可能造成數據不一致 log-slave-updates = 1 #是否記錄從服務器同步數據動作 gtid-mode = on #啟用gitd功能 enforce-gtid-consistency = 1 #開啟強制GTID一致性 master-info-repository = TABLE #記錄IO線程讀取已經讀取到的master binlog位置，用于slave宕機后IO線程根據文件中的POS點重新拉取binlog日志 relay-log-info-repository = TABLE #記錄SQL線程讀取Master binlog的位置，用于Slave 宕機后根據文件中記錄的pos點恢復Sql線程 sync-master-info = 1 #啟用確保無信息丟失；任何一個事務提交后, 將二進制日志的文件名及事件位置記錄到文件中 slave-parallel-workers = 2 #設定從服務器的復制線程數；0表示關閉多線程復制功能 binlog-checksum = CRC32 #設置binlog校驗算法（循環冗余校驗碼） master-verify-checksum = 1 #設置主服務器是否校驗 slave-sql-verify-checksum = 1 #設置從服務器是否校驗 binlog-rows-query-log_events = 1 #用于在二進制日志記錄事件相關的信息，可降低故障排除的復雜度 sync_binlog = 1 #保證master crash safe，該參數必須設置為1 innodb_flush_log_at_trx_commit = 1 #保證master crash safe，該參數必須設置為1

從庫配置

[mysqld] server_id = 32 log-bin=mysql-bin binlog_format = row gtid-mode = on enforce-gtid-consistency = 1 master-info-repository = TABLE relay-log-info-repository = TABLE sync-master-info = 1 slave-parallel-workers = 4 binlog-checksum = CRC32 master-verify-checksum = 1 slave-sql-verify-checksum = 1 binlog-rows-query-log_events = 1 #sync_binlog = 1 #innodb_flush_log_at_trx_commit = 1 log-slave-updates = 0 # crash safe slave 5.6版本需要開啟 relay_log_recovery = 1 # crash safe slave read_only=on #設置一般用戶為只讀模式 super_read_only=on #設置super（root）用戶為只讀模式 #tx_read_only=on #設置事務為只讀模式

主庫權限設置

mysql > grant replication slave on *.* to slave@'192.168.11.32' identified by 'slave123'; mysql > flush privileges;

自動同步連接主庫(方法一)

適用于master也是新建不久的情況。

如果你的master所有的binlog還在。可以安裝slave，slave直接change master to到master端。

原理是直接獲取master所有的GTID并執行。

優點：簡單方便。

缺點：如果binlog太多，數據完全同步需要時間較長，并且master一開始就啟用了GTUD。

change master to master_host='192.168.11.31',\ master_user='slave',master_password='slave123',\ master_port=3306,master_auto_position=1#master_auto_position=1 從庫自動找同步點

備份導入連接主庫(方法二)

Xtrabackup_binlog_info文件中，包含global.gtid_purged=’XXXXXX:XXXX’的信息。

然后到slave去手工的 SET @@GLOBAL.GTID_PURGED=’XXXXXX:XXXX’。

恢復備份，開啟change master to 命令。

備份導入連接主庫(方法三)

適用于擁有較大數據的情況。（推薦）

通過master或者其他slave的備份搭建新的slave。

原理：獲取master的數據和這些數據對應的GTID范圍，然后通過slave設置master_auto_position=1,自動同步，跳過備份包含的gtid。

缺點：相對來說有點復雜。

將主庫設為只讀模式

注：生產環境會影響不能寫入數據

mysql> flush tables with read lock; Query OK, 0 rows affected (0.00 sec)mysql> set global read_only=on; Query OK, 0 rows affected (0.00 sec)

主庫使用mysqldump導出

可以同時導出多個數據庫，如music、record

mysqldump --databases <數據庫名> --single-transaction --order-by-primary -r <備份文件名> --routines -h<服務器地址> -P<端口號> -u<用戶名> -p<密碼> mysqldump --default-character-set=utf8mb4 --single-transaction --triggers --routines --events --hex-blob --databases muisc record > music_record.sql

記錄GTID_PURGED

grep -r "GLOBAL.GTID_PURGED" music_record.sql SET @@GLOBAL.GTID_PURGED='3cdb9ce6-0d7e-11e8-abe4-001517b5a5f0:1-698887';

將主庫設為可讀寫模式

數據庫導出完成后將主庫重新設為可讀寫模式。

mysql> set global read_only=off; mysql> unlock tables;

從庫數據導入

##mysql> create database `music`; ##mysql -u root -p muisc < /root/music.sql mysql -u root -p < /root/music_record.sql mysql> reset slave all; mysql> reset master; mysql> SET @@GLOBAL.GTID_PURGED='3cdb9ce6-0d7e-11e8-abe4-001517b5a5f0:1-698887';

從庫連接主庫

change master to master_host='192.168.11.31',master_user='slave',master_password='slave123',master_port=3306,master_auto_position=1;

從庫啟動復制線程

mysql> start slave;

從庫查看復制狀態

mysql> show slave status\G; *************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.11.31Master_User: slaveMaster_Port: 3306Connect_Retry: 60Master_Log_File: master-bin.000002Read_Master_Log_Pos: 149375983Relay_Log_File: db2-relay-bin.000002Relay_Log_Pos: 321Relay_Master_Log_File: master-bin.000002Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 149375983Relay_Log_Space: 526Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id: 31Master_UUID: 834449ff-4487-11e8-8b27-000c294b06caMaster_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set:Executed_Gtid_Set:Auto_Position: 0Replicate_Rewrite_DB:Channel_Name:Master_TLS_Version: 1 row in set (0.00 sec)ERROR: No query specified

檢查主從復制通信狀態

Slave_IO_State #從站的當前狀態

Slave_IO_Running：Yes #讀取主程序二進制日志的I/O線程是否正在運行

Slave_SQL_Running：Yes #執行讀取主服務器中二進制日志事件的SQL線程是否正在運行。與I/O線程一樣

Seconds_Behind_Master #是否為0，0就是已經同步了

如果再次查詢狀態仍然 發現Slave_IO_Running 或者Slave_SQL_Running 不同時為YES,嘗試執行

mysql> stop slave; mysql> reset slave; mysql> start slave;

主庫查看狀態

mysql> show master status; +-------------------+-----------+--------------+------------------+--------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------+-----------+--------------+------------------+--------------------------------------------+ | master-bin.000002 | 149375983 | | | 834449ff-4487-11e8-8b27-000c294b06ca:1-254 | +-------------------+-----------+--------------+------------------+--------------------------------------------+ 1 row in set (0.00 sec)mysql> show slave hosts; +-----------+------+------+-----------+--------------------------------------+ | Server_id | Host | Port | Master_id | Slave_UUID | +-----------+------+------+-----------+--------------------------------------+ | 32 | | 3306 | 31 | 68303133-4489-11e8-84e9-000c293eaee6 | +-----------+------+------+-----------+--------------------------------------+ 1 row in set (0.00 sec)mysql> show global variables like '%gtid%'; +----------------------------------+--------------------------------------------+ | Variable_name | Value | +----------------------------------+--------------------------------------------+ | binlog_gtid_simple_recovery | ON | | enforce_gtid_consistency | ON | | gtid_executed | 834449ff-4487-11e8-8b27-000c294b06ca:1-255 | | gtid_executed_compression_period | 1000 | | gtid_mode | ON | | gtid_owned | | | gtid_purged | | | session_track_gtids | OFF | +----------------------------------+--------------------------------------------+ 8 rows in set (0.00 sec)

其他命令

mysql> show binlog events; mysql> show binlog events in 'master-bin.000001'; mysql> show master logs; mysql> show processlist mysql> show full processlist;

想知道更多？掃描下面的二維碼關注我

后臺回復”加群“獲取公眾號專屬群聊入口

當當優惠碼福利來一波！當當全場自營圖書5折，用優惠碼：TASEMU（長按復制），滿200（原價400）再減30，相當于170=400，四折多一點。使用渠道：當當小程序或當當APP。使用時間：4/10-4/23。

【原創系列 | 精彩推薦】

• Paxos、Raft不是一致性算法嘛？

• 越說越迷糊的CAP

• 面試官居然問我Raft為什么會叫做Raft!

• 面試官給我挖坑：URI中的//有什么用

• 網關Zuul科普

• 網關Spring Cloud?Gateway科普

• 分布式事務科普——初識篇

• 分布式事務科普——終結篇

• 面試官給我挖坑：a[i][j]和a[j][i]有什么區別？
  

>>>?字節跳動社招內推入口?<<<

>>> 字節跳動校招內推入口 <<<

朕已閱?

總結

以上是生活随笔為你收集整理的MySQL 5.7基于GTID及多线程主从复制的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。