MySQL5.7x 主从复制

在MySQL中，主从复制（Master-Slave Replication）是一种常用的数据库复制技术，用于将主服务器（Master）的数据实时复制到一个或多个从服务器（Slave）。这有助于负载均衡、备份和高可用性。下面是实现MySQL主从复制的详细步骤。

前提条件

• 主服务器和从服务器的MySQL版本应兼容（最好相同版本）。
• 所有服务器时钟应该同步，可以使用NTP确保时间一致性。
• 从服务器初始时的数据要与主服务器一致（可以通过备份恢复的方式实现）。

一、配置主服务器（Master）

1. 编辑主服务器的MySQL配置文件
   打开MySQL主服务器的配置文件 my.cnf 。

   添加或修改以下内容：

   [mysqld]
   server-id = 1                # 唯一的服务器ID，任意正整数，但需要在集群中唯一
   log-bin = mysql-bin           # 启用二进制日志，确保日志记录所有数据变更
   binlog-do-db = my_database    # 要复制的数据库（可以指定多个，也可以省略）

   • server-id：每个服务器都需要有唯一的server-id。
   • log-bin：启用二进制日志，主从复制就是基于二进制日志的。
   • binlog-do-db：指定要复制的数据库，可以指定多个数据库。省略这行会复制所有数据库。

   重启MySQL服务：

   sudo systemctl restart mysqld

2. 创建用于复制的用户
   在主服务器上登录MySQL并创建一个用于复制的用户。

   CREATE USER 'slave'@'%' IDENTIFIED BY 'password';
   
   GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';
   
   FLUSH PRIVILEGES;

   这里创建了一个名为slave的用户，密码为password，并授予了复制权限。

3. 获取主服务器的状态信息
   在主服务器上运行以下命令，记录输出的信息：

   SHOW MASTER STATUS;

   输出类似以下内容：

   +------------------+----------+--------------+------------------+
   | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
   +------------------+----------+--------------+------------------+
   | mysql-bin.000001 | 12345    | my_database  |                  |
   +------------------+----------+--------------+------------------+

   • File：二进制日志的文件名。
   • Position：当前日志的偏移量。这两个值将在配置从服务器时使用。

二、配置从服务器（Slave）

1. 编辑从服务器的MySQL配置文件

   打开从服务器的配置文件my.cnf，添加或修改如下内容：

   [mysqld]
   server-id = 2             # 唯一的服务器ID，与主服务器不同
   relay-log = mysql-relay   # 中继日志，用于存储从主服务器接收到的日志

   重启从服务器的MySQL服务：

   sudo systemctl restart mysqld

2. 初始化从服务器

   登录从服务器的MySQL，并执行以下步骤来设置复制：

   CHANGE MASTER TO
       MASTER_HOST='master_ip_address',
       MASTER_USER='replicator',
       MASTER_PASSWORD='password',
       MASTER_LOG_FILE='mysql-bin.000001',  -- 从SHOW MASTER STATUS获取的日志文件名
       MASTER_LOG_POS=12345;                -- 从SHOW MASTER STATUS获取的偏移量

   在这里，master_ip_address是主服务器的IP地址，MASTER_LOG_FILE和MASTER_LOG_POS是前面SHOW MASTER STATUS命令中看到的值。

3. 启动复制

   在从服务器上启动复制进程：

   START SLAVE;

4. 检查复制状态

   检查从服务器的复制状态，确保复制正常运行：

   SHOW SLAVE STATUS\G;

   查看输出中的两个关键字段：

   • Slave_IO_Running: Yes
   • Slave_SQL_Running: Yes

   如果这两个值都是Yes，说明主从复制正常。如果不是，可以检查错误信息并进行排查。

三、测试主从复制

1. 在主服务器上创建一个测试表或插入数据：

   USE my_database;
   CREATE TABLE test_table (id INT PRIMARY KEY, data VARCHAR(50));
   INSERT INTO test_table VALUES (1, 'Test Data');

2. 在从服务器上检查该表是否存在以及数据是否同步：

   USE my_database;
   SELECT * FROM test_table;

   如果能看到相同的表和数据，说明主从复制配置成功。

四、常见问题排查

1. 网络问题：确保主从服务器之间网络通信正常，防火墙允许MySQL端口（通常是3306）。
2. 权限问题：确保在主服务器上为replicator用户授予了REPLICATION SLAVE权限。
3. 日志文件或位置错误：如果从服务器的二进制日志文件或位置配置错误，可能会导致复制失败。重新检查MASTER_LOG_FILE和MASTER_LOG_POS的设置。
4. 数据不一致问题：在配置复制之前，确保从服务器的数据和主服务器保持一致，可以通过导出主服务器数据并导入到从服务器来实现。

总结

MySQL主从复制的核心是通过二进制日志（Binary Log）将主服务器的更新传递到从服务器。配置过程中，最关键的是确保主从服务器的MySQL配置正确，并正确地设置二进制日志文件名和位置。此外，网络配置、用户权限、时间同步等因素也会影响复制的成功。