2

TL;DR: given a master/slave setup between two MySQL databases, where the connection from the master to the slave is read only, how can you restore the master from the slave in the event of a failure of master?

I'm investigating alternative backup solutions for our main MySQL database.

Up to now, we've been using mysqldump to dump our databases, with the resulting file being zipped & the old backups being rotated.

This has worked well for several years, but we're now getting to the point where the database is so large that the overnight backup operation is extending into business hours and impacting operations (since some or all of the tables are still locked when employees start arriving in the morning).

I have set up a slave database on another server & have it syncing with our main database. It's working nicely, and I have the slave backing up (via mysqldump again). But I have two questions/problems:

  1. How can I be sure that I'll be able to restore master from the mysqldump output from slave?

I'm reasonably confident that the dump files are OK, but there are stored procedures & views in our database and I've had issues with mysqldump before - it can be a little finicky about dumping these things in the correct order/with correct permissions/etc.

I've thought about doing a test restore to a blank system (in a Docker container), but a) it's going to take a LONG time, b) I'll need to write a script to actually compare the test system with the master, and c) it will be hard to get a comparison at a single point in time.

  1. As an alternative, I've thought about simply shutting down the slave database, backing up the data files, then restarting the slave. This will be faster, simpler, and probably more reliable, but how do I then restore in the event of a failure?

I have read the MySQL documentation on the subject, but it only talks about how to do the backup, not how to restore it.

I imagine that it would be possible to shut down all the systems, revise the settings on the slave to remove readonly = 1, then somehow force the slave to push everything to the master.

So, given this setup, my main question is this:

What are the steps involved in restoring all the data from the read-only slave to the master in the event of a failure of the master?

2

1) Backups:

  • You can verify if your backup is successful by tailing the mysqldump for "dump completed successfully" to verify the success of a mysqldump.

  • Invalid backup is as-good-as (or as-bad-as) no backup. It's a good idea employ a backup-validation process by restoring it to a provisioned location as possible. Do it.

  • If you want to stick to logical backup, try using with myduper/myloader, which will be quicker than mysqldump.

  • Again physical backups are faster than logical and this is specifically true for large sizes. If you can giveup on MySQLDump which is slow for restores and especially if you have all-innodb, you should consider using xtrabackup from Percona (for hotbackup). Check installing and configuring xtrabackup with holland framework.

2) Restoring master from slave.

As such there are few questions and articles available but i will quickly notedown steps I can makeout for restoring master from slave.

  • Considering your master is M and slave is S. (M --> S)
  • M is down.
  • S is promoted to Master ( readonly=false, binlogging is enabled)
  • M is later fixed and ready to join but need a restore.
  • Restore M from latest backup, mark it as readonly=true.
  • Upon restore, make it slave of S. (S --> M)
  • Once it catches up, you may want to failback to original master.
  • Stop production traffic, Mark M as readonly=false, S as readonly=true.
  • Mark S as slave of M (should be simple change master to from present binlog of M)
  • Thus making them active-passive master-master pair.
  • Move traffic to M.
  • You might want to break the master-master but it's fine to keep active-passive.
  • Making sure nothing writes to Passive master.

Hope this helps.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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