MK
摩柯社区 - 一个极简的技术知识社区
AI 面试

MariaDB binlog手动清理方法与实践

2023-12-101.3k 阅读

MariaDB binlog 概述

binlog 是什么

在 MariaDB 数据库中，二进制日志（binlog）起着至关重要的作用。它记录了数据库执行的所有修改性操作，包括数据的插入、更新、删除，以及数据库结构的更改，例如创建表、修改表结构等操作。binlog 主要用于主从复制和数据恢复。

在主从复制场景下，主库将 binlog 发送给从库，从库通过重放这些日志来保持与主库数据的一致性。而在数据恢复时，如果数据库发生故障，可以通过重放 binlog 中的记录，将数据库恢复到故障前的某个状态。

binlog 的工作原理

MariaDB 以追加的方式写入 binlog。当一个事务开始时，并不会立即将相关操作写入 binlog，而是在事务提交时，才会将整个事务的所有操作记录写入 binlog 中。这种机制确保了事务的原子性，即要么整个事务的所有操作都被记录并应用，要么都不被记录和应用。

binlog 采用了循环写的方式，当当前的 binlog 文件大小达到一定阈值（可通过配置参数 max_binlog_size 设置）或者手动执行 FLUSH LOGS 命令时，MariaDB 会关闭当前的 binlog 文件，并创建一个新的 binlog 文件继续记录。例如，默认情况下 max_binlog_size 的值为 1073741824 字节（1GB），当一个 binlog 文件达到这个大小，就会产生新的 binlog 文件。

MariaDB binlog 清理的必要性

磁盘空间占用

随着数据库的运行，binlog 文件会不断增长。如果长时间不清理，它们将占用大量的磁盘空间。在磁盘空间有限的情况下，这可能会导致数据库服务器无法正常工作，甚至影响整个系统的稳定性。例如，当磁盘空间使用率达到 100% 时，数据库可能无法写入新的 binlog 文件，从而导致写入操作失败。

性能影响

过多的 binlog 文件也会对数据库的性能产生影响。在进行主从复制时，从库需要读取主库的 binlog 文件进行数据同步。如果 binlog 文件数量过多或者单个文件过大，会增加从库读取和解析日志的时间，进而影响主从复制的延迟。此外，数据库在启动或者进行某些维护操作时，也需要处理 binlog 文件，过多的 binlog 文件会增加这些操作的时间开销。

查看 binlog 相关信息

查看当前 binlog 文件

在 MariaDB 中，可以使用 SHOW BINARY LOGS 命令来查看当前存在的 binlog 文件列表。示例如下：

SHOW BINARY LOGS;

执行上述命令后，会得到类似以下的结果：

Log_nameFile_sizeEncrypted
mariadb-bin.000001168No
mariadb-bin.000002154No
mariadb-bin.000003154No

上述结果中，Log_name 表示 binlog 文件的名称，File_size 表示文件的大小（单位为字节），Encrypted 表示该文件是否加密（在一般情况下为 No）。

查看当前正在使用的 binlog 文件

使用 SHOW MASTER STATUS 命令可以查看当前正在使用的 binlog 文件以及当前的写入位置。示例如下：

SHOW MASTER STATUS;

执行结果类似如下：

FilePositionBinlog_Do_DBBinlog_Ignore_DBExecuted_Gtid_Set
mariadb-bin.000003154

其中，File 字段显示当前正在使用的 binlog 文件名称，Position 字段表示当前在该文件中的写入位置。

查看 binlog 配置参数

可以通过 SHOW VARIABLES LIKE 'binlog%' 命令来查看与 binlog 相关的配置参数。例如：

SHOW VARIABLES LIKE 'binlog%';

常见的配置参数及其含义如下：

  • binlog_format：指定 binlog 的格式，常见的值有 STATEMENT（基于语句记录）、ROW（基于行记录）和 MIXED（混合模式）。不同的格式在记录日志的详细程度和性能方面有所差异。
  • max_binlog_size：指定单个 binlog 文件的最大大小，达到该大小后会创建新的 binlog 文件。
  • sync_binlog：控制 binlog 写入磁盘的频率。取值为 0 时，表示由操作系统决定何时将 binlog 缓存写入磁盘；取值为 1 时，表示每次事务提交时都将 binlog 写入磁盘，这样可以保证数据的安全性，但可能会影响性能。

MariaDB binlog 手动清理方法

使用 PURGE BINARY LOGS 命令

