MySQL部署基于二进制日志文件位置的主从复制集群

MySQL主从复制介绍

MySQL 主从复制（Master-Slave Replication） 作为一种经典的数据库复制方案，被广泛应用于企业生产环境，尤其是在提升数据库性能、实现数据备份和分布式扩展方面具有重要作用。

官方文档：https://dev.mysql.com/doc/refman/8.4/en/replication.html

复制功能可将一个 MySQL 数据库服务器（称为源）的数据复制到一个或多个 MySQL 数据库服务器（称为副本）。默认情况下，复制是异步的；副本不需要永久连接就能从源服务器接收更新。根据配置，您可以复制所有数据库、选定的数据库，甚至是数据库中选定的表。

MySQL 复制的优势包括：

• 扩展解决方案–将负载分散到多个副本中以提高性能。在这种环境下，所有写入和更新都必须在源服务器上进行。而读取可以在一个或多个副本上进行。这种模式可以提高写入的性能（因为源服务器专门用于更新），同时显著提高越来越多副本的读取速度。
• 数据安全–由于副本可以暂停复制过程，因此可以在副本上运行备份服务，而不会损坏相应的源数据。
• 分析 - 实时数据可在源上创建，而信息分析可在副本上进行，不会影响源的性能。
• 远距离数据分发–您可以使用复制功能创建一个本地数据副本，供远程站点使用，而无需永久访问源。

MySQL 支持不同的复制方法：

• 传统方法基于从源的二进制日志中复制事件，需要在源和副本之间同步日志文件和其中的位置。
• 基于全局事务标识符（GTID）的新方法是事务性的，因此不需要处理日志文件或这些文件中的位置，从而大大简化了许多常见的复制任务。只要在源上提交的所有事务也应用到了副本上，使用 GTID 的复制就能保证源和副本之间的一致性。

MySQL 中的复制支持不同类型的同步：

• 最初的同步类型是单向异步复制，即一台服务器作为源，一台或多台其他服务器作为副本。这与同步复制形成了鲜明对比，同步复制是 NDB 群集的特征之一。
• 在 MySQL 8.4 中，除了内置的异步复制外，还支持半同步复制。使用半同步复制时，在返回执行事务的会话之前，会先在源块上执行提交，直到至少有一个副本确认收到并记录了事务的事件。
• MySQL 8.4 还支持延迟复制，即副本故意落后源至少一段指定时间。
• 对于需要同步复制的情况，请使用 NDB 群集。

复制格式有两种核心类型：

基于语句的复制（SBR）和基于行的复制（RBR），前者复制整个 SQL 语句，后者只复制已更改的行。您还可以使用第三种复制格式，即混合复制（MBR）。

部署节点规划

以下是一个 MySQL 一主两从的主从复制集群，采用基于二进制日志文件位置的主从复制方案，主从同步类型采用默认的异步复制。

节点规划

数据目录：/var/lib/mysql

系统用户：mysql

数据库版本：MySQL 8.4.4 LTS

主从复制节点架构如下：

环境准备

每个节点单独配置主机名

hostnamectl set-hostname mysql-node31
hostnamectl set-hostname mysql-node32
hostnamectl set-hostname mysql-node33

每个节点配置hosts解析

MySQL默认会反向解析DNS，对于访问者MySQL不会判断是hosts还是ip都会进行dns反向解析。如果不配置主机名解析，可以通过[mysqld]下配置skip_name_resolve = 1禁用，强制使用 IP 地址连接。

cat >/etc/hosts<<EOF
192.168.72.31 mysql-node31
192.168.72.32 mysql-node32
192.168.72.33 mysql-node33
EOF

安装MySQL

使用APT源安装MySQL：https://dev.mysql.com/downloads/repo/apt/

添加MySQL APT仓库

wget https://repo.mysql.com//mysql-apt-config_0.8.33-1_all.deb
dpkg -i mysql-apt-config_0.8.33-1_all.deb

选择第一项：MySQL Server & Cluster (Currently selected: mysql-8.4-lts)

选择mysql-8.4-lts

更新APT仓库

apt update -y

查看可安装的版本

