MySQL 复制基础

简介

在这个实验中，我们将探索 MySQL 复制的基础知识。主要目标是搭建一个基本的主从复制环境，实现两台 MySQL 服务器之间的数据同步。

实验首先会配置一个启用二进制日志的主服务器，这对于记录所有数据修改至关重要。我们将编辑 MySQL 配置文件 (/etc/mysql/mysql.conf.d/mysqld.cnf)，将 log_bin 设置为 mysql-bin，并将 server_id 设置为 1。随后，我们会设置一个从服务器来从主服务器复制数据，使用 SHOW SLAVE STATUS 验证复制状态，最后模拟并修复一个复制错误，以了解故障排除技术。

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL mysql(("MySQL")) -.-> mysql/BasicKeywordsandStatementsGroup(["Basic Keywords and Statements"]) mysql(("MySQL")) -.-> mysql/TransactionManagementandSecurityGroup(["Transaction Management and Security"]) mysql(("MySQL")) -.-> mysql/SystemManagementToolsGroup(["System Management Tools"]) mysql/BasicKeywordsandStatementsGroup -.-> mysql/create_database("Database Creation") mysql/BasicKeywordsandStatementsGroup -.-> mysql/create_table("Table Creation") mysql/BasicKeywordsandStatementsGroup -.-> mysql/select("Data Retrieval") mysql/BasicKeywordsandStatementsGroup -.-> mysql/insert("Data Insertion") mysql/TransactionManagementandSecurityGroup -.-> mysql/grant_permission("Permission Granting") mysql/SystemManagementToolsGroup -.-> mysql/show_status("Status Overview") mysql/SystemManagementToolsGroup -.-> mysql/show_variables("Configuration Overview") subgraph Lab Skills mysql/create_database -.-> lab-550913{{"MySQL 复制基础"}} mysql/create_table -.-> lab-550913{{"MySQL 复制基础"}} mysql/select -.-> lab-550913{{"MySQL 复制基础"}} mysql/insert -.-> lab-550913{{"MySQL 复制基础"}} mysql/grant_permission -.-> lab-550913{{"MySQL 复制基础"}} mysql/show_status -.-> lab-550913{{"MySQL 复制基础"}} mysql/show_variables -.-> lab-550913{{"MySQL 复制基础"}} end

配置启用二进制日志的主服务器

在这一步中，我们将把一台 MySQL 服务器配置为主服务器，用于复制设置。此配置的关键部分是启用二进制日志。二进制日志会记录对数据库所做的所有数据修改，从服务器将使用这些记录来复制更改。

首先，让我们了解一下为什么二进制日志如此重要。如果没有二进制日志，从服务器将无法知道要从主服务器复制哪些更改。二进制日志就像事务日志一样，确保从服务器与主服务器保持同步。

现在，让我们来配置主服务器。

编辑 MySQL 配置文件：

MySQL 配置文件通常位于 /etc/mysql/mysql.conf.d/mysqld.cnf。我们将使用 nano 来编辑此文件。
```
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
```
启用二进制日志：

在配置文件的 mysqld 部分添加以下行。如果 mysqld 部分不存在，请找到 [mysqld] 行，并在其下方添加以下配置。
```
log_bin = mysql-bin
server_id = 1
```
- log_bin = mysql-bin：这将启用二进制日志，并将日志文件的基本名称设置为 mysql-bin。MySQL 会自动在这个基本名称后面追加一个序列号（例如，mysql-bin.000001、mysql-bin.000002 等）。
- server_id = 1：这为主服务器设置了一个唯一的 ID。在复制设置中，每个服务器都必须有一个唯一的 server_id。
配置文件的 mysqld 部分现在应该类似于以下内容：
```
[mysqld]
#
## * Basic Settings
#
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
log_bin = mysql-bin
server_id = 1
## ... 其他配置 ...
```
按 Ctrl+X，然后按 Y，再按 Enter 保存更改并退出 nano。
重启 MySQL 服务器：

为了应用更改，需要重启 MySQL 服务器。由于我们是在 Docker 容器中，不能使用 systemctl。相反，我们将使用 mysql.server 脚本。
```
sudo /etc/init.d/mysql restart
```
你应该会看到类似以下的输出：
```
[ ok ] Restarting mysql (via systemctl): mysql.service.
```

