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

標籤：date   欄位   evel   復原   工具   arch   prim   osc   操作   

為保證沒有其他參數配置影響，重新安裝配置了一台迷你安裝的CentOS7虛擬機器

1. 基礎知識?

安裝mysql5.6資料庫
Mysql binlog初步理解

2. 配置mysql 開啟binlog、修改binlog模式為Row Level模式?

[[email protected] ~]# 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 changedmysql> 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)

備份資料庫

[[email protected] ~]# 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: 0mysql> 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檔案

[[email protected] ~]# ls /var/lib/mysql auto.cnf ibdata1 ib_logfile1 mysql-bin.000001 mysql-bin.000003 mysql.sockdemo 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.sqlmysql> 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 第一次insert320 - 520 第二次insert520 - 754 誤操作update

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

[[email protected] ~]# 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

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