MySQL8采用xtrabackup8备份与恢复方案

一，环境介绍

1，主机信息

主机系统：Centos7，数据库：mysql8，IP： 192.168.1.240 ，备份目录： /data/backup
备用系统：openeuler 2203，数据库：mysql8，IP： 192.168.1.33， 备份目录： /data/backup

2，方案简介

生产环境使用 192.168.1.240 主机，将数据库的备份文件同步到 备份机上，生产环境有问题后，可以在备用机上紧急恢复数据库。

二，备份还原操作。

1，安装MySQL8数据库

两台主机安装mysql8尽量采用相同参考文档进行安装。这里不再详解。

2，安装xtrabackup8软件

#数据库主机Centos7安装xtrabackup8软件
[root@wx]$ yum -y install openssl-devel libaio libaio-devel automake autoconf bison libtool ncurses-devel libgcrypt-devel libev-devel libcurl-devel zlib-devel vim-common libcurl-devel libudev-devel

[root@wx]$ yum -y install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
[root@wx]$ yum -y install percona-xtrabackup-80

#数据库备机openeuler2203系统安装xtrabackup软件
[root@os33]$ wget https://downloads.percona.com/downloads/Percona-XtraBackup-8.0/Percona-XtraBackup-8.0.31-24/binary/tarball/percona-xtrabackup-8.0.31-24-Linux-x86_64.glibc2.17-minimal.tar.gz
#官网有不同的版本,这里采用最小软件包版本
[root@os33]$ tar -zxvf percona-xtrabackup-8.0.31-24-Linux-x86_64.glibc2.17-minimal.tar.gz
[root@os33]$ mv percona-xtrabackup-8.0.31-24-Linux-x86_64.glibc2.17-minimal /data/xtrabackup8
#配置软件执行文件的变量
[root@os33]$ echo "PATH=/data/xtrabackup8:\$PATH" > /etc/profile.d/xtrabackup.sh

#重新进入系统
# 执行命令查看软件安装版本
[root@os33]# xtrabackup --version
xtrabackup version 8.0.31-24 based on MySQL server 8.0.31 Linux (x86_64) (revision id: f0754edb)

3，备份mysql8

#第一次备份采用全备份
#配置备份账号与权限,当然可以直接使用root账号

CREATE USER 'xtrabk'@'127.0.0.1' IDENTIFIED BY 'Xtrabk#123';
ALTER USER 'xtrabk'@'127.0.0.1' IDENTIFIED BY 'Xtrabk#123';
GRANT BACKUP_ADMIN, PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'xtrabk'@'127.0.0.1';
GRANT SELECT ON performance_schema.log_status TO 'xtrabk'@'127.0.0.1';
FLUSH PRIVILEGES;

[root@os33]# mysql_config_editor set --login-path=xtrlogin -uxtrabk -h127.0.0.1 -P3306 -p
#或是以下命令
[root@os33]# mysql_config_editor set --login-path=xtrlogin --user=xtrabk --host=127.0.0.1 --port=3306 --password
#上面命令回车后,需要输入密码
[root@os33]# mysql_config_editor print --login-path=xtrlogin
[root@os33]# mysql_config_editor reset
#打印与重置登陆信息

[root@os33]# mkdir /data/backup

# 首次采用全备份
[root@os33]# xtrabackup -uxtrabk -pXtrabk#123 -S /tmp/mysql.sock --backup --target-dir=/data/backup/base

# 首次采用全备份
[root@os33]# xtrabackup --login-path=xtrlogin --backup -S /tmp/mysql.sock --target-dir=/data/backup/base
#也可以使用以上命令全备,这样不用输密码

# 基于全备的首次差异备份
[root@os33]# xtrabackup -uxtrabk -pXtrabk#123 -S /tmp/mysql.sock --backup --target-dir=/data/backup/inc1 --incremental-basedir=/data/backup/base

# 进行第二次增量备份
[root@os33]# xtrabackup -uxtrabk -pXtrabk#123 -S /tmp/mysql.sock --backup --target-dir=/data/backup/inc2 --incremental-basedir=/data/backup/inc1

# 备份过程生成三个备份目录，/data/backup/{base，inc1，inc2}

[root@os33]# xtrabackup -uxtrabk -pXtrabk#123 -S /tmp/mysql.sock --databases="phoenix" --backup --target-dir=/data/backup/phoenix

4，将备份文件传到备机

