MySQL5.7x 主从复制

原文链接：https://blog.liuzijian.com/post/9f8ede8e-26de-75d6-6347.html

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

前提条件

• 主服务器和从服务器的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;
   

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