验证二进制日志是否已启用：

以 root 用户身份登录到 MySQL 服务器。如果你还没有为 root 用户设置密码，可能需要使用 sudo 来访问 MySQL。

sudo mysql -u root

运行以下 SQL 查询来检查二进制日志的状态：

SHOW VARIABLES LIKE 'log_bin';

输出应该是：

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)

这确认了二进制日志已启用。

你还可以检查服务器 ID：

SHOW VARIABLES LIKE 'server_id';

输出应该是：

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+
1 row in set (0.00 sec)

退出 MySQL 监视器：

exit

你现在已经成功配置了启用二进制日志的主服务器。下一步将涉及设置从服务器，以从这个主服务器复制数据。

设置从服务器以复制数据

在这一步中，我们将把一台 MySQL 服务器配置为从服务器，从在上一步中配置的主服务器复制数据。这包括配置从服务器的设置并建立与主服务器的连接。

配置从服务器：

首先，我们需要为从服务器配置一个唯一的 server_id 并启用中继日志。中继日志用于从服务器在将从主服务器接收到的二进制日志事件应用到自己的数据库之前进行存储。

编辑从服务器上的 MySQL 配置文件（通常位于 /etc/mysql/mysql.conf.d/mysqld.cnf）：
```
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
```
在 [mysqld] 部分添加或修改以下行：
```
server_id = 2
relay_log = mysql-relay-bin
```
- server_id = 2：这为从服务器设置了一个唯一的 ID。它必须与主服务器的 server_id 不同。
- relay_log = mysql-relay-bin：这启用了中继日志，并设置了中继日志文件的基本名称。
配置文件的 mysqld 部分现在应该类似于以下内容：
```
[mysqld]
#
## * Basic Settings
#
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
server_id = 2
relay_log = mysql-relay-bin
## ... 其他配置 ...
```
按 Ctrl+X，然后按 Y，再按 Enter 保存更改并退出 nano。
重启从服务器上的 MySQL 服务器：

重启 MySQL 服务器以应用更改：
```
sudo /etc/init.d/mysql restart
```
你应该会看到类似以下的输出：
```
[ ok ] Restarting mysql (via systemctl): mysql.service.
```
在主服务器上创建复制用户：

在主服务器上，以 root 用户身份登录到 MySQL 服务器：
```
sudo mysql -u root
```
创建一个专门用于复制的用户。将 'slave_user' 替换为你想要的用户名，将 'password' 替换为强密码。为了提高安全性，将 '%' 替换为从服务器的 IP 地址。如果你不确定从服务器的 IP 地址，可以使用 '%' 允许从任何主机进行连接（不建议在生产环境中使用）。
```
CREATE USER 'slave_user'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%';
FLUSH PRIVILEGES;
```
- CREATE USER 'slave_user'@'%' IDENTIFIED BY 'password'：创建一个名为 slave_user 的新 MySQL 用户，该用户可以从任何主机（'%'）连接。
- GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%'：授予 slave_user 在所有数据库和表上的 REPLICATION SLAVE 权限。从服务器需要此权限才能向主服务器请求二进制日志更新。
- FLUSH PRIVILEGES：重新加载授权表以应用新权限。
退出主服务器上的 MySQL 监视器：
```
exit
```
配置从服务器连接到主服务器：

在从服务器上，以 root 用户身份登录到 MySQL 服务器：
```
sudo mysql -u root
```
告诉从服务器如何连接到主服务器。将 'master_host' 替换为主服务器的 IP 地址或主机名，将 'slave_user' 替换为你创建的复制用户名，将 'password' 替换为复制密码。
```
CHANGE MASTER TO
    MASTER_HOST='master_host',
    MASTER_USER='slave_user',
    MASTER_PASSWORD='password',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=4;
```
- MASTER_HOST：主服务器的主机名或 IP 地址。
- MASTER_USER：从服务器用于连接主服务器的用户名。
- MASTER_PASSWORD：复制用户的密码。
- MASTER_LOG_FILE：开始复制的主服务器二进制日志文件的名称。你可以通过在主服务器上运行 SHOW MASTER STATUS; 来找到这个名称。对于全新的设置，通常是 mysql-bin.000001。
- MASTER_LOG_POS：开始复制的二进制日志文件中的位置。你可以通过在主服务器上运行 SHOW MASTER STATUS; 来找到这个位置。对于全新的设置，通常是 4。
重要提示： 你需要从主服务器确定正确的 MASTER_LOG_FILE 和 MASTER_LOG_POS。在主服务器上，执行以下查询：
```
SHOW MASTER STATUS;
```
输出将显示 File（对应于 MASTER_LOG_FILE）和 Position（对应于 MASTER_LOG_POS）。在从服务器的 CHANGE MASTER TO 命令中使用这些值。

