Mysql使用binlog恢複數據解決誤操作問題的兩種方法

為保證沒有其他參數配置影響，重新安裝配置了一臺最小化安裝的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