MySQL主从复制原理和参数

约定不等于承诺〃 2023-07-22 15:27 47阅读 0赞

**@MySQL复制（replication）原理**  
1）主服务器（master）把数据更改记录到二进制日志（binlog）中。  
2）从服务器（slave）把主服务器的二进制日志复制到自己的中继日志（relay log）中。  
3）从服务器重做中继日志中的日志，把更改应用到自己的数据库上，以达到数据的最终一致性。

注意:复制不是完全实时地进行同步，而是异步实时。

**@从服务器有2个线程**  
I/O线程，负责读取主服务器的二进制日志，并将其保存为中继日志；  
SQL线程，复制执行中继日志。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d5X2hoeHg_size_16_color_FFFFFF_t_70][]

**@SHOW FULL PROCESSLIST**  
（1）查看从服务器状态，可以看到类似如下内容：

mysql>SHOW FULL PROCESSLIST\G;
    *************************** 1. row ***************************
          Id: 1
        User: system user
        Host:
          db: NULL
     Command: Connect
        Time: 6501
       State: Waiting for master to send event
        Info: NULL
    *************************** 2. row ***************************
          Id: 2
        User: system user
        Host:
          db: NULL
     Command: Connect
        Time: 0
       State: Has read all relay log; waiting for the slave I/O thread to update it
        Info: NULL
    *************************** 3. row ***************************
          Id: 206
        User: root
        Host: localhost
          db: NULL
     Command: Query
        Time: 0
       State: NULL
        Info: SHOW FULL PROCESSLIST
    3 rows in set (0.00 sec)

说明：ID为1的线程是I/O线程，目前的状态是等待主服务器发送二进制日志。ID为2的线程是SQL线程，负责读取中继日志并执行。目前的状态是已读取所有的中继日志，等待中继日志被I/O线程更新。

（2）查看主服务器状态，可以看到类似如下内容：

mysql>SHOW FULL PROCESSLIST\G;
    ……
    *************************** 65. row ***************************
          Id: 26541
        User: rep
        Host: 192.168.190.98:39549
          db: NULL
     Command: Binlog Dump
        Time: 6857
       State: Has sent all binlog to slave; waiting for binlog to be updated
        Info: NULL
    ……

说明：可以看到一个线程负责发送二进制日志。

**@SHOW SLAVE STATUS和SHOW MASTER STATUS**

（1）SHOW SLAVE STATUS命令可以观察当前复制的运行状态：

mysql>SHOW SLAVE STATUS\G;
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.190.10
                      Master_User: rep
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000007
              Read_Master_Log_Pos: 555176471
                   Relay_Log_File: gamedb-relay-bin.000048
                    Relay_Log_Pos: 224355889
            Relay_Master_Log_File: mysql-bin.000007
                 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: mysql.%,DBA.%
                       Last_Errno: 0
                       Last_Error:
                     Skip_Counter: 0
              Exec_Master_Log_Pos: 555176471
                  Relay_Log_Space: 224356045
                  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:
    1 row in set (0.00 sec)

一些主要参数说明如下，

<table> 
 <tbody> 
  <tr> 
   <td style="width:338px;">Slave_IO_State: Waiting for master to send event</td> 
   <td style="width:532px;">显示当前IO线程的状态，如等待主服务器发送二进制日志</td> 
  </tr> 
  <tr> 
   <td style="width:338px;">Master_Log_File: mysql-bin.000007</td> 
   <td style="width:532px;">显示当前同步的主服务器的二进制日志</td> 
  </tr> 
  <tr> 
   <td style="width:338px;">Read_Master_Log_Pos: 555176471</td> 
   <td style="width:532px;">显示当前同步的主服务器的二进制日志的偏移量位置，单位是字节。如当前已同步mysql-bin.000007这个日志文件的529MB&nbsp; (555176471M）内容</td> 
  </tr> 
  <tr> 
   <td style="width:338px;">Relay_Master_Log_File: mysql-bin.000007</td> 
   <td style="width:532px;">当前中继日志同步的二进制日志</td> 
  </tr> 
  <tr> 
   <td style="width:338px;">Relay_Log_File: gamedb-relay-bin.000048</td> 
   <td style="width:532px;">显示当前写入的中继日志</td> 
  </tr> 
  <tr> 
   <td style="width:338px;">Relay_Log_Pos: 224355889</td> 
   <td style="width:532px;">显示当前执行到中继日志的偏移位置</td> 
  </tr> 
  <tr> 
   <td style="width:338px;">Slave_IO_Running: Yes</td> 
   <td style="width:532px;">从服务器中IO线程的运行状态，YES表示运行正常</td> 
  </tr> 
  <tr> 
   <td style="width:338px;">Slave_SQL_Running: Yes</td> 
   <td style="width:532px;">从服务器中SQL线程的运行状态，YES表示运行正常</td> 
  </tr> 
  <tr> 
   <td style="width:338px;">Exec_Master_Log_Pos: 555176471</td> 
   <td style="width:532px;">表示同步到主服务器的二进制日志偏移量的位置。<strong>（Read_Master_Log_Pos-Exec_Master_Log_Pos）可以表示当前SQL线程运行的延时，单位是字节。</strong>该例主从服务器完全同步</td> 
  </tr> 
 </tbody> 
</table>

（2）SHOW MASTER STATUS命令可以用来查看主服务器中二进制日志的状态：

mysql>SHOW MASTER STATUS\G;
    *************************** 1. row ***************************
                File: mysql-bin.000007
            Position: 606181078
        Binlog_Do_DB:
    Binlog_Ignore_DB:
    1 row in set (0.01 sec)

当前二进制日志记录了偏移量606181078的位置，**该值减去这一时间点时从服务器上的Read\_Master\_Log\_Pos，就可以得知I/O线程的延时**。

参考资料：《MySQL技术内幕：InnoDB存储引擎(第2版)》 姜承尧，章节8.7 复制

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d5X2hoeHg_size_16_color_FFFFFF_t_70]: /images/20230528/4eb25fa83eef4fd5b50d75660453a900.png