如何配置MySQL复制环境

准备工作

在开始配置 MySQL 复制环境之前，需要确保以下几点：

1. 操作系统与 MySQL 版本：

   • 确保参与复制的所有 MySQL 服务器运行在稳定且兼容的操作系统上，常见的如 Linux（如 CentOS、Ubuntu 等）或 Windows 服务器版本。不同操作系统在文件路径、权限等方面存在差异，需提前了解并适配。
   • 所有服务器应使用相同大版本的 MySQL 数据库，虽然小版本差异在一定程度上可以兼容，但为了避免潜在的兼容性问题，建议使用完全相同的版本。例如，都使用 MySQL 8.0.26 版本。

2. 服务器网络与防火墙：

   • 参与复制的主服务器（Master）和从服务器（Slave）之间应具备可靠的网络连接。可以通过 ping 命令测试网络连通性，如在 Linux 系统中，执行 ping <目标服务器 IP>，确保有正常的响应。
   • 检查并配置防火墙规则，允许 MySQL 服务所使用的端口进行通信。MySQL 默认使用 3306 端口，在 Linux 系统（以 CentOS 7 为例）中，可以通过以下命令开放 3306 端口：

   firewall - cmd --zone = public --add - port = 3306/tcp --permanent
   firewall - cmd --reload
   

   在 Windows 系统中，可在控制面板 - 系统和安全 - Windows 防火墙 - 高级设置中，创建入站规则允许 3306 端口的 TCP 连接。

3. MySQL 配置文件备份：

   • 在对 MySQL 配置文件进行任何修改之前，务必对其进行备份。在 Linux 系统中，MySQL 配置文件通常为 /etc/my.cnf 或 /etc/mysql/my.cnf，可以使用以下命令备份：

   cp /etc/my.cnf /etc/my.cnf.backup
   

   在 Windows 系统中，MySQL 配置文件一般位于 MySQL 安装目录下，名为 my.ini，备份操作可通过复制粘贴完成。

主服务器配置

1. 修改 MySQL 配置文件：

   • 打开主服务器的 MySQL 配置文件（以 Linux 系统的 /etc/my.cnf 为例），添加或修改以下配置参数：

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

   • server - id：这是每个 MySQL 服务器在复制环境中的唯一标识符，必须为正整数且在整个复制拓扑中不重复。这里设置为 1，不同的主从服务器应设置不同的值。
   • log - bin：指定二进制日志文件的路径和前缀。二进制日志记录了主服务器上的数据更改操作，从服务器通过读取这些日志来同步数据。这里设置为 /var/log/mysql/mysql - bin.log，需确保该路径存在且 MySQL 服务对其有写入权限。可以通过以下命令创建目录并设置权限：

   mkdir -p /var/log/mysql
   chown mysql:mysql /var/log/mysql
   

   • binlog - format：指定二进制日志的格式，ROW 格式记录每行数据的更改，相比 STATEMENT 格式，它在复制数据时更加安全和准确，能避免一些由于语句执行环境差异导致的复制错误。

2. 重启 MySQL 服务：

   • 在修改配置文件后，需要重启 MySQL 服务使配置生效。在 Linux 系统中，可以使用以下命令重启 MySQL：

   systemctl restart mysqld
   

   在 Windows 系统中，可以通过服务管理控制台找到 MySQL 服务，右键选择重启。

3. 创建复制用户：

   • 登录到主服务器的 MySQL 命令行界面，例如在 Linux 系统中执行 mysql - u root - p，输入密码后进入。
   • 创建一个用于从服务器连接主服务器进行复制的用户。以创建名为 repl_user，密码为 password123 的用户为例，执行以下 SQL 语句：

   CREATE USER'repl_user'@'%' IDENTIFIED BY 'password123';
   GRANT REPLICATION SLAVE ON *.* TO'repl_user'@'%';
   FLUSH PRIVILEGES;
   

   • CREATE USER 语句创建了一个新用户，@'%' 表示该用户可以从任何主机连接。
   • GRANT REPLICATION SLAVE 授予该用户复制相关的权限，ON *.* 表示对所有数据库和表都有相应权限。
   • FLUSH PRIVILEGES 刷新权限表，使新权限立即生效。

4. 获取主服务器状态：

   • 在主服务器的 MySQL 命令行中执行以下语句获取主服务器状态：

   SHOW MASTER STATUS;
   

   执行结果类似如下：

   +------------------+----------+--------------+------------------+-------------------+
   | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
   +------------------+----------+--------------+------------------+-------------------+
   | mysql - bin.000003 | 154      |              |                  |                   |
   +------------------+----------+--------------+------------------+-------------------+
   1 row in set (0.00 sec)
   

   记录下 File 和 Position 的值，在从服务器配置时会用到。File 表示当前二进制日志文件的名称，Position 表示当前二进制日志文件中的位置。

