xtrabackup8.0本地备份和恢复(xbstream+compress)

1.备份数据库
压缩+流式备份
mkdir -p /opt/xtrabackup_file
/opt/xtrabackup-8.0.35-34/bin/xtrabackup --defaults-file=/home/middle/mysql8/conf/my.cnf --user=root --password=mysql -P13306 --socket=/tmp/mysql.sock --no-version-check --backup --compress --stream=xbstream --target-dir=/opt/xtrabackup_file>/opt/xtrabackup_file/mybackup_`date "+%Y%m%d"`.xb

备份完成后会显示ok提示
[Xtrabackup] completed OK!

查看备份后的文件

[root@localhost xtrabackup_file]# ls -al /opt/xtrabackup_file
total 1580
drwxr-xr-x 2 root root      31 Sep 10 09:42 .
drwxr-xr-x 8 root root     132 Sep  8 16:40 ..
-rw-r--r-- 1 root root 1616316 Sep 10 09:42 mybackup_20250910.xb

 

2.停掉数据库
systemctl stop mysql
或是
/home/middle/mysql8/bin/mysqladmin -h localhost -uroot -P13306 -pmysql shutdown

 

3.备份原来的数据目录
[root@localhost mysql8]#cd /home/middle/mysql8
[root@localhost mysql8]# mv data bak_data

 

4.创建空的数据目录
[root@localhost mysql8]# cd /home/middle/mysql8
[root@localhost mysql8]# mkdir data

 

5.恢复
直接解压到数据目录,减少--copy-back的步骤
/opt/xtrabackup-8.0.35-34/bin/xbstream -x --parallel=4 -C /home/middle/mysql8/data < /opt/xtrabackup_file/mybackup_20250910.xb

这个时候解压的文件是这样的,还需要下面的解压缩

 

[root@localhost data]# ls -al
total 1476
drwxr-xr-x  6 root  root     4096 Sep 10 09:52 .
drwxr-xr-x 15 mysql mysql    4096 Sep 10 09:48 ..
-rw-r-----  1 root  root      301 Sep 10 09:52 backup-my.cnf.zst
-rw-r-----  1 root  root      133 Sep 10 09:52 binlog.000012.zst
-rw-r-----  1 root  root       56 Sep 10 09:52 binlog.index.zst
drwxr-x---  2 root  root       29 Sep 10 09:52 db_test
-rw-r-----  1 root  root      526 Sep 10 09:52 ib_buffer_pool.zst
-rw-r-----  1 root  root     3952 Sep 10 09:52 ibdata1.zst
drwxr-x---  2 root  root      167 Sep 10 09:52 mysql
-rw-r-----  1 root  root  1359236 Sep 10 09:52 mysql.ibd.zst
drwxr-x---  2 root  root     8192 Sep 10 09:52 performance_schema
drwxr-x---  2 root  root       32 Sep 10 09:52 sys
-rw-r-----  1 root  root    43774 Sep 10 09:52 undo_001.zst
-rw-r-----  1 root  root    42807 Sep 10 09:52 undo_002.zst
-rw-r-----  1 root  root       72 Sep 10 09:52 xtrabackup_binlog_info.zst
-rw-r-----  1 root  root      134 Sep 10 09:52 xtrabackup_checkpoints
-rw-r-----  1 root  root      434 Sep 10 09:52 xtrabackup_info.zst
-rw-r-----  1 root  root      327 Sep 10 09:52 xtrabackup_logfile.zst
-rw-r-----  1 root  root       52 Sep 10 09:52 xtrabackup_tablespaces.zst

 

常规解压
/opt/xtrabackup-8.0.35-34/bin/xtrabackup --decompress --parallel=4 --remove-original --target-dir=/home/middle/mysql8/data

这个时候解压后的文件如下

[root@localhost data]# ls -al
total 70712
drwxr-xr-x  6 root  root      4096 Sep 10 09:53 .
drwxr-xr-x 15 mysql mysql     4096 Sep 10 09:48 ..
-rw-r--r--  1 root  root       447 Sep 10 09:53 backup-my.cnf
-rw-r--r--  1 root  root       197 Sep 10 09:53 binlog.000012
-rw-r--r--  1 root  root        50 Sep 10 09:53 binlog.index
drwxr-x---  2 root  root        25 Sep 10 09:53 db_test
-rw-r--r--  1 root  root      3599 Sep 10 09:53 ib_buffer_pool
-rw-r--r--  1 root  root  12582912 Sep 10 09:53 ibdata1
drwxr-x---  2 root  root       143 Sep 10 09:53 mysql
-rw-r--r--  1 root  root  26214400 Sep 10 09:53 mysql.ibd
drwxr-x---  2 root  root      8192 Sep 10 09:53 performance_schema
drwxr-x---  2 root  root        28 Sep 10 09:53 sys
-rw-r--r--  1 root  root  16777216 Sep 10 09:53 undo_001
-rw-r--r--  1 root  root  16777216 Sep 10 09:53 undo_002
-rw-r--r--  1 root  root        59 Sep 10 09:53 xtrabackup_binlog_info
-rw-r-----  1 root  root       134 Sep 10 09:52 xtrabackup_checkpoints
-rw-r--r--  1 root  root       696 Sep 10 09:53 xtrabackup_info
-rw-r--r--  1 root  root      2560 Sep 10 09:53 xtrabackup_logfile
-rw-r--r--  1 root  root        39 Sep 10 09:53 xtrabackup_tablespaces

 

恢复
/opt/xtrabackup-8.0.35-34/bin/xtrabackup --defaults-file=/home/middle/mysql8/conf/my.cnf --user=root --prepare --target-dir=/home/middle/mysql8/data

 

这里--prepare类似2.4版本的innobackupex --apply-log 

 

 

修改权限
[root@localhost middle]# cd /home/middle/
[root@localhost middle]# chown -R mysql:mysql ./mysql8/

 

启动
systemctl start mysql
或是
/home/middle/mysql8/bin/mysqld_safe --defaults-file=/home/middle/mysql8/conf/my.cnf --user=mysql &

 

登录查看
/home/middle/mysql8/bin/mysql -h localhost -uroot -P13306 -pmysql