root@mysql-node31:~# apt-cache policy mysql-server
mysql-server:
  Installed: (none)
  Candidate: 8.4.4-1ubuntu22.04
  Version table:
     8.4.4-1ubuntu22.04 500
        500 http://repo.mysql.com/apt/ubuntu jammy/mysql-8.4-lts amd64 Packages
     8.0.41-0ubuntu0.22.04.1 500
        500 http://mirrors.aliyun.com/ubuntu jammy-updates/main amd64 Packages
        500 http://mirrors.aliyun.com/ubuntu jammy-security/main amd64 Packages
     8.0.28-0ubuntu4 500
        500 http://mirrors.aliyun.com/ubuntu jammy/main amd64 Packages

安装MySQL数据库

apt install -y mysql-server

设置ROOT密码

或者执行apt install命令前通过非交互式设置数据库ROOT密码

sudo debconf-set-selections <<< "mysql-server mysql-server/root_password password Mysql#123456"
sudo debconf-set-selections <<< "mysql-server mysql-server/root_password_again password Mysql#123456"

然后执行

sudo DEBIAN_FRONTEND=noninteractive apt install -y mysql-server

查看安装版本

root@mysql-node31:~# mysql -V
mysql  Ver 8.4.4 for Linux on x86_64 (MySQL Community Server - GPL)

查看数据目录

root@mysql-node31:~# ll /var/lib/mysql
total 103512
drwxr-x---  7 mysql mysql     4096 Jan 16 23:34  ./
drwxr-xr-x 47 root  root      4096 Jan 16 23:32  ../
-rw-r-----  1 mysql mysql       56 Jan 16 23:32  auto.cnf
-rw-r-----  1 mysql mysql      505 Jan 16 23:32  binlog.000001
-rw-r-----  1 mysql mysql      181 Jan 16 23:32  binlog.000002
-rw-r-----  1 mysql mysql      158 Jan 16 23:34  binlog.000003
-rw-r-----  1 mysql mysql       48 Jan 16 23:34  binlog.index
-rw-------  1 mysql mysql     1705 Jan 16 23:32  ca-key.pem
-rw-r--r--  1 mysql mysql     1108 Jan 16 23:32  ca.pem
-rw-r--r--  1 mysql mysql     1108 Jan 16 23:32  client-cert.pem
-rw-------  1 mysql mysql     1705 Jan 16 23:32  client-key.pem
-rw-r-----  1 mysql mysql  6291456 Jan 16 23:36 '#ib_16384_0.dblwr'
-rw-r-----  1 mysql mysql 14680064 Jan 16 23:32 '#ib_16384_1.dblwr'
-rw-r-----  1 mysql mysql     3439 Jan 16 23:32  ib_buffer_pool
-rw-r-----  1 mysql mysql 12582912 Jan 16 23:34  ibdata1
-rw-r-----  1 mysql mysql 12582912 Jan 16 23:34  ibtmp1
drwxr-x---  2 mysql mysql     4096 Jan 16 23:34 '#innodb_redo'/
drwxr-x---  2 mysql mysql      187 Jan 16 23:34 '#innodb_temp'/
drwxr-x---  2 mysql mysql      143 Jan 16 23:32  mysql/
-rw-r-----  1 mysql mysql 26214400 Jan 16 23:34  mysql.ibd
-rw-r-----  1 mysql mysql      124 Jan 16 23:32  mysql_upgrade_history
drwxr-x---  2 mysql mysql     8192 Jan 16 23:32  performance_schema/
-rw-------  1 mysql mysql     1709 Jan 16 23:32  private_key.pem
-rw-r--r--  1 mysql mysql      452 Jan 16 23:32  public_key.pem
-rw-r--r--  1 mysql mysql     1108 Jan 16 23:32  server-cert.pem
-rw-------  1 mysql mysql     1705 Jan 16 23:32  server-key.pem
drwxr-x---  2 mysql mysql       28 Jan 16 23:32  sys/
-rw-r-----  1 mysql mysql 16777216 Jan 16 23:36  undo_001
-rw-r-----  1 mysql mysql 16777216 Jan 16 23:36  undo_002

测试访问数据库

root@mysql-node31:~# mysql -uroot -pMysql#123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.4.4 MySQL Community Server - GPL

Copyright (c) 2000, 2025, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

mysql> 

默认已开启binlog

