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:
- Execute xtrabackup with –backup option to copy the MySQL data files and additional information for the tool
- 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.
- 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:
- xtrabackup –backup –compress – copy and compress on-the-fly.
- xtrabackup –decompress – decompress the backup files
- xtrabackup –prepare – make point-in-time consistent MySQL data files, which are ready for a MySQL server.
- 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
yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm yum install -y percona-xtrabackup-24 qpress
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.
[root@srv ~]# xtrabackup --backup --slave-info --datadir=/var/lib/mysql/ --target-dir=/mnt/backups/sql-xtrabackup/ xtrabackup: recognized server arguments: --datadir=/var/lib/mysql --log_bin --server-id=1 --open_files_limit=5000 --innodb_buffer_pool_size=256M --innodb_log_buffer_size=32M --innodb_log_files_in_group=2 --innodb_flush_log_at_trx_commit=0 --innodb_file_per_table=1 --innodb_flush_method=O_DIRECT --datadir=/var/lib/mysql/ xtrabackup: recognized client arguments: --password=* --backup=1 --slave-info=1 --target-dir=/mnt/backups/sql-xtrabackup/ 200919 14:35:11 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' (using password: YES). 200919 14:35:11 version_check Connected to MySQL server 200919 14:35:11 version_check Executing a version check against the server... 200919 14:35:11 version_check Done. 200919 14:35:11 Connecting to MySQL server host: localhost, user: not set, password: set, port: not set, socket: not set Using server version 5.7.31-log xtrabackup version 2.4.20 based on MySQL server 5.7.26 Linux (x86_64) (revision id: c8b4056) xtrabackup: uses posix_fadvise(). xtrabackup: cd to /var/lib/mysql/ xtrabackup: open files limit requested 5000, set to 5000 xtrabackup: using the following InnoDB configuration: xtrabackup: innodb_data_home_dir = . xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend xtrabackup: innodb_log_group_home_dir = ./ xtrabackup: innodb_log_files_in_group = 2 xtrabackup: innodb_log_file_size = 50331648 xtrabackup: using O_DIRECT InnoDB: Number of pools: 1 200919 14:35:11 >> log scanned up to (1135932912) xtrabackup: Generating a list of tablespaces InnoDB: Allocated tablespace ID 34 for mywordpress/wp_users, old maximum was 0 200919 14:35:12 [01] Copying ./ibdata1 to /mnt/backups/sql-xtrabackup/ibdata1 200919 14:35:12 >> log scanned up to (1135932912) 200919 14:35:13 >> log scanned up to (1135932912) 200919 14:35:14 [01] ...done 200919 14:35:14 >> log scanned up to (1135932912) 200919 14:35:15 >> log scanned up to (1135932912) 200919 14:35:16 [01] Copying ./mywordpress/wp_users.ibd to /mnt/backups/sql-xtrabackup/mywordpress/wp_users.ibd 200919 14:35:16 [01] ...done 200919 14:35:16 [01] Copying ./mywordpress/wp_gglcptch_whitelist.ibd to /mnt/backups/sql-xtrabackup/mywordpress/wp_gglcptch_whitelist.ibd 200919 14:35:16 [01] ...done 200919 14:35:16 [01] Copying ./mywordpress/wp_usermeta.ibd to /mnt/backups/sql-xtrabackup/mywordpress/wp_usermeta.ibd 200919 14:35:16 [01] ...done 200919 14:35:16 >> log scanned up to (1135932912) 200919 14:35:17 [01] Copying ./mywordpress/wp_postmeta.ibd to /mnt/backups/sql-xtrabackup/mywordpress/wp_postmeta.ibd 200919 14:35:17 [01] ...done 200919 14:35:17 [01] Copying ./mywordpress/wp_posts.ibd to /mnt/backups/sql-xtrabackup/mywordpress/wp_posts.ibd 200919 14:35:17 >> log scanned up to (1135932912) 200919 14:35:18 >> log scanned up to (1135932912) 200919 14:35:19 [01] ...done 200919 14:35:19 >> log scanned up to (1135932912) ..... ..... 200919 14:36:30 [01] Copying ./mysql/columns_priv.MYD to /mnt/backups/sql-xtrabackup/mysql/columns_priv.MYD 200919 14:36:30 [01] ...done 200919 14:36:30 >> log scanned up to (1135933567) 200919 14:36:31 [01] Copying ./mysql/proxies_priv.frm to /mnt/backups/sql-xtrabackup/mysql/proxies_priv.frm 200919 14:36:31 [01] ...done 200919 14:36:31 [01] Copying ./mysql/help_category.frm to /mnt/backups/sql-xtrabackup/mysql/help_category.frm 200919 14:36:31 [01] ...done 200919 14:36:31 [01] Copying ./mysql/proc.frm to /mnt/backups/sql-xtrabackup/mysql/proc.frm 200919 14:36:31 [01] ...done 200919 14:36:31 Finished backing up non-InnoDB tables and files 200919 14:36:31 [00] Writing /mnt/backups/sql-xtrabackup/xtrabackup_slave_info 200919 14:36:31 [00] ...done 200919 14:36:31 [00] Writing /mnt/backups/sql-xtrabackup/xtrabackup_binlog_info 200919 14:36:31 [00] ...done 200919 14:36:31 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS... xtrabackup: The latest check point (for incremental): '1135933558' xtrabackup: Stopping log copying thread. .200919 14:36:31 >> log scanned up to (1135933567) 200919 14:36:32 Executing UNLOCK TABLES 200919 14:36:32 All tables unlocked 200919 14:36:32 [00] Copying ib_buffer_pool to /mnt/backups/sql-xtrabackup/ib_buffer_pool 200919 14:36:32 [00] ...done 200919 14:36:32 Backup created in directory '/mnt/backups/sql-xtrabackup/' MySQL binlog position: filename 'srv-bin.000001', position '1004' 200919 14:36:32 [00] Writing /mnt/backups/sql-xtrabackup/backup-my.cnf 200919 14:36:32 [00] ...done 200919 14:36:32 [00] Writing /mnt/backups/sql-xtrabackup/xtrabackup_info 200919 14:36:32 [00] ...done xtrabackup: Transaction log of lsn (1135932903) to (1135933567) was copied. 200919 14:36:33 completed OK!
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:
Keep on reading!