2. MySQL 主从搭建 —— 查看同步状态

博主： zhaojun
发布时间：2023 年 09 月 27 日
1852 次浏览
暂无评论
8995字数
分类：默认分类

上一节我们快速搭建了一个主从同步的环境，可以将主库的修改自动同步到从库，如果你失败了，没有同步成功也没关系，通过这篇文档你可以看到如何查看同步的状态和错误信息来排查问题。

## 1. 通过命令查看状态
我们可以通过命令 `SHOW SLAVE STATUS` 可以查看当前从库的状态:

```mysql
mysql> SHOW SLAVE STATUS \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: mysql-master
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 157
               Relay_Log_File: mysql-relay-bin.000007
                Relay_Log_Pos: 373
        Relay_Master_Log_File: mysql-bin.000005
             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: 157
              Relay_Log_Space: 799
              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
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: 9262b8ed-52c9-11ee-81cb-0242ac1e0003
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
       Master_public_key_path:
        Get_master_public_key: 0
            Network_Namespace:
1 row in set, 1 warning (0.00 sec)
```

## 2. 状态信息详解

### 2.1 连接信息

关于主从同步主库的连接信息参数包含：
- `Master_Bind`：slave 从库在多网络接口的情况下使用，以确定用哪一个 slave 网络接口连接到 master。
- `Master_Host`：主库的 IP 地址。
- `Master_User`：用于从主库复制的账号。
- `Master_Port`：主库的端口号。
- `Connect_Retry`：从库连接主库失败后，重试的时间间隔。
- `Master_Retry_Count`：从库连接主库失败后，最大重试次数。

> 主库连接信息可通过 `CHANGE MASTER TO` 命令修改，可参考前面的命令。不过必须在停止主从同步的状态下进行，停止命令为 `STOP SLAVE;`

### 2.2 主库信息

关于主从同步主库的基本信息参数包含：
- `Master_Server_Id`：主库的 SERVER_ID。
- `Master_UUID`：主库的 UUID。
- `Master_Info_File`：主库的信息文件，老版本默认存储在文件中，如 `/var/lib/mysql/master.info`；8.0 开始默认存储在表中，如 `mysql.slave_master_info`。

### 2.3 主从同步状态

关于主从同步运行状态的参数包含：
- `Slave_IO_State`：用于表示从库 IO 线程当前的状态（slave 连接 master 的状态），通过 `show processlist` 也能看到 `User = system user` 的 `State` 列也显示了这个连接状态。
- `Slave_IO_Running`：用于表示从库 IO 线程是否正常，该线程用来从主库拉取 binlog 日志写入到 relay log 中。如果拉取过程出现异常，如网络问题，主库宕机，用于从主库复制的账号密码变化等，该线程会停止。
- `Slave_SQL_Running_State`：用于表示从库 SQL 线程当前的状态（slave 执行 binlog 日志的状态）。
- `Slave_SQL_Running`：用于表示从库 SQL 线程是否正常，该线程用来将 relay log 中的日志在从库执行，这样会记录到当前从库的 binlog 中。如果执行过程出现异常，如 sql 语句报错，主键/唯一键冲突等，该线程会停止。
- `Last_SQL_Errno`：用于表示从库 SQL 线程最后一次执行出错的错误码，正常情况下应该为 0。
- `Last_SQL_Error`：用于表示从库 SQL 线程最后一次执行出错的错误信息。（可能会在提示信息中让去 performance_schema.replication_applier_status_by_worker 表中查看更详细的错误信息）
- `Last_SQL_Error_Timestamp`：用于表示从库 SQL 线程最后一次执行出错的时间。
- `Last_Errno`：等同于 `Last_SQL_Errno`，只是另一种别名。
- `Last_Error`：等同于 `Last_SQL_Error`，只是另一种别名。
- `Last_IO_Errno`：用于表示从库 IO 线程最后一次执行出错的错误码，正常情况下应该为 0。
- `Last_IO_Error`：用于表示从库 IO 线程最后一次执行出错的错误信息，如网络问题，主库宕机，用于从主库复制的账号密码变化等。
- `Last_IO_Error_Timestamp`：用于表示从库 IO 线程最后一次执行出错的时间。
- `Seconds_Behind_Master`: 表示当前从库与主库的延迟：为 0 表示同步正常，为正数表示延迟的**秒数**，为 NULL 表示 IO 线程或 SQL 线程中至少有一个处于停止状态。
- `Retrieved_Gtid_Set`：用于表示从库已经从主库获取到的 GTID 集合。
- `Executed_Gtid_Set`：用于表示从库已经执行过的 GTID 集合。
- `Auto_Position`：用于表示从库是否使用 GTID 模式，如果使用则为 1，否则为 0。GTID 模式会自动定位到主库的 binlog 文件和位置，不需要手动配置 `MASTER_LOG_FILE` 和 `MASTER_LOG_POS`。

