MySQL 增量备份与恢复

操作系统崩溃、断电、文件系统崩溃以及硬件故障等意外情况，均可能导致我们正在使用的数据库出现故障，进而引发数据不一致问题。为保障数据库安全，必须定期进行数据库备份。数据库备份可分为完整备份、日志备份、增量备份和文件备份。对于大型数据库而言，频繁执行完整备份耗时较长，且完整备份会多次重复备份未发生变化的数据，造成资源浪费。当前最常用的数据库备份策略，是在完成一次完整备份的基础上，更频繁地执行增量备份。例如，我们可在业务低峰期每周执行一次完整备份，随后每日执行一次增量备份，以捕获该时间段内可能对数据库造成修改的操作。

增量备份简介

增量备份是指仅备份自上次完整备份或上一次增量备份以来新增或修改过的文件。也就是说，第一次增量备份涵盖自完整备份之后新增或修改的文件；第二次增量备份涵盖自第一次增量备份之后新增或修改的文件，依此类推。该备份方式最显著的优势在于不会产生重复的备份数据，从而减小备份体积、缩短备份时间。然而，从增量备份中恢复数据更为复杂：必须同时具备最近一次完整备份及全部增量备份（丢失或损坏任一增量备份均会导致恢复失败），且需严格按照时间顺序，先恢复完整备份，再依次恢复各增量备份，这将显著延长恢复所需时间。

例如，如果我们有一个包含 20GB 数据的数据库，并且每天新增 10MB 数据，那么每天执行一次完整备份将给服务器带来巨大负担。相反，我们只需备份当天新增的数据，从而减轻服务器压力。

Binlog 简介

MySQL 本身不提供直接的增量备份方法，但可通过 MySQL 二进制日志（binlog）间接实现增量备份。

二进制日志（binlog）通过配置文件中的 log-bin 选项启用。MySQL 服务器将在数据根目录下创建两个新文件：“XXX-bin.001”和“xxx-bin.index”。若配置选项中未指定文件名，MySQL 将使用主机名来命名这两个文件。“.index”文件包含所有日志文件的列表。MySQL 在“XXX-bin.n”文件中记录对数据库内容和结构的所有修改，但不会记录未实际更新数据的“SELECT”和“UPDATE”语句。

当 MySQL 数据库停止或重启时，服务器会记录到下一个日志文件中。MySQL 在重启时会生成一个新的二进制日志（binlog）文件，其文件编号递增。此外，如果某个日志文件大小超过了由系统变量 “max_binlog_size” 所设定的限制，也会创建一个新的日志文件。

增量备份依赖于 MySQL 的二进制日志（binlog）机制。数据库中所有插入、更新、删除及查询操作的每一步都会被记录在 binlog 中。通过先执行一次全量备份，并同时刷新 binlog，此后所有的操作都将被记录到新的 binlog 文件中。因此，增量备份只需备份新生成的 binlog 文件，即可实现对持续增长的数据库的完美备份。

二进制日志对备份的重要性如下：

• 二进制日志存储所有更新或可能更新数据的操作。

• 
  

• 二进制日志在 MySQL 服务器启动后开始记录，当达到设定的文件大小或执行“flush logs”命令时，会创建新的日志文件。

• 通过定期执行“flush logs”命令，创建新的二进制日志文件序列，并将这些文件安全存储，即可完成一段时间内的增量备份。

如何执行 MySQL 增量备份？

MySQL 增量备份可通过 MySQL 日志实现。MySQL 日志包括三种类型：二进制日志、事务日志 和错误日志。其中，二进制日志是 MySQL 的主要日志，以二进制格式记录所有数据修改操作，并存储在磁盘上。通过备份二进制日志，即可实现 MySQL 增量备份。

MySQL 增量备份步骤：

1. 启用 MySQL 二进制日志：

在 MySQL 配置文件（“my.cnf”）中设置以下参数：

 [mysql]  
   log-bin=mysql-bin  
   server-id=1

“log-bin”参数用于指定二进制日志文件的前缀，“server-id”参数用于唯一标识MySQL实例。

2. 创建备份用户：

 CREATE USER 'backup'@'localhost' IDENTIFIED BY 'your_password';  
 GRANT REPLICATION CLIENT, REPLICATION SLAVE, PROCESS, SUPER, RELOAD ON *.* TO 'backup'@'localhost';

此处，“backup”为用户名。该用户必须拥有以下权限：“REPLICATION CLIENT”、“REPLICATION SLAVE”、“PROCESS”、“SUPER”和“RELOAD”。

3. 执行完整备份：

使用“mysqldump”命令将整个 MySQL 数据库备份到文件中：

mysqldump -u root -P 3306 -h 127.0.0.1 -p123456 ks_flask > ks_flask.sql

此处，“localhost”是MySQL主机，“root”是用户名，“ks_flask”是数据库名称。

