MySQL主從複製原理深入講解

標籤：線程   預設   ast   def   導致   一主多從   原來   pat   RoCE   

利用MySQL自身提供的主從複製技術，在企業生產情境中，可以很好的對數資料進行多處自動備份，並且實現資料庫的擴充。比如：在做定時備份時，備份的過程可能需要鎖表操作，在備份鎖表期間，使用者無法訪問資料，雖然可以選擇在業務低穀期進行備份，但是多少都會有影響，這時可以通過主從複製的從庫進行鎖表備份。在主從複製的基礎上通過讀寫分離技術還能提升資料庫的負載效能（主庫寫，從庫讀）。
主從複製模型

一主一從
一主多從
雙主
線性級聯
環狀級聯
這次用來講解的是一主一從模型
主從複製原理圖

主從複製過程存在三個線程，Master端的I/O線程，Slave的I/O線程與SQL線程。Master端需要開啟binlog日誌，Slave端需要開啟relay日誌。
1、Slave端的I/O讀取master.info檔案，擷取binlog檔案名稱和位置點，然後向Master端的I/O線程請求，該binlog檔案名稱和位置點的binlog資訊。
（master.info檔案在配置主從複製時使用change master命令來指定產生）
2、Master端的I/O線程會根據Slave端的I/O線程請求的資訊來讀取Master的binlog日誌資訊與及讀取到最新的binlog檔案名稱和位置點一同返回給Slave的I/O線程。
3、Slave端的I/O線程會把擷取到的binlog日誌寫入relay日誌（中繼日誌）檔案中，並且更新master.info檔案資訊。（把讀取到Master最新的binlog記錄檔名和位置點更新到master.info檔案中，下一次當前位置去讀取Master的binlog日誌）
4、Slave端的SQL線程會定期讀取relay日誌，把二進位的日誌解析成SQL語句，並執行這些SQL語句，同步資料到從庫中。
主從複製實戰配置

配置小結
Master端
1、同步Master端的未經處理資料到所有Slave端
2、開啟binlog日誌，保持server-id唯一
3、配置Slave驗證授權使用者，許可權replication slave
Slave端
1、執行change master語句，產生master.info檔案
2、開啟relay日誌，保持server-id唯一
3、啟動Slave複製（start slave）

Master端全備資料庫同步到Slave端
在開始做主從複製之前，需要把Master原有的資料都先同步到所有的Slave，否則在做同步複製之時，因為原有資料不一致導致同步失敗。（注意，如果使用原來備份時間點比如昨天淩晨的全備資料同步所有Slave資料時，還需要把目前時間點之前的所有binlog增量備份同步，使在截取主從複製時間點時，Master和所有Slave的資料保持一致）

原有資料不一致導致主從複製失敗

mysql> show slave status\GLast_Error: Error ‘Table ‘ricky.test‘ doesn‘t exist‘ on query. Default database: ‘ricky‘. Query: ‘insert into test values(987654,9,‘gogo‘,30)‘

並且全備時，要鎖表備份，並匯入所有Slave，保證截取的時間點資料一致，同時重新整理binlog日誌。

[[email protected] ~]# mysqldump -uroot -p123456 -S /data/3306/mysql.sock -A -B -R --flush-logs --lock-all-tables --events >/tmp/mysql_bak_$(date +%F).sql[[email protected] ~]# mysql -uroot -p123456 -S /data/3307/mysql.sock </tmp/mysql_bak_2018-07-12.sql[[email protected] ~]# mysql -uroot -p123456 -S /data/3307/mysql.sockmysql> show databases;

Master端配置

Master端開啟binlog日誌功能

[[email protected] ~]# egrep "log-bin|server-id" /data/3306/my.cnf log-bin = /data/3306/mysql-binserver-id = 1

Master配置slave複製授權使用者

#許可權replication slavemysql> GRANT REPLICATION SLAVE ON *.* TO ‘mysql52‘@‘172.16.1.52‘ IDENTIFIED BY ‘123456‘;mysql> flush privileges;

檢查Master端當前binlog記錄檔名和位置點
當前binlog檔案名稱和位置點，在配置Slave端時，需要告訴Slave端

mysql> show master status\G*************************** 1. row ***************************            File: mysql-bin.000004        Position: 337    Binlog_Do_DB: Binlog_Ignore_DB: 1 row in set (0.00 sec)

Slave端配置

Slave端開啟relaylog日誌功能

[[email protected] ~]# egrep "relay-bin|server-id" /data/3307/my.cnf       relay-log = /data/3307/relay-binserver-id = 3

告訴Slave端主從複製時串連的Master（ip+連接埠【預設可以不需要】）、授權串連使用者和密碼、binlog檔案名稱和位置點。這裡的binlog檔案名稱和位置點還有另一種解決辦法，在mysqldump全備時加入--master-data參數，把change master【1，不注釋；2，注釋】語句嵌入到備份檔案中（mysqldump邏輯備份，以SQL語句形式匯出資料），把備份檔案複製到Slave端匯入時，如果參數選項為“1”，則會自動執行change master語句，把binlog檔案名稱和位置點寫入Slave端的master.info檔案中。其它的master端資訊、授權使用者和密碼則需要手動change master寫入master.info檔案。

執行change master語句

mysql> CHANGE MASTER TO MASTER_HOST=‘172.16.1.52‘,      -> MASTER_PORT=3306,    -> MASTER_USER=‘mysql52‘,    -> MASTER_PASSWORD=‘123456‘,    -> MASTER_LOG_FILE=‘mysql-bin.000004‘,    -> MASTER_LOG_POS=337;

啟動Slave

mysql> start slave;

查看Slave端狀態

mysql> show slave status\G*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 172.16.1.52                  Master_User: mysql52                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-bin.000004          Read_Master_Log_Pos: 337               Relay_Log_File: relay-bin.000002                Relay_Log_Pos: 253        Relay_Master_Log_File: mysql-bin.000004             Slave_IO_Running: Yes            Slave_SQL_Running: Yes              Replicate_Do_DB:           Replicate_Ignore_DB: mysql           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: 337              Relay_Log_Space: 403              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: 0Master_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: 11 row in set (0.00 sec)

測試主從複製功能
Master端向test表中插入一條資料

mysql> insert into test values(987654,9,‘gogo‘,30);

Slave端對應的test會同步了該條資料

mysql> select * from ricky.test;

MySQL主從複製原理深入講解