MySQL5.7延迟复制半同步复制

　　　　　　　　　　　　MySQL5.7延迟复制半同步复制

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　作者：尹正杰

一.延迟复制

1>.什么是延迟复制

　　延迟复制是指定从库对主库的延迟至少是指定的这个间隔时间，默认是0秒。可以通过CHANGE MASTER TO命令来指定。例如：CHANGE MASTER TO MASTER_DELAY=N;其原理是从库收到主库的bin log之后，而是等待指定的秒数之后再执行。

mysql> 
mysql> show slave stATUSG
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: node101.yinzhengjie.org.cn
                  Master_User: copy
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: yinzhengjie-mysql-bin.000003
          Read_Master_Log_Pos: 154
               Relay_Log_File: node102-relay-bin.000006
                Relay_Log_Pos: 391
        Relay_Master_Log_File: yinzhengjie-mysql-bin.000003
             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: 154
              Relay_Log_Space: 778
              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: b2127a6e-3cf8-11e9-ae0d-000c29fe9bef
             Master_Info_File: /yinzhengjie/softwares/mysql-5.7.25-linux-glibc2.12-x86_64/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)

mysql> 
mysql> STOP SLAVE;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> show slave stATUSG
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: node101.yinzhengjie.org.cn
                  Master_User: copy
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: yinzhengjie-mysql-bin.000003
          Read_Master_Log_Pos: 154
               Relay_Log_File: node102-relay-bin.000006
                Relay_Log_Pos: 391
        Relay_Master_Log_File: yinzhengjie-mysql-bin.000003
             Slave_IO_Running: No
            Slave_SQL_Running: No
              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: 154
              Relay_Log_Space: 778
              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: NULL
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: b2127a6e-3cf8-11e9-ae0d-000c29fe9bef
             Master_Info_File: /yinzhengjie/softwares/mysql-5.7.25-linux-glibc2.12-x86_64/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           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)

mysql> 
mysql> CHANGE MASTER TO MASTER_DELAY=5;
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> show slave stATUSG
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: node101.yinzhengjie.org.cn
                  Master_User: copy
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: yinzhengjie-mysql-bin.000003
          Read_Master_Log_Pos: 154
               Relay_Log_File: node102-relay-bin.000002
                Relay_Log_Pos: 332
        Relay_Master_Log_File: yinzhengjie-mysql-bin.000003
             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: 154
              Relay_Log_Space: 541
              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: b2127a6e-3cf8-11e9-ae0d-000c29fe9bef
             Master_Info_File: /yinzhengjie/softwares/mysql-5.7.25-linux-glibc2.12-x86_64/data/master.info
                    SQL_Delay: 5
          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)

mysql>

mysql> CHANGE MASTER TO MASTER_DELAY=5;

2>.延迟复制的使用场景

　　确保在主库上被错误修改的数据能及时找回。

　　测试在从库IO几种在恢复bin log过程中对应程序的访问影响。

　　保留一份若干天前的数据库状态，和当前状态可以做对比。

mysql> STOP SLAVE;
Query OK, 0 rows affected (0.00 sec)

mysql> CHANGE MASTER TO MASTER_DELAY=600;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> SHOW SLAVE STATUSG
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: node101.yinzhengjie.org.cn
                  Master_User: copy
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: yinzhengjie-mysql-bin.000003
          Read_Master_Log_Pos: 154
               Relay_Log_File: node102-relay-bin.000002
                Relay_Log_Pos: 332
        Relay_Master_Log_File: yinzhengjie-mysql-bin.000003
             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: 154
              Relay_Log_Space: 541
              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: b2127a6e-3cf8-11e9-ae0d-000c29fe9bef
             Master_Info_File: /yinzhengjie/softwares/mysql-5.7.25-linux-glibc2.12-x86_64/data/master.info
                    SQL_Delay: 600　　　　　　　　　　　　　　　　#这里我们指定SQL的延迟复制事件为600，默认单位是秒（s），即复制延迟的时间为10分钟。
          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)

mysql>

mysql> SHOW SLAVE STATUSG

二.MySQL复制主从切换

　　如果是使用GTID的复制方式，可以使用mysqlfailover工具做主从复制状态的监控和自动切换；如果是使用非GTID模式，则需要使用其他的方式做监控和切换。

