mysql schema 复制_MySQL复制-复制相关信息的存储及查询

最新推荐文章于 2022-09-23 11:13:23 发布

ATyla

最新推荐文章于 2022-09-23 11:13:23 发布

阅读量297

点赞数

文章标签： mysql schema 复制

本文链接：https://blog.csdn.net/weixin_35718242/article/details/114757577

版权

本文问题

如何检查复制状态，需要重点关注哪些内容？

如何在主库上查询连接到该主库的从库信息，详细信息需要通过哪些选项进行配置

在从库上能够查询到哪些主库相关的信息，这些信息都存储在什么位置？如何进行查询和更改？

P_S提供了哪些表用来监控复制相关的信息？这些表可以分为几种？

P_S中哪些表提供了主库相关的信息，其中包含了哪些内容？

P_S中哪些表提供了应用日志相关的信息，其中包含哪些内容？

P_S中哪些表提供了关于组复制相关的信息，其中包含了哪些内容？

在MySQL8.0中增加了关于复制什么信息相关的P_S表，其中包含了哪些内容？

replication_applier_status_by_worker中LAST_SEEN_TRANSACTION列的值代表什么？

检查复制相关的信息

主库

检查同步使用的线程

mysql> SHOW PROCESSLIST \G;

*************************** 4. row ***************************

Id: 10

User: root

Host: slave1:58371

db: NULL

Command: Binlog Dump

Time: 777

State: Has sent all binlog to slave; waiting for binlog to be updated

Info: NULL

mysql> SELECT * FROM performance_schema.threads WHERE processlist_command='Binlog Dump'\G

*************************** 1. row ***************************

THREAD_ID: 4738497

NAME: thread/sql/one_connection

TYPE: FOREGROUND

PROCESSLIST_ID: 4738476

PROCESSLIST_USER: repl

PROCESSLIST_HOST: 192.168.1.100

PROCESSLIST_DB: NULL

PROCESSLIST_COMMAND: Binlog Dump

PROCESSLIST_TIME: 334217

PROCESSLIST_STATE: Master has sent all binlog to slave; waiting for more updates

PROCESSLIST_INFO: NULL

PARENT_THREAD_ID: NULL

ROLE: NULL

INSTRUMENTED: YES

HISTORY: YES

CONNECTION_TYPE: TCP/IP

THREAD_OS_ID: 7412

检查连接到该主库的从库信息

mysql> SHOW SLAVE HOSTS;

+-----------+------+------+-----------+--------------------------------------+

+-----------+------+------+-----------+--------------------------------------+

| 521 | | 3306 | 1 | c92c2f8f-69d4-11ea-9aef-005056b0ee80 |

+-----------+------+------+-----------+--------------------------------------+

SHOW SLAVE HOSTS的结果可以根据主库/从库上的配置进行变更

主库

--show-slave-auth-info 在结果中增加User和Password列，显示从库连接用户的信息

从库

可以通过一系列--report-*选项设置在主库的SHOW SLAVE HOSTS结果中显示的值，可以不和实际配置相同。

--report-host 指定显示在Host列的值，默认为空

--report-port 指定显示在Port列的值，默认为实际端口号

--report-user 指定显示在User列的值，默认为空

--report-passrowd 指定显示在Password列的值，默认为空

从库

检查复制状态

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.100

Master_User: rep

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000944

Read_Master_Log_Pos: 678537245

Relay_Log_File: mysqld-relay-bin.001361

Relay_Log_Pos: 901225284

Relay_Master_Log_File: mysql-bin.000892

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: 901225071

Relay_Log_Space: 63050164162

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: 190330

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: 9f793d8c-b297-11e9-8b04-005056860daf

Master_Info_File: mysql.slave_master_info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: System lock

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:

需要关注的信息：

从库线程状态

Slave_IO_Running和Slave_SQL_Running 从库的IO和SQL线程是否正在运行。正常值应该为YES

Slave_IO_State和Slave_SQL_State 从库的线程状态

Last_IO_Error和Last_SQL_Error 最后出现的从库线程错误，正常值应该为空

Seconds_Behind_Master 从库SQL线程落后与主库二进制日志的秒数。该值是通过对比SQL线程正在执行的中继日志事务时间和IO线程获取到的最新二进制日志事务时间得到的，不能体现网络延迟，所以值为0不代表一定没有延迟

日志状态

(Master_Log_file， Read_Master_Log_Pos)IO线程读取的主库的二进制日志坐标

(Relay_Master_Log_File， Exec_Master_Log_Pos)SQL线程正在执行的主库的二进制日志坐标

(Relay_Log_File， Relay_Log_Pos) SQL线程正在执行的从库的中继日志坐标

重连信息

