xtrabackup 2.4使用方法

全量备份
创建备份用户

create user 'raybak'@'%' identified by 'raybak';
grant reload,lock tables,replication client,process,super on *.* to 'raybak'@'%';
flush privileges;

创建备份目录

mkdir -p /mysql/backup

全备数据库

./innobackupex --defaults-file=/mysql/my3306/my.cnf --no-timestamp --user raybak --password raybak /mysql/backup/full20221208

删除数据库ray

mysql> drop database ray;
Query OK, 1 row affected (0.02 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lei                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

恢复数据库

./innobackupex --defaults-file=/mysql/my3306/my.cnf --user=raybak --password raybak --apply-log /mysql/backup/full20221208

关闭数据库

mysqladmin -uroot -prootroot shutdown

重命名原来的数据目录

[root@ray1 ~]# cd /mysqldata/my3306/
[root@ray1 my3306]# mv data/ data_old

新备份集mv到数据目录

[root@ray1 ~]# cd /mysql/backup/
[root@ray1 backup]# mv full20221208/ /mysqldata/my3306/data

更改数据库文件目录所属用户和属组

[root@ray1 ~]# cd /mysqldata/my3306/
[root@ray1 my3306]# chown -R mysql:mysql data

启动MySQL数据库

[root@ray1 my3306]# systemctl start mysqld
[root@ray1 my3306]# mysql -uroot -prootroot
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.38-log MySQL Community Server (GPL)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lei                |
| mysql              |
| performance_schema |
| ray                |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

增量备份数据库
查看上一次全备的起始LSN

[root@ray1 full20221208]# cat xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 215551181
last_lsn = 215551190
compact = 0
recover_binlog_info = 0
flushed_lsn = 215551190

执行增量备份

./innobackupex --defaults-file=/mysql/my3306/my.cnf --no-timestamp --user raybak --password raybak --incremental /mysql/backup/incre20221209 --incremental-basedir=/mysql/backup/full20221208

增量恢复数据库
全量恢复数据库

./innobackupex --defaults-file=/mysql/my3306/my.cnf --user raybak --password raybak --apply-log --redo-only /mysql/backup/full20221208

恢复增量

./innobackupex --defaults-file=/mysql/my3306/my.cnf --user raybak --password raybak --apply-log --redo-only /mysql/backup/full20221208 --incremental-dir=/mysql/backup/incre20221209

恢复全量

./innobackupex --defaults-file=/mysql/my3306/my.cnf --user raybak --password raybak --apply-log /mysql/backup/full20221208

关闭数据库

mysqladmin -uroot -prootroot shutdown

重命名原来的数据目录

[root@ray1 ~]# cd /mysqldata/my3306/
[root@ray1 my3306]# mv data/ data_old

新备份集mv到数据目录

[root@ray1 ~]# cd /mysql/backup/
[root@ray1 backup]# cp -r full20221208/ /mysqldata/my3306/data

更改数据库文件目录所属用户和属组

[root@ray1 ~]# cd /mysqldata/my3306/
[root@ray1 my3306]# chown -R mysql:mysql data

启动MySQL数据库

[root@ray1 my3306]# systemctl start mysqld
[root@ray1 my3306]# mysql -uroot -prootroot
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.38-log MySQL Community Server (GPL)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lei                |
| mysql              |
| performance_schema |
| ray                |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

备份到远程机器
远程流试压缩备份

./innobackupex --defaults-file=/mysql/my3306/my.cnf --no-timestamp --user raybak --password raybak --stream=tar /tmp | gzip | ssh root@192.168.0.1 "cat - > /home/full20221208bak.tar.gz"