Introduction
Anyone who has run MySQL or MariaDB database servers for some years knows that creating backups used to be quite a hassle. Not in the least because for a long time only 2 tools were available:
- MySQL Enterprise Backup, which was good but required an expensive MySQL Enterprise license (and does not work with MariaDB)
- The tried-and-tested mysqldump, which has been a part of MySQL for ages. However, when used with locking – the only way to make properly consistent backups – it can cause applications to become unresponsive. The ultimate fix is to perform backups on a replica (new name of the ‘slave’ term), although this introduces more complexity and cost.
Fortunately, a number of years ago, an alternative called Percona XtraBackup was launched. This tool works on a regular MySQL server – even though maintained by Percona, creators of the MySQL fork. The aim: make hot backups, i.e. non-blocking backups, or backups without downtime of your applications. Even when they are doing inserts, updates and deletes. On top of that, restores are a breeze with XtraBackup.
For MariaDB there is a tool called Mariabackup, which is very similar to XtraBackup. This article will focus on XtraBackup, while highlighting the differences with MariaBackup when needed.
Versions
Anyone who has run MySQL or MariaDB database servers for some years knows that creating backups used to be quite a hassle. Not in the least because for a long time only 2 tools were available:
- MySQL Enterprise Backup, which was good but required an expensive MySQL Enterprise license (and does not work with MariaDB)
- The tried-and-tested mysqldump, which has been a part of MySQL for ages. However, when used with locking – the only way to make properly consistent backups – it can cause applications to become unresponsive. The ultimate fix is to perform backups on a replica (new name of the ‘slave’ term), although this introduces more complexity and cost.
Fortunately, a number of years ago, an alternative called Percona XtraBackup was launched. This tool works on a regular MySQL server – even though maintained by Percona, creators of the MySQL fork. The aim: make hot backups, i.e. non-blocking backups, or backups without downtime of your applications. Even when they are doing inserts, updates and deletes. On top of that, restores are a breeze with XtraBackup.
For MariaDB there is a tool called Mariabackup, which is very similar to XtraBackup. This article will focus on XtraBackup, while highlighting the differences with MariaBackup when needed.
What works
- Hot backups, without interrupting transactions
- High-speed backups
- Backup verification
- Incremental backups
- Super-fast and reliable restores
- Creating new replicas (slaves) without downtime
What does not work
If you have MyISAM tables, the backup cannot be done without locking. The database server will temporarily block all writes while backing up any MyISAM tables. If you have a lot of MyISAM data, your application(s) could become unresponsive.
How it works
XtraBackup’s backup process is a 2-part process.
Part 1 - Backup
- Part 1 starts by storing the log sequence number (LSN), which is the identifier of the last change that was written to the data files.
- The InnoDB data files are then copied.
- Simultaneously, XtraBackup keeps track of any changes made on the database and continuously copies these changes to a transaction log file.
- After the InnoDB data files have been copied, if there are any MyISAM tables, it locks the database, copies those files and then unlocks the database.
Although this could conclude the backup process, you now have a copy of the InnoDB data files and potentially a lot of changes to be made to these data files, stored in transaction log files. Therefore, it is best to run part 2 as well.
Part 2 - Prepare
- Part 2 – the ‘prepare’ step – takes the transaction log files.
- It then applies all transactions on top of the copied InnoDB data files in a process called ‘crash recovery’ – identical to when your database experiences a crash or your server loses power.
- The resulting InnoDB data files are up to date to the point where the backup process of part 1 finished.
- These InnoDB data files can now be easily restored.
In practice: the installation
Percona XtraBackup is available as packages through repositories for use in Yum (Red Hat, CentOS and compatible) and Apt (Debian, Ubuntu and compatible). There is also a binary version, source install and a Docker container.
We will use the Yum/RPM version as an example. These are the commands you will need to get a working installation. All to be run as root or using sudo:
yum install epel-release # Needed for libev
yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
percona-release enable-only tools release
yum install libev percona-xtrabackup-80 # Replace percona-xtrabackup-80 by percona-xtrabackup-24 if you're running MySQL 5.x
yum install qpress # If you want to compress your backups
In practice: setting up the right privileges
XtraBackup needs specific privileges to the database server to perform its operations. The step-by-step is:
CREATE USER 'xtrabackup'@'localhost' IDENTIFIED BY 'Choose.a.saf3.P@ssword';
# Pick something better than this password
GRANT BACKUP_ADMIN, PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* to 'xtrabackup'@'localhost';
GRANT SELECT ON performance_schema.log_status TO 'xtrabackup'@'localhost';
GRANT SELECT ON performance_schema.keyring_component_status TO 'xtrabackup'@'localhost';
In practice: making a backup
XtraBackup backup configuration options can be specified in my.cnf (usually located in /etc) or on the command-line. For this article, we will use the command line, since it allows you to see what exactly will be run when using a script to start the backup.
The commands we use are:
xtrabackup --backup --parallel=2 --user=xtrabackup --password="Choose.a.saf3.P@ssword" --throttle=10 --target-dir=/var/backup/mysql
xtrabackup --prepare --target-dir=/var/backup/mysql
As you can see, 2 commands are available: –backup is part 1 and –prepare is part 2 as described above.
Some of the options:
- –parallel allows you to specify how many threads are used to copy files concurrently. If your system can handle it, 2 is a good start.
- –throttle specifies how many chunks of 10MByte are copied per second. If you specify 10, a maximum of 100MByte/sec will be copied. This is ideal if your system is sensitive to I/O overloading.
- –target-dir obviously is used to specify where the backup will be written to.
In practice: restoring a backup
If you look at what is in the target directory of the backup after you have run the prepare command, you will notice it looks very similar to what is in your normal MySQL data directory. That is because it is nearly identical (with a few minor exceptions).
Restoring a backup is extremely simple. After stopping your MySQL server and moving your data dir (by running “mv /var/lib/mysql /var/lib/mysql.backup” or something similar):
xtrabackup --copy-back --target-dir=/var/backup/mysql/
This will restore the backup located in /var/backup/mysql to your MySQL data directory.
Next you want to make sure that the data directory and all files within have the right ownership:
chown -R mysql:mysql /var/lib/mysql
You should now be able to start your MySQL server with your restored data.
Help, my restore is not working
If you restored a backup, but your MySQL server refuses to start and complains about a corrupted database, chances are you forgot to run the prepare step after making the backup.
Fortunately, you can run this step anytime and on any machine, but always before the backup is restored.
Extra: incremental backups
As noted above, XtraBackup starts by storing the LSN at the start of each backup. It also stores the LSN at the end of the backup.
This allows you to make incremental backups. The only requirement is that you have binary logs enabled and your binary logs are large enough to cover all transactions from the LSN at the end of the backup to now.
To create incremental backups, first perform a standard backup (for example every week):
xtrabackup --backup --parallel=2 --user=xtrabackup --password="Choose.a.saf3.P@ssword" --throttle=10 --target-dir=/var/backup/mysql/full
Then you can run an incremental using the full backup as a base:
xtrabackup --backup --parallel=2 --user=xtrabackup --password="Choose.a.saf3.P@ssword" --throttle=10 --target-dir=/var/backup/inc1 --incremental-basedir=/var/backup/mysql/full
Your next incremental backup should always reference the previous incremental backup:
xtrabackup --backup --parallel=2 --user=xtrabackup --password="Choose.a.saf3.P@ssword" --throttle=10 --target-dir=/var/backup/inc2 --incremental-basedir=/var/backup/mysql/inc1
Finally, you need to prepare the backup. Note that there is an extra parameter (–apply-log-only). Without this parameter, you would not be able to create additional incremental backups based on this backup:
xtrabackup --prepare --apply-log-only --target-dir=/var/backup/mysql/full # For the full backup
xtrabackup --prepare --apply-log-only --target-dir=/var/backup/mysql/full --icnremental-dir=/var/backup/mysql/inc1 # For the first incremental backup
xtrabackup --prepare --target-dir=/var/backup/mysql/full --icnremental-dir=/var/backup/mysql/inc2 # For the second incremental backup
Attention: note that in the above example the 3rd line does not have the –apply-log-only parameter. This is because the last backup made should have any unfinished transactions rolled back. For this reason, when using incremental backups, it is best to not run the prepare command until you want to restore the database.
