Mysql增量备份与恢复

MySQL增量备份

为什么使用增量备份

使用mysqldump进行完全备份的存在的问题

• 备份数据中有重复数据
• 备份时间与恢复时间长

增量备份就是备份自上一次备份之后增加或变化的文件或者内容

增量备份的特点

• 没有重复数据，备份量不大，时间短
• 恢复麻烦：需要上次完全备份及完全备份之后所有的增量备份才能恢复，而且要对所有增量备份进行逐个反推恢复

增量备份的方法：
MySQL没有提供直接的增量备份方法，但是可以通过MySQL提供的二进制日志（binary logs）间接实现增量备份

MySQL二进制日志对备份的意义

• 二进制日志保存了所有更新或者可能更新数据库的操作
• 二进制日志在启动MySQL服务器后开始记录，并在文件达到max_binlog_size所设置的大小或者接收到flush logs命令后重新创建新的日志文件
• 只需定时执行flush logs方法重新创建新的日志，生成二进制文件序列，并及时把这些旧的日志保存到安全的地方就完成了一个时间段的增量备份

实现增量备份

要进行增量备份，首先要开启二进制日志功能

在MySQL配置文件的 [mysqld] 项中加入log-bin=filepath项,如log-bin=mysql-bin,然后重启mysqld服务

[root@localhost ~]# vi /etc/my.cnf
log-bin=mysql-bin			添加项
[root@localhost ~]# systemctl restart mysqld

使用mysqlbinlog命令查看重启生成的二进制文件内容

mysqlbinlog mysql-bin.000003

把二进制文件复制到保存备份的目录，完成增量备份

cp msyql-bin.000003 /opt/back_sql/

MySQL增量恢复

增量恢复的场景

当数据发生错误时，应根据实际情况选择使用完全备份恢复，还是增量恢复。增量恢复的场景是：

• 人为的SQL语句破坏了数据库
• 在进行下一次全备之前发生系统故障导致数据库丢失
• 在主从架构中，主库数据发生了故障
  根据数据丢失的情况可以分为两类：
• 只丢失了完全备份之后更改的数据
• 完全备份之后丢失所有数据

当丢失完全备份之后更改的数据的恢复步骤

当完全备份之后更改的数据丢失，需要把完全备份之后的所有增量备份文件恢复

• 首先做一个完全备份操作，确保生成了完全备份的sql文件。

mysqldump -uroot -p123456 ava > /opt/ava_$(date +%F).sql
ls /opt
ava_2020-08-20.sql  ...

• 使用flush-logs生成新的二进制日志文件，用以保存之后的数据库操作语句

mysqladmin -uroot -p123456 flush-logs
ls /usr/local/mysql/data
auto.cnf     ib_logfile1      mysql-bin.000001  mysql-bin.000005  
... 			....			....				....

生成的编号为5的二进制日志文件，用以保存之后的数据库操作语句

• 向数据库插入新的数据，执行一次flush-logs操作，生成新的二进制增量备份文件。

[root@localhost data]# mysql -uroot -p
Enter password: 
...
mysql [(none)]>use ava;
mysql [ava]>insert into ava values(1005)；
Query OK, 1 row affected (0.00 sec)

mysql [ava]>insert into ava values(1006);
Query OK, 1 row affected (0.00 sec)
mysql [ava]>quit;
Bye
[root@localhost data]# mysqladmin -u root -p123456 flush-logs
[root@localhost data]# ls /usr/local/mysql/data
auto.cnf     ib_logfile1   mysql-bin.000001  mysql-bin.000005  mysql_slow_query.log
ava   localhost.localdomain.err  mysql-bin.000002  mysql-bin.000006

编号为5的二进制文件中保存了插入语句

• 使用delete删除插入的两条数据，也就是假设完全备份后的数据丢失了

[root@localhost data]# mysql -u root -p
Enter password: 
...
mysql [(none)]>use ava;
mysql [ava]>delete from ava where id='1006';
Query OK, 1 row affected (0.00 sec)

mysql [ava]>delete from ava where id='1005';
Query OK, 1 row affected (0.00 sec)

• 使用二进制文件进行恢复操作

mysqlbinlog /usr/local/mysql/data/mysql-bin.000005 | mysql -uroot -p123456

• 查看数据库内容，完全备份后新插入的2条数据找回来了，说明数据恢复成功

mysql [ava]>select * from ava;
+------+
| id   |
+------+
| 1001 |
| 1002 |
| 1003 |
| 1004 |
| 1005 |
| 1006 |
+------+

完全备份之后丢失所有数据的恢复步骤

当完全备份和增量备份之后，所有的数据丢失，需要把完全备份和所有增量备份文件逐个恢复

• 首先做一个完全备份操作，确保生成了完全备份的sql文件。

mysqldump -uroot -p123456 ava > /opt/ava_$(date +%F).sql
ls /opt
ava_2020-08-20.sql  ...

• 使用flush-logs生成新的二进制日志文件，用以保存之后的数据库操作语句

mysqladmin -uroot -p123456 flush-logs
ls /usr/local/mysql/data
auto.cnf     ib_logfile1      mysql-bin.000001  mysql-bin.000005  
... 			....			....				....

生成的编号为5的二进制日志文件，用以保存之后的数据库操作语句

• 向数据库插入新的数据，执行一次flush-logs操作，生成新的二进制增量备份文件。

[root@localhost data]# mysql -uroot -p
Enter password: 
...
mysql [(none)]>use ava;
mysql [ava]>insert into ava values(1005)；
Query OK, 1 row affected (0.00 sec)