Connect_Retry 丢失主库连接后的重试时间

Master_Retry_Count 丢失主库连接后的重试次数

关于复制的状态信息

mysql> show status like 'Slave%';

+------------------------+-------+

| Variable_name | Value |

+------------------------+-------+

| Slave_open_temp_tables | 0 |

+------------------------+-------+

Performance_schema提供的关于复制的信息

复制连接信息

replication_connection_configuration 连接主库的配置参数

与SHOW SLAVE STATUS相比，额外提供了心跳间隔相关的信息(HEARTBEAT_INTERVAL)

replication_connection_status 当前与主库的连接状态

与SHOW SLAVE STATUS相比，额外提供了上次心跳时间和心跳次数信息(LAST_HEARTBEAT_TIMESTAMP,COUNT_RECEIVED_HEARTBEATS)

应用日志情况

replication_applier_configuration 从库上应用事务的配置

显示了MASTER_DELAY信息(DESIRED_DELAY)

replication_applier_status 从库上应用事务的状态

与SHOW SLAVE STATUS相比，额外提供从库重试应用事务的次数(COUNT_TRANSACTIONS_RETRIES)

多线程复制情况

replication_applier_status_by_coordinator

多线程复制的协调器工作情况，当slave_parallel_workers=0时复制是单线程的，该表结果为空

replication_applier_status_by_worker

单线程复制中SQL线程的状态/多线程复制中工作线程的状态

需要关注LAST_SEEN_TRANSACTION列，表示工作线程最后处理的一个事务，这个事务可能没有提交，仍在进行中。

当gtid_mode=OFF时，该列为ANONYMOUS

当gtid_mode=ON时：

如过没有事务执行，该列为空

当事务开始执行时，该列值等于gtid_next，事务执行完毕后不会更新该值。

在下一个事务执行时更新该列值。

复制过滤情况

replication_applier_global_filters (8.0)

replication_applier_filters (8.0)

组复制相关信息

replication_group_members

replication_group_member_stats

检查复制连接信息

#包括连接重试时间、重试次数及心跳间隔

#心跳间隔默认情况下等于Connect_Retry/2

mysql> select * from performance_schema.replication_connection_configuration\G

*************************** 1. row ***************************

CHANNEL_NAME:

HOST: 192.168.1.100

PORT: 3306

USER: rep

NETWORK_INTERFACE:

AUTO_POSITION: 0

SSL_ALLOWED: NO

SSL_CA_FILE:

SSL_CA_PATH:

SSL_CERTIFICATE:

SSL_CIPHER:

SSL_KEY:

SSL_VERIFY_SERVER_CERTIFICATE: NO

SSL_CRL_FILE:

SSL_CRL_PATH:

CONNECTION_RETRY_INTERVAL: 60 #连接重试时间

CONNECTION_RETRY_COUNT: 86400 #连接重试次数

HEARTBEAT_INTERVAL: 30.000 #心跳时间

TLS_VERSION:

1 row in set (0.00 sec)

检查复制连接状态

#包括心跳次数、上次心跳连接时间

mysql> select * from performance_schema.replication_connection_status\G

*************************** 1. row ***************************

CHANNEL_NAME:

GROUP_NAME:

SOURCE_UUID: bb4b28c0-cc47-11e8-a38f-20040fe734f0

THREAD_ID: 34

SERVICE_STATE: ON #slave_running

COUNT_RECEIVED_HEARTBEATS: 1202924

LAST_HEARTBEAT_TIMESTAMP: 2020-04-14 11:48:33

RECEIVED_TRANSACTION_SET:

LAST_ERROR_NUMBER: 0

LAST_ERROR_MESSAGE:

LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00

从库日志

从库创建两个日志来记录主库信息和中继日志信息

通过master-info-repository和relay-log-info-repository选项可以控制将这两个日志记录到文件或者表中。

FILE

MySQL5.7-默认值

日志保存在datadir的master.info和relay-log.info文件中。可以使用--master-info-file和--relay-log-info-file选项来更改文件位置和名称。

TABLE

MySQL8.0+的默认值

日志保存在mysql.slave_master_info和mysql.slave_relay_log_info表中

在从库上，还会额外创建一个供内部使用的从库状态日志，保存有关多线程复制上的工作线程的状态，这个日志根据relay-log-info-repository的值确定保存在文件/表中。当值为TABLE时，保存在mysql.slave_worker_info中，当值为FILE时，保存在worker-relay-log.info文件中。performance_schema.replication_applier_status_by_worker表提供了供外部使用的工作线程信息

在备份从库时，需要备份这两个日志和中继日志文件，以在从库还原数据库后用来恢复复制。

检查主库信息

