位置：首页-资讯-数据库

MySQL 增量备份

2024-04-02 19:55

短信预约 -IT技能 免费直播动态提醒

增量备份的特点

增量备份的优点是没有重复数据，备份量不大，时间短。缺点也很明显，需要上次完全备份及完全备份之后所有的增量备份才能恢复，而且对所有增量进行逐个反推恢复，操作较为繁琐。

MySQL 没有提供直接的增量备份方法，但是可以通过MySQL 的二进制间接实现增量备份。二进制日志对备份的意义如下：

（1）二进制日志保存了所有更新或者可能更新数据库的操作。

（2）二进制日志在启动MySQL 服务器后开始记录，并在文件到达 max_binlog_size 所设置的大小或者接收到 flush logs 命令后重新创建新的日志文件。

（3）只需要订时执行 flush logs 方法重新创建新的日志，生成二进制文件序列，并及时把这些日志保存到安全的地方就完成了一个时间段的增量备份。

在数据库school 中的表 info 的基础上进行增量备份操作

mysql> select * from info;
+----+------+-------+
| id | name | score |
+----+------+-------+
| 1 | tom | 89.00 |
| 2 | lili | 92.00 |
+----+------+-------+

一增量备份

1.实现增量备份，首先要开启二进制功能。在mysql 配置文件中添加 log-bin=mysql-bin 语句，然后重新启动服务

[root@bogon data]# vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin         //开启二进制功能
[root@localhost ~]# systemctl restart mysqld.service      //重启 mysql 服务，生成二进制文件
[root@localhost ~]# cd /usr/local/mysql/data/
[root@localhost data]# ls
auto.cnf        ib_logfile0 mysql             performance_schema
ib_buffer_pool ib_logfile1 mysql-bin.000001 school             //二进制文件序列
ibdata1         ibtmp1       mysql-bin.index   sys

2.使用 mysqldump 命令，对数据库 school 中的表 info 进行完全备份

[root@localhost data]# mysqldump -uroot -p school info > /opt/info.sql
Enter password:
[root@localhost data]# cd /opt/
[root@localhost opt]# ls
info.sql mysql-5.7.17 rh //表info 备份文件

3.向 info 表中插入数据，执行flush-logs 操作，生成新的二进制增量备份文件

[root@localhost data]# mysql -uroot –p              //进入mysql数据库
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
mysql> use school;            //进入库 school
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> insert into info (name,score) values ('test01',88);        //插入信息
Query OK, 1 row affected (0.37 sec)
mysql> insert into info (name,score) values ('test02',76);
Query OK, 1 row affected (0.00 sec)
mysql> select * from info;
+----+--------+-------+
| id | name   | score |
+----+--------+-------+
| 1 | tom    | 89.00 |
| 2 | lili   | 92.00 |
| 3 | test01 | 88.00 |
| 4 | test02 | 76.00 |
+----+--------+-------+
mysql> quit
Bye
[root@localhost data]# mysqladmin -uroot -p flush-logs              //进行增量备份
Enter password:
[root@localhost data]# ls
auto.cnf        ib_logfile0 mysql             mysql-bin.index     sys
ib_buffer_pool ib_logfile1 mysql-bin.000001 performance_schema
ibdata1         ibtmp1       mysql-bin.000002 school                    //生成新的二进制增量备份文件

4.虽然生成新的二进制增量备份文件 mysql-bin.000002 但此时数据变化保存在编号 000001 中，而000002 是一个空的日志文件，使用 mysqlbinlog 命令查看二进制文件内容。