mysql [ava]>insert into ava values(1006);
Query OK, 1 row affected (0.00 sec)
mysql [ava]>quit;
Bye
[root@localhost data]# mysqladmin -u root -p123456 flush-logs
[root@localhost data]# ls /usr/local/mysql/data
auto.cnf     ib_logfile1   mysql-bin.000001  mysql-bin.000005  mysql_slow_query.log
ava   localhost.localdomain.err  mysql-bin.000002  mysql-bin.000006

编号为5的二进制文件中保存了插入语句

• 使用drop删除表ava，也就是假设完全备份后ava的数据全部丢失

mysql [ava]>drop table ava;
Query OK, 0 rows affected (0.00 sec)

• 先使用mysql命令进行完全备份的恢复操作

mysql -u root -p123456 ava < /opt/ava_2020-08-20.sql
mysql -u root -p123456
mysql [(none)]>use ava;
mysql [ava]>select * from ava;
+------+
| id   |
+------+
| 1001 |
| 1002 |
| 1003 |
| 1004 |
+------+
4 rows in set (0.00 sec) 

被删除的表中的数据又可以查询出来，说明完全恢复是成功的

• 使用二进制文件进行增量恢复操作

mysqlbinlog /usr/local/mysql/data/mysql-bin.000005 | mysql -uroot -p123456

查看数据库内容，增量备份的新插入的数据找回了，说明数据恢复成功

mysql [ava]>select * from ava;
+------+
| id   |
+------+
| 1001 |
| 1002 |
| 1003 |
| 1004 |
| 1005 |
| 1006 |
+------+
6 rows in set (0.00 se

基于位置和时间点的恢复

利用二进制日志日志可以实现基于位置和时间点的恢复，例如由于误操作所以删除了一张表，这时完全恢复是没有用的，因为日志里面还存在误操作的语句，我们需要恢复到我操作前的状态，然后跳过误操作的语句，再恢复后面操作的语句

• 假定需要往数据库中插入两条数据，但由于误操作，两条插入语句中间删除了一条数据，而这条数据时是不应该删除的

mysqladmin -uroot -p flush-logs
Enter password:       先分割二进制日志文件
ls /usr/local/mysql/data/
.......
ib_logfile1  mysql-bin.000002      mysql-bin.000007 新的二进制文件编号为7
[root@localhost data]# mysql -u root -p
Enter password: 
...
mysql [(none)]>use ava;
Database changed
mysql [ava]>insert into ava values(1007);
Query OK, 1 row affected (0.00 sec)
mysql [ava]>delete from ava where id='1006';
Query OK, 1 row affected (0.00 sec)
mysql [ava]>insert into ava values(1008);
Query OK, 1 row affected (0.00 sec)
mysql [ava]>select * from ava;
+------+
| id   |
+------+
| 1001 |
| 1002 |
| 1003 |
| 1004 |
| 1005 |
| 1007 |
| 1008 |
+------+
7 rows in set (0.00 sec)

分割二进制日志

mysqladmin -uroot -p flush-logs
[root@localhost opt]# mysqladmin -uroot -p flush-logs
Enter password: 

这时编号为7的二进制文件中保存了正确的插入语句，同时也保存了不应该执行的删除语句

mysqlbinlog /usr/local/mysql/data/mysql-bin.000007
...
# at 197     位置
#200820 11:16:02    时间
insert into ava values(1007)
...
# at 405   位置
#200820 11:16:25  时间
delete from ava where id='1006'
...
# at 616  位置
#200820 11:16:44    时间
insert into ava values(1008)

基于位置的恢复，就是将以某个位置结束，然后以某个位置开始的二进制日志导入数据库中，从而跳过两个位置中发生错误的位置，实现正确数据的恢复

• 先恢复完全备份到操作之前的状态

mysql -uroot -p123456 ava< /opt/ava_2020-08-20.sql
[root@localhost data]# mysql -u root -p
Enter password:
...
mysql [(none)]>select * from ava.ava;
+------+
| id   |
+------+
| 1001 |
| 1002 |
| 1003 |
| 1004 |
| 1005 |
| 1006 |
+------+
6 rows in set (0.00 sec)

• 基于位置进行恢复

[root@localhost opt]# mysqlbinlog --stop-position='405' \
/usr/local/mysql/data/mysql-bin.000007 | mysql -uroot -p
Enter password: 
[root@localhost opt]# mysqlbinlog --start-position='616' /usr/local/mysql/data/mysql-bin.000007 | mysql -uroot -p
Enter password: 
[root@localhost opt]# mysql -u root -p
Enter password: 
...
mysql [(none)]>select * from ava.ava;
+------+
| id   |
+------+
| 1001 |
| 1002 |
| 1003 |
| 1004 |
| 1005 |
| 1006 |
| 1007 |
| 1008 |
+------+
8 rows in set (0.00 sec)

成功跳过误操作并恢复正确操作，完成了基于位置的恢复

基于时间点的增量恢复

基于时间点恢复命令格式

 mysqlbinlog --(stop | start)-datetime='时间点' 二进制日志路径 | mysql -u root -p密码

总结

• MySQL没有提供直接的增量备份方法，但是可以通过MySQL提供的二进制日志（binary logs）间接实现增量备份
• 当完全备份之后更改的数据丢失，需要把完全备份之后的所有增量备份文件恢复
• 当完全备份和增量备份之后，所有的数据丢失，需要把完全备份和所有增量备份文件逐个恢复
• 利用二进制日志可以实现基于时间点与位置的恢复
• 基于位置的恢复，就是将以某个位置结束，然后以某个位置开始的二进制日志导入数据库中，从而跳过两个位置中发生错误的位置，实现正确数据的恢复