流漓 · 客

Appearance

查看MySQL同步状态 ​

使用命令 ​

sql
SHOW SLAVE STATUS \G;
sql
# 查看数据库同步状态信息
mysql> show slave status\G;
*************************** 1. row ***************************
# 等待master发生事件
# 用于表示从库 IO 线程当前的状态（slave 连接 master 的状态），通过 show processlist 也能看到 User = system user 的 State 列也显示了这个连接状态
Slave_IO_State: Waiting for master to send event                    
# 主库（master）服务器IP地址
Master_Host: 192.168.100.200  
# 用来同步数据的用户
Master_User: rsync
# 主库（master）服务器mysql监听的端口
Master_Port: 3306
# 断开重连时间（从库连接主库失败后，重试的时间间隔）
Connect_Retry: 60
# I/O线程当前正在读取的主服务器二进制日志文件的名称
Master_Log_File: mysql-bin.000123
# 本机从I/O线程读取主服务器二进制日志位置
Read_Master_Log_Pos: 107456
# SQL线程当前读取执行的中继日志文件的名称
Relay_Log_File: relay-bin.000009
# SQL线程读取执行中继日志的位置
Relay_Log_Pos: 253
# 由SQL线程已读到的主服务器二进制日志文件的名称（如果主从同步有延迟可看这个参数）
Relay_Master_Log_File: mysql-bin.000121
# I/O线程是否启动并成功连接到主服务器上
Slave_IO_Running: Yes
# SQL线程是否启动成功
Slave_SQL_Running: Yes
# 设置从master服务器进行同步的数据库，通过replicate-do-db选项设置
Replicate_Do_DB: 
# 设置不从master端进行数据同步的数据库，replicate-ignore-db选项设置
Replicate_Ignore_DB:
# 设置从master服务器进行同步的表，通过replicate-ignore-table选项设置
Replicate_Do_Table: 
# 设置不从master端进行数据同步的表，replicate-ignore-table选项设置
Replicate_Ignore_Table: 
#从服务器限制复制更新通配符模式匹配到的表的语句。模式可以包含‘%’和‘_’通配符，与LIKE模式匹配操作符具有相同的含义。要指定多个表，应多次使用该选项，每个表使用一次。该选项可以跨数据库进行更新。请读取该选项后面的注意事项。例如：--replicate-wild-do-table=foo%.bar%只复制数据库名以foo开始和表名以bar开始的表的更新。
Replicate_Wild_Do_Table: 
#告诉从服务器线程不要复制通配符模式匹配到的表的语句。要想忽略多个表，应多次使用该选项，每个表使用一次。该选项可以跨数据库进行更新。请读取该选项后面的注意事项。例如：--replicate-wild-ignore-table=foo%.bar%不复制数据库名以foo开始和表名以bar开始的表的更新。
Replicate_Wild_Ignore_Table: 
# 最近的一次错误码
Last_Errno: 0
# 最近一次错误码的内容
Last_Error: 
# 最近被使用的用于SQL_SLAVE_SKIP_COUNTER的值，例如：SET GLOBAL SQL_slave_SKIP_COUNTER = n；
Skip_Counter: 0
# SQL线程执行来自master的二进制日志最后一个事件位置（可同Relay_Master_Log_File一块判断同步延迟）
Exec_Master_Log_Pos: 107
# 所有原有的中继日志结合起来的总大小
Relay_Log_Space: 408
# 复制until条件，在stop slave,start slave(不带until)或server重启的时候会自动重置 
Until_Condition: None
# 复制停止的文件名 
Until_Log_File:
# 复制停止的文件位置
Until_Log_Pos: 0
# 是否使用SSL连接master，允许Yes。禁止No。如果允许SSL连接，但是从属服务器没有让SSL支持被启用，则值为Ignored。
Master_SSL_Allowed: No
# ssl agent文件ca-cert.pem的文件名，配置文件master-ca选项设置
Master_SSL_CA_File: 
# ssl agent文件ca-cert.pem的路径名，配置文件ca-cert.pem选项设置
Master_SSL_CA_Path: 
# ssl 授权文件，配置文件master-cert选项设置
Master_SSL_Cert: 
# ssl 加密算法，配置文件master-cipher选项设置
Master_SSL_Cipher: 
# ssl 密钥文件，配置文件master-key选项设置
Master_SSL_Key: 
# 测量从属服务器SQL线程和从属服务器I/O线程之间的时间差距，单位以秒计。
# 当前从库与主库的延迟：为 0 表示同步正常，为正数表示延迟的秒数，为 NULL 表示 IO 线程或 SQL 线程中至少有一个处于停止状态
Seconds_Behind_Master: 0
# 是否检查master的授权文件
Master_SSL_Verify_Server_Cert: No
# IO thread的上一次出错的错误号
Last_IO_Errno: 0
# IO thread的上一次出错的错误信息
Last_IO_Error: 
# SQL thread的上一次出错的错误号
Last_SQL_Errno: 0
# SQL thread的上一次出错的错误信息
Last_SQL_Error: 
# 忽略复制服务器ID
Replicate_Ignore_Server_Ids: 
# master 主机server ID
Master_Server_Id: 2
# 从master获取的UUID信息
Master_UUID: f782d734-1afa-11ea-9820-661cbd2ea646
# 本地master.info文件的位置
Master_Info_File: /data/mysql/master.info
# slave必须滞后于master的秒数
SQL_Delay: 0
# 当Slave_SQL_Running_State等master执行完MASTER_DELAY秒数之后执行的时间，此字段包含剩余的延迟秒数。在其他时候这个字段是 NULL
SQL_Remaining_Delay: NULL
# SQL线程的状态（类似于 Slave_IO_State）。该值与执行SHOW PROCESSLIST显示的SQL线程的State值相同
Slave_SQL_Running_State: Waiting for table level lock
# 在连接丢失的情况下，副本可以尝试重新连接到源的次数。
Master_Retry_Count
# 用于具有多个网络接口的salve节点，并确定选择哪个slave的网络接口来连接到源。通过CHANGE MASTER TO MASTER_BIND设置
Master_Bind
# 显示最近一次I/O发生错误的时间，格式为YYMMDD hh:mm:ss 
Last_IO_Error_Timestamp
# 显示最近一次SQL线程发生错误的时间，格式为YYMMDD hh:mm:ss
Last_SQL_Error_Timestamp
.....
  • Retrieved_Gtid_Set：用于表示从库已经从主库获取到的 GTID 集合。
  • Executed_Gtid_Set：用于表示从库已经执行过的 GTID 集合。
  • Auto_Position：用于表示从库是否使用 GTID 模式，如果使用则为 1，否则为 0。GTID 模式会自动定位到主库的 binlog 文件和位置，不需要手动配置 MASTER_LOG_FILE 和 MASTER_LOG_POS。

状态信息详解 ​

连接信息 ​

关于主从同步主库的连接信息参数包含：

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

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

主库信息 ​

关于主从同步主库的基本信息参数包含：

  • Master_Server_Id：主库的 SERVER_ID。
  • Master_UUID：主库的 UUID。
  • Master_Info_File：主库的信息文件，老版本默认存储在文件中，如 /var/lib/mysql/master.info；8.0 开始默认存储在表中，如 mysql.slave_master_info。

主从同步状态 ​

关于主从同步运行状态的参数包含：

  • 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。

主从日志文件 ​

关于日志文件的参数包含：

  • 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 文件的总大小，单位字节。

主从过滤规则 ​

关于主次同步忽略数据库和表的参数包含：

  • 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.%

主从同步停止条件 ​

关于主从同步停止条件的参数包含：

  • 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）。

主从同步延迟 ​

关乎主从同步延迟的参数包含：

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

主从同步加密传输 ​

关于主从同步加密传输的参数包含 Master_SSL_xxxx，共有 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”
  • Channel_Name：用于表示从库的通道名称。详细参考：Section 17.2.2, “Replication Channels”
  • 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”