MySQL Logical vs Physical Backup and Percona XtraBackup Installation, Configuration, and Backup/Restore Procedures

1. Basic Knowledge

MySQL backups are divided into logical backups (exported SQL statements) and physical backups (binary copies of data, index, and log files). Logical backups use mysqldump and are portable but slower; physical backups use tools like Percona XtraBackup, are faster, but less portable.

Logical Backup Features

Backup file is a text file containing SQL statements.

Backup and restore speed is relatively slow.

Highly portable across MySQL versions and architectures.

Allows selective backup of databases, tables, or records.

Physical Backup Features

Backup file is a binary copy of the actual data files.

Backup and restore are generally faster.

Portability is limited to the same MySQL version and OS.

Usually requires stopping the database or using a read lock.

2. Percona XtraBackup Installation

Environment: CentOS 7.9, MySQL 5.7, instance port 3307.

yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm

Install XtraBackup 2.4:

yum install -y percona-xtrabackup-24.x86_64

Common version mapping:

MySQL 5.1 → XtraBackup 2.0

MySQL 5.6 → XtraBackup 2.2

MySQL 5.7 → XtraBackup 2.4

Typical parameters (excerpt):

--defaults-file            # same as xtrabackup's --defaults-file
--apply-log               # apply transaction logs to prepare backup
--copy-back               # copy backup files back to datadir during recovery
--remote-host=HOSTNAME    # store backup on remote host via ssh
--stream=[tar]            # output format, tar uses tar4ibd
--tmpdir=DIRECTORY        # temporary directory for logs
--redo-only               # apply only redo logs, skip rollback
--use-memory=#            # memory used during apply-log
--throttle=IOS            # throttle I/O
--include=REGEXP          # include specific databases/tables
--databases=LIST          # list of databases to backup
--slave-info              # embed CHANGE MASTER statement
--socket=SOCKET           # path to mysql.sock
--compact                 # backup without secondary indexes
--incremental-basedir=DIR # base directory for incremental backup
--incremental             # create incremental backup
--no-timestamp            # do not create timestamped sub‑folders

3. Backup and Restore Process

Backup directory: /backup/mysql

3.1 Full Backup and Restore

innobackupex --defaults-file=/etc/my3307.cnf --host=localhost --port=3307 -S /tmp/mysql.sock --user=root --password='Rootasdf2023' --no-timestamp /backup/mysql/full_`date +%Y-%m-%d_%H-%M-%S_%w`

Full restore steps:

Stop MySQL: service mysqld stop

Backup current data directory.

Apply logs: innobackupex --apply-log /backup/mysql/full_2023-05-10_14-08-36_3

Copy files back: innobackupex --defaults-file=/etc/my3307.cnf --copy-back --rsync /backup/mysql/full_2023-05-10_14-08-36_3

Set ownership: chown -R mysql:mysql /data/3307/data

Start MySQL: /etc/init.d/mysql3307 start

3.2 Incremental Backup and Restore

Two incremental strategies are described: (1) always increment on the original full backup base, (2) increment on the previous incremental backup. The article demonstrates the second method.

Example commands:

# Full backup
innobackupex --defaults-file=/etc/my3307.cnf --host=localhost --port=3307 -S /tmp/mysql.sock --user=root --password='Rootasdf2023' --no-timestamp /backup/mysql/full_`date +%Y-%m-%d_%H-%M-%S_%w`

# First incremental
innobackupex --defaults-file=/etc/my3307.cnf --host=localhost --port=3307 -S /tmp/mysql.sock --user=root --password='Rootasdf2023' --no-timestamp --incremental-basedir=/backup/mysql/full_2023-05-10_14-08-36_3 --incremental /backup/mysql/incr_`date +%Y-%m-%d_%H-%M-%S_%w`

# Second incremental
innobackupex --defaults-file=/etc/my3307.cnf --host=localhost --port=3307 -S /tmp/mysql.sock --user=root --password='Rootasdf2023' --no-timestamp --incremental-basedir=/backup/mysql/incr_2023-05-10_14-11-28_3 --incremental /backup/mysql/incr_`date +%Y-%m-%d_%H-%M-%S_%w`

Prepare (apply‑log) steps:

# Apply redo‑only to full backup
innobackupex --apply-log --redo-only /backup/mysql/full_2023-05-10_14-08-36_3

# Apply first incremental
innobackupex --apply-log --redo-only /backup/mysql/full_2023-05-10_14-08-36_3 --incremental-dir=/backup/mysql/incr_2023-05-10_14-11-28_3

# Apply second incremental (final, no redo‑only)
innobackupex --apply-log /backup/mysql/full_2023-05-10_14-08-36_3 --incremental-dir=/backup/mysql/incr_2023-05-10_14-13-17_3

# Merge all into final backup
innobackupex --apply-log /backup/mysql/full_2023-05-10_14-08-36_3

Recovery steps (after stopping MySQL, optionally removing data, then copying back):

innobackupex --defaults-file=/etc/my3307.cnf --copy-back --rsync /backup/mysql/full_2023-05-10_14-08-36_3
chown -R mysql:mysql /data/3307/data
/etc/init.d/mysql3307 start

4. Common Issues

Issue: XtraBackup –copy-back fails with "Can't create/write to file './undo_001'".

Solution: Comment out the #innodb_undo_directory=. line in /etc/my.cnf .