MySQL Replication: How to restore master FROM slave

MySQLreplication

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

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?

Best Answer

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.

Related Solutions

MySQL Delete Rows from Slave

- If you are using InnoDB with the option innodb_file_per_table option off (by default on MySQL 5.1 and before), you need to stop the server after the dump (make sure you are not using innodb_fast_shutdown = 2), then delete at least the ibdata1, ib_logfile0, ib_logfile1 and your database directory and finally restart MySQL and import only the final truncated data. Otherwise, your ibdata1 file won't shrink in size.
- If you have active the option innodb_file_per_table (by default on MySQL 5.5 and later), you have separate .ibd files per table, and your ibdata1 file is not too big, you do not need to import/export the data and delete the InnoDB files. Just delete the appropriate rows and defragment the .ibd files by doing:
```
ALTER TABLE <your table> ENGINE=InnoDB; -- for 5.5 and before

ALTER TABLE <your table> ENGINE=InnoDB, ALGORITHM=COPY; -- for 5.6 and later
```
Replication will probably break. On ROW based replication, it will certainly break as it won't find the right rows to affect. On STATEMENT mode some queries may continue working, affecting 0 rows, but as time passes, it is highly probable to find some incompatible query, like an UPDATE that can be done on the master but not on the slave due to a unique key constraint. I do not recommend running a replication with different data on master and slave unless you know for sure that those parts are not modified or they are done in a very restricted way. I can tell you that most of the replication problems I fix are due to replication filters.

Mysql – Master/Master/Slave Replication

Using the topology you mentioned from the MySQL Documentation

sxjn

Let's setup the first scenario

EXAMPLE IPs for Each DB Server

Master1 : 10.20.30.40
Master2 : 10.20.30.50
Slave_1 : 10.20.30.60
replication user is repluser@'%'
replication password is replpass

Make sure binary logging is enabled on all the Slaves

We will do the following

Promote Master2 to Master1
Promote Slave_1 to Master2
Demote Master1 to Slave1

Step 01 : On Master2, run the following

mysql> SET GLOBAL read_only = 1;
mysql> STOP SLAVE;
mysql> RESET SLAVE;
mysql> CHANGE MASTER TO master_host='';
mysql> FLUSH TABLES;
mysql> SET GLOBAL read_only = 0;

Step 02 : Move your CNAME/VIP to Master2

Step 03 : On Slave_1, run mysql> RESET MASTER; FLUSH TABLES;

Step 04 : On Slave_1, dump the data

NEW_MASTER_HOST="10.20.30.60"
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
MYSQLDUMP_OPTIONS="--single-transaction"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --routines"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --triggers"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --master-data=1"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --all-databases"
echo "STOP SLAVE;" > MySQLData.sql
echo "CHANGE MASTER TO master_host='${NEW_MASTER_IP}'," >> MySQLData.sql
echo "master_port=3306," >> MySQLData.sql
echo "master_user='repluser'," >> MySQLData.sql
echo "master_password='replpass'," >> MySQLData.sql
echo "master_log_file='bogus'," >> MySQLData.sql
echo "master_log_pos=1;" >> MySQLData.sql
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} >> MySQLData.sql
echo "START SLAVE;" >> MySQLData.sql
gzip MySQLData.sql

Step 05 : When Master1 comes back up, rsync or scp MySQLData.sql.gz from Slave_1 to Master1

Step 06 : Login to MySQL on the Master1 and setup it up to replicate from Slave_1

Don't worry about the real binary log filename and position.

Using --master-data=1 embeds CHANGE MASTER TO command with the real coordinates on line 23 of a standard dump.

You can see it with the following

less MySQLData.sql.gz | head -35 | tail -1

Step 07 : Load the data into Master1

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
gzip -d < MySQLData.sql.gz | mysql ${MYSQL_CONN}

Step 08 : Login to MySQL on Master1

mysql> SHOW SLAVE STATUS\G

and Make sure Slave_IO_Running and Slave_SQL_Running both say Yes

EPILOGUE

When Done, the topology should be

Master1 : 10.20.30.50
Master2 : 10.20.30.60
Slave_1 : 10.20.30.40

DISCLAIMER

Please try this out on Test Servers before deploying to Production

GIVE IT A TRY !!!

Note: I would advise that you have multiples slaves

One for Nightly Backups
The others for load balancing SELECTs