MySQL BinLog如何恢复误更新删除数据

陆大明

1. 前言

实际开发、生产场景中会出现，RDS 宕机时数据记录未入库导致数据丢失；
误更新、误删除操作导致记录被修改或数据丢失的情况；
对于 MySQL 我们可以通过 BinLog 找回误删除的数据。
BinLog 是 MySQL 自带的日志，偏向逻辑性的日志，记录的是对哪个表的哪一行做了更新操作，更新前后的值是什么。

2. BinLog 说明

Binary Logging 是个存储二进制文件，有两种文件类型

索引文件

• 文件名后缀为.index;
  
• 用于记录哪些日志文件正在被使用
  

日志文件

• 文件名后缀为.00000*;
  
• 记录数据库所有的 DDL 和 DML (除了数据查询语句)语句事件
  

binlog_format 三种日志格式

Statement

• 每一条修改数据的 sql 都会记录到 master 的 bin_log 中，slave 在复制的时候 sql 进程会解析成 master 端执行过的相同的 sql 在 slave 库上再次执行
  

Row

• 日志中会记录成每一行数据修改的形式，然后在slave端再对相同的数据进行修改
  

Mixed

• 混合模式，根据实际执行语句选择 Statement 和 Row 的一种进行执行。
  
• 比如对于修改表结构该表所有记录都会变更，此时会以 Statement 模式记录，而不是所有行记录变更都写入日志。
  

优点缺点Statement记录执行语句及上下文信息，不记录被语句影响到的每一条数据的信息，日志量较小主从同步可能会存在日志上下文信息不正确导致执行结果不一致的问题Row记录每一条数据的变化，足够详细，不会出现主从复制数据不一致的问题日志量较大Mixed混合模式，根据语句执行及 MySQL 优化策略选择一种模式记录日志，日志量可控相对 Row 模式不够详细通过设置配置文件 my.ini 的 log_bin 属性来开启日志功能，此时对数据库的操作会记录 binlog 日志并写入磁盘文件。
编辑 my.ini 可开启并配置 Binlog 策略

1. # Binary Logging.
   
2. # binlog 日志文件路径
   
3. log-bin=C:/ProgramData/MySQL/BinlogData
   
4. # binlog 日志格式
   
5. binlog_format=ROW
   
6. # binlog 过期清理时间/天
   
7. expire_logs_days=90
   
8. # binlog 日志文件大小/个
   
9. max_binlog_size=100M
   
10. # binlog 缓存大小
    
11. binlog_cache_size=4M
    
12. max_binlog_cache_size=512M

复制代码

通过应用程序 mysqlbinlog.exe 可以从日志文件中读取指定时间段的数据库语句变更详细日志。

1. mysqlbinlog --base64-output=decode-rows -v --database=<数据库名称> --start-datetime="<起始时间>" --stop-datetime="<截至时间>" <日志文件> > <输出文件>

复制代码

如下所示某时刻的变更详细日志，某数据库在 211129 16:53:31 时刻一条更新操作日志，根据该日志可清晰的指定更新前的数据，依据该日志可恢复记录数据到更新前的数据。

1. #211129 16:53:31 server id 1  end_log_pos 743 CRC32 0xd38b2db6         Update_rows: table id 337 flags: STMT_END_F
   
2. ### UPDATE `ecrm_jd`.`xxl_job_user`
   
3. ### WHERE
   
4. ###   @1=2
   
5. ###   @2='admin11'
   
6. ###   @3='e10adc3949ba59abbe56e057f20f883e'
   
7. ###   @4=1
   
8. ###   @5=NULL
   
9. ### SET
   
10. ###   @1=2
    
11. ###   @2='niaonao'
    
12. ###   @3='e10adc3949ba59abbe56e057f20f883e'
    
13. ###   @4=1
    
14. ###   @5=NULL

复制代码

3. BinLog 配置是否被开启

查看是否开启 BinLog，属性 log_bin 的值为 OFF 则没开启该功能无法通过本文下面的 BigLog 方式恢复数据。
log_bin 的值为 ON 则支持恢复数据。

1. mysql> show variables like 'log_bin%';
   
2. +---------------------------------+-------+
   
3. | Variable_name                   | Value |
   
