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'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

share|improve this question
    
I seem to have mostly solved the problem by adding a 'slave stop' command in my suspend script prior to suspending/hibernating. Then 'slave start' on resume. There's still an occasional error, but siginificantly less frequent, and it (so far) seems to be able to be bypassed with the 'set global_sql_skip_counter=1' technique. –  firecat53 Aug 1 '11 at 18:15
add comment

2 Answers

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

share|improve this answer
    
Thanks for the code link. Looks like it could be useful. I was attempting to stay away from a snapshot/restore for updating the slaves because of the growing size of the database and the time it takes to run the restore each day (I'm not using the newest machines in the world :) ). However, that may be my option if the replication can't be stabilized somehow. Thanks! –  firecat53 Jul 23 '11 at 4:24
add comment

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:

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

http://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:

http://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 !!!

share|improve this answer
    
Very useful links! Some were more applicable to master-master syncing, but the concept is pretty similar to what I'm trying to accomplish. I thought at first that semisynchronous replication might be the answer, but I don't think really change things for the two slaves that go to sleep periodically, if I'm understanding it correctly. I did enable compression (slave_compressed_protocol=1) on the master and slaves so that there would be a better chance of the data transmission being completed before a slave went to sleep. Haven't had a chance to see if that's helping yet, though. –  firecat53 Jul 23 '11 at 4:33
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.