0

I have a master slave setup using MySQL 5.0. I have very frequent writes to the master and slave is used only for backup purpose (no explicit writes).

In my current configuration, the slave database is synced with the master by MySQL automatically via VPN. But if the system (win 7) consisting of Master goes down for some reason, after reboot the synchronization doesn't continue from the last synced record. Instead I have to stop the services at both the ends, delete and recreate the config files, move the sql of the backup from master to slave and restart the sync. All this has to be done every time my system goes down for any reason.

Is it possible to avoid this so that whenever master system goes down, the syncing starts again as usual ?

1

If you have a normal shutdown, you should not have this problem.

Investigate if your MySQL service properly stops on shutdown and try to see what happens if you manually stop MySQL prior to shutdown.

Also check to see if sending data from master to slave over VPN results in data corruption. This can very easily happen in MySQL 5.0 since there are no checksums done for transferring binlogs to slave. I would advise to upgrade to MySQL 5.5 as it handles replication corruption better.

1

No.

Replication will survive the VPN going down, but a crash of the master host does not reliably flush the master's binary log, so on reboot there is no guarantee that the master's Binlog Dump thread does not send the same events to the slave's I/O thread.

You should find a solution that involves the master server staying online. Failing that, you might get away with having the slave run for short periods during which the master is less likely to crash, so that you reduce the possibility of the server crashing during log transfer, but it is not a reliable solution.

  • thanks. But unfortunately the master is a laptop in this situation. Is there any replication scheme that can be reliably use to solve such a problem. – krammer Jan 19 '13 at 14:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.