How to backup your InnoDB Cluster ?

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:

backup_innodb_cluster_v4Download

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

Output example:

 +-------------+----------+------------+-------------+-------------+
 | 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)

I don’t consider myself as an expert in MEB but if you have any question, you can use our popular forums, our Community Slack or by leaving a comment here.

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 !

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

21 Comments

  2. Hello lefred

    Thank you for your post. How do I restore such a backup back into the cluster? Any backup in only good after i can restore it right?

    I have been playing around with mysqldump and xtrabackup, not MEB but the process will be similar I guess.

    Kind regards Mike

  3. Hi Lefred,
    Do you know if MEB can work with MySQL Community version? And if yes, do we need a license for that?
    Can we have a separate license for MEB if needed?

    Thanks

    • Hi Sandeep,

      MEB works with MySQL Community Edition too of course.
      I think MEB is part of MySQL Enterprise but you can always reach out our Sales rep.
      Regards,

    • This post is to provide an idea on how to create such script, what are the things to check and implement. I’m not a Windows expert, I don’t even have a Windows machine or VM. You will have to write such script, sorry.

      Cheers,

  5. Hi Lefred,
    In the checkstatus function , you have mentioned mysql3 host specifically .. is that correct ?
    Do we need to change it for different nodes of the cluster?

    Regards,
    Vinay

  6. Hi Lefred,

    thank you for sharing your great work with us. Reading the scripts I have a doubt … How come you set the “group_replication_consistency = ‘BEFORE_AND_AFTER'” after running the backup? What is the initial value of the group_replication_consistency?

    Thanks
    Regards
    Mimmo

    • Hi Mimmo,

      In the script this is used as session variable (default is EVNETUAL). But I want these changes to be sync to every node before I can continue.

      So this is not global for every statements, but only in those in the session used in the backup script.

      Cheers and thank you for using MySQL.

  7. Hi Lefred,
    I am using 8.0.21 and can I use “clone local” sql command as my physical backup?

    Thanks
    …. Sushanta

  8. Dear Lefred,
    While MEB scripts run on secondary node, it’s locking it, and this thing is keeping LAG for the duration of full backup, and it causes the node to be kicked out of INNODB cluster, is there is any way to avoid lock on secondary.
    BR,

      • It’s keeping the secondary refusing transactions from primary, like it’s locking it. so I found scripts which keeping secondary in no-lock mode. but i don’t know either it’s risky or not.

        • MEB should not lock InnoDB table or at least never long enough to evict the secondary. Which version of MySQL are you using and how are you using MEB ? Also don’t hesitate to open a SR.

          • am using MySQL enterprise 8.0.27 , already opened SR, but I was curios regard why it’s locking the secondary DB while taking full backup.

Leave a Reply

Your email address will not be published. Required fields are marked *

As MySQL Community Manager, I am an employee of Oracle and the views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

You can find articles I wrote on Oracle’s blog.

Continue