Step-by-Step MySQL Full and Incremental Backup & Restore Using XtraBackup

This guide demonstrates how to create a MySQL database and table, insert sample data, and set up a dedicated backup user with the necessary privileges.

Database creation and data insertion:

mysql> create database user_info;

mysql> use user_info;

mysql> create table user (id int primary key, name varchar(40), birthday datetime);

mysql> insert into user values(1,'tainzuzhuang',NOW());

mysql> insert into user values(2,'tianshouke',NOW());

Backup user creation and privilege grant:

mysql> create user 'dbbackup'@'%' identified WITH mysql_native_password by '123456' PASSWORD EXPIRE NEVER;

mysql> GRANT BACKUP_ADMIN,SELECT,RELOAD,PROCESS,SUPER,LOCK TABLES,REPLICATION SLAVE,REPLICATION CLIENT,SHOW VIEW ON *.* TO 'dbbackup'@'%';

mysql> flush privileges;

Full backup using XtraBackup:

mkdir /backup/full -p

xtrabackup --defaults-file=/etc/my.cnf --host=localhost --user=dbbackup --password=123456 --port=3306 --backup --target-dir=/backup/full/full_`date +%Y-%m-%d-%H-%M-%S`

Compress the backup directory:

tar -zcvf full.tar.gz full_2022-03-10-16-46-31

Transfer the compressed backup to a remote server and extract:

scp full.tar.gz [email protected]:/backup/full/

tar -zxvf full.tar.gz

Restore the full backup:

xtrabackup --prepare --target-dir=/backup/full/full_2022-03-10-16-46-31

/etc/init.d/mysqld stop

xtrabackup --copy-back --target-dir=/backup/full/full_2022-03-10-16-46-31

chown -R mysql.mysql /data/mysql/

/etc/init.d/mysqld start

Perform incremental backups after inserting additional rows:

mysql> insert into user values(3,'tomtian',NOW());

mysql> insert into user values(4,'hanmeimei',NOW());

mkdir inc1

xtrabackup --defaults-file=/etc/my.cnf --host=localhost --user=dbbackup --password=123456 --port=3306 --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/full/full_2022-03-10-16-46-31/

Second incremental backup after more inserts:

mysql> insert into user values(5,'jiantom',NOW());

mysql> insert into user values(6,'jimli',NOW());

mkdir inc2

xtrabackup --defaults-file=/etc/my.cnf --host=localhost --user=dbbackup --password=123456 --port=3306 --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1

Validate LSN continuity by checking xtrabackup_checkpoints files in the full and incremental directories.

Restore the first incremental backup:

xtrabackup --prepare --target-dir=/backup/full/full_2022-03-10-16-46-31 --incremental-dir=/backup/full/inc1

Restore the second incremental backup:

xtrabackup --prepare --target-dir=/backup/full/full_2022-03-10-16-46-31 --incremental-dir=/backup/full/inc2

After preparing all logs, copy back the data, fix permissions, restart MySQL, and verify that all data—including the rows from both incremental backups—has been successfully restored.