MySQL binlog分析

背景

   在MySQL集群环境中需要开启binlog，然后可以在其它节点解析主节点的binlog，写入到当前节点，达到集群环境数据同步的效果。在实际使用场景中经常会出现某条binlog还原失败的情况，同步任务会一直尝试，导致整个mysql集同步任务卡住。我们需要解析binlog找出是哪条sql导致的，然后想办法恢复集群。

操作步骤

1.查看从库状态

show slave status\G

Slave_IO_State: Waiting for master to send event

                Master_Host: xxx.xxx.xxx.xxx

                 Master_User: sync

                 Master_Port: 3306

               Connect_Retry: 60

            Master_Log_File: mysql-bin.000010

         Read_Master_Log_Pos: 13572204

              Relay_Log_File: vm081p009-relay-bin.000019

               Relay_Log_Pos: 78535

          Slave_IO_Running: Yes

           Slave_SQL_Running: No

             Last_SQL_Error: Could not execute Update_rows event on table database.table; Can't find record in 'tablename', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000008, end_log_pos 79003

       Master_Info_File: /usr/local/mysql/data/master.info

上面的信息可以看出当前从节点的主库地址：xxx.xxx.xxx.xxx，主节点上最新的binlog为：mysql-bin.000010，此时Slave_SQL_Running为No表示已经没有继续同步，Last_SQL_Error里是错误的详细，可以看出是update数据时失败了，并说明了执行哪个binlog的位置时失败。

2.定位原因

通过mysqlbinlog命令进行解析：

mysqlbinlog -v --stop-position=79003 /usr/local/mysql/data/mysql-bin.00008 > tmpbinlog.log

命令说明

-v  详细输出

--stop-position  为错误提示信息中的end_log_pos

mysql-bin.00008一般binlog放在mysql的数据目录下，Master_Info_File信息里有会出现路径信息

解析出的信息会另存为tmpbinlog.log

由于我们已经指定了end_pos所有正常情况下我们要找的内容就在tmpbinlog.log的最后，如果文件过大则可以使用tail -200f命令查看，如果文件较小，则可以直接打开并搜索end_log_pos的id即可。

上图中的@1 表示表的第一个字段，其它以此类推。

我们在从库上查看这条数据会返回Empty set，只能在主库上能查询到该条记录。

3.修复数据

在主库上根据源数据创建一张新表

create database restore_data default charset utf8;
#创建跟进源表的数据创建一张新表
create table restore_data.tablename as select * from database.tablename where id = 255363;
select * from restore_data.tablename;

在shell控制台通过mysqldump生成insert语句。

mysqldump -uroot -p"xxxx" -t restore_data  tablename > insert_t2.sql

在insert_t2.sql文件中找到那条insert语句。

在从库上修复数据：

stop slave;
INSERT INTO `tablename` VALUES (xxxxxxxx);
start slave;
show slave status\G

查看从库状态，问题得以修复。可能会出现别的阻塞问题，需要按照步骤再次修复，如果错误数据过多只能卸载然后搭建新的从库了。