MariaDB备份恢复测试过程记录

5、MariaDB备份恢复测试过程记录

5.1、安装Mariabackup

yum方式安装：
# yum install MariaDB-backup

rpm方式安装
# rpm -vih MariaDB-backup-10.3.18-1.el7.centos.x86_64.rpm 
Preparing...                          ################################# [100%]
Updating / installing...
   1:MariaDB-backup-10.3.18-1.el7.cent################################# [100%]

5.2、备份与恢复

5.2.1、创建备份用户

[root@mysql-test-2 ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 32
Server version: 10.3.14-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> CREATE USER 'mariabackup'@'localhost' IDENTIFIED BY 'mypassword';
Query OK, 0 rows affected (0.029 sec)

MariaDB [(none)]> GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'mariabackup'@'localhost';
Query OK, 0 rows affected (0.001 sec)

5.2.2、全库备份与恢复

5.2.2.1、备份

[root@mysql-test-2 backup]#  mariabackup --backup    --target-dir=/backup/    --user=mariabackup --password=mypassword

5.2.2.2、准备备份

[root@mysql-test-2 backup]# mariabackup --prepare --target-dir=/backup/

5.2.2.4、恢复

停止数据库
[root@mysql-test-2 backup]# systemctl stop mysqld

[root@mysql-test-2 backup]# cd /var/lib/mysql
[root@mysql-test-2 mysql]# rm -rf *

[root@mysql-test-2 lib]# mariabackup --copy-back --target-dir=/backup/

[root@mysql-test-2 mysql]# chown -R mysql:mysql /var/lib/mysql/

[root@mysql-test-2 mysql]# systemctl start mysqld

5.2.4.1、使用其他工具恢复

$ rsync -avrP /var/mariadb/backup /var/lib/mysql/
$ chown -R mysql:mysql /var/lib/mysql/
$ rm /var/lib/mysql/ib_logfile*

5.2.3、增量备份及恢复

5.2.3.1、备份

5.2.3.1.1、全备份

[root@mysql-test-2 backup]#  mariabackup --backup    --target-dir=/backup/full_20191029/    --user=mariabackup --password=mypassword

5.2.3.1.2、备份增量

[root@mysql-test-2 backup]# mariabackup --backup --target-dir=/backup/full_20191029_incr/ --incremental-basedir=/backup/full_20191029/ --user=mariabackup --password=mypassword

5.2.3.1.3、再次增量

[root@mysql-test-2 backup]# mariabackup --backup --target-dir=/backup/full_20191029_incr2/ --incremental-basedir=/backup/full_20191029_incr/ --user=mariabackup --password=mypassword

5.2.3.2、准备备份

全备
[root@mysql-test-2 backup]# mariabackup --prepare --target-dir=/backup/full_20191029/

第一次增量
mariabackup --prepare --target-dir=/backup/full_20191029/ --incremental-dir=/backup/full_20191029_incr/

第二次增量
mariabackup --prepare --target-dir=/backup/full_20191029/ --incremental-dir=/backup/full_20191029_incr2/

5.2.3.3、恢复

停止数据库
[root@mysql-test-2 backup]# systemctl stop mysqld

[root@mysql-test-2 backup]# cd /var/lib/mysql
[root@mysql-test-2 mysql]# rm -rf *

[root@mysql-test-2 lib]# mariabackup --copy-back --target-dir=/backup/full_20191029/

[root@mysql-test-2 mysql]# chown -R mysql:mysql /var/lib/mysql/

[root@mysql-test-2 mysql]# systemctl start mysqld