一个简单的binlog恢复测试

     日常的数据备份及恢复测试,是DBA工作重中之重的事情,所以要做好备份及测试,日常的备份常见有mysqldump+binlog备份、xtrabackup+binlog备份,无论那一种,几乎都少不了对binlog的备份,说明了binlog在数据恢复中的重要性,下面做个小测试,是工作中不少运维或者新人DBA容易犯的错。


创建一个测试表tb1:

<test>(root@localhost) [xuanzhi]> show create table tb1\G
*************************** 1. row ***************************
       Table: tb1
Create Table: CREATE TABLE `tb1` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` char(10) CHARACTER SET latin1 DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

<test>(root@localhost) [xuanzhi]> 

往表里插入两条数据:

<test>(root@localhost) [xuanzhi]> insert into tb1 (name)  value ('aa'),('bb');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

<test>(root@localhost) [xuanzhi]> show master logs;
+----------------------+-----------+
| Log_name             | File_size |
+----------------------+-----------+
| localhost-bin.000001 |       329 |
+----------------------+-----------+
1 row in set (0.00 sec)

<test>(root@localhost) [xuanzhi]> 

这个时候对数据备份,如果是xtrabackup备份的话,会在有xtrabackup_binlog_info文件中记录此时备份是到那个binlog文件和pos点的,如果是mysqldump备份,则需要带上--master-data=2这个参数,下面我们的数据量少,用mysqldump备份:

[root@localhost ~]# mysqldump -uroot -p123456  -R --events --triggers=true --master-data=2 --single-transaction xuanzhi > xuanzhi.sql
Warning: Using a password on the command line interface can be insecure.
[root@localhost ~]# grep -i "CHANGE MASTER" xuanzhi.sql 
-- CHANGE MASTER TO MASTER_LOG_FILE='localhost-bin.000001', MASTER_LOG_POS=329;
[root@localhost ~]# 

备份完后,继续模拟数据库有写入,而还是写在localhost-bin.000001

<test>(root@localhost) [xuanzhi]> insert into tb1 (name)  value ('cc'),('dd');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

<test>(root@localhost) [xuanzhi]> show master logs;
+----------------------+-----------+
| Log_name             | File_size |
+----------------------+-----------+
| localhost-bin.000001 |       538 |
+----------------------+-----------+
1 row in set (0.00 sec)

<test>(root@localhost) [xuanzhi]> flush logs;
Query OK, 0 rows affected (0.01 sec)

<test>(root@localhost) [xuanzhi]> insert into tb1 (name)  value ('ee');
Query OK, 1 row affected (0.00 sec)

<test>(root@localhost) [xuanzhi]> show master logs;
+----------------------+-----------+
| Log_name             | File_size |
+----------------------+-----------+
| localhost-bin.000001 |       589 |
| localhost-bin.000002 |       321 |
+----------------------+-----------+
2 rows in set (0.00 sec)

<test>(root@localhost) [xuanzhi]> 

上面我们进行flush logs是为了模拟现在已经有多个binlog文件了,恢复时进行多个binlog一起恢复。

进行误操,把xunazhi库drop了:

<test>(root@localhost) [xuanzhi]> drop database xuanzhi;
Query OK, 1 row affected (0.02 sec)

<test>(root@localhost) [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

<test>(root@localhost) [(none)]> 

 创建数据xuanzhi,把备份导入:

<test>(root@localhost) [(none)]> create database xuanzhi;    
Query OK, 1 row affected (0.00 sec)
[root@localhost ~]# mysql -uroot -p123456 xuanzhi <./xuanzhi.sql 
Warning: Using a password on the command line interface can be insecure.
[root@localhost ~]# 

查看数据:

<test>(root@localhost) [(none)]> use xuanzhi
Database changed
<test>(root@localhost) [xuanzhi]> select * from tb1;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
+----+------+
2 rows in set (0.00 sec)

<test>(root@localhost) [xuanzhi]> 

可以看到备份前的数据恢复了,我们现在要结合Binlog来恢复,但前提要找出误操作前的pos点,也就是drop database xuanzhi前的pos点:

[root@localhost ~]# mysqlbinlog -v --base64-output=DECODE-ROWS localhost-bin.000002 |grep -C 10  -i "drop database" 
### INSERT INTO `xuanzhi`.`tb1`
### SET
###   @1=5
###   @2='ee'
# at 290
#170327 21:10:55 server id 1313306  end_log_pos 321 CRC32 0x825a2f99    Xid = 78
COMMIT/*!*/;
# at 321
#170327 21:19:25 server id 1313306  end_log_pos 422 CRC32 0x8c139cac    Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1490620765/*!*/;
drop database xuanzhi
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@localhost ~]# mysql -uroot -p123456 xuanzhi <./xuanzhi.sql 
Warning: Using a password on the command line interface can be insecure.
[root@localhost ~]# 

从上面可以看到,误操作前的pos点是321,那我们现在通过binlog来进行数据恢复:

[root@localhost mysql-5.6]# mysqlbinlog --start-position=329 --stop-position=321 localhost-bin.000001 localhost-bin.000002 |mysql -uroot -p123456 xuanzhi
Warning: Using a password on the command line interface can be insecure.
[root@localhost mysql-5.6]# 

 --start-position是备份后记录下的pos点, --stop-position是误操前的pos点,如果批多个binlog文件,那么start-position是第一个binlog文件的pos点,stop-position是最后一个binlog的pos点,下面我们看下数据是否恢复回来了:

<test>(root@localhost) [xuanzhi]> select * from tb1;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
|  3 | cc   |
|  4 | dd   |
|  5 | ee   |
+----+------+
5 rows in set (0.00 sec)

<test>(root@localhost) [xuanzhi]> 

这里要提的是进行恢复前,要把需要恢复的binlog备份好,或者移动拷贝一份到另一个目录,因为进行数据导入时也会继续写binlog。假如你没有误操作的情况下,就是想测试一下数据的恢复,很多人的操作是导入备份,再从备份里记录的binlog文件名和pos点进行binlog恢复,发现步骤都很完美,也没报错,恢复后就是只有备份时的数据,没有备份后的数据,下面测试一下给大家看:

<test>(root@localhost) [xuanzhi]> insert into tb1 (name)  value ('aa'),('bb');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

<test>(root@localhost) [xuanzhi]> select * from tb1;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
+----+------+
2 rows in set (0.00 sec)
<test>(root@localhost) [xuanzhi]> show master logs;
+----------------------+-----------+
| Log_name             | File_size |
+----------------------+-----------+
| localhost-bin.000001 |       329 |
+----------------------+-----------+
1 row in set (0.00 sec)

<test>(root@localhost) [xuanzhi]> 

进行备份操作:

[root@localhost ~]# mysqldump -uroot -p123456 -R --events --triggers=true --master-data=2 --single-transaction xuanzhi > xuanzhi.sql
Warning: Using a password on the command line interface can be insecure.
[root@localhost ~]# grep -i "change master" xuanzhi.sql 
-- CHANGE MASTER TO MASTER_LOG_FILE='localhost-bin.000001', MASTER_LOG_POS=329;
[root@localhost ~]# 

继续写localhost-bin.000001后进行flush logs生成新的binlog再继续写数据,这里只是想模拟localhost-bin.000001写满了切localhost-bin.000002,结合多个binlog一起恢复

<test>(root@localhost) [xuanzhi]> insert into tb1 (name)  value ('cc'),('dd');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

<test>(root@localhost) [xuanzhi]> flush logs;
Query OK, 0 rows affected (0.00 sec)

<test>(root@localhost) [xuanzhi]> insert into tb1 (name)  value ('dd');
Query OK, 1 row affected (0.01 sec)

<test>(root@localhost) [xuanzhi]> show master logs;
+----------------------+-----------+
| Log_name             | File_size |
+----------------------+-----------+
| localhost-bin.000001 |       589 |
| localhost-bin.000002 |       321 |
+----------------------+-----------+
2 rows in set (0.00 sec)

<test>(root@localhost) [xuanzhi]> 

下面进行恢复测试,正常来说先把备份导入:

[root@localhost ~]# mysql -uroot -p123456 xuanzhi <./xuanzhi.sql 
Warning: Using a password on the command line interface can be insecure.
[root@localhost ~]# 

查看数据,只有备份的那两条记录:

<test>(root@localhost) [xuanzhi]> select * from tb1;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
+----+------+
2 rows in set (0.00 sec)

<test>(root@localhost) [xuanzhi]> 

那现在通过localhost-bin.000001,localhost-bin.000002来恢复后面那3条数据,那么起始pos是那个呢,就是上面备份完后备份文件里的那个pos,我们进到binlog的存放路径:

 可以看到备份后的数据是没有恢复回来的。为什么呢?因为导入备份的时候,又开始写binlog了,而你恢复时用的binlog也就是现在导入备份时正在写的binlog。大体过程是这样的:

1、导入备份后,备份的所有操作都写进最后一个binlog了,也就是上面的localhost-bin.000002

2、进行binlog恢复,从备份文件里的pos点开始,按理来说是可以恢复到最新数据的,但是上面导入了备份,导入时的所有操作都会记录到localhost-bin.000002

3、备份导入时会有DROP TABLE和CREATE TABLE的动作写进binlog里,所以最终得到的数据,还是备份时的数据。

 

 

总结:

        一、在恢复全备数据之前必须将该binlog文件移出,否则恢复过程中,会继续写入语句到binlog,最终导致增量恢复数据部分变得比较混乱

        二、做好数据文件及binlog的备份至关重要,但不是备份完就算了,要定期进行数据恢复测试或演练

        三、恢复时建议对外停止更新,即禁止更新数据库

 

 

作者:陆炫志

出处:xuanzhi的博客 http://www.cnblogs.com/xuanzhi201111

您的支持是对博主最大的鼓励,感谢您的认真阅读。本文版权归作者所有,欢迎转载,但请保留该声明。

posted @ 2017-03-29 11:42  GoogSQL  阅读(1116)  评论(0编辑  收藏  举报