4. +---------------------------------+-------+
   
5. | log_bin                         | OFF   |
   
6. | log_bin_basename                |       |
   
7. | log_bin_index                   |       |
   
8. | log_bin_trust_function_creators | OFF   |
   
9. | log_bin_use_v1_row_events       | OFF   |
   
10. +---------------------------------+-------+
    
11. 5 rows in set (0.01 sec)

复制代码

4. BinLog 配置怎么开启

找到 MySQL 的配置文件，配置文件路径

1. C:\ProgramData\MySQL\MySQL Server 5.6\my.ini

复制代码

，只需要把

1. log-bin

复制代码

开放后(去除属性前面的 # 注释)重启服务即可，不生效则确认 my.ini 配置无误多次重启后生效。
可指定文件生成路径，默认在相对路径下。此处指定 binlog 文件生成配置 C:\ProgramData\MySQL\BinlogData，日志记录格式配置为 ROW，单文件最大 100M，三个月清理历史文件。
log-bin 不指定时，默认使用的设置是 log-bin=mysql-bin；binlog_format 默认使用 STATEMENT；

1. # Binary Logging.
   
2. # binlog 日志文件
   
3. log-bin=C:\ProgramData\MySQL\BinlogData
   
4. # binlog 日志格式
   
5. binlog_format=ROW
   
6. # binlog 过期清理时间/天
   
7. expire_logs_days=90
   
8. # binlog 日志文件大小/个
   
9. max_binlog_size=100M
   
10. # binlog 缓存大小
    
11. binlog_cache_size=4M
    
12. max_binlog_cache_size=512M

复制代码

重启服务
服务名称就是 MySQL56，可通过 WIN+R，输入 services.msc 查看服务。

1. PS C:\Users\Lenovo> net stop MySQL56
   
2. MySQL56 服务正在停止.
   
3. MySQL56 服务已成功停止。
   
4. 
   
5. PS C:\Users\Lenovo> net start MySQL56
   
6. MySQL56 服务正在启动 .
   
7. MySQL56 服务已经启动成功。
   
8. 
   
9. PS C:\Users\Lenovo> mysql -u root -p
   
10. Enter password: ****
    
11. Welcome to the MySQL monitor.  Commands end with ; or \g.
    
12. Your MySQL connection id is 1
    
13. Server version: 5.6.21-log MySQL Community Server (GPL)
    
14. 
    
15. Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
    
16. 
    
17. Oracle is a registered trademark of Oracle Corporation and/or its
    
18. affiliates. Other names may be trademarks of their respective
    
19. owners.
    
20. 
    
21. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
22. 
    
23. mysql> show variables like 'log_bin%';
    
24. +---------------------------------+---------------------------------------+
    
25. | Variable_name                   | Value                                 |
    
26. +---------------------------------+---------------------------------------+
    
27. | log_bin                         | ON                                    |
    
28. | log_bin_basename                | C:\ProgramData\MySQL\BinlogData       |
    
29. | log_bin_index                   | C:\ProgramData\MySQL\BinlogData.index |
    
30. | log_bin_trust_function_creators | OFF                                   |
    
31. | log_bin_use_v1_row_events       | OFF                                   |
    
32. +---------------------------------+---------------------------------------+
    
33. 5 rows in set (0.00 sec)

复制代码

可以通过

1. show variables like 'log_bin%'

复制代码

看到此时配置 log_bin 已开启为 ON.

5. 误更新或删除数据

以数据库 ecrm_jd 的用户表 xxl_job_user 演示，更新一条记录，删除两条记录。
再根据 binlog 日志来追踪数据。

1. mysql> use ecrm_jd;
   
2. Database changed
   
3. 
   
4. mysql> select * from xxl_job_user;
   
5. +----+----------+----------------------------------+------+------------+
   
6. | id | username | password                         | role | permission |
   
7. +----+----------+----------------------------------+------+------------+
   
8. |  1 | admin    | e10adc3949ba59abbe56e057f20f883e |    1 | NULL       |
   
9. +----+----------+----------------------------------+------+------------+
   
10. 1 row in set (0.00 sec)
    
11. 
    
12. mysql> insert xxl_job_user(username,password,role) values('admin11', 'e10adc3949ba59abbe56e057f20f883e', 1),('admin12', 'e10adc3949ba59abbe56e057f20f883e', 1),('admin13', 'e10adc3949ba59abbe56e057f20f883e', 0);
    
13. Query OK, 3 rows affected (0.01 sec)
    
14. Records: 3  Duplicates: 0  Warnings: 0
    
15. 
    
16. mysql> select * from xxl_job_user;
    
17. +----+----------+----------------------------------+------+------------+
    
18. | id | username | password                         | role | permission |
    
19. +----+----------+----------------------------------+------+------------+
    
20. |  1 | admin    | e10adc3949ba59abbe56e057f20f883e |    1 | NULL       |
    
21. |  2 | admin11  | e10adc3949ba59abbe56e057f20f883e |    1 | NULL       |
    
22. |  3 | admin12  | e10adc3949ba59abbe56e057f20f883e |    1 | NULL       |
    
23. |  4 | admin13  | e10adc3949ba59abbe56e057f20f883e |    0 | NULL       |
    
24. +----+----------+----------------------------------+------+------------+
    
25. 4 rows in set (0.00 sec)
    
26. 
    
27. mysql> update xxl_job_user set username = 'niaonao' where id = 2;
    
28. Query OK, 1 row affected (0.00 sec)
    
29. Rows matched: 1  Changed: 1  Warnings: 0
    
30. 
    
31. mysql> delete from xxl_job_user where id in (1,4);
    
32. Query OK, 2 rows affected (0.01 sec)
    
33. 
    
34. mysql> select * from xxl_job_user;
    
35. +----+----------+----------------------------------+------+------------+
    
36. | id | username | password                         | role | permission |
    
37. +----+----------+----------------------------------+------+------------+
    
38. |  2 | niaonao  | e10adc3949ba59abbe56e057f20f883e |    1 | NULL       |
    
39. |  3 | admin12  | e10adc3949ba59abbe56e057f20f883e |    1 | NULL       |
    
40. +----+----------+----------------------------------+------+------------+
    
41. 2 rows in set (0.00 sec)

复制代码

6. binlog 日志跟踪查找被删除的数据

这里是 2021-11-29 16:56 左右修改的，在 C:\ProgramData\MySQL 下找到 <filename>.000003 日志文件。
通过应用程序 mysqlbinlog 查看 binlog。
去安装路径下找到应用程序

1. ~\MySQL Server 5.6\bin\mysqlbinlog.exe

复制代码

WIN+R 输入 cmd 打开命令行窗口，切换到 mysqlbinlog 所在目录，执行以下命令导出脚本。

1. mysqlbinlog --base64-output=decode-rows -v --database=<数据库名称> --start-datetime="<起始时间>" --stop-datetime="<截至时间>" <日志文件> > <输出文件>

复制代码

此处从文件 BinlogData.000003 中解析导出数据库 ecrm_jd 在 2021-11-29 16:50:00 ~ 2021-11-29 17:30:00 时间内的日志，输出到文件 binlog202111291650.sql

1. C:\Program Files (x86)\MySQL\MySQL Server 5.6\bin>mysqlbinlog --base64-output=decode-rows -v --database=ecrm_jd --start-datetime="2021-11-29 16:50:00" --stop-datetime="2021-11-29 17:30:00" C:\ProgramData\MySQL\BinlogData.000003 > binlog202111291650.sql

复制代码

打开文件内容如下：

1. /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;/*!40019 SET @@session.max_insert_delayed_threads=0*/;/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER /*!*/;# at 4#211129 15:20:44 server id 1  end_log_pos 120 CRC32 0x7b673bd6         Start: binlog v 4, server v 5.6.21-log created 211129 15:20:44 at startup# Warning: this binlog is either in use or was not closed properly.ROLLBACK/*!*/;# at 120#211129 16:52:19 server id 1  end_log_pos 195 CRC32 0xeeb79b0f         Query        thread_id=4        exec_time=0        error_code=0SET TIMESTAMP=1638175939/*!*/;SET @@session.pseudo_thread_id=4/*!*/;SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;SET @@session.sql_mode=1344274432/*!*/;SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;/*!\C gbk *//*!*/;SET @@session.character_set_client=28,@@session.collation_connection=28,@@session.collation_server=33/*!*/;SET @@session.lc_time_names=0/*!*/;SET @@session.collation_database=DEFAULT/*!*/;BEGIN/*!*/;# at 195#211129 16:52:19 server id 1  end_log_pos 263 CRC32 0xd1715424         Table_map: `ecrm_jd`.`xxl_job_user` mapped to number 337# at 263#211129 16:52:19 server id 1  end_log_pos 439 CRC32 0x93f08743         Write_rows: table id 337 flags: STMT_END_F### INSERT INTO `ecrm_jd`.`xxl_job_user`### SET###   @1=2###   @2='admin11'###   @3='e10adc3949ba59abbe56e057f20f883e'###   @4=1###   @5=NULL### INSERT INTO `ecrm_jd`.`xxl_job_user`### SET###   @1=3###   @2='admin12'###   @3='e10adc3949ba59abbe56e057f20f883e'###   @4=1###   @5=NULL### INSERT INTO `ecrm_jd`.`xxl_job_user`### SET###   @1=4###   @2='admin13'###   @3='e10adc3949ba59abbe56e057f20f883e'###   @4=0###   @5=NULL# at 439#211129 16:52:19 server id 1  end_log_pos 470 CRC32 0x48cdfe14         Xid = 391COMMIT/*!*/;# at 470#211129 16:53:31 server id 1  end_log_pos 545 CRC32 0xdc9527f7         Query        thread_id=4        exec_time=0        error_code=0SET TIMESTAMP=1638176011/*!*/;BEGIN/*!*/;# at 545#211129 16:53:31 server id 1  end_log_pos 613 CRC32 0x7b2ee120         Table_map: `ecrm_jd`.`xxl_job_user` mapped to number 337# at 613#211129 16:53:31 server id 1  end_log_pos 743 CRC32 0xd38b2db6         Update_rows: table id 337 flags: STMT_END_F
   
2. ### UPDATE `ecrm_jd`.`xxl_job_user`
   
3. ### WHERE
   
4. ###   @1=2
   
5. ###   @2='admin11'
   
6. ###   @3='e10adc3949ba59abbe56e057f20f883e'
   
7. ###   @4=1
   
8. ###   @5=NULL
   
9. ### SET
   
10. ###   @1=2
    
11. ###   @2='niaonao'
    
12. ###   @3='e10adc3949ba59abbe56e057f20f883e'
    
13. ###   @4=1
    
14. ###   @5=NULL# at 743#211129 16:53:31 server id 1  end_log_pos 774 CRC32 0x0423f88b         Xid = 397COMMIT/*!*/;# at 774#211129 16:54:21 server id 1  end_log_pos 849 CRC32 0x6280ce3c         Query        thread_id=4        exec_time=0        error_code=0SET TIMESTAMP=1638176061/*!*/;BEGIN/*!*/;# at 849#211129 16:54:21 server id 1  end_log_pos 917 CRC32 0xd4ee0972         Table_map: `ecrm_jd`.`xxl_job_user` mapped to number 337# at 917#211129 16:54:21 server id 1  end_log_pos 1044 CRC32 0xcc058cef         Delete_rows: table id 337 flags: STMT_END_F### DELETE FROM `ecrm_jd`.`xxl_job_user`### WHERE###   @1=1###   @2='admin'###   @3='e10adc3949ba59abbe56e057f20f883e'###   @4=1###   @5=NULL### DELETE FROM `ecrm_jd`.`xxl_job_user`### WHERE###   @1=4###   @2='admin13'###   @3='e10adc3949ba59abbe56e057f20f883e'###   @4=0###   @5=NULL# at 1044#211129 16:54:21 server id 1  end_log_pos 1075 CRC32 0x23f55056         Xid = 401COMMIT/*!*/;DELIMITER ;# End of log fileROLLBACK /* added by mysqlbinlog */;/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

复制代码

通过程序 mysqlbinlog 恢复的日志中可以查看指定时间段内的数据库操作语句，找到误 UPDATE、DELETE 语句可以看到被删除的记录属性和属性值，依据该日志可恢复该记录。

总结

以上为个人经验，希望能给大家一个参考，也希望大家多多支持脚本之家。

来源:https://www.jb51.net/database/322054grh.htm
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！