### 2.4 主从日志文件

关于日志文件的参数包含：
- `Master_Log_File`：IO 线程当前正在读取的主库 binlog 文件名。
- `Read_Master_Log_Pos`：IO 线程当前正在读取的主库 binlog 文件偏移量。
- `Relay_Log_File`：SQL 线程当前执行到的 relay log 文件名。
- `Relay_Log_Pos`：SQL 线程当前执行到的 relay log 文件偏移量；到此位置的事件已在当前从库上执行。
- `Relay_Master_Log_File`：SQL 线程当前正在执行 relay log 中的 sql 所在主库的 binlog 文件名。（如 Master_Log_File: mysql-bin.000005，但是 Relay_Master_Log_File: mysql-bin.000004，说明当前主从存在延迟，主库已经写入到了 mysql-bin.000005，但是从库还在执行 mysql-bin.000004）
- `Exec_Master_Log_Pos`:SQL 线程当前正在执行 relay log 中的 sql 所在主库的 binlog 文件偏移量。

简单来讲就是从库先通过 IO 线程读取主库的二进制文件（Master_Log_File）和位置（Read_Master_Log_Pos）然后缓存到本地（从库服务器）的中继文件（Relay_Log_File）中并记录已经读取到的位置（Relay_Log_Pos），再通过从库的 SQL 线程去读取中继文件（Relay_Log_File），这个 SQL 线程执行会记录已经执行到的 SQL 在主库中对应哪个文件（Relay_Master_Log_File）和哪个位置（Exec_Master_Log_Pos）。
如果 Master_Log_File = Relay_Master_Log_File，且 Read_Master_Log_Pos = Exec_Master_Log_Pos 说明主从同步正常，当前状态一致。

> 这里的 xxx_Pos 指文件的偏移量，用字节表示，如 100 表示文件的第 100 个字节。

- `Relay_Log_Space`：所有 relay log 文件的总大小，单位字节。

### 2.5 主从过滤规则

关于主次同步忽略数据库和表的参数包含：
- `Replicate_Do_DB`：需要同步的数据库，多个数据库之间用逗号分隔。
- `Replicate_Ignore_DB`：需要忽略的数据库，多个数据库之间用逗号分隔。
- `Replicate_Do_Table`：需要同步的表，多个表之间用逗号分隔，注意这里的表名是 `db.table` 的形式。如果需要同步多个库的同名表，可以使用 `db1.table1,db2.table1` 的形式。
- `Replicate_Ignore_Table`：需要忽略的表，多个表之间用逗号分隔，注意这里的表名是 `db.table` 的形式。如果需要同步多个库的同名表，可以使用 `db1.table1,db2.table1` 的形式。
- `Replicate_Wild_Do_Table`：需要同步的表，支持通配符，多个表之间用逗号分隔。格式为 `db.table` 的形式，但是支持通配符 `%` 和 `_`，含义和 like 语句中的通配符一样。
- `Replicate_Wild_Ignore_Table`：需要忽略的表，支持通配符，多个表之间用逗号分隔。格式为 `db.table` 的形式，但是支持通配符 `%` 和 `_`，含义和 like 语句中的通配符一样（`%` 表示任意字符，`_` 表示任意单个字符）。
- `Replicate_Ignore_Server_Ids`：需要让 IO 线程忽略的主库 ID（不从该主库获取事件），多个 ID 之间用逗号分隔。