4. 记录当前二进制日志位置：

执行首次完整备份后，请记录当前二进制日志文件名和位置，以便后续进行增量备份。

SHOW MASTER STATUS;

此命令返回当前二进制日志文件及其位置。

5. 执行增量备份：

使用“mysqlbinlog”命令，并指定“--start-datetime”、“--stop-datetime”和“--start-position”参数，以设定时间范围及上一次备份的位置。

mysqlbinlog --start-datetime='2023-10-23 00:00:00'
--stop-datetime='2023-10-23 23:59:59'
--start-position=[previous position]
mysql-bin.000001 > incr_backup_2023-10-23.sql

此处，“[先前位置]”是指上次备份时记录的二进制日志位置。

6. 记录新的二进制日志位置：

执行增量备份后，使用“SHOW MASTER STATUS”记录下一个增量备份所需的新的二进制日志文件名和位置。

SHOW MASTER STATUS;

7. 定期执行增量备份：

根据需求每日执行增量备份，确保包含前一天的更改内容。可使用自动化脚本来定期记录和更新二进制日志位置。

8. 恢复备份数据：

将完整备份和增量备份合并并还原到 MySQL：

mysql -hlocalhost -uroot -p your_database < ks_flask.sql  
mysql -hlocalhost -uroot -p your_database < incr_backup_2023-10-23.sql

如果存在多个增量备份，请按时间顺序依次还原。

如何从 MySQL 增量备份中恢复？

增量恢复有三种常用方法：常规恢复、基于位置的恢复和基于时间点的恢复。

1. 一般恢复

恢复所有已备份的二进制日志内容。

mysqlbinlog [--no-defaults] incremental backup file | mysql -u username -p password

2. 基于职位的恢复 

数据库管理员执行操作时，错误操作和正确操作可能同时发生。基于位置的恢复可实现更精确的恢复。

恢复至特定位置

mysqlbinlog --stop-position='operation ID' binary log | mysql -u username -p password

从特定位置开始恢复

mysqlbinlog --start-position='operation ID' binary log | mysql -u username -p password

3. 时间点恢复 

跳过特定的错误时间点以恢复数据。共有三种方法：

1）从日志起始位置恢复至特定时间点

mysqlbinlog [--no-defaults] --stop-datetime='YYYY-MM-DD HH:MM:SS' binary log | mysql -u username -p password

2) 从特定时间点恢复至日志末尾

mysqlbinlog [--no-defaults] --start-datetime='YYYY-MM-DD HH:MM:SS' binary log | mysql -u username -p password

3）从一个时间点恢复到另一个时间点

mysqlbinlog [--no-defaults] --start-datetime='YYYY-MM-DD HH:MM:SS' --stop-datetime='YYYY-MM-DD HH:MM:SS' binary log | mysql -u username -p password

更简单的 MySQL 增量备份创建方法

如您所见，使用二进制日志执行 MySQL 增量备份与恢复的整个过程十分复杂且耗时。由于这些操作均需通过命令行完成，在备份与还原过程中一旦出错，将无法回退。

幸运的是，功能强大的 MySQL 备份软件 Vinchin 备份与恢复提供了用户友好的界面，使操作更加便捷。

Vinchin备份与恢复软件 提供更自动化、高效且安全的解决方案，通过集中化管理、快速恢复、增量备份及灵活的存储策略，简化MySQL备份流程，确保数据长期可用。此外，它还为Oracle数据库、SQL Server、PostgreSQL、Postgres Pro和MariaDB提供灵活的备份策略，支持自动备份、差异备份和事务日志备份。

要创建 MySQL 数据库的增量备份任务，请前往 物理备份 > 数据库备份 > 备份 页面（请注意：首次备份必须为全量备份）：

1. 选择需要备份的数据库。

2. 选择一个备份节点，用于处理和存储备份数据。

3. 根据您的需求配置备份策略。

在此，您可以按计划选择增量备份。

4. 审查并确认设置。

点击下方按钮，试用 Vinchin 的15天免费试用版，体验高效可靠的数据备份与恢复解决方案！

                               

下载免费试用版

适用于多种数据备份

* 15天全功能免费安全下载

MySQL 增量备份常见问题解答

1. 在恢复增量备份前，如果二进制日志文件已被删除，会发生什么情况？

如果所需的二进制日志文件缺失，恢复过程可能不完整，从而导致数据丢失。请务必确保二进制日志保留至其不再需要为止。

2. 增量备份时，MySQL 二进制日志应保留多长时间？

这取决于您的备份策略。通常，日志应保留至新的完整备份完成并验证通过为止。

结论

建议每日刷新增量备份的二进制日志。这可确保数据库备份频率足够高，从而最大限度降低数据丢失风险。通过每日刷新增量备份，可更有效地管理数据库变更，以便在需要时恢复至最新状态。