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:
- How can I be sure that I'll be able to restore master from the
mysqldumpoutput 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.
- 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?