一、主从配置

MySQL 主从复制（Master-Slave Replication）是一种非常常用的数据冗余与备份方案，也能用于实现读写分离和负载均衡。
下面我将为您提供一个清晰、详细的配置步骤。

MySQL 主从复制原理概述

简单来说，主从复制的过程分为三步：

1. 主库（Master） 将数据变更记录到自己的二进制日志（Binary Log）中。
2. 从库（Slave） 将主库的二进制日志拷贝到自己的中继日志（Relay Log）中。
3. 从库（Slave） 重做中继日志中的事件，将数据变更应用到自己的数据库中。

配置前提与假设

• 操作系统：以 Linux（如 CentOS 7/8, Ubuntu）为例，Windows 版步骤类似。
• MySQL 版本：5.7 或 8.0，不同版本配置参数可能略有差异，但核心步骤一致。

• 服务器角色：

  • 主库 (Master)：IP: 192.168.1.100
  • 从库 (Slave)：IP: 192.168.1.101
• 确保主从库网络互通，并且防火墙已开放 MySQL 端口（默认 3306）。

第一部分：主库（Master）配置

1. 编辑主库配置文件

   使用 vim 或 nano 编辑 MySQL 配置文件 my.cnf。

   vim /etc/my.cnf
   # 或者
   vim /etc/mysql/my.cnf

2. 在 [mysqld] 段落下添加/修改以下参数

   [mysqld]
   # 每个服务器的唯一标识，主从不能相同
   server-id = 1
   # 启用二进制日志，这是复制的基石。建议给日志起个名字
   log-bin = mysql-bin
   # (可选) 指定需要复制的数据库，如果需要复制多个，可重复设置
   binlog-do-db = your_database_name
   # (可选) 指定不需要复制的数据库
   # binlog-ignore-db = mysql
   # binlog-ignore-db = information_schema
   # (MySQL 8.0 默认使用 caching_sha2_password，如果从库是旧版本，可改为以下配置以确保兼容性)
   # default_authentication_plugin = mysql_native_password

   注意：如果不指定 binlog-do-db 或 binlog-ignore-db，默认会复制所有数据库。

3. 重启 MySQL 服务使配置生效

   systemctl restart mysqld
   # 或者
   systemctl restart mysql

4. 创建用于复制的用户

   登录主库 MySQL，创建一个专门用于从库连接和同步数据的用户。

   mysql -u root -p

   -- 创建用户 ‘repl’，并允许从 ‘192.168.1.101’ 登录
   CREATE USER 'repl'@'192.168.1.101' IDENTIFIED BY 'YourStrongPassword123!';
   
   -- 授予复制权限
   GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.101';
   
   -- 刷新权限
   FLUSH PRIVILEGES;

5. 查看主库状态并记录关键信息

   执行以下命令，记录下 File 和 Position 的值，从库配置时会用到。

   SHOW MASTER STATUS;

   输出会类似这样：

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

   不要退出当前窗口，后续操作完成前，主库不要再有新的写操作，以免 Position 发生变化。

第二部分：从库（Slave）配置

1. 编辑从库配置文件

   vim /etc/my.cnf

2. 在 [mysqld] 段落下添加/修改以下参数

   [mysqld]
   # 服务器唯一ID，必须与主库不同
   server-id = 2
   # (可选) 开启中继日志
   relay-log = mysql-relay-bin
   # (可选) 允许从库将重做事件也记录到自己的二进制日志中
   log_slave_updates = 1
   # (可选) 设置只读模式，防止从库被意外写入数据（对具有 SUPER 权限的用户无效）
   read-only = 1

3. 重启从库 MySQL 服务

   systemctl restart mysqld

