mysql恢复测试报告_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)