PURGE BINARY LOGS 命令用于删除指定的 binlog 文件。该命令有两种常见的使用方式：

根据文件名删除

可以使用 PURGE BINARY LOGS TO 'log_name' 语句来删除指定文件名之前的所有 binlog 文件（不包括指定的文件）。例如，要删除 mariadb-bin.000002 之前的所有 binlog 文件，可以执行以下命令：

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

执行此命令后，mariadb-bin.000001 等更早的 binlog 文件将被删除。

根据时间删除

使用 PURGE BINARY LOGS BEFORE 'date' 语句可以删除指定日期时间之前创建的所有 binlog 文件。日期时间格式为 YYYY-MM-DD HH:MM:SS。例如，要删除 2023 年 10 月 1 日 00:00:00 之前创建的所有 binlog 文件，可以执行以下命令：

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

执行该命令后，在指定时间之前创建的 binlog 文件将被删除。

需要注意的是，在使用 PURGE BINARY LOGS 命令时要谨慎操作，确保删除的 binlog 文件不再需要用于主从复制或者数据恢复。如果误删了正在被从库使用的 binlog 文件，可能会导致主从复制中断。

使用 RESET MASTER 命令

RESET MASTER 命令会删除所有的 binlog 文件，并重新创建一个新的 binlog 文件，编号从 000001 开始。执行该命令示例如下：

RESET MASTER;

此命令通常在以下场景中使用：

  • 当不再需要之前的 binlog 文件用于任何目的，例如重新搭建主从复制环境，且希望从一个全新的状态开始记录 binlog。
  • 在进行数据库的彻底清理和重新初始化时，可以使用 RESET MASTER 命令删除所有历史 binlog 文件。

但是，使用 RESET MASTER 命令同样要非常小心，因为它会不可逆地删除所有 binlog 文件。如果有从库正在基于当前的 binlog 进行数据同步，执行此命令会导致主从复制关系完全中断，从库需要重新进行全量同步才能恢复正常。

在主从复制环境中清理 binlog

主库 binlog 清理注意事项

在主从复制环境下，主库的 binlog 清理需要格外谨慎。因为从库是通过读取主库的 binlog 来进行数据同步的。如果主库误删了从库还未读取的 binlog 文件，会导致主从复制出现故障。

为了确保安全清理主库 binlog，首先要了解从库的复制状态。可以在主库上使用 SHOW SLAVE STATUS \G 命令获取从库的相关信息，重点关注 Relay_Master_Log_File 和 Exec_Master_Log_Pos 字段。Relay_Master_Log_File 表示从库当前正在读取的主库 binlog 文件名称，Exec_Master_Log_Pos 表示从库在该文件中的执行位置。

在清理主库 binlog 时，要保证不会删除从库还在使用的 binlog 文件及其之前的文件。例如，如果从库的 Relay_Master_Log_File 为 mariadb-bin.000003，那么在主库清理 binlog 时，不能删除 mariadb-bin.000003 及更早的文件，直到确认从库已经完全同步了这些文件中的内容。

从库 binlog 清理

从库本身也会有 binlog 文件，这些 binlog 文件主要用于级联复制（即从库作为其他从库的主库时）。一般情况下，如果从库不需要作为其他从库的主库，从库上的 binlog 文件可以安全删除。

可以在从库上使用 PURGE BINARY LOGS 命令或者 RESET MASTER 命令来清理 binlog 文件。例如，要删除从库上除了当前正在使用的 binlog 文件之外的所有其他 binlog 文件，可以先使用 SHOW BINARY LOGS 命令查看当前 binlog 文件列表，找到当前正在使用的文件，假设为 mariadb - bin.000005，然后执行以下命令：

PURGE BINARY LOGS TO'mariadb - bin.000005';

这样就可以删除从库上除了 mariadb - bin.000005 之外的所有 binlog 文件。

binlog 清理的实践案例

案例一：定期清理 binlog 文件

假设我们有一个 MariaDB 数据库，每天产生的 binlog 文件大小约为 100MB。为了避免 binlog 文件占用过多磁盘空间，我们决定每周日凌晨 2:00 清理一周前的 binlog 文件。

首先，编写一个 shell 脚本，例如 clean_binlog.sh：

#!/bin/bash
DATE=$(date -d '1 week ago' +%Y-%m-%d' '00:00:00)
mysql -u root -p'password' -e "PURGE BINARY LOGS BEFORE '$DATE'"