[root@localhost data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000001        //查看编号000001 的日志文件
# at 348                                                                                     //不会生成乱码
#180904 16:45:20 server id 1 end_log_pos 398 CRC32 0x398bd3e5     Write_rows: table id 118 flags: STMT_END_F
### INSERT INTO `school`.`info`
### SET
###   @1=3                                              //表info 中插入的信息
###   @2='test01'
###   @3=88.00
# at 398
#180904 16:45:20 server id 1 end_log_pos 429 CRC32 0xd2d905fe     Xid = 63
COMMIT;
# at 623
#180904 16:45:32 server id 1 end_log_pos 673 CRC32 0x4164c313     Write_rows: table id 118 flags: STMT_END_F
### INSERT INTO `school`.`info`
### SET
###   @1=4
###   @2='test02'
###   @3=76.00
# at 673
#180904 16:45:32 server id 1 end_log_pos 704 CRC32 0x8287a8b1     Xid = 64
COMMIT;

二。增量备份恢复

增量恢复比完全恢复操作更加繁琐，每个增量备份都是单独的个体，数据不重复，需要控制的更加准确。

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

当完全备份和增量备份之后，所有数据丢失，需要把完全备份和所有增量备份文件逐个恢复，这里演示对库 school 中的info 表的恢复操作。

（1）模拟数据库 school 中表info 数据丢失，使用drop 删除info 表

[root@localhost ~]# mysql -uroot -p
Enter password:
mysql> use school;
mysql> select * from info;
+----+--------+-------+
| id | name   | score |
+----+--------+-------+
| 1 | tom    | 89.00 |
| 2 | lili   | 92.00 |
| 3 | test01 | 88.00 |
| 4 | test02 | 76.00 |
+----+--------+-------+
4 rows in set (0.00 sec)

mysql> drop table info;                  //删除info 表
Query OK, 0 rows affected (0.01 sec)

mysql> select * from info;
ERROR 1146 (42S02): Table 'school.info' doesn't exist

（2）先使用mysql 命令进行完全备份恢复的操作。

[root@localhost ~]# mysql -uroot -p school < /opt/info.sql       //恢复school 库中 info 表，备份文件为/opt/info.sql
Enter password:
[root@localhost ~]# mysql -uroot -p -e 'use school;show tables;select * from info;'     //不进入数据库查看info 表的恢复情况
Enter password:
+------------------+
| Tables_in_school |
+------------------+
| info             |
+------------------+
+----+------+-------+
| id | name | score |
+----+------+-------+
| 1 | tom | 89.00 |                     //完全备份操作已经恢复
| 2 | lili | 92.00 |
+----+------+-------+

（3）被删除的表中数据又可以查询出来，说明完全恢复是成功的。接下来使用二进制文件进行增量恢复操作，需要注意的是恢复的顺序，要恢复最先生成的二进制文件，然后依次执行

[root@localhost ~]# cd /usr/local/mysql/data/
[root@localhost data]# ls
auto.cnf        ib_logfile0 mysql             mysql-bin.000003    school
ib_buffer_pool ib_logfile1 mysql-bin.000001 mysql-bin.index     sys
ibdata1         ibtmp1       mysql-bin.000002 performance_schema
[root@localhost data]# mysqlbinlog --no-defaults mysql-bin.000001 | mysql -uroot –p         //使用二进制文件恢复
Enter password:
[root@localhost data]# mysql -uroot -p -e 'use school;select * from info;'     //查看恢复情况
Enter password:
+----+--------+-------+
| id | name   | score |
+----+--------+-------+
| 1 | tom    | 89.00 |
| 2 | lili   | 92.00 |
| 3 | test01 | 88.00 |
| 4 | test02 | 76.00 |
+----+--------+-------+

三。基于时间点与位置的恢复

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

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

由于误操作，在添加完 test04 后将 ‘tom’ 给误删除，有添加了test 05.

（1）基于时间点的恢复，就是讲某个起始时间的二进制日志导入数据库中，从而跳过某个发生错误的时间点实现数据恢复。

使用 mysqlbinlog 加上 --stop-datetime 选项，表示在哪个时间点结束，后面误操作的语句不执行。

使用 mysqlbinlog 加上 --start-datetime 选项，表示执行后面的语句，结合使用跳过误操作语句，完成恢复。需要注意的是，二进制文件中保存的日期格式需要调整为 “ –“ 分割。

mysql> select * from info;
+----+--------+-------+
| id | name   | score |
+----+--------+-------+
| 2 | lili   | 92.00 |
| 3 | test01 | 88.00 |
| 4 | test02 | 76.00 |
| 5 | test04 | 80.00 |
| 6 | test05 | 90.00 |
+----+--------+-------+
5 rows in set (0.00 sec)
[root@localhost data]# ls                     //有错误操作的增量备份的二进制文件
auto.cnf        ib_logfile0 mysql             mysql-bin.000003 performance_schema
ib_buffer_pool ib_logfile1 mysql-bin.000001 mysql-bin.000004 school
ibdata1         ibtmp1       mysql-bin.000002 mysql-bin.index   sys
[root@localhost data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000003                  //查看增量备份二进制文件
# at 2272        //位置点，上一次可以执行的位置
#180904 20:26:49 server id 1 end_log_pos 2322 CRC32 0x50dbd722     Write_rows: table id 222 flags: STMT_END_F   //时间点
### INSERT INTO `school`.`info`       //正确操作语句
### SET
###   @1=5
###   @2='test04'
###   @3=80.00
# at 2322
#180904 20:26:49 server id 1 end_log_pos 2353 CRC32 0x9ffdf83c     Xid = 93
COMMIT;

# at 2547
#180904 20:27:19 server id 1 end_log_pos 2594 CRC32 0xaed6d12c     Delete_rows: table id 222 flags: STMT_END_F
### DELETE FROM `school`.`info`         //误操作语句
### WHERE
###   @1=1
###   @2='tom'
###   @3=89.00
# at 2594                         // 下一次可以被执行的位置
#180904 20:27:19 server id 1 end_log_pos 2625 CRC32 0x84e5fdb3     Xid = 94
COMMIT;

# at 2819
#180904 20:32:27 server id 1 end_log_pos 2869 CRC32 0x5babb6b2     Write_rows: table id 222 flags: STMT_END_F
### INSERT INTO `school`.`info`       //正确操作语句
### SET
###   @1=6
###   @2='test05'
###   @3=90.00
# at 2869
#180904 20:32:27 server id 1 end_log_pos 2900 CRC32 0x73283b1b     Xid = 95
COMMIT;

（2）删除info 表，先进性完全备份恢复，在按顺序进行增量备份恢复

mysql> use school; //进入school 数据库
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> drop table info; //删除info 表
Query OK, 0 rows affected (0.35 sec)

mysql> quit
Bye
[root@localhost data]# mysql -uroot -p school < /opt/info.sql           //先进行完全备份
Enter password:
[root@localhost data]# mysql -uroot -p -e 'use school;show tables;select * from info;'Enter password:       //查看完全备份info 表
+------------------+
| Tables_in_school |
+------------------+
| info             |
+------------------+
+----+------+-------+
| id | name | score |
+----+------+-------+
| 1 | tom | 89.00 |
| 2 | lili | 92.00 |
+----+------+-------+

[root@localhost data]# mysqlbinlog --no-defaults mysql-bin.000001|mysql -uroot –pabc123        //进行第一次增量备份恢复
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost data]# mysql -uroot -p -e 'use school;show tables;select * from info;'
Enter password:
+------------------+
| Tables_in_school |
+------------------+
| info             |
+------------------+
+----+--------+-------+
| id | name   | score |
+----+--------+-------+
| 1 | tom    | 89.00 |
| 2 | lili   | 92.00 |
| 3 | test01 | 88.00 |
| 4 | test02 | 76.00 |

//删除‘tom’语句错误操作时间

[root@localhost data]# mysqlbinlog --no-defaults --stop-datetime='2018-09-04 20:27:19' /usr/local/mysql/data/mysql-bin.000003 | mysql -uroot -p
Enter password:
[root@localhost data]# mysql -uroot -p -e 'use school;show tables;select * from info;'
Enter password:
+------------------+
| Tables_in_school |
+------------------+
| info             |
+------------------+
+----+--------+-------+
| id | name   | score |
+----+--------+-------+
| 1 | tom    | 89.00 |
| 2 | lili   | 92.00 |
| 3 | test01 | 88.00 |
| 4 | test02 | 76.00 |
| 5 | test04 | 80.00 |
+----+--------+-------+

//下一次正确操作时间点

[root@localhost data]# mysqlbinlog --no-defaults --start-datetime='2018-09-04 20:32:27 ' /usr/local/mysql/data/mysql-bin.000003 | mysql -uroot -p
Enter password:

[root@localhost data]# mysql -uroot -p -e 'use school;show tables;select * from info;'Enter password:
+------------------+
| Tables_in_school |
+------------------+
| info             |
+------------------+
+----+--------+-------+
| id | name   | score |
+----+--------+-------+
| 1 | tom    | 89.00 |
| 2 | lili   | 92.00 |
| 3 | test01 | 88.00 |                               //info 表恢复正确
| 4 | test02 | 76.00 |
| 5 | test04 | 80.00 |
| 6 | test05 | 90.00 |
+----+--------+-----

免责声明：

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

阅读原文内容投诉