MariaDB binlog与数据库备份恢复方案

MariaDB Binlog 基础

Binlog 是什么

Binlog（Binary Log）即二进制日志，是 MariaDB 中用于记录数据库更改操作的日志文件。与 InnoDB 存储引擎的重做日志（redo log）不同，Binlog 记录的是数据库的逻辑操作，比如执行的 SQL 语句。它主要用于主从复制（replication）以及数据恢复，确保在系统崩溃或数据丢失时能够通过重放这些日志来恢复数据库到某个时间点的状态。

Binlog 的作用

1. 数据备份与恢复：通过定期备份数据库，并结合 Binlog，可以实现基于时间点的恢复（Point - in - Time Recovery，PITR）。当出现数据丢失或损坏时，先恢复最近的全量备份，然后重放备份之后生成的 Binlog，将数据库恢复到故障前的某个时刻。
2. 主从复制：在主从复制架构中，主库将数据库的更改记录到 Binlog 中，从库通过读取主库的 Binlog 并在本地重放这些更改，从而保持与主库的数据一致性。这使得 MariaDB 能够实现高可用性和负载均衡。

Binlog 的格式

MariaDB 支持三种 Binlog 格式：

1. Statement - based replication（SBR）：在这种格式下，Binlog 记录的是实际执行的 SQL 语句。例如，执行 UPDATE users SET age = age + 1 WHERE gender = 'Male';，Binlog 中就记录这条 SQL 语句。优点是日志量小，因为只记录语句而非每一行的变化。缺点是在某些情况下可能导致主从数据不一致，比如使用了一些不确定函数（如 NOW()），主从库执行结果可能不同。
2. Row - based replication（RBR）：Row - based 格式下，Binlog 记录的是每一行数据的实际更改。对于上述 UPDATE 语句，Binlog 会记录每一个符合条件的 users 表行的旧值和新值。优点是数据一致性更高，尤其适用于复杂的复制场景。缺点是日志量较大，因为要记录每一行的变化。
3. Mixed - based replication（MBR）：这是一种混合模式，MariaDB 会根据执行的 SQL 语句自动选择使用 Statement - based 还是 Row - based 格式。对于大多数语句，使用 Statement - based 以减少日志量；对于可能导致主从不一致的语句（如包含不确定函数的语句），则使用 Row - based 格式。

MariaDB Binlog 配置

开启 Binlog

要开启 Binlog，需要在 MariaDB 的配置文件（通常是 my.cnf 或 my.ini）中进行配置。在 [mysqld] 部分添加或修改以下参数：

log - bin = /var/lib/mysql/mysql - bin
server - id = 1

• log - bin 参数指定了 Binlog 文件的路径和前缀。上述配置中，Binlog 文件将存储在 /var/lib/mysql/ 目录下，文件名为 mysql - bin.xxxxxx（xxxxxx 为序列号）。
• server - id 是每个 MariaDB 实例的唯一标识符，在主从复制环境中，每个节点的 server - id 必须不同。

修改配置文件后，重启 MariaDB 服务使配置生效：

sudo systemctl restart mariadb

查看 Binlog 配置

可以通过以下命令查看当前 MariaDB 实例的 Binlog 配置：

SHOW VARIABLES LIKE 'log_bin';
SHOW VARIABLES LIKE'server_id';

SHOW VARIABLES LIKE 'log_bin'; 命令用于查看 Binlog 是否开启，结果为 ON 表示已开启，OFF 表示未开启。SHOW VARIABLES LIKE'server_id'; 命令用于查看当前实例的 server - id。

设置 Binlog 格式

要设置 Binlog 格式，可以在配置文件的 [mysqld] 部分添加或修改 binlog - format 参数：

binlog - format = ROW

这里设置为 ROW 格式，也可以根据需求设置为 STATEMENT 或 MIXED。同样，修改配置后需要重启 MariaDB 服务。

也可以在运行时通过 SQL 语句临时修改 Binlog 格式：

SET GLOBAL binlog_format = 'ROW';

但这种方式只在当前会话有效，重启 MariaDB 后会恢复到配置文件中的设置。

操作 MariaDB Binlog

查看当前 Binlog 文件

可以使用以下 SQL 语句查看当前正在写入的 Binlog 文件以及当前位置：

