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

6.2.1：备份策略：全量+binlog

• 查看当前数据：

MariaDB [sample_db]> SELECT * FROM sample_db.tbl1;
+----+-------+
| id | name  |
+----+-------+
|  1 | user1 |
|  2 | user2 |
+----+-------+
2 rows in set (0.00 sec)

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

进行全量备份

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

[root@node103 ~]# ll /data/backup/2020-11-30_20-48-48/
total 18460
-rw-r----- 1 root root      434 Nov 30 20:48 backup-my.cnf
-rw-r----- 1 root root 18874368 Nov 30 20:48 ibdata1
drwxr-x--- 2 root root     4096 Nov 30 20:48 mysql
drwxr-x--- 2 root root     4096 Nov 30 20:48 performance_schema
drwxr-x--- 2 root root       49 Nov 30 20:48 sample_db
drwxr-x--- 2 root root       49 Nov 30 20:48 sample_db1
-rw-r----- 1 root root       25 Nov 30 20:48 xtrabackup_binlog_info
-rw-r----- 1 root root      135 Nov 30 20:48 xtrabackup_checkpoints
-rw-r----- 1 root root      479 Nov 30 20:48 xtrabackup_info
-rw-r----- 1 root root     2560 Nov 30 20:48 xtrabackup_logfile

删除数据

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

prepare 全量备份

[root@node103 ~]# innobackupex --apply-log /data/backup/2020-11-30_20-48-48/

恢复

[root@node103 ~]# systemctl stop mariadb

[root@node103 ~]# innobackupex --copy-back /data/backup/2020-11-30_20-48-48/

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

验证数据

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

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 |
+----+-------+