mysqldump+mysqlbinlog恢复测试

一、模拟环境
mysql> create database test;
Query OK, 1 row affected (0.02 sec)

mysql> use test;
Database changed
mysql> reset master;
Query OK, 0 rows affected (0.04 sec)

mysql> show binary logs;
+—————–+———–+
| Log_name | File_size |
+—————–+———–+
| mysqlbin.000001 | 107 |
+—————–+———–+
1 row in set (0.00 sec)

mysql> create table t(id int,name varchar(10));
Query OK, 0 rows affected (0.09 sec)

mysql> insert into t values(1,’aaaa’);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t values(2,’bbbb’);
Query OK, 1 row affected (0.00 sec)

二、使用mysqldump备份数据
mysqldump -u root -ppassw0rd –skip-opt –extended-insert=false –master-data=2 –single-transaction –allow-keywords –add-locks –add-drop-table -F -q test >/tmp/test.sql

三、继续操作
mysql> insert into t values(3,’cccc’);
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect…
Connection id: 208
Current database: test

Query OK, 1 row affected (0.00 sec)

mysql> insert into t values(4,’dddd’);
Query OK, 1 row affected (0.00 sec)

mysql> drop table t;
Query OK, 0 rows affected (0.04 sec)

mysql> create table t1 as
-> select * from information_schema.tables;
Query OK, 79 rows affected (0.27 sec)
Records: 79 Duplicates: 0 Warnings: 0

四、报告误删除表，开始恢复准备
mysql> show binary logs;
+—————–+———–+
| Log_name | File_size |
+—————–+———–+
| mysqlbin.000001 | 605 |
| mysqlbin.000002 | 14686 |
+—————–+———–+
2 rows in set (0.00 sec)

tee /tmp/output.txt
Logging to file ‘/tmp/output.txt’
mysql> show binlog events in ‘mysqlbin.000002′;
……………………省略
mysql> notee
Outfile disabled.
mysql> exit
Bye

查找删除表的位置（起点和终点）
[root@ECP-UC-DB1 tmp]# cat output.txt |grep “DROP TABLE”
| mysqlbin.000002 | 461 | Query | 1 | 564 | use `test`; DROP TABLE `t` /* generated by server */
[root@ECP-UC-DB1 mysqllog]# cat /tmp/test.sql |grep MASTER
– CHANGE MASTER TO MASTER_LOG_FILE=’mysqlbin.000002′, MASTER_LOG_POS=107;

生成恢复sql语句
mysqlbinlog –start-position=107 –stop-position=461 mysqlbin.000002>/tmp/drop.sql
mysqlbinlog –start-position=564 mysqlbin.000002>>/tmp/drop.sql

五、开始恢复
找一个测试库，拷贝/tmp/drop.sql和/tmp/test.sql到备库，然后进行恢复
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> use test;
Database changed
mysql> source /tmp/test.sql
mysql> source /tmp/drop.sql

六、测试恢复结果
mysql> show tables;
+—————-+
| Tables_in_test |
+—————-+
| t |
| t1 |
+—————-+
2 rows in set (0.00 sec)

mysql> select * from t;
+——+——+
| id | name |
+——+——+
| 1 | aaaa |
| 2 | bbbb |
| 3 | cccc |
| 4 | dddd |
+——+——+
4 rows in set (0.00 sec)

mysql> select count(*) from t1;
+———-+
| count(*) |
+———-+
| 79 |
+———-+
1 row in set (0.02 sec)