Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ?

share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

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.

share|improve this answer
add comment

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.

share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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