MariaDB 开启 binlog 的最佳实践

MariaDB 简介

MariaDB 是一个基于 MySQL 开发的开源关系型数据库管理系统，由 MySQL 的原开发者主导开发。它与 MySQL 高度兼容，在性能、稳定性和功能特性上不断进行优化和扩展，被广泛应用于各种规模的 Web 应用、企业级应用等场景中。

binlog 介绍

1. 什么是 binlog
   • binlog（Binary Log），即二进制日志，是 MariaDB 用于记录数据库更改操作的日志文件。它记录了所有对数据库数据进行修改的 SQL 语句（如 INSERT、UPDATE、DELETE 等），以及数据库结构变更语句（如 CREATE、ALTER 等），但不记录 SELECT 等查询语句。
   • binlog 主要用于数据备份、数据恢复以及主从复制等场景。在主从复制中，主库将 binlog 发送给从库，从库通过重放 binlog 中的记录来保持与主库的数据一致性。
2. binlog 的工作原理
   • 当 MariaDB 执行一个修改数据的事务时，相关的操作记录会被写入 binlog 缓存中。当事务提交时，binlog 缓存中的内容会被刷新到 binlog 文件中。
   • binlog 采用追加写的方式，不会覆盖原有记录，随着数据库操作的不断进行，binlog 文件会逐渐增大。MariaDB 会根据配置对 binlog 文件进行切换和管理，例如按照文件大小或者时间周期进行切换。

MariaDB 开启 binlog 的准备工作

1. 检查 MariaDB 版本
   • MariaDB 不同版本对 binlog 的支持和配置方式可能略有差异。可以通过以下命令检查 MariaDB 的版本：

   SELECT VERSION();
   

   • 确保使用的 MariaDB 版本支持 binlog 功能，一般较新的版本都能很好地支持。
2. 确定配置文件位置
   • MariaDB 的配置文件通常位于 /etc/mysql/mariadb.conf.d/50 - server.cnf 或者 /etc/my.cnf 等位置，不同的操作系统和安装方式可能会有所不同。
   • 可以通过以下命令查找配置文件：

   mysql --help | grep 'Default options' -A 1
   

   • 该命令会输出类似如下信息：

   Default options are read from the following files in the given order:
   /etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf
   

   • 从输出中找到实际使用的配置文件路径。

开启 binlog 的配置步骤

1. 编辑配置文件
   • 使用文本编辑器（如 vim）打开找到的 MariaDB 配置文件：

   sudo vim /etc/mysql/mariadb.conf.d/50 - server.cnf
   

2. 添加 binlog 相关配置
   • 在配置文件的 [mysqld] 部分添加或修改以下配置参数：

   [mysqld]
   log - bin=/var/log/mysql/mysql - bin.log
   server - id = 1
   binlog - format = ROW
   

   • log - bin：指定 binlog 文件的路径和前缀。这里设置为 /var/log/mysql/mysql - bin.log，意味着 binlog 文件将存储在 /var/log/mysql/ 目录下，文件名为 mysql - bin.log 及其后续编号文件（如 mysql - bin.000001，mysql - bin.000002 等）。请确保 MariaDB 服务对该目录有写入权限。如果目录不存在，需要先创建并设置合适的权限：

   sudo mkdir -p /var/log/mysql
   sudo chown mysql:mysql /var/log/mysql
   sudo chmod 750 /var/log/mysql
   

   • server - id：每个参与主从复制或者使用 binlog 的 MariaDB 实例都需要有一个唯一的标识。这里设置为 1，在生产环境中，如果有多个 MariaDB 实例，每个实例的 server - id 必须不同，取值范围是 1 到 2^32 - 1。
   • binlog - format：指定 binlog 的格式，有三种取值：STATEMENT、ROW 和 MIXED。
     • STATEMENT：基于语句的格式，记录的是实际执行的 SQL 语句。这种格式的优点是日志文件相对较小，因为只记录语句而非数据本身。但在一些情况下可能会导致主从复制不一致，例如使用了一些不确定的函数（如 NOW()），在主库和从库执行时可能得到不同的结果。
     • ROW：基于行的格式，记录的是每一行数据的实际更改。这种格式能确保主从复制的高度一致性，但日志文件会相对较大，因为要记录每行数据的变化。
     • MIXED：混合格式，MariaDB 会根据具体的 SQL 语句自动选择使用 STATEMENT 或者 ROW 格式。一般情况下使用 STATEMENT 格式，当遇到可能导致主从复制不一致的语句时，自动切换到 ROW 格式。在大多数场景下，ROW 格式是比较推荐的，因为它能提供更可靠的主从复制保障。