mysql> SHOW VARIABLES LIKE '%log_bin%';
+---------------------------------+-----------------------------+
| Variable_name                   | Value                       |
+---------------------------------+-----------------------------+
| log_bin                         | ON                          |
| log_bin_basename                | /var/lib/mysql/binlog       |
| log_bin_index                   | /var/lib/mysql/binlog.index |
| log_bin_trust_function_creators | OFF                         |
| sql_log_bin                     | ON                          |
+---------------------------------+-----------------------------+
5 rows in set (0.01 sec)

默认未开启及gtid模式

mysql> SHOW VARIABLES LIKE 'gtid_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_mode     | OFF   |
+---------------+-------+
1 row in set (0.01 sec)

mysql> 

创建复制账号

以下操作在所有节点执行。

备份账号的目的是为了让备份工具（如 mysqldump 或其他工具）能够访问数据库进行备份。备份账号通常被授予只读权限，确保它可以访问数据，但不能修改数据。建议在所有节点（1主2从）上都创建备份账号，原因如下：

• 高可用考虑：如果主库出现故障，从库可能需要提升为新主库，需要保证在切换后仍然可以正常执行备份操作，在所有节点都有备份账号可以避免主从切换后手忙脚乱地创建账号
• 数据验证需求：有时需要对从库做备份来验证数据一致性，从库备份可以降低主库负载，以及用于特殊场景如数据验证、数据迁移等
• 注意事项：权限要统一，避免各节点权限不一致导致问题，密码最好也保持一致，便于管理，建议限制允许连接的IP地址，不要用’%'，定期检查账号状态和权限

连接到数据库

mysql -uroot -pMysql#123456

创建专门用于复制的账户，MySQL使用 REPLICATION SLAVE 权限来创建用于复制的用户。

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

检查创建是否成功

mysql> select user,host from mysql.user where User='repl';
+------+------+
| user | host |
+------+------+
| repl | %    |
+------+------+
1 row in set (0.00 sec)

检查当前用户获得的授权

mysql> SHOW GRANTS FOR 'repl'@'%';
+----------------------------------------------+
| Grants for repl@%                            |
+----------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO `repl`@`%` |
+----------------------------------------------+
1 row in set (0.00 sec)

配置mysql-node31主数据库

查看默认配置

root@mysql-node31:~# cat /etc/mysql/mysql.conf.d/mysqld.cnf | grep -v "^#"

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
log-error       = /var/log/mysql/error.log

创建主从配置文件

cat >/etc/mysql/conf.d/replication.cnf<<EOF
[mysqld]
# 基础配置
server_id = 31
report-host = mysql-node31
read_only = 0

# 二进制日志 (Binlog) 相关
log_bin = /var/lib/mysql/binlog
log_bin_index = /var/lib/mysql/binlog.index
binlog_format = ROW
log_slave_updates = 1
sync_binlog = 1
binlog_cache_size = 1M
binlog_expire_logs_seconds = 604800

# 复制 (Replication) 相关
relay_log = /var/lib/mysql/relaylog
relay_log_index = /var/lib/mysql/relaylog.index
skip_slave_start = 0
sync-master-info = 1

# InnoDB 相关
innodb_flush_log_at_trx_commit = 1
EOF

配置参数说明：

• server-id：设置 MySQL 服务器的唯一标识符，通常用于主从复制环境。主从复制需要每个 MySQL 实例有唯一的 server_id。值可以是整数，取值范围：1 – 2的32次方-1，默认为1

• report-host：在主节点的复制日志中报告从节点的主机名或 IP 地址。在多主复制或有多个从节点时，设置此参数可以帮助主节点正确识别从节点的名称。

• read-only：0（默认）表示读写（主机），1表示只读（从机），设置 MySQL 实例是否允许写操作。0 表示关闭只读模式，允许写入（通常用于主库）。如果是从库，建议设置为 1，即只读模式（除了复制线程外不允许写入）。

• log-bin：二进制日志名，在 MySQL 8.0 及之后的版本中，binlog 默认启用。启用二进制日志，并设置二进制日志文件的路径和前缀。二进制日志用于记录数据更改（例如 INSERT、UPDATE 等）。

  在主从复制中，主库将通过二进制日志向从库同步数据。

  这里的路径 /var/lib/mysql/binlog 是二进制日志文件的存储位置，日志文件会以此为前缀。

• log_bin_index：指定用于记录二进制日志文件列表的索引文件路径。该文件存储所有二进制日志文件的名称。MySQL 会通过这个索引文件来管理和定位二进制日志。

