MySQL8.0备份与还原工具XtraBackup

关闭防火墙

systemctl stop firewalld.service
systemctl disable firewalld.service

安装MySQL 8.0.27

后面会用到

yum install openssl-devel
rpm -e mariadb-libs --nodeps
yum install -y perl-Module-Install.noarch

wget https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.27-1.el7.x86_64.rpm-bundle.tar
tar -xvf mysql-8.0.27-1.el8.x86_64.rpm-bundle.tar

解压以后，全部文件需要安装

rpm -ivh mysql-community-common-8.0.27-1.el8.x86_64.rpm
rpm -ivh mysql-community-client-plugins-8.0.27-1.el8.x86_64.rpm
rpm -ivh mysql-community-libs-8.0.27-1.el8.x86_64.rpm
rpm -ivh mysql-community-client-8.0.27-1.el8.x86_64.rpm
rpm -ivh libaio-0.3.112-1.el8.x86_64.rpm
rpm -ivh mysql-community-server-8.0.27-1.el8.x86_64.rpm

配置初始化账号密码

启动mysql服务

systemctl start mysqld

初始化密码

sudo grep 'temporary password' /var/log/mysqld.log

修改密码 上面出现 password

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Node@123';

允许远程访问

CREATE USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'Node@123';

授权 如果不行那么先update host 然后再执行

GRANT ALL ON . TO 'root'@'%' WITH GRANT OPTION; FLUSH PRIVILEGES;

退出msyql shell

quit

重启

systemctl restart mysqld

安装XtraBackup for Centos

$ sudo yum install
https://repo.percona.com/yum/percona-release-latest.
noarch.rpm

打开仓库

$ sudo percona-release enable-only tools release

工具集

sudo percona-release enable-only tools

yum install percona-xtrabackup-80

创建备份文件夹

mkdir /backup

执行完全备份

xtrabackup -uroot -p --backup --target-dir=/backup/base

xtrabackup --user=root --password=Node@123 --backup --target-dir=/backup/full

全备还原:

停掉mysql 服务器

systemctl stop mysqld.service

清理mysql 文件夹

