首页 > 数据库 >MySQL 8与复制可观察性

MySQL 8与复制可观察性

时间:2023-08-26 19:02:05浏览次数:42  
标签:00 125fcb39 235056b0rhj3 eb11 none replication 复制 MySQL 观察

许多老MySQL DBA都使用show replica status中的seconds_behind_source来了解(异步)复制的状态和正确执行情况。

不过,MySQL复制已经有了很大的发展,复制团队已经努力为MySQL的所有复制方式提供了大量有用的信息。

例如,增加了并行复制、组复制......所有这些信息都是老的show replica status结果中缺少的。

performance_schema为监控和观察复制过程提供了更好的方法。

 

目前,performance_schema中有15个表与复制相关:

+------------------------------------------------------+
| Tables_in_performance_schema (replication%)          |
+------------------------------------------------------+
| replication_applier_configuration                    |
| replication_applier_filters                          |
| replication_applier_global_filters                   |
| replication_applier_status                           |
| replication_applier_status_by_coordinator            |
| replication_applier_status_by_worker                 |
| replication_asynchronous_connection_failover         |
| replication_asynchronous_connection_failover_managed |
| replication_connection_configuration                 |
| replication_connection_status                        |
| replication_group_communication_information          |
| replication_group_configuration_version              |
| replication_group_member_actions                     |
| replication_group_member_stats                       |
| replication_group_members                            |
+------------------------------------------------------+

 

但是,要理解这些指标的含义以及在哪里可以找到对我们这些MySQL DBA很重要的有用信息,确实并非易事:比如复制是否落后于源库?

可以在sys库中创建一些视图,使用这些指标中的大多数指标来获取相关的信息:mysql_8_replication_observability.sql。下载地址:https://gist.github.com/lefred/1bad64403923664a14e0f20f572d7526

下面来看看这写视图。
复制延迟:

select * from sys.replication_lag;
+--------------+-----------------------+------------------------+
| channel_name | max_lag_from_original | max_lag_from_immediate |
+--------------+-----------------------+------------------------+
|              | 0                     | 0                      |
+--------------+-----------------------+------------------------+


复制状态:

>select * from sys.replication_status;
+---------+----------+----------+---------+-------------------+--------------------+
| channel | io_state | co_state | w_state | lag_from_original | lag_from_immediate |
+---------+----------+----------+---------+-------------------+--------------------+
|  (1)    | ON       | ON       | ON      | none              | none               |
|  (2)    | ON       | ON       | ON      | none              | none               |
|  (3)    | ON       | ON       | ON      | none              | none               |
|  (4)    | ON       | ON       | ON      | none              | none               |
+---------+----------+----------+---------+-------------------+--------------------+

 

查看复制更详细的信息:

>select * from sys.replication_status_full;
+---------+---------------+------+-----------+--------------------------------------+------------+----------------------------+--------------------+----------+----------------------------------+----------+-----------+----------------------------+----------+----------------------------------------------------------+----------+-----------+----------------------------+---------+---------------------------------------+---------+----------+----------------------------+-------------------------+--------------------+-------------------+--------------------+----------------+-------------------+------------+-----------------------------------------------+-----------------------------------------------+--------------------------+
| channel | host          | port | user      | source_uuid                          | group_name | last_heartbeat_timestamp   | heartbeat_interval | io_state | io_thread_state                  | io_errno | io_errmsg | io_errtime                 | co_state | co_thread_state                                          | co_errno | co_errmsg | co_errtime                 | w_state | w_thread_state                        | w_errno | w_errmsg | w_errtime                  | time_since_last_message | applier_busy_state | lag_from_original | lag_from_immediate | transport_time | time_to_relay_log | apply_time | last_applied_transaction                      | last_queued_transaction                       | queued_gtid_set_to_apply |
+---------+---------------+------+-----------+--------------------------------------+------------+----------------------------+--------------------+----------+----------------------------------+----------+-----------+----------------------------+----------+----------------------------------------------------------+----------+-----------+----------------------------+---------+---------------------------------------+---------+----------+----------------------------+-------------------------+--------------------+-------------------+--------------------+----------------+-------------------+------------+-----------------------------------------------+-----------------------------------------------+--------------------------+
|  (1)    | 192.168.137.1 | 3306 | repl_test | 125fcb39-eb11-11ed-9aec-235056b0rhj3 |            | 2023-08-18 14:38:20.978058 |             30.000 | ON       | Waiting for source to send event |        0 |           | 0000-00-00 00:00:00.000000 | ON       | Replica has read all relay log; waiting for more updates |        0 |           | 0000-00-00 00:00:00.000000 | ON      | Waiting for an event from Coordinator |       0 |          | 0000-00-00 00:00:00.000000 | 00:00:01.656046         | IDLE               | none              | none               | 79.00 us       | 14.00 us          | 2.50 ms    | 125fcb39-eb11-11ed-9aec-235056b0rhj3:82415939 | 125fcb39-eb11-11ed-9aec-235056b0rhj3:82415939 |                          |
|  (2)    | 192.168.137.1 | 3306 | repl_test | 125fcb39-eb11-11ed-9aec-235056b0rhj3 |            | 2023-08-18 14:38:20.978058 |             30.000 | ON       | Waiting for source to send event |        0 |           | 0000-00-00 00:00:00.000000 | ON       | Replica has read all relay log; waiting for more updates |        0 |           | 0000-00-00 00:00:00.000000 | ON      | Waiting for an event from Coordinator |       0 |          | 0000-00-00 00:00:00.000000 | 00:00:01.656046         | IDLE               | none              | none               | 79.00 us       | 14.00 us          | 2.63 ms    | 125fcb39-eb11-11ed-9aec-235056b0rhj3:82415928 | 125fcb39-eb11-11ed-9aec-235056b0rhj3:82415939 |                          |
|  (3)    | 192.168.137.1 | 3306 | repl_test | 125fcb39-eb11-11ed-9aec-235056b0rhj3 |            | 2023-08-18 14:38:20.978058 |             30.000 | ON       | Waiting for source to send event |        0 |           | 0000-00-00 00:00:00.000000 | ON       | Replica has read all relay log; waiting for more updates |        0 |           | 0000-00-00 00:00:00.000000 | ON      | Waiting for an event from Coordinator |       0 |          | 0000-00-00 00:00:00.000000 | 00:00:01.656046         | IDLE               | none              | none               | 79.00 us       | 14.00 us          | 2.63 ms    | 125fcb39-eb11-11ed-9aec-235056b0rhj3:82415929 | 125fcb39-eb11-11ed-9aec-235056b0rhj3:82415939 |                          |
|  (4)    | 192.168.137.1 | 3306 | repl_test | 125fcb39-eb11-11ed-9aec-235056b0rhj3 |            | 2023-08-18 14:38:20.978058 |             30.000 | ON       | Waiting for source to send event |        0 |           | 0000-00-00 00:00:00.000000 | ON       | Replica has read all relay log; waiting for more updates |        0 |           | 0000-00-00 00:00:00.000000 | ON      | Waiting for an event from Coordinator |       0 |          | 0000-00-00 00:00:00.000000 | 00:00:01.656046         | IDLE               | none              | none               | 79.00 us       | 14.00 us          | 3.24 ms    | 125fcb39-eb11-11ed-9aec-235056b0rhj3:82415879 | 125fcb39-eb11-11ed-9aec-235056b0rhj3:82415939 |                          |
+---------+---------------+------+-----------+--------------------------------------+------------+----------------------------+--------------------+----------+----------------------------------+----------+-----------+----------------------------+----------+----------------------------------------------------------+----------+-----------+----------------------------+---------+---------------------------------------+---------+----------+----------------------------+-------------------------+--------------------+-------------------+--------------------+----------------+-------------------+------------+-----------------------------------------------+-----------------------------------------------+--------------------------+

  

标签:00,125fcb39,235056b0rhj3,eb11,none,replication,复制,MySQL,观察
From: https://www.cnblogs.com/abclife/p/17656381.html