上述脚本中，使用 date -d '1 week ago' +%Y-%m-%d' '00:00:00 获取一周前的日期时间，然后通过 mysql 命令执行 PURGE BINARY LOGS BEFORE '$DATE' 来删除一周前的 binlog 文件。

接着，将该脚本添加到系统的定时任务中。在 Linux 系统中，可以使用 crontab 命令。编辑 crontab 文件：

crontab -e

在文件中添加以下内容，表示每周日凌晨 2:00 执行该脚本：

0 2 * * 0 /path/to/clean_binlog.sh

其中，/path/to/clean_binlog.sh 是 clean_binlog.sh 脚本的实际路径。

案例二：在主从复制环境中安全清理 binlog

假设我们有一个主从复制环境，主库和从库都在运行。现在主库上 binlog 文件占用空间较大，需要进行清理。

  1. 在主库上执行 SHOW SLAVE STATUS \G 命令，查看从库的复制状态：
SHOW SLAVE STATUS \G

获取 Relay_Master_Log_File 和 Exec_Master_Log_Pos 字段的值。假设 Relay_Master_Log_File 为 mariadb - bin.000008，Exec_Master_Log_Pos 为 154。

  1. 等待从库完全同步 mariadb - bin.000008 文件中的内容。可以通过持续查看 SHOW SLAVE STATUS \G 中的 Seconds_Behind_Master 字段，如果该字段的值为 0，表示从库已经完全同步。

  2. 确认从库已经同步完成后，在主库上执行 PURGE BINARY LOGS TO'mariadb - bin.000008' 命令，删除 mariadb - bin.000008 之前的所有 binlog 文件：

PURGE BINARY LOGS TO'mariadb - bin.000008';

通过这样的步骤，可以在主从复制环境中安全地清理主库的 binlog 文件，避免对主从复制造成影响。

binlog 清理过程中的常见问题及解决方法

误删 binlog 文件导致主从复制中断

如果在主库上误删了从库还未读取的 binlog 文件，会导致主从复制中断。此时，可以通过以下步骤尝试恢复：

  1. 在从库上停止复制：
STOP SLAVE;
  1. 在主库上重新生成 binlog 文件（可以使用 FLUSH LOGS 命令），然后获取主库的新的 binlog 文件名称和位置（使用 SHOW MASTER STATUS 命令）。
  2. 在从库上重新配置主库连接信息，指定新的 binlog 文件名称和位置：
CHANGE MASTER TO
    MASTER_HOST='master_host_ip',
    MASTER_USER='replication_user',
    MASTER_PASSWORD='replication_password',
    MASTER_LOG_FILE='new_binlog_file_name',
    MASTER_LOG_POS=new_binlog_position;
  1. 在从库上启动复制：
START SLAVE;

通过上述步骤，一般可以恢复主从复制关系，但前提是从库的数据没有因为 binlog 文件的误删而丢失。

清理 binlog 后空间未释放

有时候，执行 binlog 清理命令后，磁盘空间并没有立即释放。这是因为在 Linux 系统中，文件被删除后，其占用的空间并不会立即释放，直到所有打开该文件的进程关闭对它的引用。

如果 MariaDB 进程仍然打开着已删除的 binlog 文件（例如在文件删除前，MariaDB 正在写入该文件），则磁盘空间不会立即释放。解决方法是重启 MariaDB 服务，这样 MariaDB 会关闭所有打开的文件句柄，从而释放被删除 binlog 文件占用的磁盘空间。在重启 MariaDB 服务之前，要确保数据库处于安全状态，例如可以先停止写入操作，并且确认主从复制等功能已经妥善处理。

binlog 清理命令执行失败

如果 PURGE BINARY LOGS 或 RESET MASTER 命令执行失败，可能有以下原因：

  • 权限不足：执行这些命令需要具有 SUPER 权限。确保执行命令的用户具有足够的权限。可以通过 GRANT SUPER ON *.* TO 'user'@'host'; 命令为用户授予 SUPER 权限。
  • 文件正在使用：如果 binlog 文件正在被写入或者被其他进程占用，清理命令可能会失败。可以等待一段时间，确保没有写入操作，或者停止相关进程（例如 MariaDB 服务，然后重新启动）后再尝试执行清理命令。

binlog 清理与备份策略的结合

基于 binlog 的点时间恢复（PITR）与清理

点时间恢复（PITR）是一种通过结合数据库备份和 binlog 来将数据库恢复到某个特定时间点的技术。在进行 binlog 清理时，要考虑 PITR 的需求。

