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
修改权限
[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