从服务器配置

1. 修改 MySQL 配置文件：

   • 打开从服务器的 MySQL 配置文件（同样以 /etc/my.cnf 为例），添加或修改以下配置参数：

   [mysqld]
   server - id = 2
   

   • 这里 server - id 设置为 2，与主服务器的 server - id 不同，确保在整个复制环境中的唯一性。

2. 重启 MySQL 服务：

   • 同主服务器一样，修改配置文件后需要重启 MySQL 服务。在 Linux 系统中执行 systemctl restart mysqld，在 Windows 系统中通过服务管理控制台重启 MySQL 服务。

3. 配置主服务器连接信息：

   • 登录到从服务器的 MySQL 命令行界面，执行以下 SQL 语句配置主服务器连接信息：

   CHANGE MASTER TO
       MASTER_HOST = '主服务器 IP',
       MASTER_USER ='repl_user',
       MASTER_PASSWORD = 'password123',
       MASTER_LOG_FILE ='mysql - bin.000003',
       MASTER_LOG_POS = 154;
   

   • MASTER_HOST：指定主服务器的 IP 地址。
   • MASTER_USER 和 MASTER_PASSWORD：即主服务器上创建的用于复制的用户及其密码。
   • MASTER_LOG_FILE 和 MASTER_LOG_POS：填写在主服务器上执行 SHOW MASTER STATUS 得到的 File 和 Position 的值。

4. 启动从服务器复制线程：

   • 在配置好主服务器连接信息后，执行以下 SQL 语句启动从服务器的复制线程：

   START SLAVE;
   

5. 检查从服务器状态：

   • 执行以下 SQL 语句检查从服务器的复制状态：

   SHOW SLAVE STATUS \G;
   

   重点关注以下两个参数：

   • Slave_IO_Running：如果显示 Yes，表示从服务器的 I/O 线程正在运行，负责从主服务器读取二进制日志。
   • Slave_SQL_Running：如果显示 Yes，表示从服务器的 SQL 线程正在运行，负责将读取到的二进制日志应用到从服务器的数据库中。 另外，还需检查 Seconds_Behind_Master 参数，该参数表示从服务器落后主服务器的时间（以秒为单位），如果该值持续为 0 或较小的值，说明复制延迟较小，运行正常。如果该值较大，则可能存在复制延迟问题，需要进一步排查原因。例如网络延迟、主服务器负载过高、从服务器性能不足等都可能导致复制延迟。

常见问题及解决方法

1. 权限问题：

   • 症状：从服务器在执行 START SLAVE 时出现权限相关错误，如 ERROR 1045 (28000): Access denied for user'repl_user'@'从服务器 IP' (using password: YES)。
   • 原因：可能是主服务器上创建的复制用户权限不足，或者从服务器连接主服务器的 IP 地址不在允许的范围内。
   • 解决方法：在主服务器上再次确认复制用户的权限，确保 GRANT REPLICATION SLAVE ON *.* TO'repl_user'@'%'; 语句已正确执行，并且 @'%' 表示允许任何主机连接。如果需要限制特定 IP 连接，可以将 @'%' 替换为 @'特定 IP'。同时，检查主服务器和从服务器之间的网络连接以及防火墙设置，确保从服务器能够正常连接主服务器。

2. 二进制日志位置错误：

   • 症状：从服务器启动复制后，Slave_IO_Running 为 No，并且错误日志中显示与二进制日志位置相关的错误，如 Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'。
   • 原因：可能是在从服务器配置 CHANGE MASTER TO 语句时，MASTER_LOG_FILE 或 MASTER_LOG_POS 的值填写错误。
   • 解决方法：在主服务器上重新执行 SHOW MASTER STATUS，获取最新的 File 和 Position 值，然后在从服务器上停止复制线程（STOP SLAVE;），重新执行 CHANGE MASTER TO 语句，正确填写 MASTER_LOG_FILE 和 MASTER_LOG_POS 的值，最后再次启动复制线程（START SLAVE;）。