rm -rf /var/lib/mysql/*

一致性检查

xtrabackup --prepare --target-dir=/backup/base

执行上面一句可以再执行这句

xtrabackup --copy-back --target-dir=/backup/base

修改文件及文件夹属主

chown -R mysql.mysql /var/lib/mysql

启动数据库

systemctl start mysqld

增量备份

做基础的全备

xtrabackup -uroot -p --backup --target-dir=/backup/new

全备后做一次增量备份

xtrabackup -uroot -p--backup --target-dir=/backup/inc1 --incremental-basedir=/backup/new

数据变更后 做第二次增量备份

xtrabackup -uroot -p--backup --target-dir=/backup/inc1 --incremental-basedir=/backup/new

复制到远程机器

所有的文件需要检查一致性

xtrabackup --prepare --apply-log-only --target-dir=/backup/new
xtrabackup --prepare --apply-log-only --target-dir=/backup/new --incremental-dir=/backup/inc1
xtrabackup --prepare --target-dir=/backup/new --incremental-dir=/backup/inc2

做数据恢复

xtrabackup --copy-back --target-dir=/backup/new

权限修改

chown -R mysql.mysql /var/lib/mysql

启动服务检查数据

systemctl start mysqld

不同机器直接备份

备份到远程机器

scp -r /backup/base root@192.168.10.105:/backup

保证远程机器的数据库配置一致，直接在远程机器执行还原

自动执行脚本

!/bin/bash

#################################################

Version : v1.0.0

Author : Allen Wu

Time : 2017-11-28

Auto HotBackup For MySQL #

#################################################

autohotbackup tools

备份根目录

BackupRoot=/backup

全量备份根目录

FullBackupRoot=${BackupRoot}/full

增量备份根目录

IncrementalBackupRoot=${BackupRoot}/incre

xtrabackup 备份时log

Log=/data/log/autohotbackup_$(date +%Y-%m-%d).log

现在星期几

Week=$(date +%w)
Hour=$(date +%H)

全量备份时间，按照星期定义,周一到周日依次1,2,3,4,5,6,7

FullBackupWeek=(1 2 3 4 5)

是否开启binlog备份,ON / OFF

BinlogBackup=ON

全量备份时间,

例子, 周一 10-11 点响应 ，周二 15-16 点

FullBackupTime=(1:10 2:15)

FullBackupTime=(1:04 2:04 3:04 4:04 5:04 6:04 0:04)

增量备份时间,小时

IncrementalBackupIntervalTime=2

过期时间，超过这个时间将删除,天

DeadLine=4

MySQL信息

readonly MysqlAddress="localhost"
readonly MysqlPort="3306"
readonly MysqlUser="root"
readonly MysqlPassword="Node@123"
readonly MysqlSocket=
readonly MysqlConfigFile=/etc/my.cnf

Innobackupex使用内存

readonly UseMemory=1000M

Innobackupex其他选项

readonly InnobackupexConfigure="--backup"

SCP，传输已备份文件到备份服务器

readonly BackupServerAddress=192.168.10.105
readonly BackupServerPort=22
readonly BackupServerUser=root
readonly BackupServerDir=/backup

模块开关

备份文件到另一台服务器,ON/on代表打开，OFF/off代表关闭

readonly IncrementalBackup=ON
readonly TarZip=ON
readonly ScpFile=ON
readonly BinlogBackup=OFF
readonly DeleteFile=ON

pigz 多线程压缩工具是否启用，可能会消耗更多的CPU资源

readonly UsePigz=YES

bin Path

readonly INNOBACKUPEX=$(which xtrabackup 2>/dev/null || echo "xtrabackup")
readonly MYSQLADMIN=$(which mysqladmin 2>/dev/null || echo "mysqladmin")
readonly MYSQL=$(which mysql 2>/dev/null || echo "mysql")
readonly MKDIR=$(which mkdir 2>/dev/null || echo "mkdir")
readonly FIND=$(which find 2>/dev/null || echo "find")
readonly TAR=$(which tar 2>/dev/null || echo "tar")
readonly SCP=$(which scp 2>/dev/null || echo "scp")
readonly PIGZ=$(which pigz 2>/dev/null || echo "pigz")

echo "*************** $(date +%c): Xtrabackup 进行MySQL热备份 ***************"

echo " "

主函数

function Main_Fun() {
echo " "
echo "*************** $(date +%c): Xtrabackup 进行MySQL热备份 ***************"
echo ""
Delete_File
Backup_Policy
Tar_Gzip
Scp_File
#Delete_File
}

判断mysql可以连接

function Mysql_Alive() {

local TestConnectMySQL=$(mysqladmin -h"${MysqlAddress}" -P"${MysqlPort}" -u"${MysqlUser}" -p"${MysqlPassword}" ping 2>/dev/null | grep -c alive)
    if [ "${TestConnectMySQL}" = 1 ];then
            echo "连接 MySQL($MysqlAddress) 成功,开始备份 !"
    else
            echo "连接 MySQL($MysqlAddress) 失败,退出"
            exit 1
    fi

}

全量备份

function Full_Backup() {
#判断备份目录是否存在
echo "*** 进行全量备份 ***"
[ -d ${FullBackupRoot} ] || ${MKDIR} ${FullBackupRoot} -p
#[ $? == 0 ] || echo "创建 ${BackupRoot} 失败,退出! " ; exit 1
if [ $? != 0 ];then
echo "创建 ${FullBackupRoot} 失败,退出! "
exit 1
fi

#判断mysql可活可连接
Mysql_Alive
#进行备份命令
${INNOBACKUPEX}  --user="${MysqlUser}" --password="${MysqlPassword}" "${InnobackupexConfigure}" --target-dir="${FullBackupRoot}"
#进行判断
[ $? == 0 ] && echo "全量备份成功" || echo "全量备份失败!"
BackupType="FULL"

}

基于全量进行增量备份

function Base_Full_Backup() {
if [ "${IncrementalBackup}" == "ON" -o "${IncrementalBackup}" == "on" ];then
echo "*** 尝试基于全量增量备份 "
elif [ "${IncrementalBackup}" == "OFF" -o "${IncrementalBackup}" == "off" ];then
echo " 增量备份开关已关闭 "
return
else
echo "增量备份开关配置错误,错误配置 IncrementalBackup= ${IncrementalBackup} ! "
return
fi
#echo " 尝试基于全量增量备份 ***"
#获取上次全量备份目录名,根据全名查找，如果找不到，则破例进行全量备份
local LatestFullBackup=$(${FIND} ${FullBackupRoot} -mindepth 1 -maxdepth 1 -type d -printf "%P\n" 2>/dev/null | sort -nr | head -1)
if [ ! "${LatestFullBackup}" ];then
echo "在 ${FullBackupRoot} 下面没有找到全量备份,将进行全量备份 !"
sleep 2
Full_Backup
return
#exit
fi
local LatestFullBackupRoot=${FullBackupRoot}/${LatestFullBackup}
local IncrementalBasedirRoot=${LatestFullBackupRoot}
#判断mysql可活可连接
Mysql_Alive
echo "本次(基于全量)--incremental-basedir= ${IncrementalBasedirRoot}"
echo " "
sleep 3
#判断增量备份目录是否存在
[ -d ${IncrementalBackupRoot} ] || ${MKDIR} ${IncrementalBackupRoot} -p
#判断mysql可活可连接
Mysql_Alive
#进行备份操作
${INNOBACKUPEX} --defaults-file="${MysqlConfigFile}" --use-memory="${UseMemory}" --host="${MysqlAddress}" --user="${MysqlUser}" --password="${MysqlPassword}" "${InnobackupexConfigure}" --incremental --incremental-basedir="${IncrementalBasedirRoot}" ${IncrementalBackupRoot}
#判断是否成功备份
[ $? == 0 ] && echo "基于 ${IncrementalBasedirRoot} 全量的增量备份成功 " || echo "基于 ${IncrementalBasedirRoot} 全量的增量备份失败!"
BackupType="INCREMENTAL"
}

基于增量进行增量备份

function Base_Incremental_Backup() {
if [ "${IncrementalBackup}" == "ON" -o "${IncrementalBackup}" == "on" ];then
echo "*** 尝试基于全量增量备份 "
elif [ "${IncrementalBackup}" == "OFF" -o "${IncrementalBackup}" == "off" ];then
echo " 增量备份开关已关闭 "
return
else
echo "增量备份开关配置错误,错误配置 IncrementalBackup= ${IncrementalBackup} ! "
return
fi
#echo " 尝试基于增量的增量备份 ***"
local LatestIncrementalBackup=$(${FIND} ${IncrementalBackupRoot} -mindepth 1 -maxdepth 1 -type d -printf "%P\n" 2>/dev/null | sort -nr | head -1)
if [ ! "$LatestIncrementalBackup" ];then
echo "在 ${IncrementalBackupRoot} 没找到增量备份,将运行基于全量备份模块 !"
sleep 2
Base_Full_Backup
return
#exit
fi
local LatestIncrementalBackupRoot=${IncrementalBackupRoot}/${LatestIncrementalBackup}
local IncrementalBasedirRoot=${LatestIncrementalBackupRoot}
#判断mysql可活可连接
Mysql_Alive
#
echo "本次(基于增量)--incremental-basedir= ${IncrementalBasedirRoot} "
echo " "
sleep 3
#开始操作备份
${INNOBACKUPEX} --defaults-file="${MysqlConfigFile}" --use-memory="${UseMemory}" --host="${MysqlAddress}" --user="${MysqlUser}" --password="${MysqlPassword}" "${InnobackupexConfigure}" --incremental --incremental-basedir="${IncrementalBasedirRoot}" ${IncrementalBackupRoot}
#判断是否成功备份
[ $? == 0 ] && echo "基于 ${IncrementalBasedirRoot} 增量的增量备份成功 " || echo "基于 ${IncrementalBasedirRoot} 增量的增量备份失败!"
BackupType="INCREMENTAL"
}

打包加压模块，主要进行整理文件

function Tar_Gzip() {
#echo ${BackupType}
[ ! "${BackupType}" ] && return

#判断是否打开打包加压开关
    if [ "${TarZip}" == "ON" -o  "${TarZip}" == "on" ];then
            echo "******************* 进行打包加压操作 ***********************"
    elif [ "${TarZip}" == "OFF" -o  "${TarZip}" == "off" ];then
	echo "打包加压开关为 off 状态 !"
            return
    else
	echo "打包加压开关配置错误,错误配置 TarZip= ${TarZip} !"
            return
    fi

#echo " "
#echo "进行打包加压操作 !"
sleep 3
if [ "${BackupType}" == "FULL" ];then
	local LatestFullBackup=$(${FIND} ${FullBackupRoot} -mindepth 1 -maxdepth 1 -type d -printf "%P\n" 2>/dev/null | sort -nr | head -1)
	echo " "
	cd "${FullBackupRoot}"
	# 判断是否启用pigz工具多线程压缩，默认不启用
	if [ "${UsePigz}" == "YES" -o  "${UsePigz}" == "yes" ];then
		echo "使用 ${PIGZ} 进行多线程压缩 ${LatestFullBackup}"
		${TAR} -icvf - ${LatestFullBackup}  --remove-file | ${PIGZ} > "${LatestFullBackup}".tar.gz
	else
		${TAR} -izcf "${LatestFullBackup}".tar.gz "${LatestFullBackup}" --remove-file
	fi
	[ $? == 0 ] && echo "在 ${FullBackupRoot} 下面已对 ${LatestFullBackup} 打包加压成 ${LatestFullBackup}.tar.gz !"
	TarZipFile=${FullBackupRoot}/${LatestFullBackup}.tar.gz
elif [ "${BackupType}" == "INCREMENTAL" ];then
	local LatestIncrementalBackup=$(${FIND} ${IncrementalBackupRoot} -mindepth 1 -maxdepth 1 -type d -printf "%P\n" 2>/dev/null | sort -nr | head -1)
	echo " "
	cd "${IncrementalBackupRoot}"
	# 判断是否启用pigz工具多线程压缩,默认不启用
	if [ "${UsePigz}" == "YES" -o  "${UsePigz}" == "yes" ];then
		echo "使用 ${PIGZ} 进行多线程压缩 ${LatestFullBackup}"
		${TAR} -icvf - ${LatestIncrementalBackup}  --remove-file | ${PIGZ} > "${LatestIncrementalBackup}".tar.gz
	else
		${TAR} -izcf "${LatestIncrementalBackup}".tar.gz "${LatestIncrementalBackup}" --remove-file
	fi
	[ $? == 0 ] && echo "在 ${IncrementalBackupRoot} 下面已对 ${LatestIncrementalBackup} 打包加压成 ${LatestIncrementalBackup}.tar.gz !"
	TarZipFile=${IncrementalBackupRoot}/${LatestIncrementalBackup}.tar.gz
fi

}

时间控制，主要进行控制备份策略

function Backup_Policy() {
local NOW=$(date +%c)
echo "******************************* ${NOW} *****************************"
#FullBackupTime
for BackupTime in ${FullBackupTime[]}
do
#echo ${BackupTime}
FullBackupWeek=${BackupTime%%:}
if [ "${FullBackupWeek}" == "${Week}" ];then
#判断是否全量备份
local FullBackupHour=${BackupTime##:}
if [ "${FullBackupHour}" == "${Hour}" ];then
echo " 进行全量备份 "
sleep 3
Full_Backup
return
fi
#判断需不需要基于全量备份
local HourNow=$((IncrementalBackupIntervalTime + FullBackupHour))
if [ "${Hour}" == "${HourNow}" ];then
#进行基于全量的增量备份
echo " 尝试基于全量增量备份 ***"
sleep 3
Base_Full_Backup
return
fi
fi
done
#判断需不需要基于全量备份
#HourNow=$((IncrementalBackupIntervalTime + FullBackupHour))

echo "*** 尝试基于增量的增量备份 ***"
sleep 3
Base_Incremental_Backup
return

}

进行同步备份文件,只是同步已经打包加压成 .tar.gz 的文件

function Scp_File() {
#间接获取是否执行innobackupex
[ ! "${BackupType}" ] && return
#判断打包压缩模块是否打开，打开备份模块才生效
[ "${TarZip}" == "ON" -o "${TarZip}" == "on" ] || return

#判断是否打开备份开关
if [ "${ScpFile}" == "ON" -o  "${ScpFile}" == "on" ];then
	echo "******************* 将使用SCP命令传输备份数据库文件 ***********************"
elif [ "${ScpFile}" == "OFF" -o  "${ScpFile}" == "off" ];then
	echo " "
	echo "备份状态为 off,没进行传备份文件 ${TarZipFile} 到服务器 ${BackupServerAddress} !"
	return
else
	echo " "
	echo "备份配置错误,错误配置 ScpFile= ${ScpFile}"
	return
fi

#echo "******************* 将使用SCP命令传输备份数据库文件 ***********************"
${SCP} -P "${BackupServerPort}" "${TarZipFile}" "${BackupServerUser}"@${BackupServerAddress}:${BackupServerDir}
#判断是成功备份
[ "$?" == "0" ] && echo "备份文件 ${TarZipFile} 到服务器 ${BackupServerAddress} 的 ${BackupServerDir} 成功 !" || echo "备份文件 ${TarZipFile} 到服务器失败 !"

}

删除备份文件，删除备份策略

function Delete_File() {
echo ""
if [ "${DeleteFile}" == "ON" -o "${DeleteFile}" == "on" ];then
echo "*** 查询并删除过期文件 ***"
elif [ "${DeleteFile}" == "OFF" -o "${DeleteFile}" == "off" ];then
echo "删除文件状态为关闭 !"
return
else
echo "删除模块配置错误,错误配置 DeleteFile=${DeleteFile} "
return
fi

#整理全量备份的目录
echo "*** 查询全量备份目录 ${FullBackupRoot} ***"
local FindResult=$(${FIND} ${FullBackupRoot}/*.tar.gz -type f -mtime +${DeadLine} 2>/dev/null)
if [ "${FindResult}" ];then
	echo "将在目录 ${FullBackupRoot} 删除一下文件: "
            echo "${FindResult}"
            #进行删除文件
	sleep 5
            ${FIND} ${FullBackupRoot}/*.tar.gz -type f -mtime +${DeadLine} -exec rm -rf {} \;
else
	echo "在目录 ${FullBackupRoot} 下面没有可删除过期( ${DeadLine} 天)文件 !"
fi

#整理增量备份的目录
echo "*** 查询增量备份目录 ${IncrementalBackupRoot} ***"
local FindResult=$(${FIND} ${IncrementalBackupRoot}/*.tar.gz -type f -mtime +${DeadLine} 2>/dev/null)
if [ "${FindResult}" ];then
	echo "将在目录 ${IncrementalBackupRoot} 删除一下文件: "
            echo "${FindResult}"
            #进行删除文件
            ${FIND} ${IncrementalBackupRoot}/*.tar.gz -type f -mtime +${DeadLine} -exec rm -rf {} \;
	sleep 5
	${FIND} ${IncrementalBackupRoot}/*.tar.gz -type f -mtime +${DeadLine} -exec rm -rf {} \;
else
	echo "在目录 ${IncrementalBackupRoot} 下面没有可删除过期( ${DeadLine} 天)文件 !"
fi

}

Backup_Policy

Main_Fun >> ${Log} 2>&1

Base_Incremental_Backup

Base_Full_Backup

Incremental_Backup

Full_Backup

两天服务器直接做免密处理

posted @ 2022-11-26 17:16  EchoSong  阅读(280)  评论(0)  编辑  收藏  举报