MySQL 6.2.2：xtrabackup 的 “全量+增量+binlog” 备份及恢复实验

6.2.2：备份策略：全量+增量+binlog

执行全量备份

[root@node103 ~]# innobackupex --user=root --password=123456 --no-timestamp /data/backup/all-db

添加数据

MariaDB [(none)]> INSERT INTO sample_db.tbl1 (id,name) VALUES (3,'incremental1');

MariaDB [(none)]> SELECT * FROM sample_db.tbl1;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | user1        |
|  2 | user2        |
|  3 | incremental1 |
+----+--------------+

执行第一次增量备份

[root@node103 ~]# innobackupex --user=root --password=123456 --incremental --no-timestamp /data/backup/incremental-1 --incremental-basedir=/data/backup/all-db

添加数据

MariaDB [(none)]> INSERT INTO sample_db.tbl1 (id,name) VALUES (4,'incremental2');

MariaDB [(none)]> SELECT * FROM sample_db.tbl1;                                  
+----+--------------+
| id | name         |
+----+--------------+
|  1 | user1        |
|  2 | user2        |
|  3 | incremental1 |
|  4 | incremental2 |
+----+--------------+

执行第二次增量备份

[root@node103 ~]# innobackupex --user=root --password=123456 --incremental --no-timestamp /data/backup/incremental-2 --incremental-basedir=/data/backup/incremental-1

删除数据

[root@node103 ~]# rm -rf /data/mysql/*
[root@node103 ~]# rm -rf /data/mysql_logs/*

prepare

[root@node103 ~]# innobackupex --apply-log --redo-only /data/backup/all-db/
[root@node103 ~]# innobackupex --apply-log --redo-only /data/backup/all-db/ --incremental-dir=/data/backup/incremental-1
[root@node103 ~]# innobackupex --apply-log --redo-only /data/backup/all-db/ --incremental-dir=/data/backup/incremental-2
[root@node103 ~]# innobackupex --apply-log /data/backup/all-db/

恢复

[root@node103 ~]# systemctl stop mariadb

[root@node103 ~]# innobackupex --copy-back /data/backup/all-db/

[root@node103 ~]# chown -R mysql:mysql /data/mysql*

验证

[root@node103 ~]# systemctl start mariadb

MariaDB [(none)]> SELECT * FROM sample_db.tbl1;                                  
+----+--------------+
| id | name         |
+----+--------------+
|  1 | user1        |
|  2 | user2        |
|  3 | incremental1 |
|  4 | incremental2 |
+----+--------------+

MariaDB [(none)]> SELECT * FROM sample_db1.tbl1;
+----+-------+
| id | name  |
+----+-------+
|  1 | user1 |
|  2 | user2 |
|  3 | user3 |
|  4 | user4 |
|  5 | user5 |
|  6 | user6 |
|  7 | user7 |
|  8 | user8 |
+----+-------+