mysql从库产生大量binlog_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 |