[关闭]
@Zjmainstay 2023-02-02T09:47:10.000000Z 字数 2788 阅读 1794

mysql binlog数据恢复.md

MySQL binlog 数据恢复


@author Zjmainstay
@website http://www.zjmainstay.cn

一、启动bin-log日志

在my.cnf [mysqld]节点添加:

  1. log-bin=mysql-bin
  2. binlog_format=mixed

二、查看bin-log是否启动成功

  1. show variables like '%log_bin%';
Variable_name Value
log_bin ON

三、查看mysql-bin.000001文件所在位置

  1. show variables like '%datadir%';
Variable_name Value
datadir /var/lib/mysql/

四、基于sql查询查看bin-log文件中的内容

  1. show binlog events in 'mysql_bin.000001'\G

五、基于mysqlbinlog命令查看bin-log文件的内容

  1. mysqlbinlog --no-defaults /var/lib/mysql/mysql-bin.000001|more
  2. 可以指定参数:--start-position=0 --stop-position=xxx

六、日志显示参考

请留意日志中额外添加的注释,如(作者注:开始的位置)

  1. #161118 21:58:58 server id 1 end_log_pos 1974 CRC32 0x733660ed Query thread_id=7 exec_time=0 error_code=0
  2. SET TIMESTAMP=1479477538/*!*/;
  3. insert into `tb_abc` (`id`, `name`) values ('1', 'abc')
  4. /*!*/;
  5. # at 1974(作者注:开始的位置)
  6. # (作者注:需要恢复的内容Begin)
  7. #161118 21:58:06(开始的时间) server id 1 end_log_pos 2161 CRC32 0xcd3cd3d2 Query thread_id=7 exec_time=784 error_code=0
  8. SET TIMESTAMP=1479477486/*!*/;
  9. CREATE TABLE `binlog-find` (id INT(11) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT) DEFAULT CHARACTER SET `utf8`
  10. /*!*/;
  11. # at 2161
  12. #161118 21:58:18 server id 1 end_log_pos 2307 CRC32 0x1e28b93d Query thread_id=7 exec_time=772 error_code=0
  13. SET TIMESTAMP=1479477498/*!*/;
  14. ALTER TABLE `binlog-find` ADD `name` INT
  15. NULL
  16. DEFAULT NULL
  17. AFTER `id`
  18. /*!*/;
  19. # at 2307
  20. #161118 21:58:23 server id 1 end_log_pos 2386 CRC32 0x34669ad7 Query thread_id=7 exec_time=767 error_code=0
  21. SET TIMESTAMP=1479477503/*!*/;
  22. BEGIN
  23. /*!*/;
  24. # at 2386
  25. #161118 21:58:23 server id 1 end_log_pos 2518 CRC32 0xf4f4adb4 Query thread_id=7 exec_time=767 error_code=0
  26. SET TIMESTAMP=1479477503/*!*/;
  27. INSERT INTO `binlog-find` (`id`, `name`) VALUES ('1', '2')
  28. /*!*/;
  29. # at 2518
  30. #161118 21:58:23 server id 1 end_log_pos 2549 CRC32 0x54679b22 Xid = 2426
  31. COMMIT/*!*/;
  32. # at 2549(作者注:结束的位置)
  33. # (作者注:需要恢复的内容End)
  34. #161118 21:58:27(作者注:结束的时间) server id 1 end_log_pos 2673 CRC32 0x35b852fa Query thread_id=7 exec_time=860 error_code=0
  35. SET TIMESTAMP=1479477507/*!*/;
  36. DROP TABLE `binlog-find` /* generated by server */
  37. /*!*/;
  38. # at 2673

七、数据恢复

  1. ##导入sql结果到sql文件
  2. sudo mysqlbinlog --no-defaults --start-position="1974" --stop-position="2549" /var/lib/mysql/mysql-bin.000001 >> res.sql
  3. ##通过管道方式直接运行(基于位置)
  4. sudo mysqlbinlog --no-defaults --start-position="1974" --stop-position="2549" /var/lib/mysql/mysql-bin.000001 |mysql -uzjmainstay --binary-mode
  5. --start-position 为需要恢复开始语句上一条语句运行结束位置
  6. --stop-position 为需要恢复结束语句运行结束位置(删表上一条语句)
  7. ##通过管道方式直接运行(基于时间)
  8. sudo mysqlbinlog --no-defaults --start-datetime="2016-11-18 21:58:06" --stop-datetime="2016-11-18 21:58:27" /var/lib/mysql/mysql-bin.000001 |mysql -uzjmainstay --binary-mode
  9. --start-datetime 为需要恢复开始语句运行时间
  10. --stop-datetime 为需要恢复结束语句下一条语句运行时间(删表语句)

八、数据恢复原理

  1. 恢复上一次备份;
  2. 找到上一次数据备份对应的binlog文件及其position,从该位置恢复到删表操作前的位置,也就是说,恢复文件并不一定是mysql-bin.000001;
  3. 如果从上次备份到现在已产生多个binlog,则要按照binlog产生的顺序从小到大进行恢复;

如果没有上一次备份,使用类似mysqlbinlog /var/lib/mysql/mysql-bin.000001 --start-position=0 --stop-position=9999999999999 | mysql -uroot -pxxxx 从第一个bin-log日志文件,逐步恢复到删表操作前的位置

--stop-position=9999999999999设置成接近无限大是为了无视文件的结束位置,方便操作

添加新批注
在作者公开此批注前,只有你和作者可见。
回复批注