xtrabackup增量备份与还原

环境配置

yum install percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm
yum install mariadb-server
systemctl restart mariadb
msyql < hellodb_innodb.sql

完全备份与增量备份

mkdir /backup
xtrabackup --backup --target-dir=/backup

-- 第一次修改数据
MariaDB [hellodb]> delete from toc;
MariaDB [hellodb]> update teachers set name='zhangsan' where tid=4;
-- 第一次增量备份
xtrabackup --backup --target-dir=/backup/inc1 --incremental-basedir=/backup
-- 第二次修改数据
MariaDB [hellodb]> insert into students(name,age)values('wangwu',32);
-- 第二次增量备份
xtrabackup --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1

将备份迁移到另外一台机器上,整理数据，封口

scp -r /backup 192.168.209.19:/
yum install percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm
yum install mariadb-server
mkdir /data/msyql

vim /etc/my.cnf
[mysqld]
datadir=/data/mysql

xtrabackup --prepare --apply-log-only --target-dir=/backup/base
xtrabackup --prepare --apply-log-only --target-dir=/backup/base --incremental-dir=/backup/inc1
xtrabackup --prepare  --target-dir=/backup/base --incremental-dir=/backup/inc2

还原数据

xtrabackup --copy-back --target-dir=/backup/base
chown -R mysql.mysql /data/mysql
systemcctl restart mariadb

验证

MariaDB [hellodb]> select * from students where name='wangwu';
+-------+--------+-----+--------+---------+-----------+
| StuID | Name   | Age | Gender | ClassID | TeacherID |
+-------+--------+-----+--------+---------+-----------+
|    26 | wangwu |  32 | F      |    NULL |      NULL |
+-------+--------+-----+--------+---------+-----------+
1 row in set (0.00 sec)

MariaDB [hellodb]> select * from toc;
Empty set (0.00 sec)

MariaDB [hellodb]> select * from teachers where name='zhangsan';
+-----+----------+-----+--------+
| TID | Name     | Age | Gender |
+-----+----------+-----+--------+
|   4 | zhangsan |  93 | F      |
+-----+----------+-----+--------+