相关文章

  • mysql中truncate表对auto_increment的影响
    在mysql中,如果对表执行truncate操作后,会重新设置auto_increment的值,比如:root@localhost(none)>useabce;Databasechangedroot@localhostabce>createtabletest(idintnotnullauto_incrementprimarykey,ageint);QueryOK,0rowsaffected(0.02sec)root@localho......
  • 【MySQL 8.0】新特性:ALTER TABLE … ALGORITHM=INSTANT
    MySQL8.0.29之前,在线DDL操作中即时添加列只能添加在表的最后一列MySQL8.0.29扩展了对ALTERTABLE…ALGORITHM=INSTANT的支持:用户可以在表的任何位置即时添加列、即时删除列、添加列时评估行大小限制(root@node01)>altertablecustomeraddcolumnc_commentvarcha......
  • rhel 7.3搭建MySQL 5.7.21(一主一从GTID半同步复制)
    文档课题:rhel7.3搭建MySQL5.7.21(一主一从GTID半同步复制)数据库:MySQL5.7.21系统:rhel7.3环境:角色 主机名 IP 操作系统 server_id MySQL安装包master mysql-leo-master 192.168.133.111 rhel7.3 1 Percona-Server-5.7.21-20-Linux.x86_64.ssl101.tar.gzslave mysql-leo-s......
  • mysql 深入学习三 索引优化一
    测试建表CREATETABLE`employees`(`id`int(11)NOTNULLAUTO_INCREMENT,`name`varchar(24)NOTNULLDEFAULT''COMMENT'姓名',`age`int(11)NOTNULLDEFAULT'0'COMMENT'年龄',`position`varchar(20)NOT......
  • 【MySQL 8.0】新特性:支持CHECK约束
    (root@node01)>createtablestudent(idintprimarykey,namevarchar(255),ageint,genderchar(1),scorefloat,constraintchk_agecheck(age>=18),constraintchk_scorecheck(score>=0andscore<=100));QueryOK,0rowsaffected(0.20......
  • MySQL 一行记录是怎么存储的?
    一、数据存在哪个文件可以看到,共有三个文件,这三个文件分别代表着:db.opt,用来存储当前数据库的默认字符集和字符校验规则。t_order.frm,t_order的表结构会保存在这个文件。在MySQL中建立一张表都会生成一个.frm文件,该文件是用来保存每个表的元数据信息的,主要包含表结构定义......
  • 【MySQL 8.0】通过pt-archiver实现表的历史数据归档
    (root@node02)>setgloballocal_infile=on;QueryOK,0rowsaffected(0.00sec)(root@node02)>createtablecustomer_jplikecustomer;QueryOK,0rowsaffected(0.20sec)(root@node01)>setgloballocal_infile=on;QueryOK,0rowsaffected......
  • 启动mysql数据库时报错unknown variable 'rpl_semi_sync_slave_enabled=1'
    问题描述:启动mysql数据库时报错unknownvariable'rpl_semi_sync_slave_enabled=1'.数据库:mysql5.7.21系统:rhel7.31、异常重现--启动数据库[mysql@mysql-leo-slavedata]$/usr/local/mysql/bin/mysqld_safe--defaults-file=/home/mysql/etc/my.cnf&--告警信息2023-08-......
  • 【MySQL 8.0】通过pt-heartbeat监控从库与主库的复制延迟
    [root@node01~]#wgethttps://repo.percona.com/yum/percona-release-latest.noarch.rpm[root@node01~]#rpm-ivhpercona-release-latest.noarch.rpm[root@node01~]#yuminstall-ypercona-toolkit[root@node01~]#pt-heartbeat--versionpt-heartbeat3.5.2[......
  • SQL Server 相比 MySQL 有何优势?
    两种产品并不是一样的类型,mysql是单纯的数据库存储,mssql是一整套数据解决方案。如果有兴趣可以去了解一下microsoftsqlserverbusinessintelligence和datamining相关的产品,以及datacube,高斯分布计算等各种features,你就会改变观念了。mysql是互联网公司广泛使用的,免费的(最重......