MySQL主從同步原理

MySQL主從同步是在MySQL主從復(fù)制(Master-Slave Replication)基礎(chǔ)上實(shí)現(xiàn)的，通過(guò)設(shè)置在Master MySQL上的binlog(使其處于打開(kāi)狀態(tài))，Slave MySQL上通過(guò)一個(gè)I/O線程從Master MySQL上讀取binlog，然后傳輸?shù)絊lave MySQL的中繼日志中，然后Slave MySQL的SQL線程從中繼日志中讀取中繼日志，然后應(yīng)用到Slave MySQL的數(shù)據(jù)庫(kù)中。這樣實(shí)現(xiàn)了主從數(shù)據(jù)同步功能。

MySQL中主從復(fù)制的優(yōu)點(diǎn)

• 橫向擴(kuò)展解決方案
  在多個(gè)從庫(kù)之間擴(kuò)展負(fù)載以提高性能。在這種環(huán)境中，所有寫(xiě)入和更新在主庫(kù)上進(jìn)行。但是，讀取可能發(fā)生在一個(gè)或多個(gè)從庫(kù)上。該模型可以提高寫(xiě)入的性能（由于主庫(kù)專用于更新），同時(shí)在多個(gè)從庫(kù)上讀取，可以大大提高讀取速度。
• 數(shù)據(jù)安全性
  由于主庫(kù)數(shù)據(jù)被復(fù)制到從庫(kù)，從庫(kù)可以暫停復(fù)制過(guò)程，可以在從庫(kù)上運(yùn)行備份服務(wù)，而不會(huì)破壞對(duì)應(yīng)的主庫(kù)數(shù)據(jù)。
• 分析
  可以在主庫(kù)上創(chuàng)建實(shí)時(shí)數(shù)據(jù)，而信息分析可以在從庫(kù)上進(jìn)行，而不會(huì)影響主服務(wù)器的性能。

Gtid概念

從 MySQL 5.6.5 開(kāi)始新增了一種基于 GTID 的復(fù)制方式。通過(guò) GTID保證了每個(gè)在主庫(kù)上提交的事務(wù)在集群中有一個(gè)唯一的ID。這種方式強(qiáng)化了數(shù)據(jù)庫(kù)的主備一致性，故障恢復(fù)以及容錯(cuò)能力。

在原來(lái)基于二進(jìn)制日志的復(fù)制中，從庫(kù)需要告知主庫(kù)要從哪個(gè)偏移量進(jìn)行增量同步，如果指定錯(cuò)誤會(huì)造成數(shù)據(jù)的遺漏，從而造成數(shù)據(jù)的不一致。借助GTID，在發(fā)生主備切換的情況下，MySQL的其它從庫(kù)可以自動(dòng)在新主庫(kù)上找到正確的復(fù)制位置，這大大簡(jiǎn)化了復(fù)雜復(fù)制拓?fù)湎录旱木S護(hù)，也減少了人為設(shè)置復(fù)制位置發(fā)生誤操作的風(fēng)險(xiǎn)。另外，基于GTID的復(fù)制可以忽略已經(jīng)執(zhí)行過(guò)的事務(wù)，減少了數(shù)據(jù)發(fā)生不一致的風(fēng)險(xiǎn)。

什么是Gitd

GTID (Global Transaction ID) 是對(duì)于一個(gè)已提交事務(wù)的編號(hào)，并且是一個(gè)全局唯一的編號(hào)。GTID 實(shí)際上 是由UUID+TID 組成的。其中 UUID 是一個(gè) MySQL 實(shí)例的唯一標(biāo)識(shí)。TID代表了該實(shí)例上已經(jīng)提交的事務(wù)數(shù)量，并且隨著事務(wù)提交單調(diào)遞增。
下面是一個(gè)GTID的具體形式：
3E11FA47-71CA-11E1-9E33-C80AA9429562:23，冒號(hào)分割前邊為uuid，后邊為TID。

GTID 集合可以包含來(lái)自多個(gè) MySQL 實(shí)例的事務(wù)，它們之間用逗號(hào)分隔。

如果來(lái)自同一MySQL實(shí)例的事務(wù)序號(hào)有多個(gè)范圍區(qū)間，各組范圍之間用冒號(hào)分隔。例如：
e6954592-8dba-11e6-af0e-fa163e1cf111:1-5:11-18,
e6954592-8dba-11e6-af0e-fa163e1cf3f2:1-27 可以使用show master status實(shí)時(shí)查看當(dāng)前事務(wù)執(zhí)行數(shù)

