Mysql使用binlog恢复数据解决误操作问题的两种方法
程序员文章站
2022-03-13 19:29:23
1. 基础知识 安装mysql5.6数据库Mysql binlog初步理解 2. 配置mysql 开启binlog、修改binlog模式为Row Level模式 修改mysql配置文件,在[mysqld]下增加以下内容 3. 重启mysql数据库 binlog开启 生成文件/var/lib/mysq ......
为保证没有其他参数配置影响,重新安装配置了一台最小化安装的centos7虚拟机
1. 基础知识
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的详细使用方法参看官方文档
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