I set up a replica between two MySQL servers. After my initial setup, the replication worked without any problem, but now they are out of sync.

On Master I can see:

Variable    Valor
File    mysql-bin.000200
Position    93069547


ID del servidor Servidor

On the Slave:

Variable    Valor
Slave_IO_State  Queueing source event to the relay log
Master_User c44b0fbab37cf3
Master_Port 3306
Connect_Retry   60
Master_Log_File mysql-bin.000006
Read_Master_Log_Pos 8759143
Relay_Log_File  ns328448-relay-bin.000003
Relay_Log_Pos   67837
Relay_Master_Log_File   mysql-bin.000006
Slave_IO_Running    Yes
Slave_SQL_Running   Yes
Last_Errno  0
Skip_Counter    0
Exec_Master_Log_Pos 67621
Relay_Log_Space 8759800
Until_Condition None
Until_Log_Pos   0
Master_SSL_Allowed  No
Seconds_Behind_Master   2606688

After a reboot on the slave, it seems the server is catching up with the pending files.

  1. What can be done to prevent this "out of sync" to happen?
  2. How can I investigate what caused the system to get stucked?
  3. I see all the log files are stored on the server, how can I set a max number of files to be kept?

Thanks, Javi

-- EDIT --

Today the status for servers are: Master

Variable    Valor
File    mysql-bin.000209
Position    61419650


Variable    Valor
Slave_IO_State  Waiting for source to send event
Master_Log_File mysql-bin.000209
Read_Master_Log_Pos 62149115
Relay_Log_File  ns328448-relay-bin.000016
Relay_Log_Pos   42109202
Relay_Master_Log_File   mysql-bin.000011
Slave_IO_Running    Yes
Slave_SQL_Running   Yes
Exec_Master_Log_Pos 42108986
Relay_Log_Space 19138941381
Seconds_Behind_Master   2528578

It catch my attention that in both cases, "Master_Log_File" points to the same file and "Read_Master_Log_Pos" (almost) to the same position.

"Seconds_Behind_Master" has not changed much.

If I restart the Slave server, and check its status again, it has "magically" changed to:

Slave_IO_State  Queueing source event to the relay log
Master_Log_File mysql-bin.000008
Read_Master_Log_Pos 3812616
Relay_Log_File  ns328448-relay-bin.000003
Relay_Log_Pos   6100
Relay_Master_Log_File   mysql-bin.000008
Slave_IO_Running    Yes
Slave_SQL_Running   Yes
Exec_Master_Log_Pos 5884
Relay_Log_Space 3813225
Seconds_Behind_Master   2626954

And now "Master_Log_File" and "Read_Master_Log_Pos" point to a different place.

Does this help?

  • but now they are out of sync, what caused the out of sync? 2.6M seconds it's a long time and it can't occurred due to heavy writings on the master Jan 12, 2023 at 13:49
  • Do any clients run DDL or locking queries on the replica? They could be blocking replication from executing. So the replication threads count as "Running" but they are just waiting. Jan 12, 2023 at 14:27
  • Your question about log files is unclear. Which server do you mean, the source or the replica? On the replica, relay logs are removed immediately once they are executed. You might have many relay logs, but that's natural if your SBM is 2.6M seconds. Jan 12, 2023 at 14:29
  • @ErgestBasha, I do not know what was the root cause of the issue. I set the slave to ignore the errors, as it is not critical if they are not exactly the same. What can be consider "heavy writing"? In the worst case, there might be just 200 writes per second on the master.
    – Javi M
    Jan 12, 2023 at 16:11
  • 1
    There are options to purge binary log files automatically by time, not by the number of log files. See dev.mysql.com/doc/refman/8.0/en/… or if you use MySQL 8.0 use dev.mysql.com/doc/refman/8.0/en/… Jan 12, 2023 at 16:48


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.