MariaDB复制技术性能调优与故障排查

MariaDB 复制技术概述

MariaDB 复制是一种将数据从一个 MariaDB 数据库服务器（主服务器）复制到一个或多个其他 MariaDB 数据库服务器（从服务器）的过程。这种技术在很多场景下都非常有用，例如实现数据备份、分担读负载以及高可用性等。

在 MariaDB 复制架构中，主服务器记录所有影响数据的修改操作到二进制日志（binary log）中。从服务器通过 I/O 线程连接到主服务器，读取主服务器的二进制日志，并将其记录到自己的中继日志（relay log）中。然后，从服务器的 SQL 线程读取中继日志并在本地执行其中记录的操作，从而保持与主服务器的数据同步。

复制技术的基本配置

1. 主服务器配置
   • 打开 MariaDB 配置文件（通常是 /etc/my.cnf 或 /etc/mysql/my.cnf）。
   • 确保以下配置项设置正确：

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

server - id 是每个服务器在复制拓扑中的唯一标识符，这里设置为 1。log - bin 配置了二进制日志的路径和文件名前缀。binlog - format 设置为 ROW 格式，这种格式在记录二进制日志时以行记录数据修改，相比其他格式更节省空间且更安全。

重启 MariaDB 服务使配置生效：

sudo systemctl restart mariadb

2. 从服务器配置
   • 同样打开 MariaDB 配置文件，并设置：

[mysqld]
server - id = 2

这里 server - id 设置为 2，确保与主服务器不同。

重启 MariaDB 服务：

sudo systemctl restart mariadb

3. 配置从服务器连接主服务器 在从服务器上登录 MariaDB 客户端：

mysql - u root - p

执行以下命令配置主服务器连接信息：

CHANGE MASTER TO
    MASTER_HOST='主服务器 IP 地址',
    MASTER_USER='复制用户',
    MASTER_PASSWORD='复制用户密码',
    MASTER_LOG_FILE='主服务器二进制日志文件名',
    MASTER_LOG_POS=主服务器二进制日志位置;

例如：

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，这表示复制正常运行。

MariaDB 复制技术性能调优

1. 网络优化
   • 降低网络延迟：复制过程中，主从服务器之间需要频繁传输二进制日志和其他复制相关信息。确保主从服务器之间的网络延迟尽可能低。可以通过优化网络拓扑、使用高速网络设备以及调整网络配置来实现。例如，避免网络拥塞，合理设置网络带宽分配。
   • 使用可靠的网络连接：不稳定的网络连接可能导致复制中断或数据丢失。建议使用冗余网络连接（如双网卡绑定）或网络负载均衡设备来提高网络可靠性。
2. 主服务器性能优化
   • 优化二进制日志写入：
     • 调整日志刷盘策略：MariaDB 提供了 sync_binlog 参数来控制二进制日志的刷盘频率。默认值为 1，表示每次事务提交时都将二进制日志刷盘到磁盘，这保证了数据的一致性，但会对性能有一定影响。如果可以接受在系统崩溃时可能丢失少量事务数据，可以将其设置为大于 1 的值，例如 100，表示每 100 次事务提交将二进制日志刷盘一次。修改 my.cnf 文件：

[mysqld]
sync_binlog = 100

然后重启 MariaDB 服务。 - 合理设置日志大小：max_binlog_size 参数定义了单个二进制日志文件的最大大小。如果设置过小，会导致频繁切换日志文件，增加 I/O 开销；如果设置过大，在恢复或复制时可能需要处理更大的文件。一般建议根据服务器的负载和可用磁盘空间设置一个合适的值，例如 100M：

[mysqld]
max_binlog_size = 100M

- **优化事务处理**：
  - **减少大事务**：大事务会占用大量系统资源，并且在复制时可能导致从服务器长时间落后。尽量将大事务拆分成多个小事务。例如，在批量插入数据时，可以分批进行插入操作。

import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password",
    database="test"
)

mycursor = mydb.cursor()
data = [(1, 'a'), (2, 'b'), (3, 'c')]
batch_size = 100
for i in range(0, len(data), batch_size):
    batch = data[i:i + batch_size]
    sql = "INSERT INTO my_table (id, value) VALUES (%s, %s)"
    mycursor.executemany(sql, batch)
    mydb.commit()

  - **优化事务隔离级别**：不同的事务隔离级别对性能有不同影响。在大多数读多写少的场景下，可以将事务隔离级别设置为 `READ - COMMITTED`，相比默认的 `REPEATABLE - READ` 有更好的性能。在 `my.cnf` 文件中添加：

[mysqld]
transaction - isolation = READ - COMMITTED

然后重启 MariaDB 服务。 3. 从服务器性能优化 - 优化中继日志处理： - 调整中继日志刷盘策略：类似于主服务器的二进制日志，从服务器的中继日志也有刷盘策略。sync_relay_log 参数控制中继日志的刷盘频率，默认值为 0，表示由操作系统决定刷盘时机。可以根据实际情况设置为 1 或其他合适的值。在 my.cnf 文件中添加：

[mysqld]
sync_relay_log = 100

  - **合理设置中继日志大小**：`relay_log_space_limit` 参数定义了中继日志占用空间的最大限制。如果设置过小，可能导致中继日志频繁切换，增加 I/O 开销；如果设置过大，可能会占用过多磁盘空间。可以根据从服务器的负载和磁盘空间设置合适的值，例如 1G：

[mysqld]
relay_log_space_limit = 1G

- **并行复制**：从 MariaDB 10.0 版本开始支持并行复制。通过并行执行中继日志中的事务，可以显著提高从服务器的复制速度。在 `my.cnf` 文件中添加以下配置启用并行复制：

