How to Customize your MySQL & MariaDB Backups with ClusterControl

ClusterControl’s centralized backup management feature supports the standard mysqldump, Percona Xtrabackup backup and Mariabackup provided by MariaDB. We believe the chosen command line arguments for the respective methods are optimal for most database workloads, and comply with the MySQL backup best practices. We base ourselves on all the feedback we have received over the years, when working with DBAs and sysadmins. However, the configuration might not be enough in some circumstances. You might still want to customize it to suit your environment, using the respective backup method. In this post, we will show you how to do this.

mysqldump

To perform a backup from the ClusterControl UI, go to ClusterControl -> Select Cluster -> Backup section. Here you can see the generated backups and you can create or schedule a new one.

From the ClusterControl UI, we have some different options to take the backup. We can create one dumpfile for each database, enable partial backup, store the backup on the node or on the ClusterControl server; we can specify the backup directory and subdirectory, or we can automatically archive the backup to the cloud (AWS, Google Cloud or Azure) using the cloud upload feature.

Also, we can use compression, encrypt our backup and specify the retention period.

By default, ClusterControl allows us to choose between 4 different dump types to perform the backup:

• Schema and Data: Database schema and data
• Schema Only: Database schema
• Data Only: Database data
• MySQL Db Only: MySQL system database

Let’s say we have 5 databases and we chose to backup all of them. Here is what ClusterControl will execute when performing the backup using the mysqldump method (commands are trimmed with backslash for readability):

• Schema and Data

  $ /usr/bin/mysqldump 
  --defaults-file=/etc/my.cnf 
  --flush-privileges 
  --hex-blob 
  --opt 
  --single-transaction 
  --skip-lock-tables 
  --triggers 
  --routines 
  --events 
  --set-gtid-purged=OFF 
  --databases mysql proxydemo sakila sbtest mydb 
  --ignore-table='mysql.innodb_index_stats' 
  --ignore-table='mysql.innodb_table_stats' 
  |gzip -6 -c > /root/backups/BACKUP-1/mysqldump_2018-11-06_203010_schemaanddata.sql.gz'.

• Schema Only

  $ /usr/bin/mysqldump 
  --defaults-file=/etc/my.cnf 
  --flush-privileges 
  --hex-blob 
  --opt 
  --no-data 
  --add-drop-table 
  --triggers 
  --routines 
  --events 
  --single-transaction 
  --skip-comments 
  --skip-lock-tables 
  --set-gtid-purged=OFF 
  --databases mysql proxydemo sakila sbtest mydb 
  |gzip -6 -c > /root/backups/BACKUP-2/mysqldump_2018-11-06_210040_schemaonly.sql.gz'.

• Data Only

  $ /usr/bin/mysqldump 
  --defaults-file=/etc/my.cnf 
  --flush-privileges 
  --hex-blob 
  --opt 
  --no-create-info 
  --single-transaction 
  --skip-comments 
  --skip-lock-tables 
  --skip-triggers 
  --skip-add-locks 
  --set-gtid-purged=OFF 
  --databases mysql proxydemo sakila sbtest mydb 
  --ignore-table='mysql.innodb_index_stats' 
  --ignore-table='mysql.innodb_table_stats' 
  |gzip -6 -c > /root/backups/BACKUP-3/mysqldump_2018-11-06_210445_dataonly.sql.gz'.

• MySQL DB Only

  $ /usr/bin/mysqldump 
  --defaults-file=/etc/my.cnf 
  --flush-privileges 
  --hex-blob 
  --opt 
  --single-transaction 
  --skip-comments 
  --skip-lock-tables 
  --skip-add-locks 
  --set-gtid-purged=OFF mysql 
  |gzip -6 -c > /root/backups/BACKUP-5/mysqldump_2018-11-06_211135_mysqldbonly.sql.gz'.

If our MySQL node is generating binary logs we’ll have the parameter master-data=2 included in the commands above and 1 extra dump type available:

• Complete PITR-compatible

  $ /usr/bin/mysqldump 
  --defaults-file=/etc/my.cnf 
  --flush-privileges 
  --hex-blob 
  --opt 
  --master-data=1 
  --single-transaction 
  --skip-lock-tables 
  --skip-lock-tables 
  --triggers 
  --routines 
  --events 
  --all-databases 
  |gzip -6 -c > /root/backups/BACKUP-6/mysqldump_2018-11-06_211451_complete.sql.gz'.