Gtid的作用

Gtid采用了新的復(fù)制協(xié)議，舊協(xié)議是，首先從服務(wù)器上在一個(gè)特定的偏移量位置連接到主服務(wù)器上一個(gè)給定的二進(jìn)制日志文件，然后主服務(wù)器再?gòu)慕o定的連接點(diǎn)開(kāi)始發(fā)送所有的事件。
新協(xié)議有所不同，支持以全局統(tǒng)一事務(wù)ID (GTID)為基礎(chǔ)的復(fù)制。當(dāng)在主庫(kù)上提交事務(wù)或者被從庫(kù)應(yīng)用時(shí)，可以定位和追蹤每一個(gè)事務(wù)。GTID復(fù)制是全部以事務(wù)為基礎(chǔ)，使得檢查主從一致性變得非常簡(jiǎn)單。如果所有主庫(kù)上提交的事務(wù)也同樣提交到從庫(kù)上，一致性就得到了保證。

Gtid的工作原理

①當(dāng)一個(gè)事務(wù)在主庫(kù)端執(zhí)行并提交時(shí)，產(chǎn)生GTID，一同記錄到binlog日志中。
②binlog傳輸?shù)絪lave,并存儲(chǔ)到slave的relaylog后，讀取這個(gè)GTID的這個(gè)值設(shè)置gtid_next變量，即告訴Slave，下一個(gè)要執(zhí)行的GTID值。
③sql線程從relay log中獲取GTID，然后對(duì)比slave端的binlog是否有該GTID。
④如果有記錄，說(shuō)明該GTID的事務(wù)已經(jīng)執(zhí)行，slave會(huì)忽略。
⑤如果沒(méi)有記錄，slave就會(huì)執(zhí)行該GTID事務(wù)，并記錄該GTID到自身的binlog，
在讀取執(zhí)行事務(wù)前會(huì)先檢查其他session持有該GTID，確保不被重復(fù)執(zhí)行。
⑥在解析過(guò)程中會(huì)判斷是否有主鍵，如果沒(méi)有就用二級(jí)索引，如果沒(méi)有就用全部掃描。

配置步驟

操作環(huán)境

1.系統(tǒng)：CentOS 7
2.數(shù)據(jù)庫(kù)：Percona MySQL 5.7
3.主庫(kù)：192.168.11.31
4.從庫(kù)：192.168.11.32

主庫(kù)配置

1.[mysqld]
2.datadir=/data/mysql/3306
3.socket=/tmp/mysql.sock
4.symbolic-links=0
5.
6.server_id=31                              #服務(wù)器ID
7.log-bin=master-bin                        #二進(jìn)制日志文件名
8.binlog_format = row                       #強(qiáng)烈建議，其他格式可能造成數(shù)據(jù)不一致
9.log-slave-updates = 1                     #是否記錄從服務(wù)器同步數(shù)據(jù)動(dòng)作
10.gtid-mode = on                            #啟用gitd功能
11.enforce-gtid-consistency = 1              #開(kāi)啟強(qiáng)制GTID一致性
12.master-info-repository = TABLE            #記錄IO線程讀取已經(jīng)讀取到的master binlog位置，用于slave宕機(jī)后IO線程根據(jù)文件中的POS點(diǎn)重新拉取binlog日志
13.relay-log-info-repository = TABLE         #記錄SQL線程讀取Master binlog的位置，用于Slave 宕機(jī)后根據(jù)文件中記錄的pos點(diǎn)恢復(fù)Sql線程
14.sync-master-info = 1                      #啟用確保無(wú)信息丟失；任何一個(gè)事務(wù)提交后, 將二進(jìn)制日志的文件名及事件位置記錄到文件中
15.slave-parallel-workers = 2                #設(shè)定從服務(wù)器的復(fù)制線程數(shù)；0表示關(guān)閉多線程復(fù)制功能
16.binlog-checksum = CRC32                   #設(shè)置binlog校驗(yàn)算法（循環(huán)冗余校驗(yàn)碼）
17.master-verify-checksum = 1                #設(shè)置主服務(wù)器是否校驗(yàn)
18.slave-sql-verify-checksum = 1             #設(shè)置從服務(wù)器是否校驗(yàn)
19.binlog-rows-query-log_events = 1          #用于在二進(jìn)制日志記錄事件相關(guān)的信息，可降低故障排除的復(fù)雜度
20.sync_binlog = 1                           #保證master crash safe，該參數(shù)必須設(shè)置為1
21.innodb_flush_log_at_trx_commit = 1        #保證master crash safe，該參數(shù)必須設(shè)置為1