• binlog_format：设置二进制日志的记录格式。可选值：STATEMENT：记录 SQL 语句。ROW：记录每行数据的变化。MIXED：混合模式。推荐使用 ROW，因为它能更精确地记录数据更改，避免因 SQL 语句依赖上下文而导致的不一致。

• log_slave_updates：启用从节点记录其接收到的 binlog。默认情况下，从节点不会记录自己接收到的日志。启用此选项后，从节点会将接收到的事件也记录到自己的 binlog 中，这对于链式复制（即从节点作为其他从节点的主节点）是有用的。

• sync_binlog：控制二进制日志的同步频率。1 表示每次提交事务时，将二进制日志同步到磁盘，确保数据的持久性。值越大，性能越高，但可能丢失更多的数据。推荐设置为 1，特别是在需要高数据一致性的场景。

• binlog_cache_size：每个会话的二进制日志缓存大小，减少大事务写入临时文件的开销。

• binlog_expire_logs_seconds：二进制日志的过期时间（7天），避免日志无限增长而占用磁盘空间。

• relay_log：设置从节点的 relay log 文件的存储路径。Relay log 用于记录从主节点接收到的二进制日志事件，从节点执行这些事件来同步数据。

• relay_log_index：设置记录 relay log 文件索引的路径。该文件用于跟踪当前使用的 relay log 文件，并帮助从节点在重新启动时继续执行未完成的日志事件。

• skip_slave_start：控制 MySQL 是否在启动时自动开始从复制的进程。设置为 0（默认值）表示从节点在启动时会自动启动复制进程。如果设置为 1，则从节点不会自动启动复制进程，必须手动启动。

• skip_slave_start = 1： 用于配置 MySQL 复制，默认从服务器在启动时不会自动启动 SQL_THREAD，需要手动执行 START SLAVE 来启动复制。

• innodb_flush_log_at_trx_commit：控制事务提交时 InnoDB 日志的刷新方式。1 表示每次提交事务时，将日志写入磁盘，提供最高的一致性。值可选：0：日志写入内存，每秒刷新一次到磁盘。1：每次提交事务时刷新到磁盘。2：提交事务时写入内存，每秒刷新到磁盘。推荐设置为 1，确保事务的持久性和一致性。

重新启动主数据库服务

systemctl restart mysql.service

查看数据库服务运行状态

root@mysql-node31:~# systemctl status mysql.service 
● mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: active (running) since Sat 2025-02-08 14:12:02 CST; 6s ago
       Docs: man:mysqld(8)
             http://dev.mysql.com/doc/refman/en/using-systemd.html
    Process: 4510 ExecStartPre=/usr/share/mysql-8.4/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
   Main PID: 4550 (mysqld)
     Status: "Server is operational"
      Tasks: 35 (limit: 4557)
     Memory: 428.4M
        CPU: 1.288s
     CGroup: /system.slice/mysql.service
             └─4550 /usr/sbin/mysqld

Feb 08 14:12:01 mysql-node31 systemd[1]: Starting MySQL Community Server...
Feb 08 14:12:02 mysql-node31 systemd[1]: Started MySQL Community Server.

获取复制源二进制日志坐标

在主节点上执行。

刷新所有表并阻止写入语句

mysql> FLUSH TABLES WITH READ LOCK;

要配置副本在正确的点开始复制过程，需要在二进制日志中记下源的当前坐标。

mysql> SHOW BINARY LOG STATUS;

示例输出

mysql> SHOW BINARY LOG STATUS;
+---------------+----------+--------------+------------------+------------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000003 |      859 |              |                  | a6045416-e5e7-11ef-ba53-005056aa4e2c:1-3 |
+---------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.01 sec)

解锁表:

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

mysql> exit;
Bye

参数说明

• File：表示当前正在使用的二进制日志文件名。例如：binlog.000003 表示当前活跃的二进制日志文件是 binlog.000003。
• Position：表示二进制日志文件中当前的写入位置（字节偏移量）。例如：1125 表示下一条事务或事件会记录到该文件的偏移位置 1125。
• Binlog_Do_DB：指定需要同步的数据库。显示被二进制日志包含（记录） 的数据库列表。如果为空（如上所示），表示没有设置过滤规则，所有数据库的修改都会记录到二进制日志。
• Binlog_Ignore_DB : 指定不需要同步的数据库。显示被二进制日志忽略的数据库列表。如果为空，表示没有设置过滤规则，所有数据库的修改都会记录。
• Executed_Gtid_Set：表示当前服务器已经执行过的 GTID 集合。例如：75f09330-d097-11ef-bf6e-005056aa349a:1-6，UUID：75f09330-d097-11ef-bf6e-005056aa349a 是生成这些事务的服务器的 UUID。事务范围：1-6 表示该服务器生成的从事务 1 到事务 6 已经被执行。