3. 保存并退出配置文件
   • 在 vim 编辑器中，按下 Esc 键，输入 :wq 并回车，保存配置文件并退出。

重启 MariaDB 服务

1. 重启服务命令
   • 在不同的操作系统上，重启 MariaDB 服务的命令有所不同。
   • 在 Ubuntu 系统上：

   sudo systemctl restart mariadb
   

   • 在 CentOS 系统上：

   sudo service mariadb restart
   

2. 检查服务状态
   • 重启服务后，可以使用以下命令检查 MariaDB 服务是否正常启动：
   • 在 Ubuntu 系统上：

   sudo systemctl status mariadb
   

   • 在 CentOS 系统上：

   sudo service mariadb status
   

   • 如果服务正常启动，输出中会显示 active (running) 等类似信息。

验证 binlog 是否开启

1. 登录 MariaDB
   • 使用以下命令登录 MariaDB 数据库：

   mysql -u root -p
   

   • 输入密码后进入 MariaDB 命令行界面。
2. 查看 binlog 状态
   • 在 MariaDB 命令行中，执行以下命令查看 binlog 相关信息：

   SHOW VARIABLES LIKE 'log_bin';
   

   • 如果 binlog 已开启，输出结果如下：

   +---------------+-------+
   | Variable_name | Value |
   +---------------+-------+
   | log_bin       | ON    |
   +---------------+-------+
   

   • 还可以通过以下命令查看当前正在使用的 binlog 文件和位置：

   SHOW MASTER STATUS;
   

   • 输出类似如下信息：

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

   • 其中 File 表示当前使用的 binlog 文件，Position 表示当前 binlog 文件中的位置。

binlog 的高级配置

1. binlog 缓存相关配置
   • binlog_cache_size：该参数设置每个线程用于 binlog 缓存的内存大小，单位是字节。默认值通常是 32768（32KB）。对于一些包含大量数据修改的事务，可能需要适当增大该值，以避免多次从磁盘读取和写入 binlog 缓存。例如，可以在配置文件 [mysqld] 部分添加：

   binlog_cache_size = 65536 # 设置为 64KB
   

   • max_binlog_cache_size：限制单个事务可以使用的最大 binlog 缓存大小。如果一个事务所需的 binlog 缓存超过了这个值，MariaDB 会将超出部分写入临时文件。默认值是 18446744073709547520（2^63 - 2^16）字节，在大多数情况下，不需要修改该值。但如果遇到事务因 binlog 缓存不足而写入临时文件导致性能问题，可以适当增大该值。
2. binlog 过期策略
   • expire_logs_days：设置 binlog 文件自动删除的天数。例如，在配置文件 [mysqld] 部分添加：

   expire_logs_days = 7
   

   • 表示 binlog 文件在生成 7 天后会被自动删除。这有助于控制磁盘空间的使用，避免 binlog 文件无限增长。
