innobackupex incremental backup and recovery

Download: Xtrabackup

Unzip, install yum

tar -xvf Percona-XtraBackup-2.4.8-r97330f7-el7-x86_64-bundle.tar
yum install percona-xtrabackup-24-2.4.8-1.el7.x86_64.rpm

Full backup

innobackupex –user=root –password=123456 –no-timestamp /backup/mysql/full

Add data in database

Add a database db1, set up table t1 in db1 (the engine of table is innodb), and insert data

MariaDB [(none)]> create database if not exists db1;
MariaDB [(none)]> use db1;
MariaDB [db1]> create table t1(id int,name varchar(20))engine=innodb;
MariaDB [db1]> insert into t1 values(1,'aa');

Incremental backup first time

[root@apenglinux-001 ~]# innobackupex --user=root --password=123456 --no-timestamp --incremental-basedir=/backup/mysql/full --incremental /backup/mysql/01

Add data in database

mysql -uroot -p123456 -e "insert into db1.t1(id,name)values(2,'100');"

Incremental backup second time

[root@apenglinux-001 ~]# innobackupex --user=root --password=123456 --no-timestamp --incremental-basedir=/backup/mysql/01 --incremental /backup/mysql/02

Simulate data loss and delete all contents in the database

cd /var/lib/mysql/
rm -rf *

Restore full backup

[root@apenglinux-001 mysql]# innobackupex --user=root --password=123456 --apply-log --redo-only /backup/mysql/full/

Restore first incremental backup

[root@apenglinux-001 mysql]# innobackupex --user=root --password=123456 --apply-log --redo-only /backup/mysql/full/ --incremental-dir=/backup/mysql/01

Restore the second incremental backup

[root@apenglinux-001 mysql]# innobackupex --user=root --password=123456 --apply-log /backup/mysql/full/ --incremental-dir=/backup/mysql/02

Make an application log for the combined data as a whole

[root@apenglinux-001 mysql]# innobackupex --user=root --password=123456 --copy-back /backup/mysql/full/

Setting permissions

[root@apenglinux-001 mysql]# chown -R mysql.mysql /var/lib/mysql/

Restart to see if the recovery is successful

[root@apenglinux-001 mysql]# systemctl restart mariadb
[root@apenglinux-001 mysql]# mysql -uroot -p123456 -e "select * from db1.t1;"
| id   | name |
|    1 | aa   |
|    2 | 100  |

Keywords: MySQL Database MariaDB yum

Added by Hatdrawn on Sat, 14 Dec 2019 17:40:51 +0200