MySQL主从同步原理 & 主从状态命令解析

一 MySQL主从同步

1.1、 MySQL主从同步原理图

主从服务器上线程分布情况：

MySQL服务器角色	线程分布情况
主服务器（Master）	IO dump 线程
从服务器（Slaver）	① IO 同步线程； ②SQL重做进程

1.2、主从同步步骤

1. master将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log
   events）；
2. slave将master的binary log events拷贝到它的中继日志(relay log)；
3. slave重做中继日志中的事件，同步数据.

1.3 主从同步的延迟原因

1. 首先就是主库可以并发写入，从库只能通过单sql thread完成任务（MySQL5.7之前）
2. MySQL主从之间的同步，本来就不是时时同步的，是异步的同步，也就是说，主库提交事务之后，从库才再来执行一遍。
3. 在主库上的不合理操作：如对无索引的大表的某列进行delete或者update等触发大量数据操作的行为；
4. 从库的硬件配置， 相比主库要差，忽略了从库的重要性；
5. 各类网络问题：网络抖动， 网络不通。

1.4 解决方案

1. mysql5.7版本可以使用并行复制；
2. 采用MySQL高可用PXC架构方案；
3. 业务初期合理的规划和分库分表。业务层也可以尽量避免先写再读；
4. 增加硬件，适当调整缓冲池（buffer pool） 的大小，如 优化Innodb数据库参数：

• A) innodb_buffer_pool_size

• 配置缓冲池的大小，在内存允许的情况下，DBA往往会建议调大这个参数，越多数据和索引放到内存里，数据库的性能会越好。

• B) innodb_old_blocks_pct

• 老生代占整个LRU链长度的比例，默认是37，即整个LRU中新生代与老生代长度比例是63:37。(如果把这个参数设为100，就退化为普通LRU了)

• C) innodb_old_blocks_time

• 老生代停留时间窗口，单位是毫秒，默认是1000，即同时满足“被访问”与“在老生代停留时间超过1秒”两个条件，才会被插入到新生代头部。

二、主从状态命令解析

2.1 主从命令（---->从库角度）

我们可以在构建好的主从MySQL的 slave从库上，执行以下命令：

show slave status \G

来查看主从同步的当前状态。

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 123.123.123.123
                  Master_User: binlog_replica_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000062
          Read_Master_Log_Pos: 338947803
               Relay_Log_File: relay-log.000190
                Relay_Log_Pos: 338948016
        Relay_Master_Log_File: mysql-bin.000062
             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: 338947803
              Relay_Log_Space: 338948264
              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: 855c2efa-0a7a-11ea-803d-fa163ec03224
             Master_Info_File: /data/mysql/data/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)

参数解析：
1). Slave_IO_State

这里显示了当前slave I/O线程的状态(slave连接到master的状态)。状态信息和使用show processlist | grep “system user”(会显示两条信息，一条slave I/O线程的，一条是slave SQL线程的)显示的内容一样。

slave I/O线程的状态，有以下几种：

   1) waiting for master update

       这是connecting to master状态之前的状态

   2) connecting to master

      I/O线程正尝试连接到master

   3) checking master version

      在与master建立连接后，会出现该状态。该状态出现的时间非常短暂。

   4) registering slave on master

      在与master建立连接后，会出现该状态。该状态出现的时间非常短暂。  

   5) requesting binlog dump

       在与master建立连接后，会出现该状态。该状态出现的时间非常短暂。在这个状态下，I/O线程向master发送请求，请求binlog，位置从指定的binglog 名字和binglog的position位置开始。

   6) waiting to reconnect after a failed binlog dump request

       如果因为连接断开，导致binglog的请求失败，I/O线程会进入睡眠状态。然后定期尝试重连。尝试重连的时间间隔，可以使用命令"change master to master_connect_trt=X;"改变。

   7) reconnecting after a failed binglog dump request

       I/O进程正在尝试连接master

   8) waiting for master to send event

       说明，已经成功连接到master，正等待二进制日志时间的到达。如果master 空闲，这个状态会持续很长时间。如果等待的时间超过了slave_net_timeout(单位是秒)的值，会出现连接超时。在这种状态下，I/O线程会人为连接失败，并开始尝试重连

   9) queueing master event to the relay log

      此时，I/O线程已经读取了一个event，并复制到了relay log 中。这样SQL 线程可以执行此event

   10) waiting to reconnect after a failed master event read

      读取时出现的错误(因为连接断开)。在尝试重连之前，I/O线程进入sleep状态，sleep的时间是master_connect_try的值(默认是60秒)

   11) reconnecting after a failed master event read

      I/O线程正尝试重连master。如果连接建立，状态会变成"waiting for master to send event"

   12) waiting for the slave sql thread to free enough relay log space

     这是因为设置了relay_log_space_limit，并且relay log的大小已经整张到了最大值。I/O线程正在等待SQL线程通过删除一些relay log，来释放relay log的空间。

   13) waiting for slave mutex on exit

      I/O线程停止时会出现的状态，出现的时间非常短。