3. 复制延迟问题：

   • 症状：Seconds_Behind_Master 的值持续较大，表明从服务器落后主服务器较多。
   • 原因：
     • 网络问题：主从服务器之间网络不稳定或带宽不足，导致从服务器读取二进制日志速度慢。可以通过 ping 命令测试网络连通性，使用 iperf 等工具测试网络带宽。
     • 主服务器负载过高：主服务器忙于处理大量的事务，导致二进制日志生成速度过快，从服务器来不及同步。可以通过 top 命令查看主服务器的 CPU、内存等资源使用情况，优化主服务器的负载，如调整应用程序的事务处理逻辑，避免大量并发事务。
     • 从服务器性能不足：从服务器的硬件资源（如 CPU、内存、磁盘 I/O 等）不足以支持快速同步数据。可以考虑升级从服务器的硬件配置，或者优化从服务器的 MySQL 配置参数，如调整 innodb_buffer_pool_size 等参数，提高数据库性能。
   • 解决方法：
     • 对于网络问题，优化网络环境，确保主从服务器之间有稳定且足够带宽的网络连接。如果是网络设备（如路由器、交换机等）配置问题，调整相关配置。
     • 针对主服务器负载过高，合理分配主服务器的负载，如将一些只读查询业务迁移到从服务器，或者对主服务器的数据库进行分库分表等优化操作。
     • 对于从服务器性能不足，根据实际情况升级硬件或调整 MySQL 配置参数。同时，定期清理从服务器上的无用数据和日志文件，释放磁盘空间，提高磁盘 I/O 性能。

4. 数据不一致问题：

   • 症状：在复制过程中，发现主从服务器上的数据出现不一致的情况，如某些表的数据行数不同，或者相同行的数据值不一致。
   • 原因：
     • 主服务器上的数据修改未正确记录到二进制日志：可能是由于错误的 MySQL 配置，导致部分数据修改操作未被记录到二进制日志中。例如，binlog - format 设置不正确，或者某些操作在 autocommit = 0 的情况下执行，而未显式提交事务。
     • 从服务器应用二进制日志错误：可能是由于从服务器的 MySQL 版本与主服务器不兼容，导致在应用二进制日志时出现错误。或者从服务器在同步过程中遇到死锁等异常情况，导致部分数据未正确应用。
   • 解决方法：
     • 检查主服务器的 MySQL 配置，确保 binlog - format 正确设置，并且所有的数据修改操作都能正确记录到二进制日志中。可以通过在主服务器上执行一些简单的插入、更新操作，然后查看二进制日志文件来验证。
     • 确认主从服务器的 MySQL 版本一致性，如不一致，考虑升级或降级到相同版本。对于从服务器出现死锁等异常情况，查看从服务器的错误日志，找到具体的异常原因并进行修复。同时，可以尝试停止从服务器复制线程，重新配置主服务器连接信息（CHANGE MASTER TO），然后再次启动复制线程，确保数据能够正确同步。

多从服务器配置

在实际应用中，可能需要配置多个从服务器来分担读压力或实现数据备份等功能。配置多从服务器的基本步骤与单个从服务器类似，但需要注意以下几点：

1. 每个从服务器的 server - id 必须唯一：

   • 例如，第二个从服务器可以设置 server - id = 3，第三个从服务器设置 server - id = 4 等。确保在整个复制环境中，所有服务器（包括主服务器和从服务器）的 server - id 都不重复。

2. 分别配置每个从服务器的主服务器连接信息：

   • 每个从服务器都需要执行 CHANGE MASTER TO 语句来配置主服务器的连接信息，包括 MASTER_HOST、MASTER_USER、MASTER_PASSWORD、MASTER_LOG_FILE 和 MASTER_LOG_POS。这里的 MASTER_LOG_FILE 和 MASTER_LOG_POS 可以使用主服务器上 SHOW MASTER STATUS 获取的值，也可以在第一个从服务器配置完成并启动复制后，从第一个从服务器的 SHOW SLAVE STATUS 结果中获取 Relay_Master_Log_File 和 Exec_Master_Log_Pos 的值来配置其他从服务器，这样可以确保所有从服务器从相同的位置开始同步数据。

3. 启动和检查每个从服务器的复制状态：

   • 配置完成后，分别在每个从服务器上执行 START SLAVE 启动复制线程，并通过 SHOW SLAVE STATUS \G 检查复制状态，确保 Slave_IO_Running 和 Slave_SQL_Running 都为 Yes，并且 Seconds_Behind_Master 的值在可接受范围内。

半同步复制配置

MySQL 的半同步复制是一种增强数据安全性的复制方式，它确保在主服务器提交事务之前，至少有一个从服务器接收到并写入中继日志。相比异步复制，半同步复制可以减少数据丢失的风险。

