Percona provides a really interesting tool for MySQL backups! It works on a live running MySQL server by copying the MySQL binary files from the data directory and because the tool knows how the engines work (InnoDB, MyISAM, and so on) it can make point-in-time consistent MySQL data files. Of course, using this tool on a database with InnoDB tables only is the best case, because no write lock will be used.

There are two main tasks when making a backup and one, which is not mandatory:

1. Execute xtrabackup with –backup option to copy the MySQL data files and additional information for the tool
2. Execute xtrabackup with –prepare option to prepare the MySQL (InnoDB) files ready for use of a MySQL server. The files are consistented, i.e. the whole backup is consistent despite the copy of the different files that happened at different times.
3. Execute xtrabackup with –prepare option again to further prepare the MySQL (InnoDB) files ready for use of a MySQL server. Additional preparation such as InnoDB log files and more. This step is not mandatory and it may be skipped because the MySQL server, which uses the data files, will create the InnoDB log files.

The directory with MySQL copied files contains not only the MySQL files but additional information plus my.cnf (MySQL current configuration) backup. The backup files may be prepared (and restore) in a different server than the original, on which the backup was made.

xtrabackup may enable compressing on-the-fly when copying the binary files with the option –compress but the steps to use (restore) the backup in a new server are different – additional step to decompress before prepare. So the above steps become:

1. xtrabackup –backup –compress – copy and compress on-the-fly.
2. xtrabackup –decompress – decompress the backup files
3. xtrabackup –prepare – make point-in-time consistent MySQL data files, which are ready for a MySQL server.
4. xtrabackup –prepare – additional preparation to make the start up of the MySQL server with those files faster.

And here is a real-world example:

STEP 1) Install the xtrabackup utility

The percona-xtrabackup-24 provides the percona xtrabackup tool for backup of MySQL 5.1, 5.5, 5.6 and 5.7 servers, as well as Percona Server for MySQL with XtraDB. Install percona-xtrabackup-80 for use with MySQL 8 and later.
The whole output is included in the Bonus 3 section below.

STEP 2) Make backup

Make the backup. The datadir of the MySQL server is needed and a new direcotry for the backup files. The MySQL server is running and serving requests.

In the end, there must be written: “completed OK!”.
The whole output of the command is included in the Bonus 1 section below. An example with compress option enabled is in Bonus 2 section below.
Here is what contains the backup diectory:

To use this backup to become a slave server, the change master command should use the information from file xtrabackup_binlog_info – the MASTER_LOG_FILE and MASTER_LOG_POS. It should be noted that if binloging is not enabled in the server configuration this file would not present in the backups!

STEP 3) Use (restore) backup

Before to be able to use the above backup files a prepare command should be executed over the directory. At least one prepare command is required or the data will not be point-in-time consistent. The second

Now it is ready to use the files in a new MySQL server replacing the datadir with the above files (aka the whole directory).

And here it is included the second prepare execution:

Bonus 1 – Backup of a sample wordpress database server

Bonus 2 – Making backup with compress option enabled

Before –prepare command the –decompress should be used!

Bonus 3 – Installation under CentOS 7

The whole output in CentOS 7 and what packages you may have including percona-xtrabackup in the name.