记录一下File和Position参数值，在从库配置同步源的时候需要用到。

配置mysql-node32从数据库

创建主从配置文件，其中仅server_id 、report-host、read_only参数与主节点不同。

cat >/etc/mysql/conf.d/replication.cnf<<EOF
[mysqld]
# 基础配置
server_id = 32
report-host = mysql-node32
read_only = 1

# 二进制日志 (Binlog) 相关
log_bin = /var/lib/mysql/binlog
log_bin_index = /var/lib/mysql/binlog.index
binlog_format = ROW
log_slave_updates = 1
sync_binlog = 1
binlog_cache_size = 1M
binlog_expire_logs_seconds = 604800

# 复制 (Replication) 相关
relay_log = /var/lib/mysql/relaylog
relay_log_index = /var/lib/mysql/relaylog.index
skip_slave_start = 0
sync-master-info = 1

# InnoDB 相关
innodb_flush_log_at_trx_commit = 1
EOF

重新启动从库服务：

systemctl restart mysql.service

使用root账号进行连接。

mysql -uroot -pMysql#123456

设置从库的同步源

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='192.168.72.31',
  SOURCE_USER='repl',
  SOURCE_PASSWORD='password',
  SOURCE_LOG_FILE='binlog.000003',
  SOURCE_LOG_POS=859,
  GET_SOURCE_PUBLIC_KEY=1;

以下是 CHANGE REPLICATION SOURCE TO 命令中每个参数的逐一说明：

• SOURCE_HOST 指定主服务器的主机地址或 IP 地址。复制从服务器将连接到该主服务器以获取更新。
• SOURCE_USER 指定用于连接主服务器的用户名。该用户需要具备主服务器上的 REPLICATION SLAVE 权限。
• SOURCE_PASSWORD 指定与 SOURCE_USER 对应的密码，用于从服务器验证连接主服务器的身份。
• SOURCE_LOG_FILE 指定复制开始的二进制日志文件名。从服务器将从主服务器的这个文件开始读取数据。
• SOURCE_LOG_POS 指定复制开始的二进制日志文件的字节偏移量（位置）。从服务器从该位置开始读取日志数据。
• GET_SOURCE_PUBLIC_KEY=1 启用获取主服务器的 RSA 公钥功能。用于在启用了 caching_sha2_password 身份验证插件时，从服务器安全地获取主服务器的公钥以建立加密的连接。

开始副本上的复制

mysql> START REPLICA;

在从库上查看复制状态:

mysql> SHOW REPLICA STATUS\G
*************************** 1. row ***************************
             Replica_IO_State: Waiting for source to send event
                  Source_Host: 192.168.72.31
                  Source_User: repl
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: binlog.000005
          Read_Source_Log_Pos: 198
               Relay_Log_File: relaylog.000002
                Relay_Log_Pos: 325
        Relay_Source_Log_File: binlog.000005
           Replica_IO_Running: Yes
          Replica_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_Source_Log_Pos: 198
              Relay_Log_Space: 529
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Source_SSL_Allowed: No
           Source_SSL_CA_File: 
           Source_SSL_CA_Path: 
              Source_SSL_Cert: 
            Source_SSL_Cipher: 
               Source_SSL_Key: 
        Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Source_Server_Id: 31
                  Source_UUID: 0bc34e5c-d41f-11ef-89eb-005056aa4e2c
             Source_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
    Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Source_Retry_Count: 10
                  Source_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Source_SSL_Crl: 
           Source_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 03cd971d-d41f-11ef-826e-005056aa349a:1-2
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Source_TLS_Version: 
       Source_public_key_path: 
        Get_Source_public_key: 1
            Network_Namespace: 
1 row in set (0.00 sec)

参数说明

