MySQL 5.7基于GTID及多線程主從復制
MySQL主從同步原理
MySQL主從同步是在MySQL主從復制(Master-Slave Replication)基礎上實現(xiàn)的，通過設置在Master MySQL上的binlog(使其處于打開狀態(tài))，Slave MySQL上通過一個I/O線程從Master MySQL上讀取binlog，然后傳輸?shù)絊lave MySQL的中繼日志中，然后Slave MySQL的SQL線程從中繼日志中讀取中繼日志，然后應用到Slave MySQL的數(shù)據(jù)庫中。這樣實現(xiàn)了主從數(shù)據(jù)同步功能。
MySQL中主從復制的優(yōu)點
- 橫向擴展解決方案
在多個從庫之間擴展負載以提高性能。在這種環(huán)境中，所有寫入和更新在主庫上進行。但是，讀取可能發(fā)生在一個或多個從庫上。該模型可以提高寫入的性能（由于主庫專用于更新），同時在多個從庫上讀取，可以大大提高讀取速度。 - 數(shù)據(jù)安全性
由于主庫數(shù)據(jù)被復制到從庫，從庫可以暫停復制過程，可以在從庫上運行備份服務，而不會破壞對應的主庫數(shù)據(jù)。 - 分析
可以在主庫上創(chuàng)建實時數(shù)據(jù)，而信息分析可以在從庫上進行，而不會影響主服務器的性能。
Gtid概念
從 MySQL 5.6.5 開始新增了一種基于 GTID 的復制方式。通過 GTID保證了每個在主庫上提交的事務在集群中有一個唯一的ID。這種方式強化了數(shù)據(jù)庫的主備一致性，故障恢復以及容錯能力。
在原來基于二進制日志的復制中，從庫需要告知主庫要從哪個偏移量進行增量同步，如果指定錯誤會造成數(shù)據(jù)的遺漏，從而造成數(shù)據(jù)的不一致。借助GTID，在發(fā)生主備切換的情況下，MySQL的其它從庫可以自動在新主庫上找到正確的復制位置，這大大簡化了復雜復制拓撲下集群的維護，也減少了人為設置復制位置發(fā)生誤操作的風險。另外，基于GTID的復制可以忽略已經(jīng)執(zhí)行過的事務，減少了數(shù)據(jù)發(fā)生不一致的風險。
什么是Gitd
GTID (Global Transaction ID) 是對于一個已提交事務的編號，并且是一個全局唯一的編號。GTID 實際上 是由UUID+TID 組成的。其中 UUID 是一個 MySQL 實例的唯一標識。TID代表了該實例上已經(jīng)提交的事務數(shù)量，并且隨著事務提交單調(diào)遞增。
下面是一個GTID的具體形式：
3E11FA47-71CA-11E1-9E33-C80AA9429562:23，冒號分割前邊為uuid，后邊為TID。
GTID 集合可以包含來自多個 MySQL 實例的事務，它們之間用逗號分隔。
如果來自同一MySQL實例的事務序號有多個范圍區(qū)間，各組范圍之間用冒號分隔。例如：
e6954592-8dba-11e6-af0e-fa163e1cf111:1-5:11-18,
e6954592-8dba-11e6-af0e-fa163e1cf3f2:1-27 可以使用show master status實時查看當前事務執(zhí)行數(shù)
Gtid的作用
Gtid采用了新的復制協(xié)議，舊協(xié)議是，首先從服務器上在一個特定的偏移量位置連接到主服務器上一個給定的二進制日志文件，然后主服務器再從給定的連接點開始發(fā)送所有的事件。
新協(xié)議有所不同，支持以全局統(tǒng)一事務ID (GTID)為基礎的復制。當在主庫上提交事務或者被從庫應用時，可以定位和追蹤每一個事務。GTID復制是全部以事務為基礎，使得檢查主從一致性變得非常簡單。如果所有主庫上提交的事務也同樣提交到從庫上，一致性就得到了保證。
Gtid的工作原理
①當一個事務在主庫端執(zhí)行并提交時，產(chǎn)生GTID，一同記錄到binlog日志中。
②binlog傳輸?shù)絪lave,并存儲到slave的relaylog后，讀取這個GTID的這個值設置gtid_next變量，即告訴Slave，下一個要執(zhí)行的GTID值。
③sql線程從relay log中獲取GTID，然后對比slave端的binlog是否有該GTID。
④如果有記錄，說明該GTID的事務已經(jīng)執(zhí)行，slave會忽略。
⑤如果沒有記錄，slave就會執(zhí)行該GTID事務，并記錄該GTID到自身的binlog，
在讀取執(zhí)行事務前會先檢查其他session持有該GTID，確保不被重復執(zhí)行。
⑥在解析過程中會判斷是否有主鍵，如果沒有就用二級索引，如果沒有就用全部掃描。
配置步驟
操作環(huán)境
1.系統(tǒng)：CentOS 7
2.數(shù)據(jù)庫：Percona MySQL 5.7
3.主庫：192.168.11.31
4.從庫：192.168.11.32主庫配置
1.[mysqld]
2.datadir=/data/mysql/3306
3.socket=/tmp/mysql.sock
4.symbolic-links=0
5.
6.server_id=31 #服務器ID
7.log-bin=master-bin #二進制日志文件名
8.binlog_format = row #強烈建議，其他格式可能造成數(shù)據(jù)不一致
9.log-slave-updates = 1 #是否記錄從服務器同步數(shù)據(jù)動作
10.gtid-mode = on #啟用gitd功能
11.enforce-gtid-consistency = 1 #開啟強制GTID一致性
12.master-info-repository = TABLE #記錄IO線程讀取已經(jīng)讀取到的master binlog位置，用于slave宕機后IO線程根據(jù)文件中的POS點重新拉取binlog日志
13.relay-log-info-repository = TABLE #記錄SQL線程讀取Master binlog的位置，用于Slave 宕機后根據(jù)文件中記錄的pos點恢復Sql線程
14.sync-master-info = 1 #啟用確保無信息丟失；任何一個事務提交后, 將二進制日志的文件名及事件位置記錄到文件中
15.slave-parallel-workers = 2 #設定從服務器的復制線程數(shù)；0表示關閉多線程復制功能
16.binlog-checksum = CRC32 #設置binlog校驗算法（循環(huán)冗余校驗碼）
17.master-verify-checksum = 1 #設置主服務器是否校驗
18.slave-sql-verify-checksum = 1 #設置從服務器是否校驗
19.binlog-rows-query-log_events = 1 #用于在二進制日志記錄事件相關的信息，可降低故障排除的復雜度
20.sync_binlog = 1 #保證master crash safe，該參數(shù)必須設置為1
21.innodb_flush_log_at_trx_commit = 1 #保證master crash safe，該參數(shù)必須設置為1從庫配置
1.[mysqld]
2.server_id = 32
3.log-bin=mysql-bin
4.binlog_format = row
5.gtid-mode = on
6.enforce-gtid-consistency = 1
7.master-info-repository = TABLE
8.relay-log-info-repository = TABLE
9.sync-master-info = 1
10.slave-parallel-workers = 4
11.binlog-checksum = CRC32
12.master-verify-checksum = 1
13.slave-sql-verify-checksum = 1
14.binlog-rows-query-log_events = 1
15.#sync_binlog = 1
16.#innodb_flush_log_at_trx_commit = 1
17.log-slave-updates = 0 # crash safe slave 5.6版本需要開啟
18.relay_log_recovery = 1 # crash safe slave
19.read_only=on #設置一般用戶為只讀模式
20.super_read_only=on #設置super（root）用戶為只讀模式
21.#tx_read_only=on #設置事務為只讀模式主庫權限設置
1.mysql > grant replication slave on *.* to slave@'192.168.11.32' identified by 'slave123';
2.mysql > flush privileges;自動同步連接主庫(方法一)
適用于master也是新建不久的情況。
1、如果你的master所有的binlog還在?？梢园惭bslave，slave直接change master to到master端。
2、原理是直接獲取master所有的GTID并執(zhí)行。
3、優(yōu)點：簡單方便。
4、缺點：如果binlog太多，數(shù)據(jù)完全同步需要時間較長，并且master一開始就啟用了GTUD。
1.change master to master_host='192.168.11.31',\
2.master_user='slave',master_password='slave123',\
master_port=3306,master_auto_position=1
#master_auto_position=1 從庫自動找同步點備份導入連接主庫(方法二)
1、Xtrabackup_binlog_info文件中，包含global.gtid_purged=’XXXXXX:XXXX’的信息。
2、然后到slave去手工的 SET @@GLOBAL.GTID_PURGED=’XXXXXX:XXXX’。
3、恢復備份，開啟change master to 命令。
備份導入連接主庫(方法三)
適用于擁有較大數(shù)據(jù)的情況。（推薦）
1、通過master或者其他slave的備份搭建新的slave。
2、原理：獲取master的數(shù)據(jù)和這些數(shù)據(jù)對應的GTID范圍，然后通過slave設置master_auto_position=1,自動同步，跳過備份包含的gtid。
3、缺點：相對來說有點復雜。
將主庫設為只讀模式
注：生產(chǎn)環(huán)境會影響不能寫入數(shù)據(jù)
1.mysql> flush tables with read lock;
2.Query OK, 0 rows affected (0.00 sec)
3.
4.mysql> set global read_only=on;
5.Query OK, 0 rows affected (0.00 sec)主庫使用mysqldump導出
可以同時導出多個數(shù)據(jù)庫，如music、record
1.mysqldump --databases <數(shù)據(jù)庫名> --single-transaction --order-by-primary -r <備份文件名> --routines -h<服務器地址> -P<端口號> -u<用戶名> -p<密碼>
2.mysqldump --default-character-set=utf8mb4 --single-transaction --triggers --routines --events --hex-blob --databases muisc record > music_record.sql記錄GTID_PURGED
1.grep -r "GLOBAL.GTID_PURGED" music_record.sql
2.SET @@GLOBAL.GTID_PURGED='3cdb9ce6-0d7e-11e8-abe4-001517b5a5f0:1-698887';將主庫設為可讀寫模式
數(shù)據(jù)庫導出完成后將主庫重新設為可讀寫模式。
1.mysql> set global read_only=off;
2.mysql> unlock tables;從庫數(shù)據(jù)導入
1.##mysql> create database `music`;
2.##mysql -u root -p muisc < /root/music.sql
3.mysql -u root -p < /root/music_record.sql
4.mysql> reset slave all;
5.mysql> reset master;
6.mysql> SET @@GLOBAL.GTID_PURGED='3cdb9ce6-0d7e-11e8-abe4-001517b5a5f0:1-698887';從庫連接主庫
1.change master to master_host='192.168.11.31',master_user='slave',master_password='slave123',master_port=3306,master_auto_position=1;從庫啟動復制線程
1.mysql> start slave;從庫查看復制狀態(tài)
1.mysql> show slave status\G;
2.*************************** 1. row ***************************
3. Slave_IO_State: Waiting for master to send event
4. Master_Host: 192.168.11.31
5. Master_User: slave
6. Master_Port: 3306
7. Connect_Retry: 60
8. Master_Log_File: master-bin.000002
9. Read_Master_Log_Pos: 14937598310. Relay_Log_File: db2-relay-bin.000002
Relay_Log_Pos: 321
Relay_Master_Log_File: master-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 149375983
Relay_Log_Space: 526
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_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: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 31
Master_UUID: 834449ff-4487-11e8-8b27-000c294b06ca
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified檢查主從復制通信狀態(tài)
Slave_IO_State #從站的當前狀態(tài)
Slave_IO_Running：Yes #讀取主程序二進制日志的I/O線程是否正在運行
Slave_SQL_Running：Yes #執(zhí)行讀取主服務器中二進制日志事件的SQL線程是否正在運行。與I/O線程一樣
Seconds_Behind_Master #是否為0，0就是已經(jīng)同步了
如果再次查詢狀態(tài)仍然 發(fā)現(xiàn)Slave_IO_Running 或者Slave_SQL_Running 不同時為YES,嘗試執(zhí)行
1.mysql> stop slave;
2.mysql> reset slave;
3.mysql> start slave;主庫查看狀態(tài)
1.mysql> show master status;
2.+-------------------+-----------+--------------+------------------+--------------------------------------------+
3.| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
4.+-------------------+-----------+--------------+------------------+--------------------------------------------+
5.| master-bin.000002 | 149375983 | | | 834449ff-4487-11e8-8b27-000c294b06ca:1-254 |
6.+-------------------+-----------+--------------+------------------+--------------------------------------------+
7.1 row in set (0.00 sec)
8.
9.mysql> show slave hosts;
10.+-----------+------+------+-----------+--------------------------------------+
11.| Server_id | Host | Port | Master_id | Slave_UUID |
12.+-----------+------+------+-----------+--------------------------------------+
13.| 32 | | 3306 | 31 | 68303133-4489-11e8-84e9-000c293eaee6 |
14.+-----------+------+------+-----------+--------------------------------------+
15.1 row in set (0.00 sec)
16.
17.mysql> show global variables like '%gtid%';
18.+----------------------------------+--------------------------------------------+
19.| Variable_name | Value |
20.+----------------------------------+--------------------------------------------+
21.| binlog_gtid_simple_recovery | ON |
22.| enforce_gtid_consistency | ON |
23.| gtid_executed | 834449ff-4487-11e8-8b27-000c294b06ca:1-255 |
24.| gtid_executed_compression_period | 1000 |
25.| gtid_mode | ON |
26.| gtid_owned | |
27.| gtid_purged | |
28.| session_track_gtids | OFF |
29.+----------------------------------+--------------------------------------------+
30.8 rows in set (0.00 sec)其他命令
1.mysql> show binlog events;
2.mysql> show binlog events in 'master-bin.000001';
3.mysql> show master logs;
4.mysql> show processlist
5.mysql> show full processlist;
好啦！今天的分享到這里就結束了，希望大家持續(xù)關注馬哥教育官網(wǎng)，每天都會有大量優(yōu)質(zhì)內(nèi)容與大家分享！聲明：文章源自網(wǎng)絡，版權歸原作者所有！