I am not a replication expert, but I have been researching a mysql replication setup over the last few days
Seems that the issue with replication is that it sends the same queries over *as queries*, so any issues revolve around why the same query might not do the same thing at each end.
The summary of advice seems to be:
- Use LVM - then you can snapshot your database in just a few seconds and resetup your slave in slow time - Use InnoDB wherever possible. Issues with myisam particularly seems to revolve around transactions or failed queries - they will succeed at one end, but not the other end of the link... - Setup a process to compare all the tables regularly, ie spot when sync has failed somehow... - Get a support contract with MySQL - if the problem happens again they can help you resolve it, and possibly fix the bug - Perhaps get the Innodb hotbackup thing - this allows you to do online backups and recreate your slave DB without taking that master down
http://www.xaprb.com/blog/2007/01/20/how-to-make-mysql-replication-reliable/ http://dev.mysql.com/doc/refman/5.0/en/replication-features.html
Good luck
Ed W