InnoDB Cluster: Recovering an instance with MySQL Enterprise Backup.

Ok, so if you’re reading this, then I can guess you’ve got a MySQL InnoDB Cluster in an awkard shape, i.e. you need to restore a backup and add the instance back into the cluster, so we have all our instances again.

As it might be logical to think “ah, but I’ve only lost 1 instance, a read-only instance, so all I have to do is backup & restore the other read-only instance and I’m home free. Well I want to make it a little harder. So in this scenario, assume that we’ve lost both the READ-ONLY instances, so I need to backup my primary READ-WRITE instance.

I’ve got a 8.0.16 instance, on Oracle Linux 7.4. We’ll be looking at 2 hosts, ic1 & ic3.

We’ll be using the MySQL Enterprise Edition Server, that bundles MySQL Enterprise Backup with the rpm’s so we don’t need to install anything else.

I’ll assume you’ve got access to Oracle Support and hence, you might like to have a quick look at the following note:

Backup and Restore Of Group Replication Node ( MySQL 8.0 ) (Doc ID 2449881.1)

So, assuming that you’re scratching your head at the moment, asking me to “get to it” here goes:

On the READ-WRITE server, ic1, run an online hot backup with MySQL Enterprise Backup, MEB:

mysqlbackup -uic -p --socket=/var/lib/mysql/mysql.sock \
 --backup-dir=/home/vagrant/backup/mysql --with-timestamp \
 --compress backup-and-apply-log

Now copy or transfer the resulting backup-dir to host “ic3”.

On server to be restored, ic3:

$ systemctl stop mysqld.service
$ rm -rf /var/lib/mysql/*

And run the MEB restore:

mysqlbackup --defaults-file=/etc/my.cnf -uroot \
 --backup-dir=/home/vagrant/backup/mysql/2019-05-30_10-39-45/ \
 --datadir=/var/lib/mysql --uncompress copy-back

Once that’s all restored, make sure the ‘mysql’ o.s. user has permissions and also do some housekeeping (we don’t need any relay logs do we now?):

chown -R mysql:mysql /var/lib/mysql
rm -rf /var/lib/mysql/*-bin*

And to avoid anyone trying to access the environment ‘by accident’ and make sure nothing else will start trying to write:

vi /etc/my.cnf

Because we’ve restored a backup from the master, we’ll need to know what GTID was active when we ran the backup, as this will be the starting point for the node we’re restoring. So:

$ cat ~vagrant/backup/mysql/2019-05-30_10-39-45/meta/backup_gtid_executed.sql
# On a new slave, issue the following command if GTIDs are enabled:
SET @@GLOBAL.GTID_PURGED='155d01e3-5b85-11e9-b5b5-080027b46af9:1-21395756,bc780881-5b85-11e9-ba66-080027b46af9:1-356';

# Use the following command if you want to use the GTID handshake protocol:

Let’s start up the restored instance:

systemctl start mysqld.service

And let’s reset the “ic1” instance restored onto the “ic3” server MAKING SURE WE USE “set sql_log_bin = OFF”:

mysqlsh --uri ic@ic3:3306 --sql

SET sql_log_bin = OFF;
SET @@GLOBAL.GTID_PURGED='155d01e3-5b85-11e9-b5b5-080027b46af9:1-21395756,bc780881-5b85-11e9-ba66-080027b46af9:1-356';
SET sql_log_bin = ON;

Without exiting from our MySQL session:


We answer “y” when it asks us to reconfigure and restart the instance.

Now, back on the primary instance server, ic1:

mysqlsh --uri ic@ic1:3306

cluster = dba.getCluster();

We should see that the ic3 instance is MISSING, so let’s remove it:


“y” to remove ic3.

And now to add the restored instance back into the cluster. From ic1:


And just to make sure:

cluster = dba.getCluster();

Back on ic3:

vi /etc/my.cnf


mysqlsh --uri ic@ic3:3306 --sql
set SQL_LOG_BIN=0;
set global super_read_only=0;
set global event_scheduler=1
set SQL_LOG_BIN=1;

Well, it worked for me! Hope it helps someone else.

Happy Clustering!


About Keith Hollman

Focused on RDBMS' for over 25 years, both Oracle and MySQL on -ix's of all shapes 'n' sizes. Small and local, large and international or just cloud. Whether it's HA, DnR, virtualization, containered or just plain admin tasks, a philosophy of sharing out-and-about puts a smile on my face. Because none of us ever stop learning. Teams work better together.
This entry was posted in Backup, innodb cluster, MEB, MySQL 8, MySQL Enterprise Backup, MySQL Enterprise Edition, Oracle Linux, Recovery, Replication, Uncategorized and tagged , , , , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s