如果将relay_log_recovery设置为0会发生什么？

woqutechteam

于 2019-01-21 09:54:14 发布

阅读量1.7k

点赞数 1

分类专栏： MySQL 文章标签： relay-log-recovery MySQL 并行复制从库的数据一致性 GTID模式

本文链接：https://blog.csdn.net/woqutechteam/article/details/86569040

版权

本文探讨了在MySQL 5.7.22中，将relay-log-recovery设置为0的情景，特别是在并行复制环境中。通过模拟不同情况下的relay-log不完整或丢失，分析了在Retrieved_Gtid_Set小于或大于Executed_Gtid_Set时，从库如何处理这些异常，并确保数据一致性。实验结果显示，即使在relay-log-recovery=0的情况下，MySQL仍然能够根据GTID模式自我修复，但在并行复制下可能影响数据一致性。

摘要由CSDN通过智能技术生成

| 背景

在MySQL5.7版本中，开启并行复制后，设置relay-log-recovery=1，从库启动后偶尔会报错，类似于https://bugs.mysql.com/bug.php?id=83713。所以尝试将relay-log-recovery设置为0，观察会发生什么。

| 环境

MySQL5.7.22
GTID模式
relay-log-recovery=0
relay_log_purge=off
sync_relay_log = 10000
relay_log_info_repository = TABLE
不开启并行复制

| 模拟relay-log没有sync完全

Retrieved_Gtid_Set < Executed_Gtid_Set

停掉slave，查看相关信息

[root@shadow:/root 5.7.22-log_Instance1 root@localhost:(none) 17:20:34]>stop slave;
Query OK, 0 rows affected (0.01 sec)
[root@shadow:/root 5.7.22-log_Instance1 root@localhost:(none) 17:20:37]>select * from mysql.slave_relay_log_info;show slave status\G
+-----------------+-------------------------------------------------------------+---------------+------------------+----------------+-----------+-------------------+----+--------------+
| Number_of_lines | Relay_log_name | Relay_log_pos | Master_log_name | Master_log_pos | Sql_delay | Number_of_workers | Id | Channel_name |
+-----------------+-------------------------------------------------------------+---------------+------------------+----------------+-----------+-------------------+----+--------------+
| 7 | /home/mysql/data/mysqldata1/relaylog/mysql-relay-bin.000017 | 186094 | mysql-bin.000001 | 4941307 | 0 | 0 | 1 | |
+-----------------+-------------------------------------------------------------+---------------+------------------+----------------+-----------+-------------------+----+--------------+
1 row in set (0.00 sec)
*************************** 1. row ***************************
           Slave_IO_State:
              Master_Host: 10.10.30.18
              Master_User: repl
              Master_Port: 3306
            Connect_Retry: 10
          Master_Log_File: mysql-bin.000001
      Read_Master_Log_Pos: 4941307
           Relay_Log_File: mysql-relay-bin.000017
            Relay_Log_Pos: 186094
    Relay_Master_Log_File: mysql-bin.000001
         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: 4941307
          Relay_Log_Space: 4315819
          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: 330618
              Master_UUID: 2662c965-fdb2-11e8-8157-0cc47a3aae0d
         Master_Info_File: mysql.slave_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: 2662c965-fdb2-11e8-8157-0cc47a3aae0d:1-101:104-1504:1509-2079:2082-11822
        Executed_Gtid_Set: 2662c965-fdb2-11e8-8157-0cc47a3aae0d:1-11822
            Auto_Position: 1
     Replicate_Rewrite_DB:
             Channel_Name:
       Master_TLS_Version:
1 row in set (0.00 sec)

关闭数据库stopmysql

$stopmysql

查看最后一个relaylog中的最后一个事务的GTID为2662c965-fdb2-11e8-8157-0cc47a3aae0d:11822