3. binlog 同步策略
   • sync_binlog：该参数控制 binlog 缓存刷新到磁盘的频率。取值有 0、1 和 N（N 为大于 1 的整数）。
     • sync_binlog = 0：表示 MariaDB 不主动将 binlog 缓存同步到磁盘，而是由操作系统负责缓存刷新。这种方式性能最高，但在系统崩溃时可能会丢失部分 binlog 数据。
     • sync_binlog = 1：表示每次事务提交时，都将 binlog 缓存同步到磁盘。这能保证数据的完整性，但会对性能有一定影响，因为每次提交都涉及磁盘 I/O 操作。
     • sync_binlog = N：表示每 N 次事务提交后，将 binlog 缓存同步到磁盘。这种方式在性能和数据安全性之间取得了一定的平衡。例如 sync_binlog = 10，意味着每 10 次事务提交后执行一次磁盘同步操作。

binlog 在主从复制中的应用

1. 主库配置
   • 除了前面开启 binlog 的基本配置外，在主库上还需要配置 log - bin 和 server - id。假设主库的 server - id 为 1，配置如下：

   [mysqld]
   log - bin=/var/log/mysql/mysql - bin.log
   server - id = 1
   binlog - format = ROW
   

   • 重启 MariaDB 服务使配置生效。然后登录 MariaDB，执行以下命令获取主库的 binlog 信息：

   SHOW MASTER STATUS;
   

   • 记录下输出中的 File 和 Position 值，后续从库配置时会用到。
2. 从库配置
   • 在从库的 MariaDB 配置文件 [mysqld] 部分设置 server - id，且不能与主库的 server - id 相同，例如设置为 2：

   [mysqld]
   server - id = 2
   

   • 重启 MariaDB 服务。登录从库的 MariaDB，执行以下命令配置主从复制：

   CHANGE MASTER TO
       MASTER_HOST='主库IP地址',
       MASTER_USER='主从复制用户名',
       MASTER_PASSWORD='主从复制密码',
       MASTER_LOG_FILE='主库 SHOW MASTER STATUS 输出中的 File 值',
       MASTER_LOG_POS=主库 SHOW MASTER STATUS 输出中的 Position 值;
   

   • 例如：

   CHANGE MASTER TO
       MASTER_HOST='192.168.1.100',
       MASTER_USER='repl_user',
       MASTER_PASSWORD='repl_password',
       MASTER_LOG_FILE='mysql - bin.000001',
       MASTER_LOG_POS=154;
   

   • 配置完成后，启动从库的复制线程：

   START SLAVE;
   

   • 可以通过以下命令查看从库复制状态：

   SHOW SLAVE STATUS \G;
   

   • 重点关注 Slave_IO_Running 和 Slave_SQL_Running 是否都为 Yes，以及 Seconds_Behind_Master 是否为 0 或接近 0。如果 Slave_IO_Running 或 Slave_SQL_Running 为 No，则需要根据错误信息排查问题。常见问题包括网络连接问题、用户名密码错误、主从库版本不兼容等。

binlog 备份与恢复

1. 基于 binlog 的增量备份
   • 可以结合 MariaDB 的 FLUSH LOGS 命令和文件复制工具（如 rsync）进行 binlog 增量备份。
   • 首先在 MariaDB 中执行 FLUSH LOGS 命令，该命令会使 MariaDB 切换到新的 binlog 文件，这样可以确保当前正在使用的 binlog 文件已经完整记录了之前的所有更改。

   FLUSH LOGS;
   

   • 然后使用 rsync 等工具将当前 binlog 文件复制到备份存储位置：

   rsync -avz /var/log/mysql/mysql - bin.000001 /backup/mysql_binlogs/
   

   • 定期执行上述步骤，就可以实现 binlog 的增量备份。
2. 基于 binlog 的恢复
   • 假设已经有了全量备份和一系列 binlog 增量备份。在进行恢复时，首先恢复全量备份，例如通过 mysqlpump 或 mysqldump 工具的备份文件进行恢复。
   • 然后按照 binlog 备份的顺序，依次重放 binlog 文件中的记录，以恢复到故障前的状态。可以使用 mysqlbinlog 工具结合 mysql 命令来重放 binlog。
   • 例如，假设全量备份已经恢复，要重放 mysql - bin.000001 binlog 文件：

   mysqlbinlog /var/log/mysql/mysql - bin.000001 | mysql -u root -p
   

   • 输入密码后，mysqlbinlog 会将 binlog 文件中的记录解析并发送给 mysql 客户端执行，从而实现数据恢复。