[mysqld]
slave - parallel - type = LOGICAL_CLOCK
slave - parallel - workers = 4

slave - parallel - type 设置为 LOGICAL_CLOCK 表示基于逻辑时钟的并行复制方式。slave - parallel - workers 设置并行复制的线程数，这里设置为 4。

MariaDB 复制故障排查

1. 复制中断问题
   • 检查网络连接：如果 Slave_IO_Running 为 No，首先检查主从服务器之间的网络连接。可以使用 ping 命令测试网络连通性，以及 telnet 命令检查 MariaDB 服务端口是否开放。例如，在从服务器上执行 telnet 主服务器 IP 地址 3306，如果无法连接，可能是网络故障或防火墙阻止了连接。需要检查防火墙规则，确保允许主从服务器之间的 MariaDB 端口通信。
   • 检查复制用户权限：确保从服务器使用的复制用户在主服务器上有足够的权限。可以在主服务器上执行 SHOW GRANTS FOR '复制用户'@'从服务器 IP 地址'; 命令查看权限。复制用户至少需要具备 REPLICATION SLAVE 权限。如果权限不足，可以使用以下命令授予权限：

GRANT REPLICATION SLAVE ON *.* TO '复制用户'@'从服务器 IP 地址' IDENTIFIED BY '复制用户密码';
FLUSH PRIVILEGES;

- **检查二进制日志和中继日志**：如果 `Slave_IO_Running` 为 `Yes` 但 `Slave_SQL_Running` 为 `No`，可能是中继日志在执行过程中出现错误。查看 `SHOW SLAVE STATUS \G` 输出中的 `Last_Error` 字段，了解具体的错误信息。例如，如果提示 `Error 'Table 'xxx' doesn't exist'`，可能是主服务器上的表结构发生了变化，但从服务器还没有同步到。可以尝试在从服务器上手动创建该表，然后使用 `START SLAVE;` 命令重新启动复制。

2. 数据不一致问题 - 检查复制延迟：长时间的复制延迟可能导致数据不一致。查看 SHOW SLAVE STATUS \G 输出中的 Seconds_Behind_Master 字段，如果该值较大，说明从服务器落后主服务器较多。可以通过优化网络、调整主从服务器性能等方式减少延迟。例如，增加从服务器的并行复制线程数，优化主服务器的事务处理等。 - 手动同步数据：如果发现数据不一致，可以尝试手动同步数据。一种方法是在主服务器上执行 FLUSH TABLES WITH READ LOCK; 命令，锁定所有表，然后执行 SHOW MASTER STATUS; 获取当前二进制日志文件名和位置。在从服务器上执行 STOP SLAVE;，然后使用 CHANGE MASTER TO 命令重新配置主服务器连接信息，指定获取到的二进制日志文件名和位置，最后执行 START SLAVE; 重新启动复制。操作完成后，在主服务器上执行 UNLOCK TABLES; 解除表锁定。

-- 主服务器操作
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
-- 记录下 File 和 Position 的值
UNLOCK TABLES;

-- 从服务器操作
STOP SLAVE;
CHANGE MASTER TO
    MASTER_HOST='主服务器 IP 地址',
    MASTER_USER='复制用户',
    MASTER_PASSWORD='复制用户密码',
    MASTER_LOG_FILE='记录的 File 值',
    MASTER_LOG_POS=记录的 Position 值;
START SLAVE;

- **使用校验工具**：可以使用一些工具来校验主从服务器之间的数据一致性。例如，`pt - table - checksum` 是 Percona Toolkit 中的一个工具，可以计算主从服务器表的校验和并进行比较，从而发现数据不一致的情况。安装 Percona Toolkit 后，使用以下命令进行校验：

pt - table - checksum --user=root --password=password --host=主服务器 IP 地址 --recursion - method dsn='dbi:mysql:;host=从服务器 IP 地址;user=root;password=password'

根据工具输出的结果进行相应处理。

3. 性能问题排查
   • 监控系统资源：使用系统工具如 top、iostat 等监控主从服务器的 CPU、内存、磁盘 I/O 等资源使用情况。如果 CPU 使用率过高，可能是查询过于复杂或并行复制线程设置不合理；如果磁盘 I/O 繁忙，可能是日志写入或读取频繁，可以调整日志相关参数。例如，通过 top 命令查看 CPU 使用率，如果发现 MariaDB 进程占用大量 CPU，可以进一步分析查询语句，优化索引等。
   • 分析查询语句：在主从服务器上使用 SHOW PROCESSLIST; 命令查看当前执行的查询语句。对于耗时较长的查询，使用 EXPLAIN 命令分析其执行计划，找出性能瓶颈。例如，如果发现某个查询没有使用合适的索引，可以在主服务器上优化表结构，添加索引，然后等待复制同步到从服务器。

-- 查看执行计划
EXPLAIN SELECT * FROM my_table WHERE column_name = 'value';

- **检查复制线程状态**：在从服务器上，`SHOW ENGINE INNODB STATUS;` 命令可以查看 InnoDB 引擎的状态信息，其中包括复制线程的相关信息。例如，可以查看 `TRANSACTIONS` 部分，了解当前正在执行的事务以及复制线程的工作情况。如果发现某个复制线程长时间处于等待状态，可能需要进一步排查原因，如锁争用等问题。

通过对 MariaDB 复制技术的性能调优和故障排查，可以确保复制架构的高效稳定运行，满足不同业务场景下的数据同步和高可用性需求。在实际应用中，需要根据具体的服务器环境和业务需求，灵活调整配置和优化策略。