Mysql的备份与恢复全过程(使用XtraBackup)_Mysql

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;