#可以使用SCP命令
#备机的目录结构最好也是一样的。
scp -r root@192.168.1.240:/data/backup/* /data/backup

5，还原过程

# 关闭备机的mysql服务
# 清空data目录
# 预准备完成备份，此选项--apply-log-only 阻止回滚未完成的事务
[root@os33]# xtrabackup --prepare --apply-log-only --target-dir=/data/backup/base

# 合并第1次增量备份到完全备份
[root@os33]# xtrabackup --prepare --apply-log-only --target-dir=/data/backup/base --incremental-dir=/data/backup/inc1

# 合并第2次增量备份到完全备份：最后一次还原不需要加选项--apply-log-only
[root@os33]# xtrabackup --prepare --target-dir=/data/backup/base --incremental-dir=/data/backup/inc2

# 还原数据库,当然也可以使用mv命令
[root@os33]# xtrabackup --defaults-file=/data/mysql8/my.cnf --copy-back --target-dir=/data/backup/base

[root@os33]# chown -R mysql:mysql /data/mysql8/data
# 还原文件权限

6，启动mysql服务

[root@os33]# /etc/init.d/mysqld restart

#根据日志与报错,修改mysql的配置文件

三，自动备份脚本。

（1）第一种备份方案

周一进行全备，周二至周六进行根据前一天的备份数据进行增量备份。

1，xtrabackup8备份脚本

以下是示例脚本：

#!/bin/bash

#定义备份目录
BASE_BACKUP_DIR=/data/backup

#定义日期格式
BAKDATE=$(date +%F)
WEEKDAY=$(date +%u)
INC_DAY=$(date -d "1 day ago" +%Y-%m-%d)
RMDAY=$(date -d "15 day ago" +%Y-%m-%d)

#定义全备目录和增量备份目录
BACKUP_DIR=${BASE_BACKUP_DIR}/${BAKDATE}
INCR_BASEDIR=${BASE_BACKUP_DIR}/${INC_DAY}
RMDIR=${BASE_BACKUP_DIR}/${RMDAY}

#进行全量或增量备份
if [ $WEEKDAY -eq 1 ]; then

mkdir -p ${BACKUP_DIR}
echo "====================================="
echo "$(date) : 成功创建全备份目录${BACKUP_DIR}."
#进行全备
# xtrabackup --backup --target-dir=${BACKUP_DIR} --user=root --password=123456
xtrabackup --defaults-file=/data/mysql8/my.cnf --login-path=xtrlogin --backup --compress=lz4 -S /tmp/mysql.sock --target-dir=${BACKUP_DIR}
#此命令不用在脚本中明确账号与密码
if [ $? -eq 0 ]; then
echo "====================================="
echo "$(date) : 全量备份命令执行成功."
#传输备份文件到备机指定目录
rsync -avz --delete ${BACKUP_DIR} root@192.168.1.252:/ssd/mysqlbak
echo "====================================="
echo "$(date) : 全量备份目录文件传输到备机执行成功."
else
echo "====================================="
echo "$(date) : 全量备份failed,请注意检查日志."
fi

rm -rf ${RMDIR}
echo "====================================="
echo "$(date) : 删除15天前备份目录."
else

mkdir -p ${BACKUP_DIR}
echo "====================================="
echo "$(date) : 成功创建差异备份目录${BACKUP_DIR}."
#进行增量备份
xtrabackup --defaults-file=/data/mysql8/my.cnf --login-path=xtrlogin --backup --compress=lz4 -S /tmp/mysql.sock --incremental-basedir=${INCR_BASEDIR} --target-dir=${BACKUP_DIR}

if [ $? -eq 0 ]; then
echo "====================================="
echo "$(date) : 差异备份命令执行成功."
#传输备份文件到备机指定目录
rsync -avz --delete ${BACKUP_DIR} root@192.168.1.252:/ssd/mysqlbak
echo "====================================="
echo "$(date) : 差异备份目录文件传输到备机执行成功."
else
echo "====================================="
echo "$(date) : 差异备份failed,请注意检查日志."
fi

rm -rf ${RMDIR}
echo "====================================="
echo "$(date) : 删除15天前备份目录."
fi

备份按日期创建目录，可以创建定时任务，第一次使用时，如不是周一，需要先创建一个全量备份。

（2）第一种备份方案

周一进行全备，周二至周六进行根据本周一的备份数据进行增量备份。

1，xtrabackup8备份脚本

以下是示例脚本：

#!/bin/bash

# Set the backup directory
BACKUP_DIR=/path/to/backup/directory

# Set the MySQL user and password
MYSQL_USER=username
MYSQL_PASSWORD=password

# Set the MySQL host and port
MYSQL_HOST=localhost
MYSQL_PORT=3306

# Set the date format
DATE=$(date +%Y-%m-%d_%H-%M-%S)

# Create the backup directory if it doesn't exist
mkdir -p $BACKUP_DIR

# Run the full backup on Monday
if [ $(date +%u) -eq 1 ]; then
xtrabackup --backup --user=$MYSQL_USER --password=$MYSQL_PASSWORD --host=$MYSQL_HOST --port=$MYSQL_PORT --target-dir=$BACKUP_DIR/full_$DATE
else
# Run the incremental backup on other days
xtrabackup --backup --user=$MYSQL_USER --password=$MYSQL_PASSWORD --host=$MYSQL_HOST --port=$MYSQL_PORT --target-dir=$BACKUP_DIR/inc_$DATE --incremental-basedir=$BACKUP_DIR/full_$(date +%Y-%m-%d_%H-%M-%S --date='last Monday')
fi

使用脚本前，需要创建mysql_config_editer账号信息

方法如下：

[root@os-240 backup]# mysql_config_editor reset
[root@os-240 backup]# mysql_config_editor print --login-path=xtrlogin
[root@os-240 backup]# mysql_config_editor set --login-path=xtrlogin --user=xtrabk --host=127.0.0.1 --port=3306 --password
Enter password:
[root@os-240 backup]# mysql_config_editor print --login-path=xtrlogin
[xtrlogin]
user = "xtrabk"
password = *****
host = "127.0.0.1"
port = 3306

请根据您自己的情况进行修改。