Percona XtraBackup

Percona XtraBackup (PXB) 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.pdf 

Check

sudo -u mysql -s xtrabackup --version

Backup

xtrabackup --user=bkpuser --password=mypassword --backup --target-dir=/backup

xtrabackup -u bkpuser -p --backup --databases=mydatabase --target-dir=/backup --host=myhost

Compression

--compress=lz4

--compress=quicklz

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

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

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

sudo -u root -s 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

sudo -u root -s chown mysql:mysql /usr/local/bin/mysqlbackup.sh

sudo -u root -s chmod u+x /usr/local/bin/mysqlbackup.sh

Test

sudo -u mysql -s /bin/bash -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

sudo -u mysql -s crontab -e

E.g. daily backup at 10pm...

00 22 * * * /bin/bash /usr/local/bin/mysqlbackup.sh >/tmp/mysqlbackup.$(date +'\%d').log 2>&1

Check

sudo -u mysql -s crontab -l

You may need to add 'mysql' to /etc/cron.allow...
sudo -u root -s vi /etc/cron.allow

Bibliography