利用mysqlbinlog恢复数据

PS：原创文章，如需转载，请注明出处，谢谢！     

本文地址：http://flyer0126.iteye.com/blog/2210993

 

      前几天，由于同事误操作delete掉线上数据一个表的数据，由于操作库为数据主库，开启了binlog，于是开始了利用mysqlbinlog恢复表数据的折腾过程。在此仅记录一下。

      1. 确认恢复库的binlog已打开。

[mysqld]
log-bin=mysql-bin

       2. 将删除操作对应的log文件转换为sql文件

mysqlbinlog  /var/lib/mysql/mysql-bin.00014  > /var/lib/mysql/14.txt

       3. 查看并记录执行删除操作的语句起始与结束位置

#BEGIN
/*!*/;
# at 194663082
#150507 19:47:03 server id 125  end_log_pos 194663179   Query   thread_id=807717        exec_time=0     error_code=0
#SET TIMESTAMP=1430999223/*!*/;
#delete from test_table；
/*!*/;
# at 194663179
#150507 19:47:03 server id 125  end_log_pos 194663206   Xid = 9667064
#COMMIT/*!*/;

     记录起始位置：194663179， 结束位置：194663206。

 

      4. 如果有之前的备份表数据，可以根据备份日期与执行删除日期间的数据binlog来匹配查询所有关于此表的写操作（insert && update）。

#根据binlog查找对应表的数据进行恢复
mysqlbinlog  mysql-bin.0001  --result-file=binlog.1 // 转换binlog二进制文件为sql文件
grep "insert into test_table" binlog.1  >binlog-back1.sql  // 查找insert语句整理至binlog-back1.sql
grep "update test_table" binlog.1  >binlog-back11.sql  // 查找update语句整理至binlog-back11.sql 

      依次对binlog文件执行上述操作，查找表相关的写操作，然后在mysql客户端上顺序执行即可。

       5. 还可以使用mysqlbinlog指定位置和制定时间断进行数据恢复。

 

这里有几个比较关键的参数:
--database=db_name, -d db_name
该参数使mysqlbinlog仅从本地二进制日志中输出指定的db_name被use命令选作默认数据库时产生的日志事件。行为类似于mysqld的--binlog-do-db命令。若该参数指定了多次那么只有最后一次指定的内容有效。参数具体的影响依赖于二进制日志格式，只有在使用行模式的日志格式时该参数才能保证一致性。基于语句或混合模式的二进制日志格式中因为可能存在跨库的更新导致--database参数表现不同的行为，从而不能保证数据一致性。
mysqlbinlog  VMS00781-bin.000001  -d testDB | mysql -uusername -p

--force-read, -f
使用了该参数后mysqlbinlog工具在读取到不能识别的日志事件时会打印出warning，忽略事件并继续执行，没有此参数的情况下mysqlbinlog会停止。
mysqlbinlog  VMS00781-bin.000001  -d testDB -f | mysql -uusername -p

--no-defaults
阻止mysqlbinlog工具从任何配置文件读取参数， .mylogin.cnf除外（以便于安全的保存密码）
mysqlbinlog  VMS00781-bin.000001  -d testDB -f --no-defaults| mysql -uusername -p

--start-datetime=datetime
--stop-datetime=datetime
上边一组参数用于指定恢复开始时间点和结束时间点，可以一起或单独给出，也可与--start-position，--stop-position混用
mysqlbinlog  VMS00781-bin.000001  -d testDB -f --no-defaults --start-datetime=datetime --stop-position=NNNNNN | mysql -uusername -p

--start-position=N, -j N
--stop-position=N
上边一组参数用于指定恢复开始位置和结束位置，可以一起或单独给出也可与--start-datetime，--stop-datetime混用
mysqlbinlog  VMS00781-bin.000001  -d testDB -f --no-defaults --start-position=NNNNNN --stop-datetime=datetime | mysql -uusername -p

需要还原的二进制日志文件通常不止一个，那么要是有多个二进制日志文件需要还原呢，该注意些什么？
首先，可以选择上述直接重定向到mysql客户端的方法或先导入到.sql文件然后执行.sql文件的方式逐个应用二进制日志文件。但是这里存在一个隐患，及，如果二进制日志中记录有使用临时表的情况，那么当上一个日志应用完，在新连接中应用下一个二进制日志时临时表就会丢失，引起错误。所以，安全的方式是多个二进制文件同时执行。
如：
mysqlbinlog  VMS00781-bin.000001  VMS00781-bin.000002 VMS00781-bin.000003 --start-position=NNNNNN --stop-datetime=datetime | mysql -uusername -p

或mysqlbinlog  VMS00781-bin.00000[1-3] --start-position=NNNNNN --stop-datetime=datetime | mysql -uusername -p

当多个二进制日志文件同时执行时，--start-position和--stop-position分别只应用于第一个列出的二进制日志文件和最后一个列出的二进制日志文件

当然也可以先将多个二进制日志文件的输出导到同一个.sql文件最后在执行该.sql文件（适用于日志量不多的情况）