执行 CHANGE MASTER TO 命令后，启动从服务器：
```
START SLAVE;
```
退出从服务器上的 MySQL 监视器：
```
exit
```

你现在已经配置了从服务器以从主服务器复制数据。下一步，我们将验证复制是否正常工作。

使用 SHOW SLAVE STATUS 验证复制

在这一步中，我们将验证从服务器是否成功从主服务器复制数据。我们将使用 SHOW SLAVE STATUS 命令来检查复制线程的状态，并识别任何潜在的问题。

检查从服务器状态：

在从服务器上，以 root 用户身份登录到 MySQL 服务器：
```
sudo mysql -u root
```
执行以下命令查看从服务器状态：
```
SHOW SLAVE STATUS\G
```
命令末尾的 \G 会将输出垂直格式化，使其更易于阅读。
解读输出结果：

SHOW SLAVE STATUS 的输出提供了有关复制过程的详细信息。以下是一些需要关注的关键字段：
- Slave_IO_State：这表示 I/O 线程的当前状态，该线程负责从主服务器读取二进制日志事件。理想情况下，它应该显示 Waiting for master to send event。
- Slave_IO_Running：这表示 I/O 线程是否正在运行。它应该为 Yes。
- Slave_SQL_Running：这表示 SQL 线程是否正在运行。SQL 线程将从主服务器接收到的事件应用到从服务器数据库。它应该为 Yes。
- Last_IO_Error：如果与 I/O 线程相关存在任何错误，将在此处显示。
- Last_SQL_Error：如果与 SQL 线程相关存在任何错误，将在此处显示。
- Master_Log_File：从服务器当前正在读取的主服务器上的二进制日志文件的名称。
- Read_Master_Log_Pos：从服务器已读取到的二进制日志文件中的位置。
- Relay_Log_File：SQL 线程当前正在读取的从服务器上的中继日志文件的名称。
- Relay_Log_Pos：SQL 线程已读取到的中继日志文件中的位置。
- Seconds_Behind_Master：这表示从服务器落后主服务器的秒数。值为 0 表示从服务器是最新的。值越大，表示从服务器落后得越多。
一个成功的复制设置中，Slave_IO_Running 和 Slave_SQL_Running 应设置为 Yes，并且 Seconds_Behind_Master 应接近 0。

成功的 SHOW SLAVE STATUS 输出示例（已截断）：
```
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: master_host
                  Master_User: slave_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 311
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 154
              Relay_Log_Space: 472
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
            Master_SSL_CA_File:
            Master_SSL_CA_Path:
               Master_SSL_Cert:
             Master_SSL_Cipher:
                Master_SSL_Key:
         Seconds_Behind_Master: 0
...
```

在主服务器上创建测试数据库和表：

为了进一步验证复制，在主服务器上创建一个测试数据库和表：

sudo mysql -u root

CREATE DATABASE test_replication;
USE test_replication;
CREATE TABLE test_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    data VARCHAR(255)
);
INSERT INTO test_table (data) VALUES ('This is a test record from master');
exit

验证从服务器上的数据：

在从服务器上，以 root 用户身份登录到 MySQL 服务器：

sudo mysql -u root

检查 test_replication 数据库和 test_table 表是否已被复制：

USE test_replication;
SELECT * FROM test_table;
exit

你应该会看到在主服务器上插入的数据：

+----+-----------------------------------------+
| id | data                                    |
+----+-----------------------------------------+
|  1 | This is a test record from master       |
+----+-----------------------------------------+
1 row in set (0.00 sec)

如果你在从服务器上看到了测试数据库、表和数据，说明复制正常工作。

模拟复制错误并修复它

