Is MySQL Replication appropriate for keeping a laptop in sync?

Question

I've setup Mysql master slave replication with the production server as master. I have three other machines as slaves: 1 laptop, 1 desktop, and 1 server. The desktop and laptop both get suspended to ram or hibernated at least daily, if not several times a day. At first I setup the default 'mixed' replication, which is basically statement-based, until I realized that the computations on the server that involved numerous temporary tables were not getting picked up by the two slaves (the slaves would error out when then couldn't find the temporary tables). The slave server works perfectly, because it's always on.

SO, I switched to row-based replication, thinking that would work better. However, I'm still getting frequent errors after the machines are woken up, the most recent of which is:

Could not execute Update_rows event on table database.tablename; Can't find record in    'tablename', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND;

I can use the 'SET GLOBAL_SQL_SKIP_COUNTER=1' trick to bypass the error and continue, but I'm afraid the slaves will get more and more out of sync if I setup that to happen automatically after any error. FYI, the slaves are just used for development and never get written to.

Is this not an appropriate setup for replication usage? It sounded from the documentation that network availability would not be an issue (other than the temporary tables problem, which row-based replication should work around).

Anyone else have experience with this kind of setup? I can provide configurations if it would be useful. I can do a daily import of a mysqldump gz file, but it seems like a clunkier solution, especially as the production server data is growing every day...

Thanks! Scott

Answer 1

Replication is a tool specifically designed for load handling and failover--not well suited to online/offline syncing. Most replication systems fail immediately on losing connection with a host, and it is up to the admin to resolve the situation, hopefully while one or more of the machines in the cluster are still functional.

What you need is more of a snapshot/restore, or possibly a queuing method. I had one of my team write a simple tool for point-in-time recovery on mysql that may meet your need, although it isn't maintained. We use it in many of our applications.

It uses binary logging to bring hosts up to any point in time. I would suggest continuing replication with the slave server, and using a tool like this for hosts that go offline occasionally.

https://github.com/bryanagee/Pamiris-MySQL-Backup/branches

Answer 2

I have seen and heard of setups like this. This usage of replication for devices that can be connected and not connected fit under the guise of replication from a star topology.

Here are two links that I posted answers to about star topology:

https://serverfault.com/questions/264374/mysql-in-star-topology/264444#264444

https://stackoverflow.com/questions/5273043/mysql-in-star-topology/5784578#5784578

Here is another link specifically dealing with having devices that disconnect and reconnect from a single master in a star topology as you are trying to do:

https://stackoverflow.com/questions/5273043/mysql-in-star-topology/5781504#5781504

From this link, it should be surmized that it is best to run STOP SLAVE; to manually disconnect a slave, rather than letting mysql just keep the slave status in memory. If yo use MySQL 5.5 and semisychronous replication, it will be more sensitive to a master not being alive and options can be set to detect this via a Replication HeartBeat. By using STOP SLAVE; (regardless of the MySQL version), a clean I/O connection can be established with START SLAVE;

I hope these give you some direction and allow you to brainstorm on the idea. Enjoy !!!