mariabackup实现完全备份+增量备份+binlog恢复库

mariabackup实现完全备份+增量备份+binlog恢复库

注：在MariaDB 10.3及更高版本中，建议使用Mariabackup替代 Percona XtraBackup的备份方法。
在MariaDB 10.3及更高版本中，不支持 Percona XtraBackup。

mariabackup工具备份和还原，需要三步实现

1. 备份：对数据库做完全或增量备份
2. 预准备： 还原前，先对备份的数据，整理至一个临时目录
3. 还原：将整理好的数据，复制回数据库目录中

环境准备

操作系统及IP	数据库版本	描述
CentOS7.9-10.0.0.7	MariaDB-10.4.22	backup节点 备份
CentOS7.9-10.0.0.17	MariaDB-10.4.22	mysql节点 还原

查看备份前的数据库

MariaDB [mysql]> show databases;
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| hellodb2           |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+

一、完全备份

1.1 在原主机做完全备份到/backup

[root@backup ~]#mkdir /backup

#创建用户并授权
MariaDB [mysql]> CREATE USER mariabackup@'localhost' IDENTIFIED BY 'mypassword';
MariaDB [mysql]> GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'mariabackup'@'localhost';
MariaDB [mysql]> FLUSH PRIVILEGES;

[root@backup ~]#mariabackup -umariabackup -pmypassword --backup --target-dir=/backup/base
[root@backup ~]#ll /backup/base/
total 12352
-rw-r----- 1 root root    24576 Feb 28 09:29 aria_log.00000001
-rw-r----- 1 root root       52 Feb 28 09:29 aria_log_control
-rw-r----- 1 root root      324 Feb 28 09:29 backup-my.cnf
drwx------ 2 root root     4096 Feb 28 09:29 hellodb
drwx------ 2 root root     4096 Feb 28 09:29 hellodb2
-rw-r----- 1 root root      976 Feb 28 09:29 ib_buffer_pool
-rw-r----- 1 root root 12582912 Feb 28 09:28 ibdata1
-rw-r----- 1 root root     2560 Feb 28 09:29 ib_logfile0
drwx------ 2 root root     4096 Feb 28 09:29 mysql
drwx------ 2 root root     4096 Feb 28 09:29 performance_schema
-rw-r----- 1 root root       75 Feb 28 09:29 xtrabackup_checkpoints
-rw-r----- 1 root root      444 Feb 28 09:29 xtrabackup_info

1.2 第一次修改数据

MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
+-----+---------------+-----+--------+

MariaDB [hellodb]> insert teachers values(null,'wang',18,'M');
MariaDB [hellodb]> insert teachers values(null,'chen',18,'M');
MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | wang          |  18 | M      |
|   6 | chen          |  18 | M      |
+-----+---------------+-----+--------+

二、增量备份

2.1第一次增量备份

[root@backup ~]#mariabackup -umariabackup -pmypassword --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base

[root@backup ~]#ll /backup/
total 8
drwx------ 6 root root 4096 Feb 28 09:29 base
drwx------ 6 root root 4096 Feb 28 09:31 inc1

2.2 第二次修改数据

MariaDB [hellodb]> insert teachers values(null,'zhang',19,'M');
MariaDB [hellodb]> insert teachers values(null,'jie',20,'M');
MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | wang          |  18 | M      |
|   6 | chen          |  18 | M      |
|   7 | zhang         |  19 | M      |
|   8 | jie           |  20 | M      |
+-----+---------------+-----+--------+

2.3 第二次增量备份

[root@backup ~]#mariabackup -umariabackup -pmypassword --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1

[root@backup ~]#ll /backup/
total 12
drwx------ 6 root root 4096 Feb 28 12:52 base
drwx------ 6 root root 4096 Feb 28 12:59 inc1
drwx------ 6 root root 4096 Feb 28 13:03 inc2

[root@backup ~]#du -sh /backup/*
16M	/backup/base
3.0M	/backup/inc1
3.0M	/backup/inc2

三、数据库还原

3.1mysql节点停止数据库服务

[root@mysql ~]#systemctl stop mysqld

3.2确保datadir数据目录为空

#注：数据库目录必须为空，否则MySQL服务不能启动
[root@mysql ~]#cat /etc/my.cnf
[mysqld]
#datadir=/var/lib/mysql
datadir=/data/mysql

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

3.3backup节点开始复制

#目标主机无需创建/backup目录,直接复制目录本身
[root@backup ~]#scp -r /backup/ 10.0.0.17:/

3.4查看backup节点复制的文件

[root@mysql ~]#ll /backup/
total 12
drwx------ 6 root root 4096 Feb 28 13:10 base
drwx------ 6 root root 4096 Feb 28 13:10 inc1
drwx------ 6 root root 4096 Feb 28 13:10 inc2

3.5 预准备完全备份

#准备现有备份以还原到 MariaDB 服务器
[root@mysql ~]#mariabackup --prepare --target-dir=/backup/base

#合并第1次增量备份到完全备份
[root@mysql ~]#mariabackup --prepare --target-dir=/backup/base --incremental-dir=/backup/inc1

#合并第2次增量备份到完全备份
[root@mysql ~]#mariabackup --prepare --target-dir=/backup/base --incremental-dir=/backup/inc2

3.6复制到数据库目录

#注意数据库目录必须为空，MySQL服务不能启动 
[root@mysql ~]#mariabackup --copy-back --target-dir=/backup/base

#还原属性
[root@mysql ~]#chown -R mysql:mysql /data/mysql

#重启数据库服务
[root@mysql ~]#systemctl restart mysqld

[root@mysql ~]#du -sh /data/mysql
112M	/data/mysql

3.7 mysql还原节点确认数据的完整性

MariaDB [mysql]> show databases;
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| hellodb2           |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+

#注：如果出现这种问题，需要reboot重启系统
MariaDB [hellodb]> select * from teachers;
ERROR 1932 (42S02): Table 'hellodb.teachers' doesn't exist in engine

[root@mysql ~]#reboot

MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | wang          |  18 | M      |
|   6 | chen          |  18 | M      |
|   7 | zhang         |  19 | M      |
|   8 | jie           |  20 | M      |
+-----+---------------+-----+--------+