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
两天服务器直接做免密处理