$mysqlbinlog -vvv mysql-relay-bin.000017
# at 185676
#181214 17:20:36 server id 330618 end_log_pos 4940954 CRC32 0xc2acb86d GTID last_committed=11821    sequence_number=11822   rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= '2662c965-fdb2-11e8-8157-0cc47a3aae0d:11822'/*!*/;
# at 185741
#181214 17:20:36 server id 330618 end_log_pos 4941046 CRC32 0x516997b4 Query thread_id=60654    exec_time=0 error_code=0
SET TIMESTAMP=1544779236/*!*/;
BEGIN
/*!*/;
# at 185833
#181214 17:20:36 server id 330618 end_log_pos 4941139 CRC32 0x65ea73eb Rows_query
# update qdata_mysql_heartbeat set ts=now() where server_id=@@server_id
# at 185926
#181214 17:20:36 server id 330618 end_log_pos 4941212 CRC32 0xb88fd1db Table_map: `qdata_mysql`.`qdata_mysql_heartbeat` mapped to number 108
# at 185999
#181214 17:20:36 server id 330618 end_log_pos 4941276 CRC32 0x67572f7a Update_rows: table id 108 flags: STMT_END_F
BINLOG '
5HUTXB16CwUAXQAAAFNlSwCAAEV1cGRhdGUgcWRhdGFfbXlzcWxfaGVhcnRiZWF0IHNldCB0cz1u
b3coKSB3aGVyZSBzZXJ2ZXJfaWQ9QEBzZXJ2ZXJfaWTrc+pl
5HUTXBN6CwUASQAAAJxlSwAAAGwAAAAAAAEAC3FkYXRhX215c3FsABVxZGF0YV9teXNxbF9oZWFy
dGJlYXQAAggSAQAA29GPuA==
5HUTXB96CwUAQAAAANxlSwAAAGwAAAAAAAEAAgAC///8egsFAAAAAACZoZ0VI/x6CwUAAAAAAJmh
nRUkei9XZw==
'/*!*/;
### UPDATE `qdata_mysql`.`qdata_mysql_heartbeat`
### WHERE
### @1=330618 /* LONGINT meta=0 nullable=0 is_null=0 */
### @2='2018-12-14 17:20:35' /* DATETIME(0) meta=0 nullable=0 is_null=0 */
### SET
### @1=330618 /* LONGINT meta=0 nullable=0 is_null=0 */
### @2='2018-12-14 17:20:36' /* DATETIME(0) meta=0 nullable=0 is_null=0 */
# at 186063
#181214 17:20:36 server id 330618 end_log_pos 4941307 CRC32 0x78f64c71 Xid = 776909
COMMIT/*!*/;
# at 186094
#181214 17:21:45 server id 330619 end_log_pos 186117 CRC32 0x014899bc Stop
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

用以下命令模拟relaylog丢失，这样mysql重新启动的时候初始化的Retrieved_Gtid_Set集合会比Executed_Gtid_Set集合小

$dd if=mysql-relay-bin.000017 of=/tmp/mysql-relay-bin.000017 bs=1 count=185090
$mv /tmp/mysql-relay-bin.000017 ./
$chown -R mysql:mysql *
##185090处对应的是2662c965-fdb2-11e8-8157-0cc47a3aae0d:11820这个事务，所以GTID为2662c965-fdb2-11e8-8157-0cc47a3aae0d:11820的事务被截断，2662c965-fdb2-11e8-8157-0cc47a3aae0d:11821、2662c965-fdb2-11e8-8157-0cc47a3aae0d:11822被抹掉
# at 184840
#181214 17:20:34 server id 330618 end_log_pos 4940118 CRC32 0xf6f3aed0 GTID last_committed=11819    sequence_number=11820   rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= '2662c965-fdb2-11e8-8157-0cc47a3aae0d:11820'/*!*/;
# at 184905
#181214 17:20:34 server id 330618 end_log_pos 4940210 CRC32 0x22409fbf Query thread_id=60652    exec_time=0 error_code=0
SET TIMESTAMP=1544779234/*!*/;
BEGIN
/*!*