• Replica_IO_State：副本的当前状态
• Replica_IO_Running：读取源二进制日志的 I/O（接收器）线程是否正在运行。通常情况下为Yes，除非您尚未启动复制或已明确停止复制，否则您希望此线程处于运行状态。
• Replica_SQL_Running：执行中继日志中事件的SQL线程是否正在运行。与I/O线程一样，这通常应该是Yes。
• Last_IO_Error, Last_SQL_Error：处理中继日志时I/O（接收方）和SQL（应用方）线程记录的最后错误。理想情况下，这些应该是空白的，表示没有错误。
• Seconds_Behind_Source：复制SQL（应用程序）线程处理源二进制日志所落后的秒数。数字过大（或不断增加）可能表示副本无法及时处理来自源的事件。值为0，Seconds_Behind_Source通常可以解释为副本已赶上源；但在某些情况下这并不完全正确。例如，如果源和副本之间的网络连接中断，但复制 I/O（接收器）线程尚未注意到这一点，即设置的时间段 replica_net_timeout尚未过去，则可能会发生这种情况。Seconds_Behind_Source无法准确反映情况。当复制 SQL（应用程序）线程赶上 I/O 时， Seconds_Behind_Source显示 0；但当复制 I/O（接收器）线程仍在排队新事件时，Seconds_Behind_Source可能会显示较大的值，直到复制应用程序线程完成执行新事件。当事件具有旧时间戳时尤其可能出现这种情况；在这种情况下，如果您在相对较短的时间内执行SHOW REPLICA STATUS 多次，您可能会看到此值在 0 和相对较大的值之间反复来回变化。

如果要暂停副本上的复制，执行以下命令

mysql> STOP REPLICA;

如果要重置复制，执行以下命令

mysql> RESET REPLICA ALL;

在主节点上查看复制节点信息

mysql> show replicas;
+-----------+--------------+------+-----------+--------------------------------------+
| Server_Id | Host         | Port | Source_Id | Replica_UUID                         |
+-----------+--------------+------+-----------+--------------------------------------+
|        32 | mysql-node32 | 3306 |        31 | c06a6c6d-e5e7-11ef-b5f3-005056aa349a |
+-----------+--------------+------+-----------+--------------------------------------+
1 row in set (0.00 sec)

配置mysql-node33从数据库

创建主从配置文件，其中仅server_id 、report-host、read_only参数与主节点不同。

cat >/etc/mysql/conf.d/replication.cnf<<EOF
[mysqld]
# 基础配置
server_id = 33
report-host = mysql-node33
read_only = 1

# 二进制日志 (Binlog) 相关
log_bin = /var/lib/mysql/binlog
log_bin_index = /var/lib/mysql/binlog.index
binlog_format = ROW
log_slave_updates = 1
sync_binlog = 1
binlog_cache_size = 1M
binlog_expire_logs_seconds = 604800

# 复制 (Replication) 相关
relay_log = /var/lib/mysql/relaylog
relay_log_index = /var/lib/mysql/relaylog.index
skip_slave_start = 0
sync-master-info = 1

# InnoDB 相关
innodb_flush_log_at_trx_commit = 1
EOF

重新启动从库服务：

systemctl restart mysql.service

使用root账号进行连接。

mysql -uroot -pMysql#123456

设置从库的同步源

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='192.168.72.31',
  SOURCE_USER='repl',
  SOURCE_PASSWORD='password',
  SOURCE_LOG_FILE='binlog.000003',
  SOURCE_LOG_POS=859,
  GET_SOURCE_PUBLIC_KEY=1;

以下是 CHANGE REPLICATION SOURCE TO 命令中每个参数的逐一说明：

• SOURCE_HOST 指定主服务器的主机地址或 IP 地址。复制从服务器将连接到该主服务器以获取更新。
• SOURCE_USER 指定用于连接主服务器的用户名。该用户需要具备主服务器上的 REPLICATION SLAVE 权限。
• SOURCE_PASSWORD 指定与 SOURCE_USER 对应的密码，用于从服务器验证连接主服务器的身份。
• SOURCE_LOG_FILE 指定复制开始的二进制日志文件名。从服务器将从主服务器的这个文件开始读取数据。
• SOURCE_LOG_POS 指定复制开始的二进制日志文件的字节偏移量（位置）。从服务器从该位置开始读取日志数据。
• GET_SOURCE_PUBLIC_KEY=1 启用获取主服务器的 RSA 公钥功能。用于在启用了 caching_sha2_password 身份验证插件时，从服务器安全地获取主服务器的公钥以建立加密的连接。

