Master Percona XtraBackup: Fast Hot Backups & Replication for MySQL 5.7

Practical Environment

MySQL 5.7

XtraBackup 2.4

What is XtraBackup? Percona XtraBackup is an open‑source hot‑backup utility for MySQL that backs up InnoDB, XtraDB and MyISAM tables without locking the database. It supports MySQL 5.1‑5.7 and Percona Server with XtraDB.

Two tools are provided:

xtrabackup – backs up only InnoDB/XtraDB.

innobackupex – a Perl wrapper that adds MyISAM support (requires a read lock for MyISAM).

XtraBackup Features

Fast and reliable backup process.

Does not interrupt running transactions.

Compression saves disk space and bandwidth.

Automatic backup verification.

Quick restoration.

Backup Mechanism

innobackupex starts an xtrabackup_log monitoring thread to track redo‑log changes and copies new log entries to an internal log while a copy thread copies InnoDB files and records the current binlog position.

Full Backup Restore Principle

The backup files are replayed with xtrabackup_log, redo committed transactions, roll back uncommitted ones, and then the files are placed in MySQL’s data directory.

Master Database Operations

1. Install XtraBackup

$ yum install -y http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm</code>
<code>$ yum update percona-release</code>
<code>$ yum install percona-xtrabackup-24 -y

2. Perform a full backup

# Full backup</code>
<code>$ innobackupex --defaults-file=/etc/my.cnf --user=root --password=magedu /data/backup</code>
<code># Apply log to keep transaction consistency</code>
<code>$ innobackupex --apply-log /data/backup/2020-03-28_19-13-07

3. Compress and copy the backup to the slave

$ tar czf backup.tar.gz 2020-03-28_19-13-07</code>
<code>$ scp -r /data/backup/backup.tar.gz 172.21.0.8:/data/backup/

4. Grant replication privileges on the master

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY 'slave_passport';</code>
<code>mysql> FLUSH PRIVILEGES;

5. Verify backup location and prepare for slave sync

$ cat /data/backup/2020-03-28_19-13-07/xtrabackup_binlog_info

Slave Database Operations

1. Restore the full backup

$ cd /data/backup/ && tar xf backup.tar.gz</code>
<code>$ innobackupex --defaults-file=/etc/my.cnf --user=root --copy-back /data/backup/2020-03-28_19-13-07

2. Adjust file permissions $ chown -R mysql:mysql /var/lib/mysql 3. Start the slave MySQL service $ systemctl restart mysqld 4. Configure master connection information

mysql> CHANGE MASTER TO MASTER_HOST='172.21.0.9',
MASTER_USER='slave',
MASTER_PASSWORD='slave_passport',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=1053;

5. Start replication and verify status

mysql> start slave;</code>
<code>mysql> show slave status\G;