MySQL master slave replica interrupts after service restart or reboot

I’ve been using the master slave replica to keep a mySQL database and its disaster recovery perfectly aligned.

I noticed that unfortunately every time the mySQL service restarts, or one of the server is rebooted, the replica stops to work and I need to reactivate it manually.

Maybe this behavior is normal and acceptable in some kind of scenario, but in a real working environment this is not reliable and sustainable.

I need to understand if there is any option to avoid this interruption, or some way to automatize the restart. When I launch the “show master status” command, I noticed that the master log file (“file” column) is incremental, but the master log position (“position” column) is not. Is there any way to control this parameters? Or is there any other reliable process more than master slave replica, in a my SQL environment?

Many thanks in advance, Kind regards

Binary log positions are incremental within each log file. The position resets whenever there is a new binary log. New binary logs are created each time mysql restarts or if the binary log grows larger than 1GB.

Replication should automatically reconnect when you restart MySQL. Check your my.cnf config file for this: skip_slave_start = 1 and remove it if present.

You can run on the replica CHANGE MASTER TO ... MASTER_CONNECT_RETRY=20 to make the replica attempt reconnections every 20s instead of 60s (the default)

1 Like