0

I'd like to set up a zero-maintenance hands-off MySQL replication regimen for backup purposes.

It's been my experience, though, that these things inevitably eventually get out of sync. Perhaps it's due to developer abuse, unscheduled accidents, or other unknowns. But I'd like to periodically re-sync the slave(s) just to make sure that an unnoticed replication problem doesn't go uncorrected long-term.

Does anyone have a working/tested solution for going about this?

Here's my proposed solution; but I'm open to other possiblities from anyone who has experience dealing with this issue:

Presumably this is as simple as doing on the master:

mysqldump --all-databases --master-data >dbdump.sql

And then loading that SQL file on each of the slaves, running stop slave before and start slave afterward. Theoretically the slaves will use the updated master coordinates to re-establish synchronization, and off we go.

Presumably this could be automated as a weekly job during off-peak hours.

But is there a better way to do this?

Improve this question
3
  • This is basically what we do to a database full of MyISAM tables (around 18 GB dump size) on a regular basis (although instead of redirecting it to a file, we pipe it through ssh directly to the other end's mysql command). It works, but is not used as a "zero-maintenance" approach, mostly it just spares the "optimize table" runs. The proper approach would be not to use MySQL replication at all. If it is just for backup purposes, consider either snapshotting the volume or using InnoDB to reduce locking contention during backup. Commented Jan 7, 2013 at 0:04
  • @syneticon-dj typically the the periodic snapshots happen anyway for backup purposes; the replication would be for to try to fill the gap between snapshots Commented Jan 7, 2013 at 2:10
  • It's a little absurd that people are voting this question as off-topic. For heaven's sake, we're talking about configuring MySQL replication; could anything be more on-topic than that? Commented Jan 7, 2013 at 2:57

1 Answer 1

Reset to default
2

do you use innodb engines both on slaves and masters? avoid queries that can break replication? avoid unclean shutdowns of mysqls both on masters and slaves? if so replication in my experience is pretty solid.

knowing how your slaves break would be helpful - does the replication stop? if so - with what errors on the slaves? or replication runs but slaves get out of sync with masters?

if it's the second case - you can try running periodically pt-table-checksum to verify that slaves are in sync with masters. if you discover that they are not - you can use pt-table-sync [which i found to be blocking master servers even in the non blocking mode.. so dont run that in the middle of a busy day].

edit: if the total size of the data isnt huge, you run innodb on the master and dont want to block master with pt-table sync write script that you will execute periodically. the script should:

  • run pt-table-checksum, if all is fine - exit
  • take full dump of the master using mysqldump --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=1 -A
  • run on the slave echo "stop slave"| mysql
  • reload the dump

thanks to --master-data=1 your dump will contain the change master ... to statement.

but honestly - you'll be much better of finding the root cause of your problems. and monitoring is a must if this data has any value.

Improve this answer
6
  • See, that's why I said "zero-maintenance, hands-off". I have no idea why replication might break, nor will I be in a position to know. Troubleshooting the replication failures is not an option in this type of case, largely because usage of the server is out of my hands. In fact, the replication may never fail. I won't know because I won't be monitoring the slave except perhaps on a once-every-few-months checkup. This has to be a set-it-and-forget-it solution with zero interactivity. Commented Jan 5, 2013 at 23:57
  • @tylerl Who will be monitoring it, then? The job isn't done until monitoring is in place. Commented Jan 5, 2013 at 23:59
  • @MichaelHampton - No idea. Typically this type of client insists on doing the monitoring himself for cost reasons, but may not have the expertise to know what to do about it. It's crappy, but there you go. We do what we can for this type of person and give them all the appropriate warnings about "properly maintained by a qualified professional" and all that. But welcome of the world of shoestring budgets and unlimited optimism. Commented Jan 6, 2013 at 0:08
  • use pt-table-checksum to check consistency; if it fails do full reload from master's mysqldump; use mysqldump --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=1 -A ; before reloading dump on a slave issue : "stop slave". Commented Jan 6, 2013 at 10:59
  • pt-table-sync is going to place table write locks on MyISAM tables on the master due to its very nature - it re-writes every row it wants to replicate with the data previously read from the row. The drawback with pt-table-sync is that it would not make sure the table structure and metadata is consistent across replicas. The mysqldump approach would take care of that, but I am not sure the --single-transaction option is safe in conjunction with --master-data for MyISAM tables as it would apparently disable --lock-all-tables, leading to potential inconsistencies. Commented Jan 6, 2013 at 23:54

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.