在这一步中，我们将模拟一个常见的复制错误，并演示如何解决它。这将帮助你了解如何在实际场景中排查复制问题。

模拟错误：

当从服务器遇到数据不一致时，会发生常见的复制错误。我们将通过在从服务器上手动向 test_replication.test_table 表中插入一行数据来模拟这种情况，当主服务器尝试复制同一行数据时，就会引发冲突。

在从服务器上，以 root 用户身份登录到 MySQL 服务器：
```
sudo mysql -u root
```
向 test_replication.test_table 表中插入一行数据：
```
USE test_replication;
INSERT INTO test_table (data) VALUES ('This is an intentionally conflicting record on the slave');
exit
```
触发复制：

现在，在主服务器上向 test_replication.test_table 表中再插入一行数据：
```
sudo mysql -u root
```
```
USE test_replication;
INSERT INTO test_table (data) VALUES ('This is a new record from master');
exit
```
主服务器上的这次插入操作将触发向从服务器的复制。然而，由于我们在从服务器上手动插入了一行冲突的数据，复制过程可能会遇到错误。

检查从服务器状态：

在从服务器上，以 root 用户身份登录到 MySQL 服务器：

sudo mysql -u root

检查从服务器状态：

SHOW SLAVE STATUS\G

查看输出结果。你应该会看到 Slave_SQL_Running 可能已设置为 No，并且 Last_SQL_Error 字段将包含一条错误消息，表明存在重复键或类似的冲突。

错误输出示例：

...
               Slave_IO_State: Waiting for master to send event
                  Master_Host: master_host
                  Master_User: slave_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql-relay-bin.000003
                Relay_Log_Pos: 311
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1062
                   Last_Error: Duplicate entry '1' for key 'PRIMARY'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 154
              Relay_Log_Space: 472
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
            Master_SSL_CA_File:
            Master_SSL_CA_Path:
               Master_SSL_Cert:
             Master_SSL_Cipher:
                Master_SSL_Key:
         Seconds_Behind_Master: NULL
...

修复复制错误：

为了修复这个错误，我们将跳过从服务器上有问题的事件。这会告诉从服务器忽略导致错误的事件，并从下一个事件继续复制。

首先，停止从服务器的 SQL 线程：
```
STOP SLAVE SQL_THREAD;
```
接下来，跳过错误事件：
```
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
```
此命令告诉从服务器跳过中继日志中的下一个事件。

最后，启动从服务器的 SQL 线程：
```
START SLAVE SQL_THREAD;
```
验证复制是否再次运行：

再次检查从服务器状态：
```
SHOW SLAVE STATUS\G
```
你现在应该会看到 Slave_SQL_Running 已设置为 Yes，并且 Last_SQL_Error 为空。复制应该再次正常运行。

验证数据一致性：

在从服务器上，检查 test_replication.test_table 表的内容：

USE test_replication;
SELECT * FROM test_table;
exit

你应该会看到手动插入的冲突记录和来自主服务器的新记录。冲突记录已经存在，而新记录在跳过错误后已成功复制。

+----+-----------------------------------------------------+
| id | data                                                |
+----+-----------------------------------------------------+
|  1 | This is a test record from master                   |
|  2 | This is an intentionally conflicting record on the slave |
|  3 | This is a new record from master                    |
+----+-----------------------------------------------------+
3 rows in set (0.00 sec)

你现在已经成功模拟了一个复制错误，并通过跳过有问题的事件修复了它。这是解决由数据不一致导致的复制问题的常用技术。请记住，跳过事件时要谨慎，因为如果处理不当，可能会导致数据不一致。在跳过事件之前，了解错误的原因至关重要。

总结

在本次实验中，第一步的重点是通过启用二进制日志，将 MySQL 服务器配置为复制设置中的主服务器。这需要编辑 MySQL 配置文件（通常位于 /etc/mysql/mysql.conf.d/mysqld.cnf），并在 mysqld 部分添加 log_bin = mysql-bin 和 server_id = 1 这两行。

启用二进制日志至关重要，因为它能让主服务器记录所有的数据修改，从服务器随后会利用这些记录来复制更改，从而确保数据同步。log_bin 指令指定了二进制日志文件的基本名称，而 server_id 则为复制环境中的主服务器分配了一个唯一标识符。