开始副本上的复制

mysql> START REPLICA;

在从库上查看复制状态:

mysql> SHOW REPLICA STATUS\G
*************************** 1. row ***************************
             Replica_IO_State: Waiting for source to send event
                  Source_Host: 192.168.72.31
                  Source_User: repl
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: binlog.000003
          Read_Source_Log_Pos: 859
               Relay_Log_File: relaylog.000002
                Relay_Log_Pos: 325
        Relay_Source_Log_File: binlog.000003
           Replica_IO_Running: Yes
          Replica_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_Source_Log_Pos: 859
              Relay_Log_Space: 529
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Source_SSL_Allowed: No
           Source_SSL_CA_File: 
           Source_SSL_CA_Path: 
              Source_SSL_Cert: 
            Source_SSL_Cipher: 
               Source_SSL_Key: 
        Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Source_Server_Id: 31
                  Source_UUID: a6045416-e5e7-11ef-ba53-005056aa4e2c
             Source_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
    Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Source_Retry_Count: 10
                  Source_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Source_SSL_Crl: 
           Source_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Source_TLS_Version: 
       Source_public_key_path: 
        Get_Source_public_key: 1
            Network_Namespace: 
1 row in set (0.00 sec)

在主节点上查看复制节点信息

mysql> show replicas;
+-----------+--------------+------+-----------+--------------------------------------+
| Server_Id | Host         | Port | Source_Id | Replica_UUID                         |
+-----------+--------------+------+-----------+--------------------------------------+
|        33 | mysql-node33 | 3306 |        31 | c2847b99-e5e7-11ef-9c61-005056aa3e12 |
|        32 | mysql-node32 | 3306 |        31 | c06a6c6d-e5e7-11ef-b5f3-005056aa349a |
+-----------+--------------+------+-----------+--------------------------------------+
2 rows in set (0.00 sec)

测试主从复制

创建测试数据库

在设置主从同步之后，在主库上创建一个测试数据库和表，并插入一些测试数据，以验证主从复制是否正常工作。

1. 在主库上创建测试数据库

使用以下命令在主库创建一个名为 test_db 的数据库。

CREATE DATABASE test_db;

2. 切换到新建的数据库

选择 test_db 数据库作为当前数据库。

USE test_db;

3. 创建测试表

创建一个简单的表，例如 test_table，用于存储测试数据。

CREATE TABLE test_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

字段解释：

• id: 主键，自增。
• name: 存储字符串类型的名称。
• age: 存储整数类型的年龄。
• created_at: 记录创建时间，默认值为当前时间。

4. 插入测试数据

向 test_table 表插入一些测试数据。

INSERT INTO test_table (name, age) VALUES
('Alice', 25),
('Bob', 30),
('Charlie', 35);

5. 查看插入的数据

通过查询验证数据是否成功插入。

SELECT * FROM test_table;

期望输出：

+----+---------+-----+---------------------+
| id | name    | age | created_at          |
+----+---------+-----+---------------------+
|  1 | Alice   |  25 | 2025-01-19 16:00:00 |
|  2 | Bob     |  30 | 2025-01-19 16:01:00 |
|  3 | Charlie |  35 | 2025-01-19 16:02:00 |
+----+---------+-----+---------------------+

6. 在从库验证同步数据

登录从库后，查询表中的数据，检查新插入的数据是否已经同步。

USE test_db;
SELECT * FROM test_table;

从库中查询到的数据应与主库一致：

+----+---------+-----+---------------------+
| id | name    | age | created_at          |
+----+---------+-----+---------------------+
|  1 | Alice   |  25 | 2025-01-19 16:00:00 |
|  2 | Bob     |  30 | 2025-01-19 16:01:00 |
|  3 | Charlie |  35 | 2025-01-19 16:02:00 |
+----+---------+-----+---------------------+

7. 检查从库同步状态

如果从库数据没有同步或查询不到新数据，使用以下命令检查从库同步状态：

mysql> SHOW REPLICA STATUS\G

重点检查字段：

• Slave_IO_Running: Yes，表示从库 I/O 线程正常运行。
• Slave_SQL_Running: Yes，表示从库 SQL 线程正常运行。
• Seconds_Behind_Master: 0，表示从库与主库之间没有明显延迟。