MySQL - 40如何恢複誤删的資料

什麼是binlog日志

使用binlog恢複誤删的資料

#檢視所有二進制日志清單
show master logs;
#檢視正在使用的二進制日志
show master status;
#重新整理日志(重新開始新的binlog日志檔案)
flush logs
#查詢指定的binlog
show binlog events in 'binlog.000003' from 10668\G;
#導出恢複資料用的sql
mysqlbinlog "C:\ProgramData\MySQL\MySQL Server 8.0\Data\WQ-20160826MDKU-bin.000057" -- start-position 528 --stop-position 1191 > d:\backup\test.sql      

實戰案例

建立表資料

create table test_binlog(
  id int not null auto_increment primary key,
  name varchar(50)
);

insert into test_binlog(name) values('aaa');
insert into test_binlog(name) values('bbb');
insert into test_binlog(name) values('ccc');
insert into test_binlog(name) values('ddd');

mysql> show master logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |   3104223 | No        |
| binlog.000002 |       200 | No        |
| binlog.000003 |     10747 | No        |
+---------------+-----------+-----------+
3 rows in set (0.00 sec)

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000003 |    10747 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)

mysql> delete from test_binlog where id in(2, 3);
Query OK, 2 rows affected (0.01 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000005 |      156 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)      

檢視所有二進制日志清單

mysql> show master logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |   3104223 | No        |
| binlog.000002 |       200 | No        |
| binlog.000003 |     10747 | No        |
+---------------+-----------+-----------+
3 rows in set (0.00 sec)
mysql>      

檢視正在使用的二進制日志

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000003 |    10747 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql>      

查詢指定的binlog

mysql> show binlog events in 'binlog.000003'\G;
*************************** 1. row ***************************
   Log_name: binlog.000003
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 125
       Info: Server ver: 8.0.20, Binlog ver: 4      

導出恢複資料用的sql

root@9abe1dd59947:~# mysqlbinlog -v --base64-output=decode-rows "/var/lib/mysql/binlog.000003"  

root@9abe1dd59947:~# mysqlbinlog "/var/lib/mysql/binlog.000003" --start-position 9834 --stop-position 10716 > ~/test.sql

mysql> source /root/test.sql

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

...

mysql> select * from test_binlog;
+----+------+
| id | name |
+----+------+
|  1 | aaa  |
|  2 | bbb  |
|  3 | ccc  |
|  4 | ddd  |
+----+------+
4 rows in set (0.01 sec)

mysql>