From the above command lines, we can see that on each mysqldump command, ClusterControl includes the MySQL configuration file into its –defaults-file argument. By having this, the mysqldump process is able to read the content of the mysqldump directive. By default ClusterControl configures the backup user credentials in a separate file in /etc/my.cnf.d/secrets-backup.cnf and the max_allowed_packet in my.cnf, similar to the following:

$ my.cnf

[mysqldump]
max_allowed_packet = 512M
# default_character_set = utf8

$ secrets-backup.cnf

[mysqldump]
user=backupuser
password=ETgAG5VnpvuyXniE

The advantage of this is that we can include some extra options for mysqldump. Unfortunately, the –defaults-file argument can only be specified as the foremost argument. Pay attention that the latter command line arguments take precedence on what have been configured inside my.cnf under [mysqldump] directive based on the order they appear. For example, if we add skip-comments=0 inside my.cnf, while at the end of the mysqldump command, there is a –skip-comments (or –skip-comments=1), the former will be ignored and the latter will be used.

Nevertheless, we can still use it as part of our backup customization by using other mysqldump backup options. For example, we can exclude tables that we don’t want to backup by using ignore-table parameter (with “database.table” formatting). Add the following lines into the MySQL configuration file:

[mysqldump]
max_allowed_packet = 512M
# default_character_set = utf8
ignore-table=sbtest.sbtest9
ignore-table=sbtest.sbtest10
ignore-table=sbtest.sbtest1

Once configured, we can just trigger a new mysqldump job from ClusterControl and we will have those tables skipped by mysqldump. No MySQL restart is required.

You can check the mysqldump documentation for more information.

Percona Xtrabackup

ClusterControl executes the Xtrabackup depending on the options you chose. It can be full or incremental and you can choose several variables from the ClusterControl UI. Consider the following:

In this step you can choose some variables that we mentioned previously in the mysqldump section, and then:

We can specify some details like desync node during backup, Xtrabackup Parallel Copy Threads, and more.

Based on the above options, the complete Xtrabackup command would be:

$ ulimit -n 256000 && LC_ALL=C /usr/bin/innobackupex --defaults-file=/etc/mysql/my.cnf  --galera-info --parallel 1 --stream=xbstream --no-timestamp . | gzip -6 - | socat - TCP4:192.168.100.110:9999 ) 2>&1.

The first command “ulimit -n 256000” is to ensure that Percona Xtrabackup has sufficient privileges to access a huge number of file descriptors (in case the databases contain many tables). Take note of the –defaults-file=/etc/mysql/my.cnf, which is similar to mysqldump, where innobackupex reads the content of MySQL configuration on the following directives and variables:

[mysqld]
datadir=[physical path to MySQL data directory]
tmpdir=[path to temporary directory]
[xtrabackup]
user=backupuser
password=[random password]

If you would like to customize the backup options for Percona Xtrabackup, you can add them directly under [xtrabackup] directive. For example, let’s say we want Xtrabackup to print the binary log position when the backup is taken, we can add something like this:

[xtrabackup]
user=backupuser
password=[random password]
slave-info=1

Triggering the xtrabackup job will then include a file called xtrabackup_slave_info file. No MySQL restart is required.

You can check the Percona documentation for more information about how it works.

Mariabackup

Mariabackup is a fork of Percona XtraBackup with added support for MariaDB 10.1 compression and data-at-rest encryption. It is included with MariaDB 10.1.23 and later.

The backup method can be full or incremental and you can select the same variables that you have for Percona XtraBackup, like Compression, Xtrabackup Parallel Copy Threads or Encryption.

The Mariabackup command would be:

$ /usr/bin/mariabackup 
--defaults-file=/etc/my.cnf 
--backup 
--galera-info 
--parallel 1 
--stream=xbstream 
--no-timestamp 
| gzip -6 - > /root/backups/BACKUP-8/backup-full-2018-11-07_015807.xbstream.gz ) 2>&1.

Mariabackup is based on Percona XtraBackup, so it uses the same information that Percona to perform the backup. By default, ClusterControl adds the following lines in the my.cnf file:

[xtrabackup]
databases-exclude=lost+found
# ssl_mode = DISABLED
ssl = 0

And the credentials in the secrets-backup.cnf file:

[xtrabackup]
user=backupuser
password=[random password]

If you want to add some variable, you can add it into the [xtrabackup] section.

You can check the MariaDB documentation for more information about which parameter to add.

In each case, you can check your backups from the Backup section on ClusterControl UI:

We hope this helps you to better configure your MySQL backups – you can download ClusterControl from our website (it’s free).