mysql> select * from mysql.slave_master_info\G

*************************** 1. row ***************************

Number_of_lines: 25

Master_log_name: mysql-bin.000944

Master_log_pos: 583723012

Host: 192.168.1.100

User_name: rep #复制账号

User_password: password #复制账号密码

Port: 3306

Connect_retry: 60

Enabled_ssl: 0

Ssl_ca:

Ssl_capath:

Ssl_cert:

Ssl_cipher:

Ssl_key:

Ssl_verify_server_cert: 0

Heartbeat: 30

Bind:

Ignored_server_ids: 0

Uuid: 9f793d8c-b297-11e9-8b04-005056860daf

Retry_count: 86400

Ssl_crl:

Ssl_crlpath:

Enabled_auto_position: 0

Channel_name:

Tls_version:

检查中继日志信息

mysql> select * from mysql.slave_relay_log_info\G

*************************** 1. row ***************************

Number_of_lines: 7

Relay_log_name: /home/data/mysql3306/relay_log/mysqld-relay-bin.001334

Relay_log_pos: 543150366

Master_log_name: mysql-bin.000883

Master_log_pos: 543150153

Sql_delay: 0

Number_of_workers: 0

Id: 1

Channel_name:

问题答案

如何检查复制状态，需要重点关注哪些内容？

SHOW SLAVE STATUS，需要关注以下信息

Slave_IO_Running和Slave_SQL_Running均为Yes表示复制正在正常运行

Senconds_Behind_Master表示SQL线程没有延迟

Master_Log_File,Relay_Master_Log_File和Read_Master_Log_Pos,Exec_Master_Log_Pos相同表示主从同步没有延迟

如果Slave_IO_Running或Slave_SQL_Running值为No，还要查看Last_IO_Error或Last_SQL_Error检查发生了什么错误

如何在主库上查询连接到该主库的从库信息，详细信息可以通过哪些选项进行配置？

可以通过查看是否有Binlog Dump线程来查看是否存在从库，在查看线程时可以看到从库的IP地址。还可以通过SHOW SLAVE HOSTS语句查看当前有多少个从库连接到本数据库。

配置SHOW SLAVE HOSTS显示的结果：

主库：--show-slave-auth-info，在结果中展示User和Password列

从库：--report-host,--report-port,--report-user,--report-password

在从库上能够查询到哪些主库相关的信息，这些信息都存储在什么位置？如何进行查询和更改？

使用CHAGNE MASTER TO语句设置的信息都能够查询到，大部分信息可以通过SHOW SLAVE STATUS进行查询。

除此以外，主库还维护了记录主库和中继日志的信息，根据master_info_repository和relay_log_info_repository的配置，保存表或文件中，这其中包括了复制使用的账号和明文密码：

TABLE：mysql.slave_master_info和mysql.slave_relay_log_info

FILE：数据目录的master.info和relay_log.info文件中

P_S提供了哪些表用来监控复制相关的信息？这些表可以分为几种？

主库连接相关信息replication_connection_configuration, replacation_connection_status

从库应用日志相关信息 replication_applier_configuration,replication_applier_status,replication_applier_status_by_worker

多线程从库相关信息 replication_applier_status_by_coordinator,replication_applier_status_by_worker

从库过滤器相关信息 (MySQL8.0+)replication_applier_filters,replication_applier_global_filters

组复制相关信息 replication_group_members,replication_group_member_stats

P_S中哪些表提供了主库相关的信息，其中包含了哪些内容？

replication_connection_configuration 包括host,port,user,bind,auto_position,SSL相关信息，connection_retry_time,count，心跳时间

replication_connection_staus 包括通道名称，组名称，主库UUID，线程状态，接收到的事务集合，上次心跳时间和心跳次数，IO线程错误消息

mysql> desc replication_connection_configuration;

+-------------------------------+----------------------------+------+-----+---------+-------+

+-------------------------------+----------------------------+------+-----+---------+-------+

| PORT | int(11) | NO | | NULL | |

| CONNECTION_RETRY_INTERVAL | int(11) | NO | | NULL | |

+-------------------------------+----------------------------+------+-----+---------+-------+

19 rows in set (0.00 sec)

mysql> desc replication_connection_status;

+---------------------------+-------------------------------+------+-----+---------------------+-----------------------------+

+---------------------------+-------------------------------+------+-----+---------------------+-----------------------------+

| COUNT_RECEIVED_HEARTBEATS | bigint(20) unsigned | NO | | 0 | |

| LAST_ERROR_NUMBER | int(11) | NO | | NULL | |

| LAST_ERROR_TIMESTAMP | timestamp | NO | | 0000-00-00 00:00:00 | |

