使用备份工具xtrabackup对数据库进行压缩备份并解压缩

压缩备份

• 如果要加速压缩，可以使用--compress-threads选项

• --compress-threads=4 使用四个线程同时进行压缩

清除之前的备份文件

[root@service ~]# rm -rf /data/backup/*
[root@service ~]# ls /data/backup

输入备份压缩命令

[root@service ~]# xtrabackup --defaults-file=/etc/my.cnf --backup --compress --target-dir=/data/backup/compressed/ -uroot -pWyxbuke00. -H localhost -P 3306
[root@service ~]# ls /data/backup
compressed

 停止MySQL服务，删除数据库中的文件

[root@service ~]# systemctl stop mysqld
[root@service ~]# rm -rf /var/lib/mysql/*

 

解压缩

• 解压缩需要有 qpress 命令，由于我建了yum库，可以直接通过yum安装 yum -y install qpress

• 使用 --decompress压缩的备份集在准备备份之前需要解压，解压工具是qpress。解压后的原文件不会被删除，可以使用--remove-original选项清除。

• --parallel可与--decompress选项一起使用以同时解压缩多个文件

输入解压缩命令

xtrabackup --defaults-file=/etc/my.cnf --decompress --target-dir=/data/backup/compressed/

 

准备备份文件

[root@service ~]# xtrabackup --prepare --target-dir=/data/backup/compressed

 

开始恢复

[root@service ~]# xtrabackup --defaults-file=/etc/my.cnf --copy-back --target-dir=/data/backup/compressed

 

查看恢复清空

[root@service ~]#  chown -R mysql.mysql /var/lib/mysql
[root@service ~]# systemctl start mysqld
[root@service ~]# ls /var/lib/mysql
 auto.cnf        ca-key.pem        db01                 ibdata1         mysql             performance_schema   server-key.pem             undo_001
 binlog.000023   ca.pem           '#ib_16384_0.dblwr'   ibtmp1          mysql.ibd         private_key.pem      service-relay-bin.000001   undo_002
 binlog.000024   client-cert.pem  '#ib_16384_1.dblwr'  '#innodb_redo'   mysql.sock        public_key.pem       service-relay-bin.index    xtrabackup_info
 binlog.index    client-key.pem    ib_buffer_pool      '#innodb_temp'   mysql.sock.lock   server-cert.pem      sys
[root@service ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.36 MySQL Community Server - GPL

Copyright (c) 2000, 2024, 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> use db01;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from tb_user;
+----+----------+------+
| id | name     | sex  |
+----+----------+------+
|  1 | Tom      | 1    |
|  2 | Trigger  | 0    |
|  3 | Dawn     | 1    |
|  4 | sxx      | 0    |
|  5 | wyx      | 1    |
|  6 | asd      | 1    |
|  7 | lisi     | 1    |
|  8 | zhangsan | 1    |
|  9 | haha     | 1    |
+----+----------+------+
9 rows in set (0.00 sec)