Sample Header Ad - 728x90

MySQL Circular Replication does not throw error, but is not syncing. Any ideas?

0 votes
1 answer
63 views
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: 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: 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?
Asked by M. Faraz (101 rep)
Dec 29, 2021, 07:35 AM
Last activity: Jan 11, 2022, 08:47 AM