+---------------------------+-------------------------------+------+-----+---------------------+-----------------------------+

11 rows in set (0.00 sec)

P_S中哪些表提供了复制应用日志相关的信息，其中包含哪些内容？

replication_applier_configuration 包括包括通道名称,从库强制延迟时间

replication_applier_status 包括包括通道名称,SQL线程状态,等待从库的延迟时间,事务重试次数

replication_applier_status_by_worker包括包括通道名称，工作线程ID，线程ID，SQL线程状态，最后执行的事务GTID，SQL线程错误信息

mysql> desc replication_applier_configuration;

+---------------+----------+------+-----+---------+-------+

+---------------+----------+------+-----+---------+-------+

| DESIRED_DELAY | int(11) | NO | | NULL | |

+---------------+----------+------+-----+---------+-------+

2 rows in set (0.00 sec)

mysql> desc replication_applier_status;

+----------------------------+---------------------+------+-----+---------+-------+

+----------------------------+---------------------+------+-----+---------+-------+

+----------------------------+---------------------+------+-----+---------+-------+

4 rows in set (0.00 sec)

mysql> desc replication_applier_status_by_worker;

+-----------------------+---------------------+------+-----+-------------------+-----------------------------+

+-----------------------+---------------------+------+-----+-------------------+-----------------------------+

| LAST_ERROR_NUMBER | int(11) | NO | | NULL | |

+-----------------------+---------------------+------+-----+-------------------+-----------------------------+

8 rows in set (0.00 sec)

P_S中哪些表提供了关于组复制相关的信息，其中包含了哪些内容？

replication_group_members 包括通道名称，成员ID,host ,port成员状态

replication_group_member_stats

mysql> desc replication_group_members;

+--------------+----------+------+-----+---------+-------+

+--------------+----------+------+-----+---------+-------+

| MEMBER_PORT | int(11) | YES | | NULL | |

+--------------+----------+------+-----+---------+-------+

5 rows in set (0.00 sec)

mysql> desc replication_group_member_stats;

+------------------------------------+---------------------+------+-----+---------+-------+

+------------------------------------+---------------------+------+-----+---------+-------+

+------------------------------------+---------------------+------+-----+---------+-------+

9 rows in set (0.00 sec)

在MySQL8.0中增加了关于复制什么信息相关的P_S表，其中包含了哪些内容？

关于复制过滤器信息的表

replication_applier_filters 包括通道名称，过滤器名称，过滤规则，过滤规则被谁配置，启用时间，已经运行了多长时间

replication_applier_global_filters 包括通道名称，过滤器名称，过滤规则，过滤规则被谁配置，启用时间

mysql> desc replication_applier_filters;

+---------------+---------------------------------------------------------------------------------------------------------------------------+------+-----+----------------------------+-------+

+---------------+---------------------------------------------------------------------------------------------------------------------------+------+-----+----------------------------+-------+

| ACTIVE_SINCE | timestamp(6) | NO | | 0000-00-00 00:00:00.000000 | |

| COUNTER | bigint unsigned | NO | | 0 | |

+---------------+---------------------------------------------------------------------------------------------------------------------------+------+-----+----------------------------+-------+

6 rows in set (0.04 sec)

mysql> desc replication_applier_global_filters;

+---------------+-----------------------------------------------------+------+-----+----------------------------+-------+

+---------------+-----------------------------------------------------+------+-----+----------------------------+-------+

| ACTIVE_SINCE | timestamp(6) | NO | | 0000-00-00 00:00:00.000000 | |

+---------------+-----------------------------------------------------+------+-----+----------------------------+-------+

4 rows in set (0.15 sec)

replication_applier_status_by_worker中LAST_SEEN_TRANSACTION列的值是如何更新的？表示什么？

表示工作线程最后处理的一个事务，这个事务可能没有提交，仍在进行中。

当gtid_mode=OFF时，该列为ANONYMOUS

当gtid_mode=ON时：

如过没有事务执行，该列为空

当事务开始执行时，该列值等于gtid_next，事务执行完毕后不会更新该值。

在下一个事务执行时更新该列值。

ATyla

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql schema 复制_MySQL复制-复制相关信息的存储及查询

本文问题如何检查复制状态，需要重点关注哪些内容？如何在主库上查询连接到该主库的从库信息，详细信息需要通过哪些选项进行配置在从库上能够查询到哪些主库相关的信息，这些信息都存储在什么位置？如何进行查询和更改？P_S提供了哪些表用来监控复制相关的信息？这些表可以分为几种？P_S中哪些表提供了主库相关的信息，其中包含了哪些内容？P_S中哪些表提供了应用日志相关的信息，其中包含哪些内容？P_S中哪些表提供了...
复制链接

扫一扫