这些参数可以在配置文件中配置，也可以在线修改，以 `REPLICATE_DO_DB` 为例： `CHANGE REPLICATION FILTER REPLICATE_DO_DB = (db1 db2);`，不过同样需要在停止主从同步的状态下进行，停止命令为 `STOP SLAVE;`。

不过对于 `Replicate_Do_DB` 和 `Replicate_Ignore_DB` 这两个参数，不建议使用，因为可能会丢失数据。比如你在从库中设置了 `Replicate_Do_DB=db1`，打算用来只同步 db1 库，但是如果你在主库执行 `use mysql; create table db1.t (id int)`，那么这个语句不会被同步到从库。`Replicate_Ignore_DB` 同理。
所以更建议使用 `Replicate_Wild_Do_Table` 和 `Replicate_Wild_Ignore_Table` 这两个参数，这样可以避免上面的问题。如之前通过 `REPLICATE_DO_DB` 配置值为 `db1 db2`，在 `Replicate_Wild_Do_Table` 上可以配置成 `db1.% db2.%`

### 2.6 主从同步停止条件

关于主从同步停止条件的参数包含：
- `Until_Condition`：用于表示从库停止同步的条件，默认为 `None`，可在 `START SLAVE UNTIL ...` 命令中修改。 如 `START SLAVE UNTIL MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=879;`，表示从库同步到主库的 `mysql-bin.000003` 文件的 `879` 位置时停止同步。可用的过滤条件有：
  - `None`：不停止。
  - `MASTER_LOG_FILE` 和 `MASTER_LOG_POS`：当从库同步到主库的指定 binlog 文件和位置时停止同步。
  - `RELAY_LOG_FILE` 和 `RELAY_LOG_POS`：当从库同步到自己的 relay log 文件的指定位置时停止同步。
  - `SQL_BEFORE_GTIDS`：之前的 GTID 都会被执行，如 `START SLAVE SQL_THREAD UNTIL SQL_BEFORE_GTIDS = 3E11FA47-71CA-11E1-9E33-C80AA9429562:11-56` 表示，当 `SQL_thread` 执行到 `3E11FA47-71CA-11E1-9E33-C80AA9429562:10` 的时候停止，不包含事务码 11。
  - `SQL_AFTER_GTIDS`：之前和指定的 GTID 都会被执行。如 `START SLAVE SQL_THREAD UNTIL SQL_AFTER_GTIDS = 3E11FA47-71CA-11E1-9E33-C80AA9429562:11-56` 表示，当 `SQL_thread` 执行到 `3E11FA47-71CA-11E1-9E33-C80AA9429562:56` 的时候停止，包含事务码 56。
- `Until_Log_File`：用于表示从库停止同步的条件，与 `Until_Condition` 配合使用，表示从库停止同步的 binlog 文件名（如果使用的不是 binlog 限制，则这里为空）。
- `Until_Log_Pos`：用于表示从库停止同步的条件，与 `Until_Condition` 配合使用，表示从库停止同步的 binlog 文件偏移量（如果使用的不是 binlog 限制，则这里为 0）。

### 2.7 主从同步延迟

关乎主从同步延迟的参数包含：
- `Seconds_Behind_Master`：用于表示当前从库与主库的延迟，为 0 表示同步正常，为正数表示延迟的**秒数**，为 NULL 表示 IO 线程或 SQL 线程中至少有一个处于停止状态。
- `SQL_Delay`：用于表示从库 SQL 线程延迟执行的时间，单位秒。设置方式如 `CHANGE MASTER TO MASTER_DELAY = 3600`，表示延迟 1 小时执行。
- `SQL_Remaining_Delay`：用于表示从库 SQL 线程延迟执行的剩余时间，单位秒。

### 2.8 主从同步加密传输