　　当新的master产生之后，需要通过在其他slave上执行CHANGE MASTER TO语句来对应到新的master上。slave不会检查自己的数据库和新的master上是否一致，而是直接获取master上的二进制日志并继续自己的复制功能。

　　新当选的master的实例需要运行在log bin模式下。

三.MySQL半同步复制

　　默认创建的MySQL复制是异步的，意味着主库将数据库修改事件写入到自己的bin log，而并不知道从库是否获取了这些事件并应用在自己身上。所以当主库崩溃要主从切换时，有可能从库上的数据不是最新的。

　　从5.7版本开始MySQL通过扩展的方式支持了半同步复制。

　　当主库执行一个更新操作事物时，提交操作会被阻止直到至少有一个半同步的复制slave确认依据接收到本次更新操作，主库的提交操作才会继续。

　　半同步复制的slave发送确认消息只会在本次更新操作已经记录到本地的relay log之后

　　如果没有任何slave发送确认消息而导致超时时，半同步复制会转换成异步复制。

　　半同步复制会对MySQL性能产生影响，因为主库的提交动作只有在收到至少一个从库的确认消息之后才能执行。但这个功能是性能和数据可靠性方面的权衡。

　　rpl_semi_sync_master_wait_point参数用来控制半同步复制的行为：AFTER_SYNC（默认值），AFTER_COMMIT。

需要开启的系统参数包括：
　　　　rpl_semi_sync_master_enabled:在主库配置，确保主库的半同步复制功能开启。
　　　　rpl_semi_sync_master_timeout:配置主库等待多少毫秒时间来保证接收备库的确认消息，当超时这个时间时，半同步变成异步方式。
　　　　rpl_semi_sync_slave_enabled：在从库配置，确保从库的半同步复制功能开启。

半同步复制是通过插件的方式建立，要分别在主库和从库安装一个插件半同步复制的前提条件：
　　　　MySQL 5.5版本及以上。
　　　　have_dynamic_loading参数必须是YES代表可以安装插件并动态加载。
　　　　实现建立好异步复制关系
相关插件安装文件会在plugin_dir文件夹下，并以semisync_master和semisync_slave名称开头。下面就是具体的案例实操

[root@node101 ~]# mysql -uroot -pyinzhengjie
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 3
Server version: 5.7.25-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> 
mysql> 
mysql> 
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.01 sec)

mysql>

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';　　　　　　　　　　　　　　#在主库上执行

[root@node102 ~]# 
[root@node102 ~]# mysql -uroot -pyinzhengjie
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 4
Server version: 5.7.25-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> 
mysql> 
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.00 sec)

mysql>

mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';　　　　　　　　　　　　　　　#在从库上执行

mysql> SET GLOBAL rpl_semi_sync_master_enabled=1;
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL rpl_semi_sync_master_timeout=10000;                                          #我们这里指定半同步复制的超时时间，这个可以根据我们自定义指定，若不指定，默认就是10秒。
Query OK, 0 rows affected (0.00 sec)

mysql>

在主库上开启半同步复制

mysql> 
mysql> SET GLOBAL rpl_semi_sync_slave_enabled=1;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)

mysql> STOP SLAVE SQL_THREAD;
Query OK, 0 rows affected (0.00 sec)

mysql>

在备库上开启半同步复制并重启SLAVE进程！

四.半同步复制监控参数

1>.rpl_semi_sync_master_clients

　　检查半同步的slave个数。

2>.rpl_semi_sync_master_status

　　1表示主库的半同步功能开启并且运行正常，0表示主库的半同步功能关闭或者半同步复制已经变成了异步复制。

3>.rpl_semi_sync_master_no_tx

　　表示有多少提交没有收到slave的确认消息。

4>.rpl_semi_sync_master_yes_tx

　　表示有多个提交收到了slave的确认消息。

5>.rpl_semi_sync_slave_status

　　1表示备库上slave功能开启并且运行正常，0表示功能为开启或者运行异常。

mysql> 
mysql> SHOW STATUS LIKE 'rpl_semi_sync%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 1     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 0     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
| Rpl_semi_sync_master_tx_wait_time          | 0     |
| Rpl_semi_sync_master_tx_waits              | 0     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 0     |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)

mysql> 
mysql>

主库通过MySQL命令查看各个参数的状态

mysql> 
mysql> SHOW STATUS LIKE 'rpl_semi_sync%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+
1 row in set (0.00 sec)

mysql> 
mysql>

从库通过MySQL命令查看各个参数的状态