binlog 性能优化

1. 合理调整 binlog 格式
   • 如前文所述，STATEMENT 格式日志文件较小，但可能导致主从复制不一致；ROW 格式能保证一致性，但日志文件较大。在选择 binlog 格式时，要根据应用场景进行权衡。如果应用中涉及大量的不确定函数或者复杂的存储过程调用，建议使用 ROW 格式；如果应用对日志文件大小较为敏感，且不存在主从复制一致性问题的场景，可以考虑 STATEMENT 格式。
2. 优化 binlog 缓存配置
   • 根据事务的大小和频率，合理调整 binlog_cache_size 和 max_binlog_cache_size。对于大型事务，可以适当增大 binlog_cache_size，减少磁盘 I/O 操作。但也不能设置过大，以免浪费内存资源。可以通过监控系统性能指标（如磁盘 I/O 使用率、内存使用率等）来逐步调整到合适的值。
3. 控制 binlog 同步频率
   • 在对数据安全性要求极高的场景下，sync_binlog = 1 是必要的。但在一些对性能要求较高且能接受一定数据丢失风险的场景下，可以将 sync_binlog 设置为大于 1 的值，如 sync_binlog = 10，以减少磁盘 I/O 次数，提高性能。不过在调整该参数后，要密切关注系统的稳定性和数据一致性。

binlog 相关常见问题及解决方法

1. binlog 文件增长过快
   • 原因分析：可能是数据库中频繁进行大量的数据修改操作，或者 binlog 过期策略设置不合理。
   • 解决方法：检查数据库的业务逻辑，优化 SQL 语句，减少不必要的数据修改。同时，合理设置 expire_logs_days 参数，确保 binlog 文件能按时删除。如果 binlog 文件已经过大，可以通过 PURGE BINARY LOGS 命令手动删除不需要的 binlog 文件，但在生产环境中执行该命令要非常谨慎，以免影响主从复制或数据恢复。

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

   • 上述命令会删除 mysql - bin.000005 及之前的所有 binlog 文件。
2. 主从复制中 binlog 同步问题
   • 原因分析：可能是网络问题、主从库配置不一致（如 server - id 重复、binlog 格式不匹配等）、主从库账号权限问题等。
   • 解决方法：检查网络连接是否正常，确保主从库之间能正常通信。核对主从库的配置，保证 server - id 唯一且 binlog 格式一致。检查主从复制账号的权限，确保从库账号有足够的权限连接主库并获取 binlog 信息。如果从库复制出现错误，可以通过 SHOW SLAVE STATUS \G 命令查看详细的错误信息，根据错误提示进行排查和修复。

总结 binlog 在 MariaDB 中的重要性及最佳实践

1. 重要性
   • binlog 在 MariaDB 中扮演着至关重要的角色，它为数据备份、恢复以及主从复制提供了基础。通过记录数据库的更改操作，binlog 确保了在系统故障、数据丢失等情况下能够快速恢复数据，同时保证了主从复制环境中数据的一致性。
2. 最佳实践
   • 在开启 binlog 时，要正确配置 log - bin、server - id 和 binlog - format 等参数。选择合适的 binlog 格式，一般推荐 ROW 格式以确保主从复制的可靠性。
   • 合理配置 binlog 缓存、过期策略和同步策略，以平衡性能和数据安全性。定期进行 binlog 备份，并结合全量备份实现数据的完整恢复。
   • 在主从复制场景中，仔细配置主库和从库的相关参数，确保 binlog 能够正确同步。及时处理 binlog 相关的常见问题，保证 MariaDB 数据库的稳定运行。

通过遵循这些最佳实践，可以充分发挥 binlog 在 MariaDB 中的作用，提高数据库系统的可靠性、可恢复性和性能。