##
奇怪的主从复制问题
今天在修正一客户的事务隔离级别时发现了一个奇怪的问题，主从复制中从库的线程Time值为负数。
1、现象
processlist的Time值有三个负数和一个正数，检查信息如下：
mysql> show full processlist;
+---------+-------------+-----------------+------+---------+----------+----------------------------------------------------------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+---------+-------------+-----------------+------+---------+----------+----------------------------------------------------------+-----------------------+
| 5 | system user | connecting host | NULL | Connect | 18183371 | Waiting for source to send event | NULL |
| 6 | system user | | NULL | Query | 0 | Replica has read all relay log; waiting for more updates | NULL |
| 7 | system user | | NULL | Query | -494 | Waiting for an event from Coordinator | NULL |
| 8 | system user | | NULL | Query | -490 | Waiting for an event from Coordinator | NULL |
| 9 | system user | | NULL | Query | -440 | Waiting for an event from Coordinator | NULL |
| 10 | system user | | NULL | Query | 6184 | Waiting for an event from Coordinator | NULL |
| 1265587 | root | localhost | NULL | Query | 0 | init | show full processlist |
+---------+-------------+-----------------+------+---------+----------+----------------------------------------------------------+-----------------------+
7 rows in set (0.00 sec)正数是可以理解的，但是为什么会有负数呢？
进一步检查主从复制的状态，两个Yes，并且 SQL_Delay=0.
mysql> show replica statusG
*************************** 1. row ***************************
Replica_IO_Running: Yes
Replica_SQL_Running: Yes
SQL_Delay: 02、解决思路
Time是与时间相关的，于是我们先从数据库的时间开始检查，比如时区，now()函数，操作系统时间，果不其然发现两台主机的时间存储很大差异。
[root@ws01 ~]# date
Fri Aug 11 18:25:22 AEST 2023
[root@dbreplica1 ~]# date
Fri Aug 11 18:17:08 AEST 20233、解决方法
date -s ""再次检查，状态正常
mysql> show full processlist;
+---------+-------------+-----------------+------+---------+----------+----------------------------------------------------------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+---------+-------------+-----------------+------+---------+----------+----------------------------------------------------------+-----------------------+
| 5 | system user | connecting host | NULL | Connect | 18184520 | Waiting for source to send event | NULL |
| 6 | system user | | NULL | Query | 0 | Replica has read all relay log; waiting for more updates | NULL |
| 7 | system user | | NULL | Query | 0 | Waiting for an event from Coordinator | NULL |
| 8 | system user | | NULL | Query | 2 | Waiting for an event from Coordinator | NULL |
| 9 | system user | | NULL | Query | 362 | Waiting for an event from Coordinator | NULL |
| 10 | system user | | NULL | Query | 7333 | Waiting for an event from Coordinator | NULL |
| 1265590 | root | localhost | NULL | Query | 0 | init | show full processlist |
+---------+-------------+-----------------+------+---------+----------+----------------------------------------------------------+-----------------------+
7 rows in set (0.00 sec)4、思考题
为什么存在时间差，但是却有一个正数？欢迎关注.
原文始发于微信公众号（库海无涯）：奇怪的主从复制问题
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/241281.html
