MySQL通过binlog日志恢复数据--误删除的表

本实验模拟生产环境误操作删除表，可利用逻辑备份和binlog恢复所删除的表。

生产库：192.168.8.31

临时库：192.168.8.32

一、数据备份

操作的前一天晚上进行了日常逻辑备份

mysqldump -uroot -pmysql -P3306 --all-databases > /mysql/backup/dump/alldb_bak.sql

二、模拟事故

模拟事故发生前后的业务情况

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| kk             |
| t1             |
| t2             |
| t3             |
| t4             |
| t5             |
| t6             |
| t7             |
+----------------+
8 rows in set (0.00 sec)
mysql> desc t7;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(30) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> create table t8 as select * from t7;
Query OK, 3 rows affected (0.17 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> select * from t8;
+----+--------+
| id | name   |
+----+--------+
|  1 | steven |
|  3 | steven |
|  4 | steven |
+----+--------+
3 rows in set (0.00 sec)
mysql> insert into t8 select * from t7;
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> select * from t8;
+----+--------+
| id | name   |
+----+--------+
|  1 | steven |
|  3 | steven |
|  4 | steven |
|  1 | steven |
|  3 | steven |
|  4 | steven |
+----+--------+
6 rows in set (0.00 sec)
mysql> update t8 set id=2 where id=3;
Query OK, 2 rows affected (0.33 sec)
Rows matched: 2  Changed: 2  Warnings: 0
mysql> update t8 set id=3 where id=4;
Query OK, 2 rows affected (0.03 sec)
Rows matched: 2  Changed: 2  Warnings: 0
mysql> select * from t8;
+----+--------+
| id | name   |
+----+--------+
|  1 | steven |
|  2 | steven |
|  3 | steven |
|  1 | steven |
|  2 | steven |
|  3 | steven |
+----+--------+
6 rows in set (0.00 sec)
mysql> drop table t8;
Query OK, 0 rows affected (0.10 sec)

三、查看当前binlog

mysql> show master status ;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |     1344 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

四、恢复数据

拷贝生产库前一天晚上的备份文件以及备份到事故期间的binlog至临时库

scp alldb_bak.sql 192.168.8.32:/mysql/backup/dump/

scp /mysql/data/mysql-bin.000001 192.168.8.32:/mysql/backup/dump/

在临时库创建出现事故的database

mysql> create database test;
Query OK, 1 row affected (0.03 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
| testdb13           |
| testdb14           |
| testdb15           |
| testdb16           |
| testdb17           |
| testdb18           |
| testdb19           |
| testdb20           |
| testdb21           |
| testdb22           |
| testdb23           |
| testdb24           |
+--------------------+
17 rows in set (0.00 sec)

从备份中恢复test数据库

mysql -uroot -pmysql -P3306 -o test < alldb_bak.sql

-o是指单独恢复test库，忽略其他数据库

从mysql-bin.000001中查看到drop table t8之前的pos是1164

update t8 set id=3 where id=4
/*!*/;
# at 1133
#181127 14:12:41 server id 330631  end_log_pos 1164 CRC32 0x1203751c 	Xid = 1661
COMMIT/*!*/;
# at 1164
#181127 14:12:53 server id 330631  end_log_pos 1229 CRC32 0x48fad728 	Anonymous_GTID	last_committed=4	sequence_number=5	rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1229
#181127 14:12:53 server id 330631  end_log_pos 1344 CRC32 0x2a7eb0d7 	Query	thread_id=3	exec_time=1	error_code=0
SET TIMESTAMP=1543299173/*!*/;
DROP TABLE `t8` /* generated by server */
/*!*/;

mysqlbinlog --no-defaults --stop-position=1164 --database=test mysql-bin.000001 |mysql -uroot -p test

五、根据临时库的数据，将该表恢复至生产库

六、数据验证

mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| kk             |
| t1             |
| t2             |
| t3             |
| t4             |
| t5             |
| t6             |
| t7             |
| t8             |
+----------------+
9 rows in set (0.01 sec)
mysql> select * from t8;
+----+--------+
| id | name   |
+----+--------+
|  1 | steven |
|  2 | steven |
|  3 | steven |
|  1 | steven |
|  2 | steven |
|  3 | steven |
+----+--------+
6 rows in set (0.00 sec)

数据恢复完毕。