mysqldump+mysqlbinlog恢复测试

联系:手机(13429648788)  QQ(107644445)

链接:https://www.orasos.com/mysqldumpmysqlbinlog%e6%81%a2%e5%a4%8d%e6%b5%8b%e8%af%95.html

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

One thought on “mysqldump+mysqlbinlog恢复测试

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

Comments are closed.