Enterprise Backup Support in InnoDB Cluster 8.0

By | November 25, 2018

In this blog post, I’ll highlight new Backup Support in the MySQL Enterprise Backup 8.0 utility for Group Replication and InnoDB Cluster. This will also include usage of the MySQL login-path utility for accessing credentials for the backup process as highlighted in this other blog post on Devops ideas with MySQL and Scripting for Binary Log Management.

This blog is part 2 in a 3 part series on MySQL InnoDB Cluster 8.0.13’s feature introductions, associated scripting we can use, devops approached enhancements and general capabilities.

Backup Integration with InnoDB Clustering

In this documentation page it points out some instructions which I’d like to emphasize in this blog post.  That is getting the proper  mysqlbackup user account created with the proper privileges.  Then getting those credentials loaded into the InnoDB Cluster members…and then storing those credentials for each server on all of the cluster member servers.  Why do we want to do this?  Let’s look at the solution first, then walk through building the authentication setup that can support it.

The Resulting Integration of MEB and InnoDB Cluster

This page on the MySQL Enterprise Backup documentation describes how “Backup History” is written to the MySQL instance. This provides locally stored details in the cluster on the completion of the backup activity for any member.

When InnoDB Cluster is setup in single-primary mode, all members except the primary are in super_read_only  mode.  To help facilitate the process of capturing the backup history, in MEB 8.0.12 MySQL introduced a new feature.

The MEB process now looks at the  performance_schema.replication_group_members  to see if its executing in a Group Replication environment, and if so, uses this to deteremine which member is the single-primary.  With this information, it provides the details for MEB to know:

  • If this is a group replication environment or not
  • If it is, who are the candidate members in the group and what is their status and role

Armed with this information, MEB can aim to write the information to the proper single-primary member, or possibly just locally to a multi-primary instance.  Once written to a proper RW member, the data will get replicated to all members.

Execute the Solution

Great, now that we know what it is supposed to do, let’s see it in action.

I’m going to run a backup job on the RO Member named gr129 .  Please use the Group Replication Enterprise Backup document to help with your backup job. I’ll expect it to complete a standard full backup, and the backup history should be written to the current single primary instance gr127 which we’ve identified from the above  performance_schema.replication_group_members  output.

Let’s not forget the validate  step

Reviewing the Written Backup History

Now that we’re reviewed the log, let’s review the data in the mysql.backup_history table.

Creating & Storing the MEB User Credentials

Now that we know the solution, let’s configure the environment to support it!!!

Creating the Database User accounts and Privileges

As mentioned in the documentation pages here and here, I’ll make the credentials on my single-primary member in the  BlogCluster setup. Cluster accounts were created in the previous blog from this series.

Note, that these credentials have also been created/replicated on my other cluster members ( gr128 , gr129 ) as seen below.

Setting up Local access on each Server for Login-path setups

As I’ve accessed each InnoDB Cluster member above using the newly created  mysqlbackup@%  user account, it is also persisting each remote credential on the server  gr127.  Although useful, this process needs to be done on all the InnoDB Cluster member as that is where the MEB mysqlbackup utility will be executed.

We need to do the same thing on each server, ensuring the login-path credentials are setup and stored. This is so the MEB utility ON ANY CLUSTER MEMBER SERVER can access any other server within the cluster.  We can’t be certain our current single primary member will remain the same.

***Note: Yes, using a wildcarded hostname will greatly simplify things.  Not every company creates hostnames for servers conducive to such niceties.

Inspecting the stored Login-path credentials

As these stored credentials are essential for supporting MEB in a batch job execution environment (which is likely how most normal backups are run), I wanted to point out how to look at those credentials.

In Linux these files are stored in the home directory of the user account here:

Here is the output of the credentials we’ve created via the MySQL-Shell storing those credentials for us.

Enjoy MySQL !

Comments, please 🙂