MySQL Full and Incremental Backup, Restore, and Recovery Using Percona XtraBackup

The article demonstrates how to back up a MySQL server with Percona XtraBackup, perform incremental backups, handle accidental database deletion, and fully restore the instance.

Preparation

Create a directory for the backups and run a full backup:

mkdir -p /backup/mysql

xtrabackup --defaults-file=/etc/my.cnf --host=localhost --user=root --password=123456 --port=3306 \
  --backup --target-dir=/backup/mysql/full_`date +%F`

List the files created by the full backup:

cd /backup/mysql/full_2022-03-09
ll

total 568372
-rw-r----- 1 root root 499 Mar 9 10:09 backup-my.cnf
-rw-r----- 1 root root 7581 Mar 9 10:09 ib_buffer_pool
-rw-r----- 1 root root 536870912 Mar 9 10:09 ibdata1
... (other files omitted for brevity)

Sample data creation

Create a test database and table, then insert some rows:

mysql> create database sfqd;

mysql> use sfqd;

mysql> create table class (id int(10) not null auto_increment, name varchar(30), grade varchar(20), primary key(id));

mysql> insert into class values (3,'jim',''),(4,'tom','');

mysql> insert into class values (25,'',''),(26,'','');

mysql> insert into class values (1,'tian',''),(2,'xue','');

Incremental backup

After the data is inserted, run an incremental backup:

xtrabackup --defaults-file=/etc/my.cnf --host=localhost --user=root --password=123456 --port=3306 \
  --backup --target-dir=/backup/mysql/full_2022-03-09-10-42-52/incr_back/ \
  --incremental-dir=/backup/incremental/incremental_bak_20220309/

Accidental deletion

The databases are dropped to simulate a mistake:

mysql> show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sfqd               |
| students_info      |
| sys                |
| zabbix             |
+--------------------+

mysql> drop database sfqd;

mysql> drop database students_info;

After dropping, the remaining databases are listed again, showing only system schemas.

Restore procedure

1. Stop MySQL service:

/etc/init.d/mysqld stop

2. Move the old data directory aside and prepare the backup:

mv /data/mysql/mysql /data/mysql/

xtrabackup --prepare --apply-log-only --target-dir=/backup/mysql/full_2022-03-09-10-42-52

3. Copy the prepared files back to the data directory:

xtrabackup --defaults-file=/etc/my.cnf --copy-back --target-dir=/backup/mysql/full_2022-03-09-10-42-52

4. Fix ownership and start MySQL:

chown -R mysql.mysql /data/mysql/

/etc/init.d/mysqld start

5. Verify the restored data and perform binlog recovery if needed:

mysql> insert into class values (6,'jim','male'),(7,'tom','female');

The article concludes with a reminder to like, share, and comment if the guide was helpful.