Percona XtraBackup
Percona XtraBackup works with MySQL, MariaDB, and Percona Server. It supports completely non-blocking backups of InnoDB, XtraDB, and HailDB storage engines. In addition, it can back up the following storage engines by briefly pausing writes at the end of the backup: MyISAM, Merge, and Archive, including partitioned tables, triggers, and database options.
https://learn.percona.com/hubfs/Datasheet/Percona_XtraBackup/Datasheet_Percona_XtraBackup.pdfInstall
Install
sudo yum install percona-xtrabackup-80
percona-xtrabackup-80 has the following dependencies...
- libdev
- mariadb-connector-c
- perl-DBD-MySQL
- perl-DBI
- perl-Data-Dumper
- perl-Digest
- perl-Digest-MD5
- perl-Math-BigInt
- perl-Math-Complex
- rsync
Create Database User
Create Database User
CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY RANDOM PASSWORD;
GRANT RELOAD ON *.* TO 'bkpuser'@'localhost';
GRANT LOCK TABLES ON *.* TO 'bkpuser'@'localhost';
GRANT BACKUP_ADMIN ON *.* TO 'bkpuser'@'localhost';
GRANT REPLICATION CLIENT ON *.* TO 'bkpuser'@'localhost';
GRANT CREATE TABLESPACE ON *.* TO 'bkpuser'@'localhost';
GRANT PROCESS ON *.* TO 'bkpuser'@'localhost';
GRANT SUPER ON *.* TO 'bkpuser'@'localhost';
GRANT CREATE ON *.* TO 'bkpuser'@'localhost';
GRANT ALTER ON *.* TO 'bkpuser'@'localhost';
GRANT INSERT ON *.* TO 'bkpuser'@'localhost';
GRANT SELECT ON *.* TO 'bkpuser'@'localhost';
GRANT SELECT ON performance_schema.log_status TO 'bkpuser'@'localhost';
GRANT SELECT ON performance_schema.keyring_component_status TO bkpuser@'localhost';
GRANT SELECT ON performance_schema.replication_group_members TO bkpuser@'localhost';
FLUSH PRIVILEGES;
NOTE: Remember to store the password in your secure backup store.
Backup
Backup
xtrabackup --user=bkpuser --password=mypassword --backup --target-dir=/backup
xtrabackup -u bkpuser -p --backup --databases=mydatabase --target-dir=/backup --host=myhost
Compression
Compression
--compress=lz4
TL;DR Use LZ4 Compression.
The following compression algorithms are available: quicklz, lz4, Zstandard (ZSTD). Quicklz is the default, but is also deprecated. ZSTD is a "tech preview" feature first available in 8.0.30. "Tech preview" features are NOT considered production ready.Example
Example
xtrabackup --login-path=xtrabackup --backup --rsync --target-dir=/var/lib/mysql/backup --host=localhost --compress=lz4
xtrabackup version 8.0.32-26 based on MySQL server 8.0.32 Linux (x86_64) (revision id: 34cf2908)230526 14:23:56 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;host=localhost' as 'root' (using password: YES).230526 14:23:56 version_check Connected to MySQL server230526 14:23:56 version_check Executing a version check against the server...230526 14:23:56 version_check Done.2023-05-26T14:23:56.630379+01:00 0 [Note] [MY-011825] [Xtrabackup] Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set2023-05-26T14:23:56.640500+01:00 0 [Note] [MY-011825] [Xtrabackup] Using server version 8.0.28-202023-05-26T14:23:56.649485+01:00 0 [Note] [MY-011825] [Xtrabackup] Executing LOCK TABLES FOR BACKUP ...2023-05-26T14:23:56.653962+01:00 0 [Note] [MY-011825] [Xtrabackup] uses posix_fadvise().2023-05-26T14:23:56.654023+01:00 0 [Note] [MY-011825] [Xtrabackup] cd to /var/lib/mysql/data2023-05-26T14:23:56.654046+01:00 0 [Note] [MY-011825] [Xtrabackup] open files limit requested 10000, set to 100002023-05-26T14:23:56.655307+01:00 0 [Note] [MY-011825] [Xtrabackup] using the following InnoDB configuration:2023-05-26T14:23:56.655335+01:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_home_dir = .2023-05-26T14:23:56.655345+01:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_file_path = ibdata1:12M:autoextend2023-05-26T14:23:56.655382+01:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_group_home_dir = ./2023-05-26T14:23:56.655392+01:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_files_in_group = 22023-05-26T14:23:56.655404+01:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_file_size = 10737418242023-05-26T14:23:56.655423+01:00 0 [Note] [MY-011825] [Xtrabackup] using O_DIRECT2023-05-26T14:23:56.657516+01:00 0 [Note] [MY-011825] [Xtrabackup] inititialize_service_handles suceeded2023-05-26T14:23:56.796965+01:00 0 [Note] [MY-012529] [InnoDB] Redo log format is v4. The redo log was created before MySQL 8.0.30.2023-05-26T14:23:56.818579+01:00 0 [Note] [MY-011825] [Xtrabackup] Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set2023-05-26T14:23:56.841881+01:00 0 [Note] [MY-011825] [Xtrabackup] Redo Log Archiving is not set up.2023-05-26T14:23:56.973941+01:00 1 [Note] [MY-011825] [Xtrabackup] >> log scanned up to (18417814)2023-05-26T14:23:57.240759+01:00 0 [Note] [MY-011825] [Xtrabackup] Generating a list of tablespaces2023-05-26T14:23:57.240833+01:00 0 [Note] [MY-012204] [InnoDB] Scanning './'2023-05-26T14:23:57.243500+01:00 0 [Note] [MY-012208] [InnoDB] Completed space ID check of 2 files.2023-05-26T14:23:57.243757+01:00 0 [Warning] [MY-012091] [InnoDB] Allocated tablespace ID 1 for sys/sys_config, old maximum was 02023-05-26T14:23:57.243868+01:00 0 [Note] [MY-013252] [InnoDB] Using undo tablespace './undo_001'.2023-05-26T14:23:57.244084+01:00 0 [Note] [MY-013252] [InnoDB] Using undo tablespace './undo_002'.2023-05-26T14:23:57.245158+01:00 0 [Note] [MY-012910] [InnoDB] Opened 2 existing undo tablespaces.2023-05-26T14:23:57.267368+01:00 2 [Note] [MY-011825] [Xtrabackup] Compressing ./ibdata1 to /var/lib/mysql/backup/ibdata1.lz42023-05-26T14:23:57.390664+01:00 2 [Note] [MY-011825] [Xtrabackup] Done: Compressing ./ibdata1 to /var/lib/mysql/backup/ibdata1.lz42023-05-26T14:23:57.395853+01:00 2 [Note] [MY-011825] [Xtrabackup] Compressing ./sys/sys_config.ibd to /var/lib/mysql/backup/sys/sys_config.ibd.lz42023-05-26T14:23:57.397010+01:00 2 [Note] [MY-011825] [Xtrabackup] Done: Compressing ./sys/sys_config.ibd to /var/lib/mysql/backup/sys/sys_config.ibd.lz42023-05-26T14:23:57.399455+01:00 2 [Note] [MY-011825] [Xtrabackup] Compressing ./mysql.ibd to /var/lib/mysql/backup/mysql.ibd.lz42023-05-26T14:23:57.584370+01:00 2 [Note] [MY-011825] [Xtrabackup] Done: Compressing ./mysql.ibd to /var/lib/mysql/backup/mysql.ibd.lz42023-05-26T14:23:57.605995+01:00 2 [Note] [MY-011825] [Xtrabackup] Compressing ./undo_002 to /var/lib/mysql/backup/undo_002.lz42023-05-26T14:23:57.727979+01:00 2 [Note] [MY-011825] [Xtrabackup] Done: Compressing ./undo_002 to /var/lib/mysql/backup/undo_002.lz42023-05-26T14:23:57.730768+01:00 2 [Note] [MY-011825] [Xtrabackup] Compressing ./undo_001 to /var/lib/mysql/backup/undo_001.lz42023-05-26T14:23:57.839240+01:00 2 [Note] [MY-011825] [Xtrabackup] Done: Compressing ./undo_001 to /var/lib/mysql/backup/undo_001.lz42023-05-26T14:23:57.974200+01:00 1 [Note] [MY-011825] [Xtrabackup] >> log scanned up to (18417814)2023-05-26T14:23:58.267262+01:00 0 [Note] [MY-011825] [Xtrabackup] Starting prep copy of non-InnoDB tables and files2023-05-26T14:23:58.368646+01:00 0 [Note] [MY-011825] [Xtrabackup] Starting rsync as: rsync -t . --files-from=/tmp/xtrabackup_rsyncfiles_pass1 /var/lib/mysql/backup/2023-05-26T14:23:58.452822+01:00 0 [Note] [MY-011825] [Xtrabackup] rsync finished successfully.2023-05-26T14:23:58.452874+01:00 0 [Note] [MY-011825] [Xtrabackup] Finished a prep copy of non-InnoDB tables and files2023-05-26T14:23:58.452995+01:00 0 [Note] [MY-011825] [Xtrabackup] Starting to backup non-InnoDB tables and files2023-05-26T14:23:58.554675+01:00 0 [Note] [MY-011825] [Xtrabackup] Starting rsync as: rsync -t . --files-from=/tmp/xtrabackup_rsyncfiles_pass2 /var/lib/mysql/backup/2023-05-26T14:23:58.580320+01:00 0 [Note] [MY-011825] [Xtrabackup] rsync finished successfully.2023-05-26T14:23:58.580452+01:00 0 [Note] [MY-011825] [Xtrabackup] Finished backing up non-InnoDB tables and files2023-05-26T14:23:58.580479+01:00 0 [Note] [MY-011825] [Xtrabackup] Executing FLUSH NO_WRITE_TO_BINLOG BINARY LOGS2023-05-26T14:23:58.604860+01:00 0 [Note] [MY-011825] [Xtrabackup] Selecting LSN and binary log position from p_s.log_status2023-05-26T14:23:58.608794+01:00 0 [Note] [MY-011825] [Xtrabackup] Compressing /var/lib/mysql/log.000005 to /var/lib/mysql/backup/log.000005.lz4 up to position 1572023-05-26T14:23:58.609123+01:00 0 [Note] [MY-011825] [Xtrabackup] Done: Compressing /var/lib/mysql/log.000005 to /var/lib/mysql/backup/log.000005.lz42023-05-26T14:23:58.613446+01:00 0 [Note] [MY-011825] [Xtrabackup] Compressing /var/lib/mysql/backup/log.index.lz42023-05-26T14:23:58.613802+01:00 0 [Note] [MY-011825] [Xtrabackup] Done: Compressing file /var/lib/mysql/backup/log.index.lz42023-05-26T14:23:58.618377+01:00 0 [Note] [MY-011825] [Xtrabackup] Compressing /var/lib/mysql/backup/xtrabackup_binlog_info.lz42023-05-26T14:23:58.618656+01:00 0 [Note] [MY-011825] [Xtrabackup] Done: Compressing file /var/lib/mysql/backup/xtrabackup_binlog_info.lz42023-05-26T14:23:58.620421+01:00 0 [Note] [MY-011825] [Xtrabackup] Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...2023-05-26T14:23:58.623093+01:00 0 [Note] [MY-011825] [Xtrabackup] The latest check point (for incremental): '18417814'2023-05-26T14:23:58.623126+01:00 0 [Note] [MY-011825] [Xtrabackup] Stopping log copying thread at LSN 184178242023-05-26T14:23:58.625030+01:00 1 [Note] [MY-011825] [Xtrabackup] >> log scanned up to (18417834)2023-05-26T14:23:59.625624+01:00 1 [Note] [MY-011825] [Xtrabackup] Starting to parse redo log at lsn = 184176862023-05-26T14:23:59.632576+01:00 0 [Note] [MY-011825] [Xtrabackup] Executing UNLOCK TABLES2023-05-26T14:23:59.632885+01:00 0 [Note] [MY-011825] [Xtrabackup] All tables unlocked2023-05-26T14:23:59.632911+01:00 0 [Note] [MY-011825] [Xtrabackup] Backup created in directory '/var/lib/mysql/backup/'2023-05-26T14:23:59.632922+01:00 0 [Note] [MY-011825] [Xtrabackup] MySQL binlog position: filename 'log.000005', position '157'2023-05-26T14:23:59.633059+01:00 0 [Note] [MY-011825] [Xtrabackup] Compressing /var/lib/mysql/backup/backup-my.cnf.lz42023-05-26T14:23:59.633251+01:00 0 [Note] [MY-011825] [Xtrabackup] Done: Compressing file /var/lib/mysql/backup/backup-my.cnf.lz42023-05-26T14:23:59.635223+01:00 0 [Note] [MY-011825] [Xtrabackup] Compressing /var/lib/mysql/backup/xtrabackup_info.lz42023-05-26T14:23:59.635410+01:00 0 [Note] [MY-011825] [Xtrabackup] Done: Compressing file /var/lib/mysql/backup/xtrabackup_info.lz42023-05-26T14:24:00.639676+01:00 0 [Note] [MY-011825] [Xtrabackup] Transaction log of lsn (18417762) to (18417844) was copied.2023-05-26T14:24:00.848313+01:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!
Backup Script
Backup Script
Prerequisites
Prerequisites
sudo -u mysql -s mysql_config_editor set --host=localhost --user=bkpuser --password --login-path=xtrabackup
If the above command does not work, you may need to do this as a workaround...sudo usermod mysql -s /bin/bashsudo su - mysqlmysql_config_editor set --host=localhost --user=bkpuser --password --login-path=xtrabackupDo the other steps below, then do this...sudo usermod mysql -s /bin/false
Script
Script
vi /usr/local/bin/mysqlbackup.sh
#!/bin/bash
# Set VariablesBACKUP_DIR="/var/lib/mysql/backup/FULL"LOCKFILE="/tmp/xtrabackup.lock"LOG_DIR="/var/lib/mysql/backup/log"LOGFILE="${LOG_DIR}/mysqlbackup.log.$(date '+%d')"
# Initialise Directories (irrelevant if they already exist)mkdir -p ${BACKUP_DIR}mkdir -p ${LOG_DIR}
# Prevent more than one copy of this script from runningtouch ${LOCKFILE}read myPID < ${LOCKFILE}[ ! -z "${myPID}" -a -d /proc/${myPID} ] && exitecho $$ > ${LOCKFILE}
# Preserve last complete backup whilst new backup in progressmv ${BACKUP_DIR} ${BACKUP_DIR}_old
# Backup. If successful, remove previous backupxtrabackup --login_path=xtrabackup --backup --rsync --target-dir=${BACKUP_DIR} --host=localhost --compress=lz4 2>>${LOGFILE}if [ $? -eq 0 ]then rm -rf ${BACKUP_DIR}_oldfi
# Remove lockfilerm -f ${LOCKFILE}
exit 0
# Set VariablesBACKUP_DIR="/var/lib/mysql/backup/FULL"LOCKFILE="/tmp/xtrabackup.lock"LOG_DIR="/var/lib/mysql/backup/log"LOGFILE="${LOG_DIR}/mysqlbackup.log.$(date '+%d')"
# Initialise Directories (irrelevant if they already exist)mkdir -p ${BACKUP_DIR}mkdir -p ${LOG_DIR}
# Prevent more than one copy of this script from runningtouch ${LOCKFILE}read myPID < ${LOCKFILE}[ ! -z "${myPID}" -a -d /proc/${myPID} ] && exitecho $$ > ${LOCKFILE}
# Preserve last complete backup whilst new backup in progressmv ${BACKUP_DIR} ${BACKUP_DIR}_old
# Backup. If successful, remove previous backupxtrabackup --login_path=xtrabackup --backup --rsync --target-dir=${BACKUP_DIR} --host=localhost --compress=lz4 2>>${LOGFILE}if [ $? -eq 0 ]then rm -rf ${BACKUP_DIR}_oldfi
# Remove lockfilerm -f ${LOCKFILE}
exit 0
Test
Test
sh -o xtrace /usr/local/bin/mysqlbackup.sh
+ BACKUP_DIR=/var/lib/mysql/backup/FULL+ LOCKFILE=/tmp/xtrabackup.lock+ LOG_DIR=/var/lib/mysql/backup/log++ date +%d+ LOGFILE=/var/lib/mysql/backup/log/mysqlbackup.log.05+ mkdir -p /var/lib/mysql/backup/FULL+ mkdir -p /var/lib/mysql/backup/log+ touch /tmp/xtrabackup.lock+ read myPID+ '[' '!' -z '' -a -d /proc/ ']'+ echo 1129646+ mv /var/lib/mysql/backup/FULL /var/lib/mysql/backup/FULL_old+ xtrabackup --login_path=xtrabackup --backup --rsync --target-dir=/var/lib/mysql/backup/FULL --host=localhost --compress=lz4+ '[' 0 -eq 0 ']'+ rm -rf /var/lib/mysql/backup/FULL_old+ rm -f /tmp/xtrabackup.lock+ exit 0
Backup Scheduling
Backup Scheduling
sudo -u mysql -s crontab -e
E.g. daily backup at 10pm...
00 22 * * * /usr/local/bin/mysqlbackup.sh >/tmp/mysqlbackup.$(date +'%d').log 2>&1
Check
sudo -u mysql -s crontab -l
Bibliography
Bibliography
https://www.percona.com/software/mysql-database/percona-xtrabackup https://learn.percona.com/hubfs/Datasheet/Percona_XtraBackup/Datasheet_Percona_XtraBackup.pdf https://www.percona.com/software/mysql-database/percona-xtrabackup/feature-comparison https://www.percona.com/downloads/Percona-XtraBackup-LATEST/ https://www.percona.com/resources/webinars/percona-xtrabackup-vs-mariabackup-vs-mysql-enterprise-backuphttps://www.percona.com/resources/technical-presentations/percona-xtrabackup-vs-mariabackup-vs-mysql-enterprise-backuphttps://github.com/percona/percona-xtrabackup/commit/e9491a8dfaadc5540bc4b4bffd547702fb41ead2https://docs.percona.com/percona-xtrabackup/8.0/index.htmlhttps://docs.percona.com/percona-xtrabackup/8.0/using_xtrabackup/privileges.htmlhttps://forums.percona.com/t/xtrabackup-only-performs-a-full-back-up-if-is-executed-with-super-user-privileges/4903https://docs.percona.com/percona-xtrabackup/8.0/create-compressed-backup.htmlhttps://docs.percona.com/percona-xtrabackup/8.0/release-notes/8.0/8.0.30-23.0.htmhttps://www.red-gate.com/simple-talk/blogs/backing-up-mysql-part-2-percona-xtrabackup/l