MySQL複製原理

標籤：status   原理   exe   執行   中繼日誌   請求   pos   dump   常見   

mysql從3.23開始提供複製功能，複製指將主庫的ddl和dml操作通過binlog檔案傳送到從庫上執行，從而保持主庫和從庫資料同步。mysql支援一台主庫同時向多台從庫複製，從庫同時也可以作為其他從庫的主庫，從而實現級聯複製功能。mysql複製功能相當於oracle資料庫的邏輯dg功能。

mysql複製原理大致如下：

1）mysql主庫事務提交時會把資料變更作為event記錄在binlog檔案中，mysql主庫的sync_binlog參數控制binlog日誌重新整理到磁碟。

2）主庫推送binlog中的event到從庫的中繼日誌relay log中，之後從庫根據中繼日誌重做資料變更操作，通過邏輯複製來達到與主庫同步的目的。

mysql通過3個線程完成複製功能：其中binlog dump線程跑在主庫上，io線程和sql線程跑在從庫上。當從庫啟動複製（start slave）時，首先建立io線程串連主庫，主庫隨後建立binlog dump線程讀取event發送給io線程，io線程擷取到event後更新到從庫中繼日誌relay log中，之後從庫sql線程根據relay log內容在從庫從做操作。從而完成mysql複製功能。mysql複製結構

主庫通過show processlist命令可以看到binlog dump進程，如下：

mysql> show processlist \G;

*************************** 1. row ***************************

     Id: 125

   User: root

   Host: localhost

     db: test

Command: Query

   Time: 0

  State: init

   Info: show processlist

*************************** 2. row ***************************

     Id: 127

   User: rep1

   Host: 192.168.80.136:44889

     db: NULL

Command: Binlog Dump

   Time: 16991

  State: Master has sent all binlog to slave; waiting for binlog to be updated

   Info: NULL

2 rows in set (0.00 sec)

ERROR:

No query specified

mysql>

從庫通過show processlist命令可以看到io線程和sql線程，如下：

mysql> show processlist \G;

*************************** 1. row ***************************

     Id: 4

   User: root

   Host: localhost

     db: test

Command: Query

   Time: 0

  State: init

   Info: show processlist

*************************** 2. row ***************************

     Id: 7

   User: system user

   Host:

     db: NULL

Command: Connect

   Time: 17079

  State: Waiting for master to send event

   Info: NULL

*************************** 3. row ***************************

     Id: 8

   User: system user

   Host:

     db: NULL

Command: Connect

   Time: 17107

  State: Slave has read all relay log; waiting for the slave I/O thread to update it

   Info: NULL

3 rows in set (0.00 sec)

ERROR:

No query specified

mysql複製涉及到兩種檔案：binlog和relay log檔案。其中根據binlog的不同設定，mysql複製分為3中模式。

複製的3中常見架構：一主多從、級聯複製、雙主架構

1）mysql一主多從架構：解決主庫讀請求壓力解決方案。

2）mysql級聯架構：解決一主多從架構中主庫io和網路壓力，缺點時額外增加了主庫到從庫的應用延遲，其中master2可以設定為blackhold（黑洞）模式來緩解複寫延遲。

3）雙主複製/dual master架構：

4）雙主級聯複製架構：

通過show master status查看主庫狀態：

mysql> show master status \G;

*************************** 1. row ***************************

             File: dbking-bin.000004

         Position: 1729

     Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set:

1 row in set (0.00 sec)

ERROR:

No query specified

通過show slave status查看從庫狀態：

mysql> show slave status \G;

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.80.133

                  Master_User: rep1

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: dbking-bin.000004

          Read_Master_Log_Pos: 1729

               Relay_Log_File: chavinking-relay-bin.000010

                Relay_Log_Pos: 714

        Relay_Master_Log_File: dbking-bin.000004

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: 1729

              Relay_Log_Space: 1056

              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: 1

                  Master_UUID: 9b92b2a8-b7e0-11e6-81e4-000c29fa5a95

             Master_Info_File: /usr/local/software/mysql-5.6.24-linux-glibc2.5-x86_64/data/master.info

                    SQL_Delay: 0

          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

           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

1 row in set (0.00 sec)

MySQL複製原理