mysqldump+mysqlbinlog恢复测试

联系:手机(+86 13429648788) QQ(107644445)QQ咨询惜分飞

标题: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)

此条目发表在 MySQL 分类目录。将固定链接加入收藏夹。

mysqldump+mysqlbinlog恢复测试》有 1 条评论

  1. 惜 分飞 说:

    mysqlbinlog: unknown variable ‘default-character-set=gbk’
    解决:
    1、使用mysqlbinlog –no-defaults
    2、[client]中的default-character-set除掉,不用重启mysql

      [引用]  [回复]

发表评论

电子邮件地址不会被公开。 必填项已用 * 标注

您可以使用这些 HTML 标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>