关于主从同步加密传输的参数包含 `Master_SSL_xxxx`，共有 9 个参数，用于表示主从同步的加密传输相关的配置，这里不再赘述。

### 2.9 其他参数

还有一些其他的参数，是新版本的参数，这里统一说明下：
- `Master_TLS_Version`：用于表示主库和从库之间的 TLS 版本，如 `TLSv1,TLSv1.1,TLSv1.2`。
- `Master_public_key_path`：用于表示主库的公钥文件路径。
- `Get_master_public_key`：用于表示从库是否获取主库的公钥，如果为 1，则从库会获取主库的公钥，如果为 0，则不会获取。
- `Network_Namespace`：用于表示从库的网络命名空间。详细参考：[Section 5.1.14, “Network Namespace Support”](https://dev.mysql.com/doc/refman/8.0/en/network-namespace-support.html)
- `Channel_Name`：用于表示从库的通道名称。详细参考：[Section 17.2.2, “Replication Channels”](https://dev.mysql.com/doc/refman/8.0/en/replication-channels.html)
- `Replicate_Rewrite_DB`：值显示指定的任何复制过滤规则。例如，如果设置了以下复制过滤规则 `CHANGE REPLICATION FILTER REPLICATE_REWRITE_DB=((db1,db2), (db3,db4));`，则 `Replicate_Rewrite_DB` 显示：`Replicate_Rewrite_DB: (db1,db2),(db3,db4)`。 详情参考：[Section 13.4.2.2, “CHANGE REPLICATION FILTER Statement”](https://dev.mysql.com/doc/refman/8.0/en/change-replication-filter.html)

最后修改：2024 年 02 月 20 日

如果觉得我的文章对你有用，请我喝杯咖啡吧。

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款

评论 *

私密评论

名称 *

🎲

邮箱 *

地址

快帆云
你好，我们的机场：快帆云，已经稳定运营三年多，现在诚邀推广员。...
在nginx层面给任意项目站点增加访问限制：casdoor+oauth2-proxy - Blog By chenchen.site
[...]如果是宝塔用户可以直接使用以下方法安装模块，参考文章...
大概
你好，我就想整一个网盘，用作知识付费，就是普通用户只可以看到标...
zhao
测试
知名1842
表评论1842

2. MySQL 主从搭建 —— 查看同步状态

zhaojun • 2023 年 09 月 27 日

## 1. 通过命令查看状态
我们可以通过命令 `SHOW SLAVE STATUS` 可以查看当前从库的状态:

## 2. 状态信息详解

### 2.1 连接信息

> 主库连接信息可通过 `CHANGE MASTER TO` 命令修改，可参考前面的命令。不过必须在停止主从同步的状态下进行，停止命令为 `STOP SLAVE;`

### 2.2 主库信息

### 2.3 主从同步状态

### 2.4 主从日志文件

> 这里的 xxx_Pos 指文件的偏移量，用字节表示，如 100 表示文件的第 100 个字节。

- `Relay_Log_Space`：所有 relay log 文件的总大小，单位字节。

### 2.5 主从过滤规则

### 2.6 主从同步停止条件

### 2.7 主从同步延迟

### 2.8 主从同步加密传输

关于主从同步加密传输的参数包含 `Master_SSL_xxxx`，共有 9 个参数，用于表示主从同步的加密传输相关的配置，这里不再赘述。

### 2.9 其他参数

2. MySQL 主从搭建 —— 查看同步状态

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款

谈裸辞和开源的体会

剑指 Offer 04. 二维数组中的查找

宝塔 nginx 增加编译模块

通过 Github Actions 自动构建 ZFile 镜像并推送到 Docker Hub

优化 Java 项目 Docker 镜像内存占用从 500 M 到 100M

MySQL InnoDB 事务隔离级别的实现原理

Shiro 学习 02 - 授权

代码命名之 Spring

宝塔 nginx 增加编译模块

leetcode 724 寻找数组的中心下标

2. MySQL 主从搭建 —— 查看同步状态

发表评论 取消回复 使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款

2. MySQL 主从搭建 —— 查看同步状态

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款