MySQL手动注册binlog文件造成主从异常的原因

一、问题来源

有一个朋友@水米田 问我,基于POSITION的主从。他做了如下的操作

将备份的一些binlog文件加入到了目录中
修改index文件,加入了这些binlog文件
flush binary logs
然后整个主从环境大量延迟。

二、朋友的测试

下面是另外一个朋友@徐晨亮的测试:

master上:
(root:db1@xucl:10:30:22)[(none)]> show binary logs;
+---------------------+-----------+
| Log_name      | File_size |
+---------------------+-----------+
| mysql-binlog.000031 |   1019 |
| mysql-binlog.000032 |    424 |
| mysql-binlog.000033 |    244 |
| mysql-binlog.000034 |   2332 |
| mysql-binlog.000035 |   2134 |
| mysql-binlog.000036 |  845915 |
| mysql-binlog.000037 |   11735 |
| mysql-binlog.000038 |    284 |
| mysql-binlog.000039 |    284 |
| mysql-binlog.000040 |    284 |
| mysql-binlog.000041 |    284 |
| mysql-binlog.000042 |    234 |
+---------------------+-----------+
12 rows in set (0.00 sec)
(root:db1@xucl:10:30:34)[(none)]> purge binary logs to 'mysql-binlog.000039';
Query OK, 0 rows affected (0.00 sec)
(root:db1@xucl:10:30:49)[(none)]> show binary logs;
+---------------------+-----------+
| Log_name      | File_size |
+---------------------+-----------+
| mysql-binlog.000039 |    284 |
| mysql-binlog.000040 |    284 |
| mysql-binlog.000041 |    284 |
| mysql-binlog.000042 |    234 |
+---------------------+-----------+
4 rows in set (0.00 sec)
执行插入数据
(root:db1@xucl:10:31:23)[xucl]> insert into t values(9,9);
将备份的binlog拷贝回原先的目录并修改index文件进行注册
[root@izbp12nspj47ypto9t6vyez logs]# ll
总用量 884
-rw-r----- 1 mysql mysql  1019 5月 20 22:03 mysql-binlog.000031
-rw-r----- 1 mysql mysql  424 5月 20 22:03 mysql-binlog.000032
-rw-r----- 1 mysql mysql  244 5月 20 22:03 mysql-binlog.000033
-rw-r----- 1 mysql mysql  2332 5月 20 22:03 mysql-binlog.000034
-rw-r----- 1 mysql mysql  2134 5月 20 22:03 mysql-binlog.000035
-rw-r----- 1 mysql mysql 845915 5月 20 22:03 mysql-binlog.000036
-rw-r----- 1 mysql mysql 11735 5月 20 22:05 mysql-binlog.000037
-rw-r----- 1 mysql mysql  284 5月 20 22:06 mysql-binlog.000038
-rw-r----- 1 mysql mysql  284 5月 21 10:28 mysql-binlog.000039
-rw-r----- 1 mysql mysql  284 5月 21 10:28 mysql-binlog.000040
-rw-r----- 1 mysql mysql  284 5月 21 10:28 mysql-binlog.000041
-rw-r----- 1 mysql mysql  491 5月 21 10:31 mysql-binlog.000042
-rw-r----- 1 mysql mysql  204 5月 21 10:30 mysql-binlog.index
主库flush binary logs
(root:db1@xucl:10:32:51)[(none)]> flush binary logs;
Query OK, 0 rows affected (0.01 sec)
(root:db1@xucl:10:32:57)[(none)]> show binary logs;
+---------------------+-----------+
| Log_name      | File_size |
+---------------------+-----------+
| mysql-binlog.000031 |   1019 |
| mysql-binlog.000032 |    424 |
| mysql-binlog.000033 |    244 |
| mysql-binlog.000034 |   2332 |
| mysql-binlog.000035 |   2134 |
| mysql-binlog.000036 |  845915 |
| mysql-binlog.000037 |   11735 |
| mysql-binlog.000038 |    284 |
| mysql-binlog.000039 |    284 |
| mysql-binlog.000040 |    284 |
| mysql-binlog.000041 |    284 |
| mysql-binlog.000042 |    541 |
| mysql-binlog.000043 |    234 |
+---------------------+-----------+
13 rows in set (0.00 sec)
此时,slave报错如下:
(root:db1@xucl:10:31:05)[(none)]> show slave status\G
*************************** 1. row ***************************
        Slave_IO_State:
         Master_Host: 127.0.0.1
         Master_User: repl
         Master_Port: 3306
        Connect_Retry: 60
       Master_Log_File: mysql-binlog.000035
     Read_Master_Log_Pos: 194
        Relay_Log_File: izbp12nspj47ypto9t6vyez-relay-bin.000011
        Relay_Log_Pos: 373
    Relay_Master_Log_File: mysql-binlog.000035
       Slave_IO_Running: No
      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: 194
       Relay_Log_Space: 648
       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: 1236
        Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Cannot replicate GTID-transaction when @@GLOBAL.GTID_MODE = OFF, at file /storage/single/mysql3306/logs/mysql-binlog.000035, position 194.; the first event 'mysql-binlog.000039' at 234, the last event read from '/storage/single/mysql3306/logs/mysql-binlog.000035' at 259, the last byte read from '/storage/single/mysql33'
        Last_SQL_Errno: 0
        Last_SQL_Error:
 Replicate_Ignore_Server_Ids:
       Master_Server_Id: 3306
         Master_UUID: e8bdf01a-c79b-11e8-82b3-00163e088352
       Master_Info_File: mysql.slave_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: 190521 10:32:57
   Last_SQL_Error_Timestamp:
        Master_SSL_Crl:
      Master_SSL_Crlpath:
      Retrieved_Gtid_Set:
      Executed_Gtid_Set: 4c423515-6661-11e9-b767-00163e088352:1-7,
e8bdf01a-c79b-11e8-82b3-00163e088352:1-57192
        Auto_Position: 0
     Replicate_Rewrite_DB:
         Channel_Name:
      Master_TLS_Version:
1 row in set (0.00 sec)
从slave上的报错来看,在主库flush binary logs后,从库又读取注册的binlog并且又apply了

MySQL手动注册binlog文件造成主从异常的原因

扫一扫手机访问