MySQL8.0備份與還原工具XtraBackup
關(guān)閉防火墻
systemctl stop firewalld.service
systemctl disable firewalld.service
安裝MySQL 8.0.27
后面會(huì)用到
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
配置初始化賬號(hào)密碼
啟動(dòng)mysql服務(wù)
systemctl start mysqld
初始化密碼
sudo grep 'temporary password' /var/log/mysqld.log
修改密碼 上面出現(xiàn) password
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Node@123';
允許遠(yuǎn)程訪問
CREATE USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'Node@123';
授權(quán) 如果不行那么先update host 然后再執(zhí)行
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
創(chuàng)建備份文件夾
mkdir /backup
執(zhí)行完全備份
xtrabackup -uroot -p --backup --target-dir=/backup/base
xtrabackup --user=root --password=Node@123 --backup --target-dir=/backup/full
全備還原:
停掉mysql 服務(wù)器
systemctl stop mysqld.service
清理mysql 文件夾
rm -rf /var/lib/mysql/*
一致性檢查
xtrabackup --prepare --target-dir=/backup/base
執(zhí)行上面一句可以再執(zhí)行這句
xtrabackup --copy-back --target-dir=/backup/base
修改文件及文件夾屬主
chown -R mysql.mysql /var/lib/mysql
啟動(dòng)數(shù)據(jù)庫
systemctl start mysqld
增量備份
做基礎(chǔ)的全備
xtrabackup -uroot -p --backup --target-dir=/backup/new
全備后做一次增量備份
xtrabackup -uroot -p--backup --target-dir=/backup/inc1 --incremental-basedir=/backup/new
數(shù)據(jù)變更后 做第二次增量備份
xtrabackup -uroot -p--backup --target-dir=/backup/inc1 --incremental-basedir=/backup/new
復(fù)制到遠(yuǎn)程機(jī)器
所有的文件需要檢查一致性
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
做數(shù)據(jù)恢復(fù)
xtrabackup --copy-back --target-dir=/backup/new
權(quán)限修改
chown -R mysql.mysql /var/lib/mysql
啟動(dòng)服務(wù)檢查數(shù)據(jù)
systemctl start mysqld
不同機(jī)器直接備份
備份到遠(yuǎn)程機(jī)器
scp -r /backup/base root@192.168.10.105:/backup
保證遠(yuǎn)程機(jī)器的數(shù)據(jù)庫配置一致，直接在遠(yuǎn)程機(jī)器執(zhí)行還原
自動(dòng)執(zhí)行腳本
!/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 備份時(shí)log
Log=/data/log/autohotbackup_$(date +%Y-%m-%d).log
現(xiàn)在星期幾
Week=$(date +%w)
Hour=$(date +%H)
全量備份時(shí)間，按照星期定義,周一到周日依次1,2,3,4,5,6,7
FullBackupWeek=(1 2 3 4 5)
是否開啟binlog備份,ON / OFF
BinlogBackup=ON
全量備份時(shí)間,
例子, 周一 10-11 點(diǎn)響應(yīng) ，周二 15-16 點(diǎn)
FullBackupTime=(1:10 2:15)
FullBackupTime=(1:04 2:04 3:04 4:04 5:04 6:04 0:04)
增量備份時(shí)間,小時(shí)
IncrementalBackupIntervalTime=2
過期時(shí)間，超過這個(gè)時(shí)間將刪除,天
DeadLine=4
MySQL信息
readonly MysqlAddress="localhost"
readonly MysqlPort="3306"
readonly MysqlUser="root"
readonly MysqlPassword="Node@123"
readonly MysqlSocket=
readonly MysqlConfigFile=/etc/my.cnf
Innobackupex使用內(nèi)存
readonly UseMemory=1000M
Innobackupex其他選項(xiàng)
readonly InnobackupexConfigure="--backup"
SCP，傳輸已備份文件到備份服務(wù)器
readonly BackupServerAddress=192.168.10.105
readonly BackupServerPort=22
readonly BackupServerUser=root
readonly BackupServerDir=/backup
模塊開關(guān)
備份文件到另一臺(tái)服務(wù)器,ON/on代表打開，OFF/off代表關(guān)閉
readonly IncrementalBackup=ON
readonly TarZip=ON
readonly ScpFile=ON
readonly BinlogBackup=OFF
readonly DeleteFile=ON
pigz 多線程壓縮工具是否啟用，可能會(huì)消耗更多的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 進(jìn)行MySQL熱備份 ***************"
echo " "
主函數(shù)
function Main_Fun() {
echo " "
echo "*************** $(date +%c): Xtrabackup 進(jìn)行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 "*** 進(jìn)行全量備份 ***"
[ -d ${FullBackupRoot} ] || ${MKDIR} ${FullBackupRoot} -p
#[ $? == 0 ] || echo "創(chuàng)建 ${BackupRoot} 失敗,退出! " ; exit 1
if [ $? != 0 ];then
echo "創(chuàng)建 ${FullBackupRoot} 失敗,退出! "
exit 1
fi
#判斷mysql可活可連接
Mysql_Alive
#進(jìn)行備份命令
${INNOBACKUPEX} ?--user="${MysqlUser}" --password="${MysqlPassword}" "${InnobackupexConfigure}" --target-dir="${FullBackupRoot}"
#進(jìn)行判斷
[ $? == 0 ] && echo "全量備份成功" || echo "全量備份失敗!"
BackupType="FULL"
}
基于全量進(jìn)行增量備份
function Base_Full_Backup() {
if [ "${IncrementalBackup}" == "ON" -o "${IncrementalBackup}" == "on" ];then
echo "*** 嘗試基于全量增量備份?"
elif [ "${IncrementalBackup}" == "OFF" -o "${IncrementalBackup}" == "off" ];then
echo "?增量備份開關(guān)已關(guān)閉?"
return
else
echo "增量備份開關(guān)配置錯(cuò)誤,錯(cuò)誤配置 IncrementalBackup= ${IncrementalBackup} ! "
return
fi
#echo "?嘗試基于全量增量備份 ***"
#獲取上次全量備份目錄名,根據(jù)全名查找，如果找不到，則破例進(jìn)行全量備份
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} 下面沒有找到全量備份,將進(jìn)行全量備份 !"
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
#進(jìn)行備份操作
${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"
}
基于增量進(jìn)行增量備份
function Base_Incremental_Backup() {
if [ "${IncrementalBackup}" == "ON" -o "${IncrementalBackup}" == "on" ];then
echo "*** 嘗試基于全量增量備份?"
elif [ "${IncrementalBackup}" == "OFF" -o "${IncrementalBackup}" == "off" ];then
echo "?增量備份開關(guān)已關(guān)閉?"
return
else
echo "增量備份開關(guān)配置錯(cuò)誤,錯(cuò)誤配置 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} 沒找到增量備份,將運(yùn)行基于全量備份模塊 !"
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"
}
打包加壓模塊，主要進(jìn)行整理文件
function Tar_Gzip() {
#echo ${BackupType}
[ ! "${BackupType}" ] && return
#判斷是否打開打包加壓開關(guān)
? ?if [ "${TarZip}" == "ON" -o ?"${TarZip}" == "on" ];then
? ? ? ? ? ?echo "******************* 進(jìn)行打包加壓操作 ***********************"
? ?elif [ "${TarZip}" == "OFF" -o ?"${TarZip}" == "off" ];then
echo "打包加壓開關(guān)為 off 狀態(tài) !"
? ? ? ? ? ?return
? ?else
echo "打包加壓開關(guān)配置錯(cuò)誤,錯(cuò)誤配置 TarZip= ${TarZip} !"
? ? ? ? ? ?return
? ?fi
#echo " "
#echo "進(jìn)行打包加壓操作 !"
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工具多線程壓縮，默認(rèn)不啟用
if [ "${UsePigz}" == "YES" -o ?"${UsePigz}" == "yes" ];then
echo "使用 ${PIGZ} 進(jìn)行多線程壓縮 ${LatestFullBackup}"
${TAR} -icvf - ${LatestFullBackup} ?--remove-file | ${PIGZ} > "${LatestFullBackup}".tar.gz
else
${TAR} -izcf "${LatestFullBackup}".tar.gz "${LatestFullBackup}" --remove-file
fi
[ $? == 0 ] && echo "在 ${FullBackupRoot} 下面已對(duì) ${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工具多線程壓縮,默認(rèn)不啟用
if [ "${UsePigz}" == "YES" -o ?"${UsePigz}" == "yes" ];then
echo "使用 ${PIGZ} 進(jìn)行多線程壓縮 ${LatestFullBackup}"
${TAR} -icvf - ${LatestIncrementalBackup} ?--remove-file | ${PIGZ} > "${LatestIncrementalBackup}".tar.gz
else
${TAR} -izcf "${LatestIncrementalBackup}".tar.gz "${LatestIncrementalBackup}" --remove-file
fi
[ $? == 0 ] && echo "在 ${IncrementalBackupRoot} 下面已對(duì) ${LatestIncrementalBackup} 打包加壓成 ${LatestIncrementalBackup}.tar.gz !"
TarZipFile=${IncrementalBackupRoot}/${LatestIncrementalBackup}.tar.gz
fi
}
時(shí)間控制，主要進(jìn)行控制備份策略
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 "?進(jìn)行全量備份?"
sleep 3
Full_Backup
return
fi
#判斷需不需要基于全量備份
local HourNow=$((IncrementalBackupIntervalTime + FullBackupHour))
if [ "${Hour}" == "${HourNow}" ];then
#進(jìn)行基于全量的增量備份
echo "?嘗試基于全量增量備份 ***"
sleep 3
Base_Full_Backup
return
fi
fi
done
#判斷需不需要基于全量備份
#HourNow=$((IncrementalBackupIntervalTime + FullBackupHour))
echo "*** 嘗試基于增量的增量備份 ***"
sleep 3
Base_Incremental_Backup
return
}
進(jìn)行同步備份文件,只是同步已經(jīng)打包加壓成 .tar.gz 的文件
function Scp_File() {
#間接獲取是否執(zhí)行innobackupex
[ ! "${BackupType}" ] && return
#判斷打包壓縮模塊是否打開，打開備份模塊才生效
[ "${TarZip}" == "ON" -o "${TarZip}" == "on" ] || return
#判斷是否打開備份開關(guān)
if [ "${ScpFile}" == "ON" -o ?"${ScpFile}" == "on" ];then
echo "******************* 將使用SCP命令傳輸備份數(shù)據(jù)庫文件 ***********************"
elif [ "${ScpFile}" == "OFF" -o ?"${ScpFile}" == "off" ];then
echo " "
echo "備份狀態(tài)為 off,沒進(jìn)行傳備份文件 ${TarZipFile} 到服務(wù)器 ${BackupServerAddress} !"
return
else
echo " "
echo "備份配置錯(cuò)誤,錯(cuò)誤配置 ScpFile= ${ScpFile}"
return
fi
#echo "******************* 將使用SCP命令傳輸備份數(shù)據(jù)庫文件 ***********************"
${SCP} -P "${BackupServerPort}" "${TarZipFile}" "${BackupServerUser}"@${BackupServerAddress}:${BackupServerDir}
#判斷是成功備份
[ "$?" == "0" ] && echo "備份文件 ${TarZipFile} 到服務(wù)器 ${BackupServerAddress} 的 ${BackupServerDir} 成功 !" || echo "備份文件 ${TarZipFile} 到服務(wù)器失敗 !"
}
刪除備份文件，刪除備份策略
function Delete_File() {
echo ""
if [ "${DeleteFile}" == "ON" -o "${DeleteFile}" == "on" ];then
echo "*** 查詢并刪除過期文件 ***"
elif [ "${DeleteFile}" == "OFF" -o "${DeleteFile}" == "off" ];then
echo "刪除文件狀態(tài)為關(guān)閉 !"
return
else
echo "刪除模塊配置錯(cuò)誤,錯(cuò)誤配置 DeleteFile=${DeleteFile} "
return
fi
#整理全量備份的目錄
echo "*** 查詢?nèi)總浞菽夸?${FullBackupRoot} ***"
local FindResult=$(${FIND} ${FullBackupRoot}/*.tar.gz -type f -mtime +${DeadLine} 2>/dev/null)
if [ "${FindResult}" ];then
echo "將在目錄 ${FullBackupRoot} 刪除一下文件: "
? ? ? ? ? ?echo "${FindResult}"
? ? ? ? ? ?#進(jìn)行刪除文件
sleep 5
? ? ? ? ? ?${FIND} ${FullBackupRoot}/*.tar.gz -type f -mtime +${DeadLine} -exec rm -rf {} \;
else
echo "在目錄 ${FullBackupRoot} 下面沒有可刪除過期( ${DeadLine} 天)文件 !"
fi
#整理增量備份的目錄
echo "*** 查詢?cè)隽總浞菽夸?${IncrementalBackupRoot} ***"
local FindResult=$(${FIND} ${IncrementalBackupRoot}/*.tar.gz -type f -mtime +${DeadLine} 2>/dev/null)
if [ "${FindResult}" ];then
echo "將在目錄 ${IncrementalBackupRoot} 刪除一下文件: "
? ? ? ? ? ?echo "${FindResult}"
? ? ? ? ? ?#進(jìn)行刪除文件
? ? ? ? ? ?${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
兩天服務(wù)器直接做免密處理
無生，無滅，看似最高境界
鏈接：https://www.dianjilingqu.com/623088.html
