mysqldump全量备份+mysqlbinlog二进制日志增量备份详解数据库

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

创建一个测试表tb1

<test>([email protected]) [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>([email protected]) [xuanzhi]>

往表里插入两条数据

<test>([email protected]) [xuanzhi]> insert into tb1 (name)  value ('aa'),('bb'); 
Query OK, 2 rows affected (0.01 sec) 
Records: 2  Duplicates: 0  Warnings: 0 
 
<test>([email protected]) [xuanzhi]> show master logs; 
+----------------------+-----------+ 
| Log_name             | File_size | 
+----------------------+-----------+ 
| localhost-bin.000001 |       329 | 
+----------------------+-----------+ 
1 row in set (0.00 sec) 
 
<test>([email protected]) [xuanzhi]>

对数据备份

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

[[email protected] ~]# 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. 
[[email protected] ~]# grep -i "CHANGE MASTER" xuanzhi.sql  
-- CHANGE MASTER TO MASTER_LOG_FILE='localhost-bin.000001', MASTER_LOG_POS=329; 
[[email protected] ~]#

继续模拟数据库有写入

这个时候是还是写在mysql-bin.000001

<test>([email protected]) [xuanzhi]> insert into tb1 (name)  value ('cc'),('dd'); 
Query OK, 2 rows affected (0.00 sec) 
Records: 2  Duplicates: 0  Warnings: 0 
 
<test>([email protected]) [xuanzhi]> show master logs; 
+----------------------+-----------+ 
| Log_name             | File_size | 
+----------------------+-----------+ 
| mysql-bin.000001 |       538 | 
+----------------------+-----------+ 
1 row in set (0.00 sec) 
 
<test>([email protected]) [xuanzhi]> flush logs; 
Query OK, 0 rows affected (0.01 sec) 
 
<test>([email protected]) [xuanzhi]> insert into tb1 (name)  value ('ee'); 
Query OK, 1 row affected (0.00 sec) 
 
<test>([email protected]) [xuanzhi]> show master logs; 
+----------------------+-----------+ 
| Log_name             | File_size | 
+----------------------+-----------+ 
| mysql-bin.000001     | 589 | 
| mysql-bin.000002     | 321 |     
+----------------------
+-----------+ 
2 rows in set (0.00 sec) <test>([email protected]) [xuanzhi]>

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

模拟误操作

把xunazhi库drop了：

<test>([email protected]) [xuanzhi]> drop database xuanzhi; 
Query OK, 1 row affected (0.02 sec) 
 
<test>([email protected]) [(none)]> show databases; 
+--------------------+ 
| Database           | 
+--------------------+ 
| information_schema | 
| mysql              | 
| performance_schema | 
| test               | 
+--------------------+ 
4 rows in set (0.00 sec) 
 
<test>([email protected]) [(none)]>

创建数据xuanzhi，把备份导入

<test>([email protected]) [(none)]> create database xuanzhi;     
Query OK, 1 row affected (0.00 sec)

[[email protected] ~]# mysql -uroot -p123456 xuanzhi <./xuanzhi.sql # 这个sql文件可以是绝对路径也可以是相对路径  
Warning: Using a password on the command line interface can be insecure. 
[[email protected] ~]#

查看数据

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

可以看到备份前的数据恢复了

接下来要结合Binlog来恢复

但前提要找出误操作前的pos点，也就是drop database xuanzhi前的pos点：

[[email protected] ~]# mysqlbinlog -v --base64-output=DECODE-ROWS mysql-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 [email protected]_COMPLETION_TYPE*/; 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; 
[[email protected] ~]# mysql -uroot -p123456 xuanzhi <./xuanzhi.sql  
Warning: Using a password on the command line interface can be insecure. 
[[email protected] ~]#

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

[[email protected] mysql-5.6]# mysqlbinlog --start-position=329 --stop-position=321 mysql-bin.000001 mysql-bin.000002 |mysql -uroot -p123456 xuanzhi 
# 这里的mysql-bin.000001和mysql-bin.000002等日志文件要从原来的位置拷贝出来，可以是绝对路径也可以是相对路径

Warning: Using a password on the command line interface can be insecure. 
[[email protected] mysql-5.6]#

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

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

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

<test>([email protected]) [xuanzhi]> insert into tb1 (name)  value ('aa'),('bb'); 
Query OK, 2 rows affected (0.01 sec) 
Records: 2  Duplicates: 0  Warnings: 0 
 
<test>([email protected]) [xuanzhi]> select * from tb1; 
+----+------+ 
| id | name | 
+----+------+ 
|  1 | aa   | 
|  2 | bb   | 
+----+------+ 
2 rows in set (0.00 sec) 
<test>([email protected]) [xuanzhi]> show master logs; 
+----------------------+-----------+ 
| Log_name             | File_size | 
+----------------------+-----------+ 
| localhost-bin.000001 |       329 | 
+----------------------+-----------+ 
1 row in set (0.00 sec) 
 
<test>([email protected]) [xuanzhi]>

进行备份操作：

[[email protected] ~]# 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. 
[[email protected] ~]# grep -i "change master" xuanzhi.sql  
-- CHANGE MASTER TO MASTER_LOG_FILE='localhost-bin.000001', MASTER_LOG_POS=329; 
[[email protected] ~]#

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

<test>([email protected]) [xuanzhi]> insert into tb1 (name)  value ('cc'),('dd'); 
Query OK, 2 rows affected (0.00 sec) 
Records: 2  Duplicates: 0  Warnings: 0 
 
<test>([email protected]) [xuanzhi]> flush logs; 
Query OK, 0 rows affected (0.00 sec) 
 
<test>([email protected]) [xuanzhi]> insert into tb1 (name)  value ('dd'); 
Query OK, 1 row affected (0.01 sec) 
 
<test>([email protected]) [xuanzhi]> show master logs; 
+----------------------+-----------+ 
| Log_name             | File_size | 
+----------------------+-----------+ 
| localhost-bin.000001 |       589 | 
| localhost-bin.000002 |       321 | 
+----------------------+-----------+ 
2 rows in set (0.00 sec) 
 
<test>([email protected]) [xuanzhi]>

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

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

查看数据，只有备份的那两条记录：

<test>([email protected]) [xuanzhi]> select * from tb1; 
+----+------+ 
| id | name | 
+----+------+ 
|  1 | aa   | 
|  2 | bb   | 
+----+------+ 
2 rows in set (0.00 sec) 
 
<test>([email protected]) [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的备份至关重要，但不是备份完就算了，要定期进行数据恢复测试或演练

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

原创文章，作者：奋斗，如若转载，请注明出处：https://blog.ytso.com/4727.html