0

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
Binlog_Do_DB    
Binlog_Ignore_DB

And:

ID del servidor Servidor
1670759689

On the Slave:

Variable    Valor
Slave_IO_State  Queueing source event to the relay log
Master_Host IP_ADDRESS_FROM_MY_MASTER
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
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 67621
Relay_Log_Space 8759800
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   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
...

Slave

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?

Improve this question
11
  • 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
    – Ergest Basha
    Jan 12 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.
    – Bill Karwin
    Jan 12 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.
    – Bill Karwin
    Jan 12 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 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/…
    – Bill Karwin
    Jan 12 at 16:48

0

Reset to default

Your Answer

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