一、问题来源
有一个朋友@水米田 问我,基于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文件造成主从异常的原因
扫一扫手机访问