SHOW MASTER STATUS;

示例输出：

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql - bin.000003 |     154   |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

其中，File 列表示当前 Binlog 文件名称，Position 列表示当前写入位置。

刷新 Binlog

有时候需要手动刷新 Binlog，生成一个新的 Binlog 文件。可以使用以下 SQL 语句：

FLUSH LOGS;

执行该语句后，MariaDB 会关闭当前的 Binlog 文件并创建一个新的 Binlog 文件，序列号会自动递增。

清除 Binlog

清除 Binlog 可以释放磁盘空间，但需要谨慎操作，因为清除 Binlog 后可能无法进行基于 Binlog 的数据恢复。有两种主要的方法清除 Binlog：

1. 删除指定之前的 Binlog 文件：

PURGE BINARY LOGS TO'mysql - bin.000002';

上述命令会删除 mysql - bin.000002 及之前的所有 Binlog 文件。

2. 删除指定日期之前的 Binlog 文件：

PURGE BINARY LOGS BEFORE '2023 - 10 - 01 12:00:00';

这条命令会删除指定日期和时间之前创建的所有 Binlog 文件。

MariaDB 数据库备份方案

全量备份

1. 使用 mysqldump 工具：
   • mysqldump 是 MariaDB 提供的一个用于备份数据库的工具，它可以将数据库中的数据和结构以 SQL 语句的形式导出到文件中。
   • 备份整个数据库的命令如下：

mysqldump - u username - p database_name > backup.sql

其中，username 是 MariaDB 的用户名，database_name 是要备份的数据库名称，backup.sql 是备份文件的名称。执行命令后，系统会提示输入密码。

• 如果要备份多个数据库，可以使用 --databases 选项：

mysqldump - u username - p --databases database1 database2 > multi_backup.sql

• 要备份所有数据库，可以使用 --all - databases 选项：

mysqldump - u username - p --all - databases > all_backup.sql

2. 使用 mariabackup 工具（适用于 InnoDB 存储引擎）：
   • mariabackup 是 MariaDB 官方提供的热备份工具，支持 InnoDB 和 XtraDB 存储引擎的在线备份。它通过拷贝物理文件的方式进行备份，速度快且对数据库性能影响较小。
   • 安装 mariabackup：在大多数 Linux 发行版中，可以通过包管理器安装，例如在 CentOS 上：

yum install mariadb - backup

• 进行全量备份的命令如下：

mariabackup --user = username --password = password --backup --target - dir = /path/to/backup

其中，username 和 password 是 MariaDB 的用户名和密码，/path/to/backup 是备份文件存储的目录。备份完成后，该目录下会包含数据库的物理文件以及一些元数据文件。

增量备份

1. 基于 Binlog 的增量备份原理：由于 Binlog 记录了数据库的所有更改，我们可以通过记录上次备份后的 Binlog 来实现增量备份。在进行全量备份后，记录下当时的 Binlog 文件和位置。之后的增量备份就是获取从上次备份位置到当前的 Binlog 内容。
2. 使用 mysqlbinlog 工具提取 Binlog 进行增量备份：
   • 假设我们已经进行了全量备份，记录下全量备份时的 Binlog 文件为 mysql - bin.000003，位置为 154。现在要进行增量备份，可以使用以下命令：

mysqlbinlog --start - position = 154 mysql - bin.000003 > incremental_backup.sql

这条命令会从 mysql - bin.000003 文件的 154 位置开始提取 Binlog 内容并输出到 incremental_backup.sql 文件中。如果在全量备份后有新的 Binlog 文件生成，还需要依次处理新的 Binlog 文件。例如，新生成了 mysql - bin.000004，则需要继续执行：

mysqlbinlog mysql - bin.000004 > incremental_backup.sql

将新 Binlog 文件的内容追加到增量备份文件中。

MariaDB 数据库恢复方案

基于全量备份恢复

1. 使用 mysqldump 备份恢复：如果备份是使用 mysqldump 生成的 SQL 文件，可以使用 mysql 命令进行恢复。例如，恢复之前备份的 backup.sql 文件：

mysql - u username - p database_name < backup.sql

输入密码后，mysql 命令会执行 backup.sql 中的 SQL 语句，将数据库恢复到备份时的状态。 2. 使用 mariabackup 备份恢复：

