Mysql 使用 binlog 复原数据解决误操作问题的两种方法

发布时间：2023-09-19 15:58:40 所属栏目：MySql教程来源：

导读：为保证没有其他参数配置影响，重新安装配置了一台最小化安装的CentOS7虚拟机

1. 基础知识 

安装 mysql5.6 数据库

Mysql binlog 初步理解

2. 配置 mysql 开启 binlog、修改 binlog 模式为 Row

为保证没有其他参数配置影响，重新安装配置了一台最小化安装的CentOS7虚拟机

1. 基础知识 

安装 mysql5.6 数据库

Mysql binlog 初步理解

2. 配置 mysql 开启 binlog、修改 binlog 模式为 Row Level 模式 

[root@localhost ~]# vi /etc/my.cnf

 修改 mysql 配置文件，在 [mysqld] 下增加以下内容

# 注释: 开启binlog 文件名以mysql-bin开头

log-bin = mysql-bin

# 注释: 备份恢复模式不需要开启Row模式闪回需要开启Row模式

binlog_format=”ROW”

3. 重启 mysql 数据库 binlog 开启生成文件 /var/lib/mysql/mysql-bin.000001

service mysqld restart

登录数据库然后创建测试数据库 demo 和测试表 user

mysql> create database demo;

Query OK, 1 row affected (0.00 sec)

mysql> use demo;

Database changed

mysql> CREATE TABLE user ( id int(8) NOT NULL AUTO_INCREMENT, name varchar(32) NOT NULL, type int(8) NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB;

Query OK, 0 rows affected (0.01 sec)

 准备完毕，刷新掉 binlog 文件，此时 binlog 文件 mysql-bin.000001 结束，进入 mysql-bin.000002 记录

mysql> flush logs;

Query OK, 0 rows affected (0.00 sec)

备份数据库

[root@localhost ~]# mysqldump -u root -p –databases demo > db_demo_bak.sql

4. 使用备份 + binlog 记录方式回滚数据库

1. 进入数据库先插入 2 条测试数据

mysql> insert into user (id,name,type) value (10001,’A’,’1’);

Query OK, 1 row affected (0.00 sec)

mysql> insert into user (id,name,type) value (10002,’B’,’1’);

Query OK, 1 row affected (0.00 sec)

2. 查看数据

mysql> select * from user;

+——-+——+——+

| id | name | type |

+——-+——+——+

| 10001 | A | 1 |

| 10002 | B | 1 |

+——-+——+——+

2 rows in set (0.00 sec)

3. 模拟误操作 update 修改数据库数据

mysql> update user set name = ‘C’;

Query OK, 2 rows affected (0.00 sec)

Rows matched: 2 Changed: 2 Warnings: 0

mysql> select * from user;

+——-+——+——+

| id | name | type |

+——-+——+——+

| 10001 | C | 1 |

| 10002 | C | 1 |

+——-+——+——+

2 rows in set (0.00 sec)

4.user 表的 name 字段被误操作修改，抓紧刷新掉 binlog 文件，mysql-bin.000002 结束，后续操作进入 mysql-bin.000003

mysql> flush logs;

Query OK, 0 rows affected (0.01 sec)

5. 此时，查看一下 mysql 的 binlog 文件

[root@localhost ~]# ls /var/lib/mysql

auto.cnf ibdata1 ib_logfile1 mysql-bin.000001 mysql-bin.000003 mysql.sock

demo ib_logfile0 mysql mysql-bin.000002 mysql-bin.index performance_schema 

那么，mysql-bin.000002 文件记录了从备份到误操作的所有数据库执行，现在我们需要恢复掉被 update 误操作掉的数据

6. 先使用备份恢复数据库此时表 user 没有数据

mysql> user demo;

mysql> drop table user;

mysql> source /root/db_demo_bak.sql

mysql> select * from user;

Empty set (0.00 sec)

7. 查看 mysql-bin.000002 记录的操作，查看各操作的 Pos 和 End_log_pos

mysql> show binlog events in ‘mysql-bin.000002’;

+——————+—–+————-+———–+————-+—————————————+

| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |

+——————+—–+————-+———–+————-+—————————————+

| mysql-bin.000002 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.39-log, Binlogver: 4 |

| mysql-bin.000002 | 120 | Query | 1 | 192 | BEGIN |

| mysql-bin.000002 | 192 | Table_map | 1 | 243 | table_id: 72 (demo.user) |

| mysql-bin.000002 | 243 | Write_rows | 1 | 289 | table_id: 72 flags: STMT_END_F |

| mysql-bin.000002 | 289 | Xid | 1 | 320 | COMMIT / xid=147 / |

| mysql-bin.000002 | 320 | Query | 1 | 392 | BEGIN |

| mysql-bin.000002 | 392 | Table_map | 1 | 443 | table_id: 72 (demo.user) |

| mysql-bin.000002 | 443 | Write_rows | 1 | 489 | table_id: 72 flags: STMT_END_F |

| mysql-bin.000002 | 489 | Xid | 1 | 520 | COMMIT / xid=148 / |

| mysql-bin.000002 | 520 | Query | 1 | 592 | BEGIN |

| mysql-bin.000002 | 592 | Table_map | 1 | 643 | table_id: 72 (demo.user) |

| mysql-bin.000002 | 643 | Update_rows | 1 | 723 | table_id: 72 flags: STMT_END_F|

| mysql-bin.000002 | 723 | Xid | 1 | 754 | COMMIT / xid=149 / |

| mysql-bin.000002 | 754 | Rotate | 1 | 801 | mysql-bin.000003;pos=4 |

+——————+—–+————-+———–+————-+—————————————+

14 rows in set (0.00 sec)

可以看出 

120 - 320 第一次insert

320 - 520 第二次insert

520 - 754 误操作update

8. 使用 mysqlbinlog 工具恢复 insert 操作 120 - 520

[root@localhost ~]# mysqlbinlog –start-position=120 –stop-position=520 -database=demo /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -u root -p mima666 -v demo

mysqlbinlog 的详细使用方法参看官方文档 https://dev.mysql.com/doc

9. 查看下 user 表数据

mysql> select * from user;

+——-+——+——+

| id | name | type |

+——-+——+——+

| 10001 | A | 1 |

| 10002 | B | 1 |

+——-+——+——+

2 rows in set (0.00 sec)

Ok，数据恢复成功

5. 使用 binlog+MyFlash 闪回方式回滚数据库 

参考美团的开源项目 MyFlash

MyFlash-gitee MyFlash-github

（编辑：聊城站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!