MySQL 误操作 数据恢复

binlog介绍

1. binlog,即二进制日志,它记录了数据库上的所有改变.
2. SQL语句执行结束时,将在binlog的末尾写入一条记录,同时通知语句解析器,语句执行完毕.
3. binlog格式

• 基于语句,无法保证所有语句都在从库执行成功,比如update … limit 1;
• 基于行,将每一次改动记为binlog中的一行.在执行一个特别复杂的update或者delete操作时,基于行的格式会有优势.

是否启用了日志
mysql>show variables like ‘log_bin’;

当前的日志
mysql> show master status;

看二进制日志文件用mysqlbinlog
shell>mysqlbinlog -v mail-bin.000001
或者shell>mysqlbinlog -v mail-bin.000001 | tail

binglog的类型

binlog的格式有三种，这也反应了mysql的复制技术：基于SQL语句的复制(statement-based replication, SBR)，基于行的复制(row-based replication, RBR)，混合模式复制(mixed-based replication, MBR)。相应地，binlog的格式也有三种：STATEMENT，ROW，MIXED。

mysql>showvariables like ‘binlog_format’    #查看binlog的格式

mysqlbinlog 恢复

不同的mysql mysqlbinlog版本不一样，需要获取到进程自身的mysqlbinlog

1. 只查看第一个binlog文件的内容
   show binlog events;
2. 查看指定binlog文件的内容
   show binlog events in 'mysql-bin.000002';
3. 查看当前正在写入的binlog文件（当前数据库使用的binlog）
   show master status;
4. 获取binlog文件列表
   show binary logs;
5. 查看所有binlog日志列表
   show master logs;
6. 清空binlog日志文件
   reset master
7. 刷新 log 日志，重新生成一个新编号的 binlog 日志文件
   flush logs;

导出 binlog

mysqlbinlog -v binlog.000003 > /home/ryan_binlog.sql

记得要加上 -v，不然导出时，无法看到修改的SQL语句，也就无法进行定位，

-v 选项可以从行事件中重建伪 SQL 语句。

导入 binlog，恢复数据

基于开始/结束时间

mysqlbinlog binlog.000003 --start-datetime=2021-01-20 10:00:00 --stop-datetime=2021-01-23 10:00:00 -v | mysql -uroot -p

基于 pos 位点

mysqlbinlog binlog.000003 --start-position=528 --stop-position=1778 -v | mysql -uroot -p

 

或者也可以使用下面的工具生成回滚 SQL 进行恢复：

https://github.com/zhengjianhong001/binlog2sql

 

https://dev.mysql.com/doc/internals/en/binary-log.html
https://www.jianshu.com/p/c16686b35807
http://www.ttlsa.com/mysql/super-useful-mysqlbinlog-command/