2). Master_Host: 123.123.123.123

mysql主库的ip地址

3). Master_User: binlog_replica_user

这个是master上面的一个用户。用来负责主从复制的用户，创建主从复制的时候建立的（具有reolication slave权限）。

4). Master_Port: 3306

master服务器的端口 一般是3306

5). Connect_Retry: 60

连接中断后，重新尝试连接的时间间隔。默认值是60秒。

与master相关的日志的信息

6). Master_Log_File: mysql-bin.001822

当前I/O线程正在读取的主服务器二进制日志文件的名称。

7). Read_Master_Log_Pos: 290072815

当前I/O线程正在读取的二进制日志的位置。

与relay log相关的信息

8). Relay_Log_File: mysqld-relay-bin.005201

当前slave SQL线程正在读取并执行的relay log的文件名。

9). Relay_Log_Pos: 256529594

当前slave SQL线程正在读取并执行的relay log文件中的位置；（Relay_Log_File下的Relay_Log_Pos其实一一对应着Relay_Master_Log_File的Exec_Master_Log_Pos。）

10). Relay_Master_Log_File: mysql-bin.001821

当前slave SQL线程读取并执行的relay log的文件中多数近期事件，对应的主服务器二进制日志文件的名称。（说白点就是我SQL线程从relay日志中读取的正在执行的sql语句，对应主库的sql语句记录在主库的哪个binlog日志中）

slave I/O和SQL线程的状态（重要）

11). Slave_IO_Running: Yes

I/O线程是否被启动并成功地连接到主服务器上。

12). Slave_SQL_Running: Yes

SQL线程是否被启动。

13). 同步过滤器filter参数

Replicate_Do_DB

Replicate_Ignore_DB

Replicate_Do_Table

Replicate_Ignore_Table

Replicate_Wild_Do_Table

Replicate_Wild_Ignore_Table

这些参数都是为了用来指明哪些库或表在复制的时候不要同步到从库，但是这些参数用的时候要小心，因为 当跨库使用的时候 可能会出现问题。

一般情况下 ，限制的时候都用Replicate_Wild_Ignore_Table这个参数。

14). Last_Errno: 0

Last_Error

slave的SQL线程读取日志参数的的错误数量和错误消息。错误数量为0并且消息为空字符串表示没有错误。

如果Last_Error值不是空值，它也会在从属服务器的错误日志中作为消息显示。

15). Skip_Counter: 0

SQL_SLAVE_SKIP_COUNTER的值，用于设置跳过sql执行步数。

16). Exec_Master_Log_Pos: 256529431

slave SQL线程当前执行的事件，对应在master相应的二进制日志中的position。（结合Relay_Master_Log_File理解，而且在Relay_Master_Log_File这个值等于Master_Log_File值的时候，Exec_Master_Log_Pos是不可能超过Read_Master_Log_Pos的。）

17). Relay_Log_Space: 709504534

所有原有的中继日志结合起来的总大小。

18). UNTIL子句参数

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

在START SLAVE语句的UNTIL子句中指定的值。

Until_Condition具有以下值：

1. 如果没有指定UNTIL子句，则没有值

2. 如果从属服务器正在读取，直到达到主服务器的二进制日志的给定位置为止，则值为Master

3. 如果从属服务器正在读取，直到达到其中继日志的给定位置为止，则值为Relay

Until_Log_File和Until_Log_Pos用于指示日志文件名和位置值。日志文件名和位置值定义了SQL线程在哪个点中止执行。