如果需要使用 PITR，在清理 binlog 之前，必须确保已经备份了相应的 binlog 文件。通常的做法是定期进行数据库全量备份，同时在备份过程中记录 binlog 的位置。例如，可以使用 xtrabackup 工具进行备份，它会在备份过程中记录 binlog 的位置信息。

假设在 2023 年 10 月 10 日 12:00 进行了一次全量备份，备份工具记录了当时的 binlog 文件为 mariadb - bin.000010，位置为 154。那么在清理 binlog 时，不能删除 mariadb - bin.000010 及更早的文件，直到确认不再需要基于该备份进行 PITR。

备份策略中的 binlog 管理

在设计备份策略时，要将 binlog 清理纳入考虑。一种常见的策略是在每次全量备份后，清理备份之前的 binlog 文件。例如，每周日进行一次全量备份，备份完成后，执行 PURGE BINARY LOGS BEFORE 'backup_date'，其中 backup_date 为全量备份开始的日期时间。

同时，为了保证备份的完整性，在备份过程中可以将 binlog 文件也进行备份。例如，可以将 binlog 文件复制到另一个存储位置，然后再在主库上清理 binlog 文件。这样既可以满足数据恢复的需求，又能有效地管理 binlog 文件占用的磁盘空间。

通过合理结合 binlog 清理与备份策略，可以在保证数据安全性和可恢复性的同时，优化数据库的存储和性能。

binlog 清理对数据库性能的影响分析

清理操作本身的性能影响

PURGE BINARY LOGS 和 RESET MASTER 等 binlog 清理命令在执行时会对数据库性能产生一定影响。这些命令需要 MariaDB 对 binlog 文件进行操作，包括删除文件、更新内部的元数据等。在执行清理命令时，数据库的 I/O 操作会增加，可能会导致其他数据库操作的延迟。

例如，当执行 PURGE BINARY LOGS 命令删除大量 binlog 文件时，文件系统需要进行大量的删除操作，这会占用磁盘 I/O 资源。如果数据库同时有大量的读写请求，可能会导致读写操作的响应时间变长。为了减少这种影响，可以选择在数据库负载较低的时间段执行 binlog 清理操作。

清理后的性能提升

清理 binlog 文件后，数据库的性能通常会有所提升。一方面，磁盘空间得到释放，文件系统的性能可能会得到改善，因为文件系统在管理较少的文件时，其查找和读写操作可能会更高效。另一方面，对于主从复制环境，清理 binlog 文件可以减少从库读取和解析 binlog 的负担，从而降低主从复制的延迟。

例如，在一个繁忙的电商数据库中，经过一段时间的运行，binlog 文件占用了大量磁盘空间，主从复制延迟逐渐增大。通过定期清理 binlog 文件，释放了磁盘空间，从库的同步速度明显加快，主从复制延迟降低，从而提高了整个数据库系统的性能和稳定性。

不同 MariaDB 版本 binlog 清理的差异

MariaDB 10.0 - 10.2 版本

在 MariaDB 10.0 - 10.2 版本中，binlog 的清理机制基本相同，主要通过 PURGE BINARY LOGS 和 RESET MASTER 命令进行清理。但是，在这些版本中，对于 binlog 文件的管理和清理的一些细节与更高版本略有不同。

例如，在早期版本中，对于 binlog 文件的删除操作可能在某些情况下不够高效，尤其是在处理大量 binlog 文件时。此外，在主从复制环境下，对于 binlog 文件清理对从库的影响处理方式也可能与更高版本存在差异。在这些版本中，可能需要更加谨慎地处理主从复制关系，以确保 binlog 清理不会导致主从复制中断。

MariaDB 10.3 及更高版本

从 MariaDB 10.3 版本开始，对 binlog 的管理和清理进行了一些优化。例如，在 binlog 文件的删除操作上进行了改进，提高了删除大量 binlog 文件时的效率。同时，在主从复制环境下，对于 binlog 清理与主从复制的协同工作有了更好的支持。

在更高版本中，当执行 binlog 清理操作时，MariaDB 能够更好地检测从库的状态，避免误删从库还在使用的 binlog 文件。并且在 binlog 清理后，对于数据库性能的恢复和优化方面也有了更好的表现。例如，在处理 binlog 文件占用空间较大的场景下，10.3 及更高版本在清理 binlog 后，数据库的性能提升更为明显。

了解不同 MariaDB 版本 binlog 清理的差异，有助于数据库管理员根据实际使用的版本，采取更合适的 binlog 清理策略，确保数据库的稳定运行和性能优化。