4. 配置从库连接主库的信息

   登录从库 MySQL，执行以下命令，使用主库上记录的信息和创建的用户。

   mysql -u root -p

   STOP SLAVE; -- 先停止旧的复制进程（如果是新配置可忽略）
   
   CHANGE MASTER TO
   MASTER_HOST = '192.168.1.100',      -- 主库的IP地址
   MASTER_USER = 'repl',               -- 主库上创建的复制用户
   MASTER_PASSWORD = 'YourStrongPassword123!', -- 复制用户的密码
   MASTER_LOG_FILE = 'mysql-bin.000001', -- 主库 `SHOW MASTER STATUS` 查到的 File
   MASTER_LOG_POS = 154;               -- 主库 `SHOW MASTER STATUS` 查到的 Position
   
   -- 对于 MySQL 8.0，如果主库使用了 caching_sha2_password，可能需要指定认证插件
   -- CHANGE MASTER TO ...,
   -- GET_MASTER_PUBLIC_KEY = 1;
   -- 或者
   -- MASTER_SSL = 1;

5. 启动从库复制进程

   START SLAVE;

6. 检查从库复制状态

   执行以下命令，查看 Slave 线程是否正常运行。

   SHOW SLAVE STATUS\G

   关键信息检查（最关键的一步）：

   • Slave_IO_State: 显示等待主库发送事件等状态。
   • Slave_IO_Running: 必须为 Yes。
   • Slave_SQL_Running: 必须为 Yes。
   • Seconds_Behind_Master: 主从延迟时间，为 0 表示无延迟。
   • Last_IO_Error / Last_SQL_Error: 错误信息，如果上面两项为 No，这里会显示详细错误。

   如果 Slave_IO_Running 和 Slave_SQL_Running 都是 Yes，恭喜你，主从复制配置成功！

第三部分：测试主从复制

1. 在主库的 your_database_name 数据库中创建一个新表或插入一条新数据。

   USE your_database_name;
   CREATE TABLE test_table (id INT, name VARCHAR(50));
   INSERT INTO test_table VALUES (1, 'Hello Replication');

2. 在从库上查询相同的数据库和表，检查数据是否已经自动同步过来。

   USE your_database_name;
   SELECT * FROM test_table;

   如果能成功查询到在主库插入的数据，说明测试成功。

常见问题与排查

• Slave_IO_Running 为 Connecting 或 No：

  • 网络问题：检查防火墙是否开放 3306 端口，使用 telnet 192.168.1.100 3306 测试连通性。
  • 用户权限问题：确认主库上创建的 repl 用户、密码、主机IP是否正确。

• Slave_SQL_Running 为 No：

  • 错误原因：通常是因为从库已经存在一些数据，与主库的二进制日志冲突。

  • 解决方法：

    • 如果从库可以重新初始化，可以重置从库：

      STOP SLAVE;
      RESET SLAVE ALL; -- 清除所有复制信息

      然后使用 mysqldump 或 xtrabackup 工具从主库重新导出一份完整的数据快照，恢复到从库后，再重新执行 CHANGE MASTER 命令（使用新的 File 和 Position）。

    • 也可以跳过错误（谨慎使用）：

      STOP SLAVE;
      SET GLOBAL sql_slave_skip_counter = 1; -- 跳过1个错误事件
      START SLAVE;

总结

1. 主库：改配置 -> 重启 -> 创建复制用户 -> 记录 File 和 Position。
2. 从库：改配置 -> 重启 -> 执行 CHANGE MASTER -> 启动复制 -> 检查状态。
3. 测试：主库写，从库读。

按照以上步骤操作，您应该能成功搭建 MySQL 主从复制环境。对于生产环境，建议使用 GTID（Global Transaction Identifier）模式进行复制，配置更简单，故障处理也更方便。

二、问题解决

如果从库因为不可控因素宕机重启后，出现数据冲突的问题，样例：

Could not execute Write_rows event on table database.table; Duplicate entry '969' for key 'table.PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's source log binlog.000180, end_log_pos 7498

出现此错误解决步骤：

1、在从库的 MySQL 中停止复制：

STOP SLAVE;

2、让从库跳过一条错误事件（这里是一条重复的 INSERT）：

SET GLOBAL sql_slave_skip_counter = 1;

3、重新启动复制：

START SLAVE;

4、立即检查复制状态，确认 Slave_IO_Running和 Slave_SQL_Running是否恢复为 Yes：

SHOW SLAVE STATUS\G