这一节内容，我们来详细介绍一下MySQL物理备份工具Xtrabackup的用法，在文章最后，我们会讲一下，使用怎样的提示语，能让ChatGPT帮我们生成一个能直接使用的物理备份脚本。
1 实验准备
1.1 下载Xtrabackup
可以进到下面链接，选择跟自己操作系统和MySQL版本对应的Xtrabackup版本。
https://www.percona.com/downloads
比如我的是8.0.25，可以执行
wget https://downloads.percona.com/downloads/Percona-XtraBackup-8.0/Percona-XtraBackup-8.0.25-17/binary/redhat/7/x86_64/percona-xtrabackup-80-8.0.25-17.1.el7.x86_64.rpm
安装，需要到备份和恢复的机器上都进行安装。
yum install percona-xtrabackup-80-8.0.25-17.1.el7.x86_64.rpm -y
1.2 创建备份和恢复用户
create user 'u_xtrabackup'@'localhost' identified WITH mysql_native_password BY 'da7gdcG_a';grant select,reload,lock tables,backup_admin,replication client,create tablespace,process,super on *.* to 'u_xtrabackup'@'localhost';
select，在备份时需要查询MySQL的一些元数据； reload，执行flush engine logs 需要reload权限； lock tables，备份过程会加备份锁，需要lock tables 权限； backup_admin，查询performance_schema.log_status需要这个权限，另外执行LOCK INSTANCE FOR BACKUP, LOCK BINLOG FOR BACKUP, 或者 LOCK TABLES FOR BACKUP都需要backup_admin权限； replication client，获取二进制位点需要这个权限； create tablespace，导入表需要这个权限； process，执行show engine innodb status需要这个权限； super，执行flush tables with read lock。
mkdir data/backup/xtrabackup_bak
2 常规备份恢复操作
2.1 执行备份操作
xtrabackup --defaults-file=/data/mysql/conf/my.cnf -uu_xtrabackup -p'da7gdcG_a' --backup --target-dir=/data/backup/xtrabackup_bak/alldata
其中，备份文件夹中除了库名，还包括这些文件：
的作用如下：
backup-my.cnf，备份命令中用到的配置选项信息
ib_buffer_pool，存储缓冲池的表空间ID和页面ID，方便更快预热
ibdata，系统表空间文件
mysql-bin.000067，备份时，MySQL使用的Binlog文件
mysql-bin.index，binlog信息
mysql.ibd，表空间文件
undo_001，undo log
undo_002，undo log
xtrabackup_binlog_info，服务器使用的二进制日志文件及其在备份时的位置
xtrabackup_checkpoints，备份的类型，状态，LSN范围，用于增量备份
xtrabackup_info，备份信息，包括备份的命令，开始和结束时间，Binlog位点和GTID信息，lsn信息等
xtrabackup_logfile，运行所需要的日志数据
xtrabackup_tablespaces，表空间文件
2.2 把备份传输到目标机器
把数据传输到要恢复数据的机器
scp -r data/backup/xtrabackup_bak/alldata 192.168.12.162:/data/backup
2.3 执行prepare
Xtrabackup在恢复之前，需要先执行prepare，也就是模拟MySQL进行崩溃恢复，将Redo Log回放到数据文件中
我们来执行一下
cd /data/backup/alldata/xtrabackup --prepare --target-dir=./
2.4 清空目标实例
使用xtrabackup恢复时，需要把目标库的数据全清空，再执行恢复
所以我们先来停目标库的MySQL
/etc/init.d/mysql.server stop
2.5 清空目标库的data目录和binlog 目录
cd data/mysql/mkdir data/mysql_bakmv data data/mysql_bak/mv binlog /data/mysql_bak/
2.6 恢复数据并启动MySQL
先来恢复数据
cd /data/backup/alldataxtrabackup --defaults-file=/data/mysql/conf/my.cnf --copy-back --target-dir=./
--copy-back 表示把备份的数据文件复制到MySQL的数据目录，当然，也可以使用--move-back，表示把备份的数据文件移动到MySQL的数据目录
进入到MySQL文件夹
cd data/mysql
ll 看一下，是不是几个移走的文件夹已经重新创建了
这里，要注意的是，新创建的文件夹属主都是root，我们需要改成MySQL。
执行
chown -R mysql.mysql ./*
再启动MySQL
/etc/init.d/mysql.server start
2.7 确定数据
登录MySQL，用户名和密码，就是我们备份实例的用户名和密码
mysql -uroot -p
进到数据库里确定数据：
use bak1;show tables;select * from t1;
3 用compress进行压缩备份和恢复
刚才讲的是不对备份文件进行压缩的方式，我们再来讲一下备份时使用compress压缩，应该怎么操作，并且后续恢复又应该怎样操作。
3.1 备份所有数据
xtrabackup --defaults-file=/data/mysql/conf/my.cnf -uu_xtrabackup -p'da7gdcG_a' --backup --compress --target-dir=/data/backup/xtrabackup_bak/alldata_compress
把备份传输到要恢复数据的机器
scp -r data/backup/xtrabackup_bak/alldata_compress 192.168.12.162:/data/backup
3.2 解压备份文件
先安装qpress
wget https://github.com/PierreLvx/qpress/archive/refs/tags/20230507.zipunzip qpress-20230507.zipcd qpress-20230507make && make install
解压
xtrabackup --decompress --target-dir=/data/backup/alldata_compress/
3.3 数据恢复
执行prepare
xtrabackup --prepare --target-dir=/data/backup/alldata_compress/
停目标库的MySQL
/etc/init.d/mysql.server stop
清空目标库的data目录和binlog 目录
cd /data/mysql/rm data binlog -rf
恢复数据
xtrabackup --defaults-file=/data/mysql/conf/my.cnf --copy-back --target-dir=/data/backup/alldata_compress
3.4 启动MySQL
修改属主
chown -R mysql.mysql data/mysql/
启动MySQL
/etc/init.d/mysql.server start
4 用xbstream进行压缩备份和恢复
4.1 在源实例进行全量备份
cd data/backupxtrabackup --defaults-file=/data/mysql/conf/my.cnf -uu_xtrabackup -p'Ijnbgt@123' --backup --stream=xbstream >/data/backup/xtrabackup.xbstream
把全备传到要恢复数据的机器
scp xtrabackup.xbstream 192.168.12.162:/data/backup/recover
4.2 解压备份文件
cd /data/backup/recoverxbstream -x < xtrabackup.xbstream
4.3 数据恢复
关闭目标机器上的MySQL实例。
清空目标机器上MySQL实例的数据目录和Binlog目录。
rm data/mysql/data/* -rfrm data/mysql/binlog/* -rf
并把全备恢复到目标实例上的MySQL中
xtrabackup --prepare --target-dir=./xtrabackup --defaults-file=/data/mysql/conf/my.cnf --copy-back --target-dir=./
4.4 启动MySQL
修改属主
chown -R mysql.mysql data/mysql/
启动MySQL
/etc/init.d/mysql.server start
5 通过ChatGPT编写自动化备份脚本
再通过ChatGPT来编写备份脚本
Prompt如下：
通过xtrabackup备份数据的命令如下：
xtrabackup --defaults-file=/data/mysql/conf/my.cnf -uu_backup -p'Ujg8G_aUU' --backup --stream=xbstream >/data/backup/xtrabackup.xbstream
请帮我写一个MySQL物理备份脚本，每天0点执行备份，
用户名和密码使用上面命令中的，并且使用变量形式，
备份文件夹的名字以bak_开头，包括时间戳，精确年月日时分秒
并且通过find 的形式删除10天之前的备份目录
最终生成的备份脚本如下：
#!/bin/bash# MySQL连接信息MYSQL_USER="u_backup"MYSQL_PASSWORD="Ujg8G_aUU"# 备份目录BACKUP_DIR="/data/backup"# 备份文件夹名字BACKUP_FOLDER="bak_$(date +%Y%m%d%H%M%S)"# 备份文件夹路径BACKUP_PATH="$BACKUP_DIR/$BACKUP_FOLDER"# 创建备份文件夹mkdir "$BACKUP_PATH"# 执行备份命令xtrabackup --defaults-file=/data/mysql/conf/my.cnf -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" --backup --stream=xbstream > "$BACKUP_PATH/xtrabackup.xbstream"# 删除10天之前的备份目录find "$BACKUP_DIR" -type d -name "bak_*" -mtime +10 -exec rm -rf {} \;
当然，在使用ChatGPT生成的脚本之前，需要先检查一下，防止误删除了有用的数据。
最后，跟大家推荐一下小编在慕课网刚上线的DBA体系课，（链接：https://class.imooc.com/sale/dba）
除了DBA需要掌握的知识体系外，还包括数据库自动化平台开发、ChatGPT帮助DBA提升工作效率、数据库企业级实战项目（如下图）。
可以点击阅读原文跳转到课程页面。
最后，关于课程，有任何疑问，可以加下方助手咨询；
当然，如果想进MySQL交流群，也可以添加助手微信，备注“进群”即可。
