目录
xtrabackup是MySQL的一种物理备份工具,相对于mysqldump,备份和还原速度更快。
一、安装 XtraBackup
MySQL 8.0版本
# CentOS 7
wget https://repo.percona.com/yum/percona-release-latest.noarch.rpm
sudo rpm -ivh percona-release-latest.noarch.rpm
sudo percona-release enable-only tools release
yum install -y percona-xtrabackup-80
# CentOS 8
dnf install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm
percona-release setup -y ps80
dnf install -y percona-xtrabackup-80
# Ubuntu
wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb
sudo dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb
sudo percona-release enable-only tools release
sudo apt update
sudo apt install -y percona-xtrabackup-80
MySQL 5.7版本
# CentOS 7
wget https://repo.percona.com/yum/percona-release-latest.noarch.rpm
sudo rpm -ivh percona-release-latest.noarch.rpm
sudo percona-release enable tools release
sudo yum install percona-xtrabackup-24
# CentOS 8
sudo dnf install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm
sudo percona-release setup tools
sudo dnf install percona-xtrabackup-24
# Ubuntu
wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb
sudo dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb
sudo percona-release enable tools release
sudo apt update
sudo apt install percona-xtrabackup-24
注意:
Percona XtraBackup 8.0 版本适合 MySQL 8.0
Percona XtraBackup 2.4 版本适合 MySQL 5.7
二、备份 MySQL 数据库
1.创建备份目录
sudo mkdir -p /data/backups/mysql
sudo chown -R mysql:mysql /data/backups
2.执行备份
(1)全量备份
# MySQL 5.7
xtrabackup --user=backupuser --password=password \
--host=localhost --backup --target-dir=/data/backups/mysql/full_$(date +%F)
# MySQL 8.0(需添加 --no-timestamp 参数)
xtrabackup --user=backupuser --password=password \
--host=localhost --backup --target-dir=/data/backups/mysql/full_$(date +%F) \
--no-timestamp
(2)增量备份(可选)
# 首次全量备份
xtrabackup --backup --target-dir=/data/backups/full
# 后续增量备份
xtrabackup --backup --target-dir=/data/backups/incremental_1 \
--incremental-basedir=/data/backups/full
xtrabackup --backup --target-dir=/data/backups/incremental_2 \
--incremental-basedir=/data/backups/incremental_1
三、恢复 MySQL 数据库
1.停止 MySQL 服务
# CentOS 7
sudo systemctl stop mysqld
# CentOS 8/Ubuntu
sudo systemctl stop mysql
2.执行恢复操作
(1)清空数据目录
sudo rm -rf /var/lib/mysql/*
(2)全量备份还原
# 注意:恢复时需使用 --copy-back 参数
xtrabackup --prepare --target-dir=/data/backups/mysql/full_$(date +%F)
xtrabackup --copy-back --target-dir=/data/backups/mysql/full_$(date +%F)
(3)增量备份还原(可选)
# 有增量备份的还原,最后一次还原不需要加选项--apply-log-only,假设存在两个增量备份:incremental_1 和 incremental_2,按顺序应用增量备份
# 准备全量备份(不生成回滚点)
xtrabackup --prepare --apply-log-only --target-dir=/data/backups/full
# 应用第一个增量备份
xtrabackup --prepare --apply-log-only \
--target-dir=/data/backups/full \
--incremental-dir=/data/backups/incremental_1
# 应用第二个增量备份
xtrabackup --prepare --apply-log-only \
--target-dir=/data/backups/full \
--incremental-dir=/data/backups/incremental_2
# 最终准备(生成回滚点)
xtrabackup --prepare --target-dir=/data/backups/full
# 还原最终回滚点
xtrabackup --copy-back --target-dir=/data/backups/mysql/full
关键参数说明
1.--apply-log-only
防止在应用增量日志后自动生成回滚点,确保可以继续应用后续增量备份
2.--incremental-dir
指定增量备份文件所在目录
3.最终 prepare 不使用 --apply-log-only
生成最终回滚点,使备份数据处于一致性状态
3.修复权限
sudo chown -R mysql:mysql /var/lib/mysql
4.启动 MySQL 服务
# CentOS 7
sudo systemctl start mysqld
# CentOS 8/Ubuntu
sudo systemctl start mysql
注意:
1.MySQL 8.0 特殊配置
需添加 --no-timestamp 参数避免创建时间戳目录
可能需要调整 validate_password 插件配置2.备份用户需具备 RELOAD, LOCK TABLES, REPLICATION CLIENT 权限
CREATE USER 'backupuser'@'localhost' IDENTIFIED BY 'password'; GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT, PROCESS ON *.* TO 'backupuser'@'localhost'; FLUSH PRIVILEGES;