19). Master_SSL系列参数：

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Master_SSL_Verify_Server_Cert: No

Master_SSL_Crl:

Master_SSL_Crlpath:

这些字段显示了被从属服务器使用加密相关的参数。这些参数用于连接主服务器。

Master_SSL_Allowed具有以下值：

① 如果允许对主服务器进行SSL连接，则值为Yes

② 如果不允许对主服务器进行SSL连接，则值为No

③ 如果允许SSL连接，但是从属服务器没有让SSL支持被启用，则值为Ignored。

与SSL有关的字段的值对应于–master-ca,–master-capath,–master-cert,–master-cipher和–master-key选项的值。

20). seconds_Behind_Master: 2923

这个值是时间戳的差值。是slave当前的时间戳和master记录该事件时的时间戳的差值。

21). 最后一次I/O线程或者SQL线程的错误号和错误消息

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

22). Replicate_Ignore_Server_Ids:

主从复制，从库忽略的主库服务器Id号。就是不以这些服务器Id为主库。

23). Master_Server_Id: 1

Master_UUID: 13ee75bb-99e2-11e6-be4d-b499baa80e6e

Master_Info_File: /home/data/mysql/master.info

分别表示主库服务器id号，主库服务器的UUID好，还有在从库中保存主库服务器相关的目录位置。

24). SQL_Delay: 0

一个非负整数，表示秒数，Slave滞后多少秒于master。

25). SQL_Remaining_Delay: NULL

当 Slave_SQL_Running_State 等待，直到MASTER_DELAY秒后，Master执行的事件，此字段包含一个整数，表示有多少秒左右的延迟。在其他时候，这个字段是NULL。

26). SQL执行replay重做时的状态

Slave_SQL_Running_State: Reading event from the relay log

SQL线程运行状态：

① Reading event from the relay log

线程已经从中继日志读取一个事件，可以对事件进行处理了。

② Has read all relay log; waiting for the slave I/O thread to update it

线程已经处理了中继日志文件中的所有事件，现在正等待I/O线程将新事件写入中继日志。

③ Waiting for slave mutex on exit

线程停止时发生的一个很简单的状态。

27). Master_Retry_Count: 86400

连接主库失败最多的重试次数。

28). Master_Bind:

slave从库在多网络接口的情况下使用，以确定用哪一个slave网络接口连接到master。

29). Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

最后一次I/O线程或者SQL线程错误时的时间戳。

GTID模式相关

30). Retrieved_Gtid_Set:

获取到的GTID<IO线程>

Executed_Gtid_Set:

执行过的GTID<SQL线程>

Auto_Position: 0

一些理解：

1、为什么执行"stop slave;" 再执行"start slave;"命令后， 可以继续主从关系呢？

执行"stop slave;“命令， 即意味着分别关闭了I/O线程（stop slave IO_THREAD;）和SQL线程（stop slave SQL_THREAD;），I/O线程会维护master.info信息的更新，SQL线程会维护relay-log.info信息的更新，在执行"start slave” 的时候，会检查master.info和relay-log.info中的checkpoint检查点信息，断点继续执行I/O线程和SQL线程。

2、怎样从一个从库再复制一个新的从库呢？

步骤：

在从库上面 “stop slave IO_THREAD;” 关闭IO线程

等待SQL线程执行完成，当Relay_Master_Log_File和Master_Log_File二进制日志文件名一致，且Exec_Master_Log_Pos等于Read_Master_Log_Pos值。

mysqldump导出所需要的库，到新的从库服务器，并导入。

主库授权replication slave权限给新的从库

新的从库使用"change master to"命令，master_log_file对应Master_Log_File值，master_log_pos对应Read_Master_Log_Pos值。

在从库执行"start slave IO_THREAD;“和在新的从库上执行"start slave;”

2.2 主从命令（---->主库角度）

我们可以在构建好的主从MySQL的 master主库上，执行以下命令：

show master status\G;

Executed_Gtid_Set值表明：每个server_uuid代表一个实例，有多个server_uuid表明这三个实例都曾经当过主库，分别执行的事务个数都确定。

参考：

• MySQL高可用架构——PXC集群
• 细说show slave status参数详解