Backup

Provides an interface for database backup and restore management, scheduling, and reporting. Each backup is assigned with a backup ID and ClusterControl creates a directory under Storage Directory to store the backups based on this ID.

Create Backup

Creates or schedules a MySQL backup.

Create Backup

To create an instant backup, you have options to create a full backup using mysqldump, Percona Xtrabackup, or MariaDB Backup. Incremental backup using Percona Xtrabackup and MariaDB Backup (MariaDB only) is only available under Schedule Backup. Backups can be stored on the database host that performs the backup, or the files can be streamed over to the ClusterControl host for centralized storage.

Backup Subdirectory

Schedule Backup

Creates backup schedules of the database. You can choose to create a full or incremental backup using xtrabackup, mariabackup or mysqldump.

Scheduled Backups

List of scheduled backups. You can enable and disable the schedule by toggling it accordingly. The created schedule can be edited and deleted.

Backup Method

This section explains the backup method used by ClusterControl.

mysqldump

ClusterControl performs mysqldump against all or selected databases by using the --single-transaction option. It automatically performs mysqldump with --master-data=2 if it detects binary logging is enabled on the particular node to generate a binary log file and position statement in the dump file. ClusterControl generates a set of 4 mysqldump files with the following suffixes:

• _data.sql.gz – Schemas’ data.
• _schema.sql.gz – Schemas’ structure.
• _mysqldb.sql.gz – MySQL system database.
• _triggerseventroutines.sql.gz – MySQL triggers, events, and routines.

Percona Xtrabackup

Percona Xtrabackup is an open-source MySQL hot backup utility from Percona. It is a combination of xtrabackup (built in C) and innobackupex (built on Perl) and can back up data from InnoDB, XtraDB, and MyISAM tables. Xtrabackup does not lock your database during the backup process. For large databases (100+ GB), it provides a much better restoration time as compared to mysqldump. The restoration process involves preparing MySQL data from the backup files before replacing or switching it with the current data directory on the target node.

Since its ability to create full and incremental MySQL backups, ClusterControl manages incremental backups, and groups the combination of full and incremental backups in a backup set. A backup set has an ID based on the latest full backup ID. All incremental backups after a full backup will be part of the same backup set. The backup set can then be restored as one single unit using the Restore Backup feature.

MariaDB Backup

MariaDB Backup is a fork of Percona XtraBackup with added support for compression and data-at-rest encryption available in MariaDB, included in MariaDB 10.1.23 and later. It is an open-source tool provided by MariaDB for performing physical online backups of InnoDB, Aria, and MyISAM tables. MariaDB Backup is available on Linux and Windows.

On all supported versions for MariaDB 10.1 and 10.2, ClusterControl will default to MariaDB Backup as the preferred backup method and SST method.

NDB backup (MySQL Cluster)

NDB backup triggers the START BACKUP command on the management node and perform mysqldump on each of the SQL nodes subsequently. These backup files will be created and streamed to the ClusterControl node based on the ClusterControl → Settings → Backup → Backup Directory location.

Backup List

Provides a list of finished backup jobs. The status can be:

All incremental backups are automatically grouped together under the last full backup and expandable with a dropdown.

Verify Backup

Performs backup verification job

Restore Backup

Restores mysqldump, Percona Xtrabackup or MariaDB Backup created by ClusterControl and listed in the Backup List. ClusterControl supports three restoration options:

• Restore on the node.
• Restore and verify on a standalone host.
• Create a cluster from backup.

Point-in-Time Recovery

For Point-in-Time Recovery (PITR) compatible backup, there will be extra options to restore your database backup from the time of a full backup to a more recent time using a set of incremental backups represented by the database’s binary log. If toggled, you will be presented with two recovery options:

Due to the dependency on the binary logs to perform recovery, the PITR-compatible backup can only be stored on the same host it was created from. Thus, this feature is applicable for Restore on a node.

Restore on node

You can restore up to a number of incremental backups by clicking on the Restore button for the respective backup ID. The following steps will be performed:

For mysqldump (online restore):