• 恢复 mariabackup 备份的步骤如下：
  • 准备恢复目录：

mkdir /path/to/restore

 - 解压备份文件：

mariabackup --copy - back --target - dir = /path/to/backup --datadir = /path/to/restore

这里 --copy - back 选项表示将备份文件恢复到指定的 --datadir 目录。 - 修改恢复目录的权限：

chown - R mysql:mysql /path/to/restore

假设 MariaDB 运行的用户是 mysql。 - 重启 MariaDB 服务，使用恢复后的目录作为数据目录。可以通过修改 MariaDB 配置文件中的 datadir 参数来指定新的数据目录：

[mysqld]
datadir = /path/to/restore

然后重启 MariaDB 服务：

sudo systemctl restart mariadb

基于全量备份和增量备份恢复（Point - in - Time Recovery，PITR）

1. 恢复全量备份：首先按照上述基于全量备份恢复的方法，恢复最近的全量备份。例如，如果是使用 mariabackup 备份的，先执行恢复全量备份的步骤。
2. 应用增量备份（重放 Binlog）：
   • 如果增量备份是通过提取 Binlog 生成的 SQL 文件，可以使用 mysql 命令依次重放这些文件。假设全量备份后有两个增量备份文件 incremental1.sql 和 incremental2.sql：

mysql - u username - p database_name < incremental1.sql
mysql - u username - p database_name < incremental2.sql

• 如果增量备份是直接使用 Binlog 文件，可以使用 mysqlbinlog 和 mysql 配合来重放 Binlog。例如，有全量备份后生成的 Binlog 文件 mysql - bin.000003（从全量备份位置开始）和 mysql - bin.000004：

mysqlbinlog mysql - bin.000003 mysql - bin.000004 | mysql - u username - p database_name

这条命令会将两个 Binlog 文件的内容重放，使数据库恢复到最近的增量备份时刻。

备份恢复方案的优化与注意事项

备份性能优化

1. 并行备份：mariabackup 工具支持并行备份，可以通过 --parallel 选项指定并行线程数。例如：

mariabackup --user = username --password = password --backup --target - dir = /path/to/backup --parallel = 4

这样可以加快备份速度，尤其适用于大数据量的情况。 2. 压缩备份：mariabackup 支持压缩备份，可以通过 --compress 选项启用。例如：

mariabackup --user = username --password = password --backup --target - dir = /path/to/backup --compress

压缩备份可以减少备份文件占用的磁盘空间，但会增加备份和恢复时的 CPU 开销。

恢复性能优化

1. 优化 Binlog 重放：在重放 Binlog 时，可以通过调整 MariaDB 的配置参数来提高性能。例如，增加 innodb_log_buffer_size 参数的值可以减少日志写入磁盘的次数，从而加快重放速度。在配置文件 my.cnf 的 [mysqld] 部分添加或修改：

innodb_log_buffer_size = 64M

2. 并行恢复：对于 mariabackup 恢复，同样可以使用并行恢复。在恢复时可以通过 --parallel 选项指定并行线程数，例如：

mariabackup --copy - back --target - dir = /path/to/backup --datadir = /path/to/restore --parallel = 4

注意事项

1. 备份频率：需要根据业务需求和数据重要性确定合适的备份频率。对于数据变化频繁且重要的系统，可能需要更频繁的备份，甚至可以每小时或更短时间进行一次增量备份。
2. 备份存储：备份文件需要妥善存储，建议存储在不同的物理位置，以防止因存储设备故障导致备份数据丢失。可以使用磁带、异地存储等方式。
3. 测试恢复：定期进行备份恢复测试，确保备份数据的可用性。在测试环境中模拟数据丢失场景，验证恢复过程是否顺利，以及恢复后的数据是否完整和正确。
4. Binlog 管理：合理管理 Binlog 文件，避免 Binlog 文件占用过多磁盘空间。同时，在清除 Binlog 文件时要谨慎操作，确保不会影响基于 Binlog 的恢复和主从复制。

通过以上对 MariaDB Binlog 以及数据库备份恢复方案的详细介绍，希望能帮助读者更好地理解和实施 MariaDB 数据库的数据保护策略，确保数据的安全性和可用性。在实际应用中，需要根据具体的业务场景和需求，灵活选择和优化备份恢复方案。