MySQL InnoDB is more and more popular. The adoption of it is even faster than I expected. Recently, during my travel in Stockholm, Sweden, a customer asked me what was the best practice to backup a cluster.
Since my interlocutor was a customer, the obvious choice is to use MySQL Enterprise Backup (known as MEB). Of course any other physical backup should be also fine.
The customer told me he was using cron to schedule his backup and was only using full backups… That’s perfect. So I told him that there is nothing complicated and that the cron job should something like:
mysqlbackup --with-timestamp --backup-dir /backup backup
Of course, I do not recommend the use of
--user clusteradmin --password=xxxxxin the cronjob but configure your crendentials using mysql_config_editor.
The customer answered me he didn’t want to backup every node each time and since MySQL InnoDB Cluster retains data consistency, making a backup of one member should be enough (he wanted to spare some disk space).
He was completely right. So I advised him to backup only one member, and I would recommend to use a secondary master.
Then he told me that this is what it was doing… but what will happen if the node where the backup should run is down ? or has a problem ?
And once again, he was right !
So, for making the perfect backup for MySQL InnoDB Cluster, our script should perform the following steps:
- check if the node where the script run is part of the cluster
- check if the node is indeed a secondary master
- eventually check if the node is lagging behind (large apply queue)
- ensure that the backup is not running on another member
Therefore, I wrote this small bash script that can be scheduled on every members of the MySQL InnoDB Cluster. The script benefits of the new Group Replication consistency to ensure the backup is running on one member only.
Let’s see the script in action (instead of running the backup via cron, I start in at the same time on each nodes using the command line):
You can download the script here and of course this is only an example and use it at your own risk:
Note that this solution only works with full backups. Incremental or differential backups might be corrupted when mixing different severs.
The following query in the
backup_historytable gives you also an overview where the backup was taken:
mysql> SELECT backup_type, end_lsn, exit_state, member_host, member_role
FROM mysql.backup_history JOIN
performance_schema.replication_group_members ON member_id=server_uuid
| backup_type | end_lsn | exit_state | member_host | member_role |
| FULL | 19571113 | SUCCESS | mysql1 | PRIMARY |
| FULL | 19756413 | SUCCESS | mysql3 | SECONDARY |
| FULL | 19757757 | SUCCESS | mysql3 | SECONDARY |
| FULL | 19861759 | SUCCESS | mysql3 | SECONDARY |
| FULL | 19821154 | SUCCESS | mysql1 | PRIMARY |
5 rows in set (0.00 sec)
Update: there was a small bug in the first version of the script when a node was online but partitioned. This has been resolved and updated. Thanks Ted for the good catch !
Update: there was an hardcoded hostname that is now replaced in getStatus(), thank you Vinay for the good catch !