1. Copy backup files to the target server.
2. Checking disk space on the target server.
3. The mysqldump files will be copied to the node.
4. The schema, data, and triggers/functions dump files are applied.
5. Optionally restore the ‘MySQL’ database. If the ‘cmon’ user privileges have changed it may cause ClusterControl to stop functioning.
6. The rest of the members will then catch up with the target server.

For Percona Xtrabackup/MariaDB Backup (offline restore):

1. Stop all nodes in the cluster.
2. Copy backup files to the target server.
3. Checking disk space on the target server.
4. Prepare and restore the backup.
5. Follow the instruction in the ClusterControl → Activity → Jobs → Restore Backup on how to bootstrap the cluster. Alternatively, you can toggle on the Bootstrap cluster from the restored node.

Restore and verify on a standalone host

Performs restoration on a standalone host and verify the backup. This requires a dedicated host that is not part of the cluster. ClusterControl will first deploy a MySQL instance on the target host, start the service, stream the backup from the backup repository and start performing the restoration. Once done, you can have an option either to shut down the server once restored or let it run so you can conduct further investigation on the server.

You can monitor the job progress under Activity → Jobs → Verify Backup where ClusterControl will report the restoration status (based on the exit code) at the end of the job.

Create cluster from backup

Creates a new cluster from the existing backup. A new MySQL Galera Cluster will be created from the selected backup. The selected backup must be accessible from the nodes in the new cluster. The root/admin user password for this cluster must the same as the MySQL admin/root password as included in the backup. For MySQL-based clusters, only the Galera Cluster is supported at the moment.

Choosing this option will open a new dialog where the selected backup will be used as a base dataset for the new cluster. The same deployment wizard for MySQL Galera Cluster will be shown to configure a new cluster. See MySQL Galera for reference.

Basically, ClusterControl performs the deployment job based on the following order:

1. Install the necessary software and dependencies on all Galera nodes.
2. Start the first node.
3. Stream and restore the backup on the first node (with auto-restart flag).
4. Configure and add the rest of the nodes.

A new Galera Cluster will be listed under the ClusterControl cluster dashboard once the job completes.

Restore External Backup

Restores an external backup that does not list in the Backup List. It could be a backup created by another ClusterControl instance or the backup was created by the user.

The following steps will be performed:

1. Stop all nodes in the cluster.
2. Copy backup files to the selected server.
3. Restore the backup.
4. Start the cluster.
5. Follow the instruction in the ClusterControl → Activity → Jobs on how to bootstrap the cluster.

Backup Encryption and Decryption

If the encryption option is enabled for a particular backup, ClusterControl will use OpenSSL to encrypt the backup using the AES-256 CBC algorithm. Encryption happens on the backup node. If you choose to store the backup on the controller node, the backup files are streamed over in encrypted format through socat or netcat.

If compression is enabled, the backup is first compressed and then encrypted resulting in smaller backup sizes. The encryption key will be generated automatically (if not exists) and stored inside the CMON configuration for the particular cluster under backup_encryption_key the option. This key is stored with base64 encoded and should be decoded first before using it as an argument to pass when decrypting the backup. The following command shows how to decode the key:

$ cat /etc/cmon.d/cmon_X.cnf | grep ^backup_encryption_key | cut -d"'" -f2 | base64 -d > keyfile.key

Where X is the cluster-ID. The above command will read the backup_encryption_key value and decode the value to a binary output. Thus, it is important to redirect the output to a file, as in the example, we redirected the output to keyfile.key. The key file which stores the actual encryption key can be used in the OpenSSL command to decrypt the backup, for example:

$ cat {BACKUPFILE}.aes256 | openssl enc -d -aes-256-cbc -pass file:/path/to/keyfile.key > backup_file.xbstream.gz

Or, you can pass the stdin to the respective restore command chain, for example:

$ cat {BACKUPFILE}.aes256 | openssl enc -d -aes-256-cbc -pass file:/path/to/keyfile.key | gzip -dc | xbstream -x -C /var/lib/mysql

Settings

Manages the backup settings.