We are using MySQL Circular replication (2 servers in a master-slave setting with each other) for an implementation with both servers is different regions.
Previously, we had the entire setup running flawlessly, but the VMWare vendor had to upgrade their underlying infrastructure and had us move the sites over to a new infrastructure. They cloned over our entire app including the servers so we had to do next to no configuration on the actual VMs. However, once traffic was diverted over, one of the servers has gotten into a wierd setting where all flags (Slave_IO_Running: Yes, Slave_SQL_Running: Yes, Seconds_Behind_Master: 0) are showing that all is well, but in reality, the Master_Log_File and Read_Master_Log_Pos variables are not changing at all and seem to be stuck (also causing our NMS not to be able to catch this). Same was verified by creating 2 tables, one on each side. Changes on B are appearing almost immediately on A, but the same is not occurring for changes on A to B.
We tried debugging it and when nothing else worked, we just got 2 new VMs, tested out replication again and moved the site over. Within 5 hours, we were at the same scenario again. Below are the outputs of Show Master Status and Show Slave Status of the newer servers.
On a side note, there is another simple slave that is also connected to db03 that is fully synced and is following db03 diligently, which proves the issue is not on db03 end. Also, the original setup on the older infrastructure is also still running strong.
Output from db-04-db (Database 04) ..
*************************** 1. row ***************************
File: mysql-bin.000191
Position: 40328871
Binlog_Do_DB: <databases>
Binlog_Ignore_DB:
Executed_Gtid_Set:
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: db-03-db
Master_User: reple
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000293
Read_Master_Log_Pos: 154
Relay_Log_File: db04-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000293
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: 154
Relay_Log_Space: 154
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: 5949f340-64f7-11ec-82b5-00505601012c
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave 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:
Tue Dec 28 13:54:37 UTC 2021
Output from db-03-db (DB 03) ..
*************************** 1. row ***************************
File: mysql-bin.000295
Position: 41834650
Binlog_Do_DB: <databases>
Binlog_Ignore_DB:
Executed_Gtid_Set:
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: db04
Master_User: reple
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000191
Read_Master_Log_Pos: 40328871
Relay_Log_File: db03-relay-bin.000588
Relay_Log_Pos: 40329084
Relay_Master_Log_File: mysql-bin.000191
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: 40328871
Relay_Log_Space: 40329337
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: 2
Master_UUID: 20aa3d73-64ad-11ec-a86d-0050560200ee
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave 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:
Tue Dec 28 13:54:34 UTC 2021
So my question simply is how can this actually occur?