1. 主服务器配置：

   • 打开主服务器的 MySQL 配置文件，添加以下配置参数：

   [mysqld]
   plugin - load = rpl_semi_sync_master = semisync_master.so
   rpl - semi - sync - master - wait - for - slave - count = 1
   rpl - semi - sync - master - timeout = 10000
   

   • plugin - load：加载半同步复制主服务器插件，这里加载 rpl_semi_sync_master.so 插件。

   • rpl - semi - sync - master - wait - for - slave - count：指定主服务器等待多少个从服务器接收并写入中继日志后才提交事务，这里设置为 1，表示至少等待一个从服务器。

   • rpl - semi - sync - master - timeout：设置主服务器等待从服务器响应的超时时间，单位为毫秒，这里设置为 10000 毫秒（10 秒）。

   • 重启 MySQL 服务使配置生效：systemctl restart mysqld。

   • 登录到主服务器的 MySQL 命令行，启用半同步复制：

   SET GLOBAL rpl_semi_sync_master_enabled = 1;
   

2. 从服务器配置：

   • 打开从服务器的 MySQL 配置文件，添加以下配置参数：

   [mysqld]
   plugin - load = rpl_semi_sync_slave = semisync_slave.so
   

   • 加载半同步复制从服务器插件 rpl_semi_sync_slave.so。

   • 重启 MySQL 服务：systemctl restart mysqld。

   • 登录到从服务器的 MySQL 命令行，启用半同步复制：

   SET GLOBAL rpl_semi_sync_slave_enabled = 1;
   STOP SLAVE IO_THREAD;
   START SLAVE IO_THREAD;
   

   • 先停止并重新启动从服务器的 I/O 线程，使半同步复制配置生效。

3. 检查半同步复制状态：

   • 在主服务器上执行以下 SQL 语句检查半同步复制状态：

   SHOW STATUS LIKE 'Rpl_semi_sync_master_status';
   

   如果 Value 为 ON，表示半同步复制在主服务器上已启用并正常工作。

   • 在从服务器上执行以下 SQL 语句检查半同步复制状态：

   SHOW STATUS LIKE 'Rpl_semi_sync_slave_status';
   

   如果 Value 为 ON，表示半同步复制在从服务器上已启用并正常工作。

GTID 复制配置

GTID（全局事务标识符）复制是 MySQL 5.6 及以上版本引入的一种更高效、可靠的复制方式。它通过为每个事务分配一个全局唯一的标识符，使得复制过程更加简单和健壮。

1. 主服务器配置：

   • 打开主服务器的 MySQL 配置文件，添加或修改以下配置参数：

   [mysqld]
   server - id = 1
   log - bin = /var/log/mysql/mysql - bin.log
   binlog - format = ROW
   gtid - mode = ON
   enforce - gtid - consistency = ON
   

   • gtid - mode = ON：启用 GTID 模式。

   • enforce - gtid - consistency = ON：确保 GTID 一致性，这会对事务执行进行一些限制，以保证 GTID 的正确使用。

   • 重启 MySQL 服务：systemctl restart mysqld。

2. 从服务器配置：

   • 打开从服务器的 MySQL 配置文件，添加或修改以下配置参数：

   [mysqld]
   server - id = 2
   gtid - mode = ON
   enforce - gtid - consistency = ON
   

   • 同样启用 GTID 模式和确保 GTID 一致性。

   • 重启 MySQL 服务：systemctl restart mysqld。

3. 配置主从连接：

   • 在从服务器上登录 MySQL 命令行，执行以下语句配置主从连接：

   CHANGE MASTER TO
       MASTER_HOST = '主服务器 IP',
       MASTER_USER ='repl_user',
       MASTER_PASSWORD = 'password123',
       MASTER_AUTO_POSITION = 1;
   

   • 与传统复制不同，这里使用 MASTER_AUTO_POSITION = 1，表示使用 GTID 自动定位主服务器的位置，而不需要手动指定 MASTER_LOG_FILE 和 MASTER_LOG_POS。

4. 启动和检查复制状态：

   • 执行 START SLAVE; 启动从服务器复制线程。
   • 通过 SHOW SLAVE STATUS \G 检查复制状态，重点关注 Slave_IO_Running、Slave_SQL_Running 和 Seconds_Behind_Master 等参数，确保复制正常运行。同时，在 GTID 复制模式下，还可以查看 Executed_Gtid_Set 参数，了解从服务器已经执行的 GTID 集合。

通过以上详细的步骤和说明，你应该能够成功配置 MySQL 的各种复制环境，包括传统复制、多从服务器复制、半同步复制以及 GTID 复制，并能够应对在配置过程中可能遇到的各种问题。在实际生产环境中，还需要根据业务需求和性能要求，对复制环境进行进一步的优化和监控，确保数据的一致性和可用性。