從庫(kù)配置

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版本需要開(kāi)啟
18.relay_log_recovery = 1  # crash safe slave
19.read_only=on        #設(shè)置一般用戶為只讀模式
20.super_read_only=on       #設(shè)置super（root）用戶為只讀模式
21.#tx_read_only=on     #設(shè)置事務(wù)為只讀模式

主庫(kù)權(quán)限設(shè)置

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

自動(dòng)同步連接主庫(kù)(方法一)

適用于master也是新建不久的情況。
1、如果你的master所有的binlog還在。可以安裝slave，slave直接change master to到master端。
2、原理是直接獲取master所有的GTID并執(zhí)行。
3、優(yōu)點(diǎn)：簡(jiǎn)單方便。
4、缺點(diǎn)：如果binlog太多，數(shù)據(jù)完全同步需要時(shí)間較長(zhǎng)，并且master一開(kāi)始就啟用了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 從庫(kù)自動(dòng)找同步點(diǎn)

備份導(dǎo)入連接主庫(kù)(方法二)

1、Xtrabackup_binlog_info文件中，包含global.gtid_purged=’XXXXXX:XXXX’的信息。
2、然后到slave去手工的 SET @@GLOBAL.GTID_PURGED=’XXXXXX:XXXX’。
3、恢復(fù)備份，開(kāi)啟change master to 命令。

備份導(dǎo)入連接主庫(kù)(方法三)

適用于擁有較大數(shù)據(jù)的情況。（推薦）
1、通過(guò)master或者其他slave的備份搭建新的slave。
2、原理：獲取master的數(shù)據(jù)和這些數(shù)據(jù)對(duì)應(yīng)的GTID范圍，然后通過(guò)slave設(shè)置master_auto_position=1,自動(dòng)同步，跳過(guò)備份包含的gtid。
3、缺點(diǎn)：相對(duì)來(lái)說(shuō)有點(diǎn)復(fù)雜。

將主庫(kù)設(shè)為只讀模式

注：生產(chǎn)環(huán)境會(huì)影響不能寫(xiě)入數(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)

主庫(kù)使用mysqldump導(dǎo)出

可以同時(shí)導(dǎo)出多個(gè)數(shù)據(jù)庫(kù)，如music、record

1.mysqldump --databases <數(shù)據(jù)庫(kù)名>  --single-transaction --order-by-primary -r <備份文件名> --routines -h<服務(wù)器地址>  -P<端口號(hào)> -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';

將主庫(kù)設(shè)為可讀寫(xiě)模式

數(shù)據(jù)庫(kù)導(dǎo)出完成后將主庫(kù)重新設(shè)為可讀寫(xiě)模式。

1.mysql> set global read_only=off;
2.mysql> unlock tables;

從庫(kù)數(shù)據(jù)導(dǎo)入

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';

從庫(kù)連接主庫(kù)

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

從庫(kù)啟動(dòng)復(fù)制線程

1.mysql> start slave;

從庫(kù)查看復(fù)制狀態(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

檢查主從復(fù)制通信狀態(tài)

Slave_IO_State #從站的當(dāng)前狀態(tài)
Slave_IO_Running：Yes #讀取主程序二進(jìn)制日志的I/O線程是否正在運(yùn)行
Slave_SQL_Running：Yes #執(zhí)行讀取主服務(wù)器中二進(jìn)制日志事件的SQL線程是否正在運(yùn)行。與I/O線程一樣
Seconds_Behind_Master #是否為0，0就是已經(jīng)同步了

如果再次查詢狀態(tài)仍然 發(fā)現(xiàn)Slave_IO_Running 或者Slave_SQL_Running 不同時(shí)為YES,嘗試執(zhí)行

1.mysql> stop slave;
2.mysql> reset slave;
3.mysql> start slave;

主庫(kù)查看狀態(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;
好啦！今天的分享到這里就結(jié)束了，希望大家持續(xù)關(guān)注馬哥教育官網(wǎng)，每天都會(huì)有大量?jī)?yōu)質(zhì)內(nèi)容與大家分享！聲明：文章源自網(wǎng)絡(luò)，版權(quán)歸原作者所有！