对于 D BA 或运维人员来讲，备份恢复是最基本也是最必要的技能之一，一个通用并且稳定的热备脚本能够极大的释放工程师的时间和精力，本篇提供 MySQL 数据库最常用的热备脚本，希望能够帮助到大家。
1 、 MySQL 逻辑热备脚本（ Linux 版）
脚本代码：
#!/bin/bash
. $HOME/.bash_profile
BACKUPDIR=/home/backup
LOGFILE=$BACKUPDIR/mysql_logicbak.log
IPADDR=192.168.239.51
USER=root
PASSWORD=Abcd321#
PORT=3306
TIME1=`date +"%Y-%m-%d %H:%M:%S"`
CONN="-h$IPADDR -u$USER -p$PASSWORD -P$PORT"
stdopt=`mysql $CONN -e"select 1" 2> /dev/null|awk 'NR>1'`
if [ "$stdopt" = "1" ];then
TIME2=`date +"%Y%m%d%H%M%S"`
BKPFILE=$BACKUPDIR/dbs_mysql_${TIME2}_bkp.sql
mysqldump $CONN --databases ceshi --routines --events --single-transaction --master-data=2 --flush-logs > $BKPFILE 2> /dev/null
sleep 2
stdopt2=`tail -1 $BKPFILE|awk '{print $2,$3}'`
if [ "$stdopt2" = "Dump completed" ];then
echo "$TIME1 [INFO] Logical backup is successed, Take it easy." >> $LOGFILE
else
echo "$TIME1 [ERROR] Logical backup is failed, Please check." >> $LOGFILE
fi
else
echo "$TIME1 [ERROR] Connect is failed, Please check." >> $LOGFILE
fi
find $BACKUPDIR -name "full_mysql_*_bkp.sql" -ctime +7 -exec rm {} \;
正常输出：
2022-11-23 07:04:22 [INFO] Logical backup is successed, Take it easy.
2022-11-24 02:45:01 [INFO] Logical backup is successed, Take it easy.
2022-11-25 02:45:01 [INFO] Logical backup is successed, Take it easy.
2022-11-26 02:45:01 [INFO] Logical backup is successed, Take it easy.
2022-11-27 02:45:01 [INFO] Logical backup is successed, Take it easy.
错误输出：
2022-11-23 06:22:55 [ERROR] Connect is failed, Please check.
2022-11-23 06:23:16 [ERROR] Connect is failed, Please check.
2022-11-23 06:23:32 [ERROR] Connect is failed, Please check.
2022-11-23 06:23:45 [ERROR] Connect is failed, Please check.
2 、 MySQL 物理热备脚本（ Linux 版）
脚本代码：
#!/bin/bash
. $HOME/.bash_profile
BACKUPDIR=/backup
LOGFILE=$BACKUPDIR/mysql_physicbak.log
USER=root
PASSWORD=Abcd321#
SOCKET=/mysql/mysql57/product/data/mysql.sock
CONN="-u$USER -p$PASSWORD -S$SOCKET"
stdopt=`mysql $CONN -e"select 1" 2> /dev/null|awk 'NR>1'`
if [ "$stdopt" = "1" ];then
TIME2=`date +"%Y%m%d%H%M%S"`
TMPFILE=$BACKUPDIR/xtrabackup_${TIME2}.LOG
#test -d $BACKUPDIR/$TIME2 || mkdir -p $BACKUPDIR/$TIME2
#xtrabackup $CONN --backup --target-dir=$BACKUPDIR/$TIME2 2> $TMPFILE
xtrabackup $CONN --backup --stream=tar 2> $TMPFILE | gzip > $BACKUPDIR/full_mysql_${TIME2}.tar.gz
sleep 2
TIME1=`date +"%Y-%m-%d %H:%M:%S"`
stdopt2=`tail -1 $TMPFILE|awk '{print $3,$4}'`
if [ "$stdopt2" = "completed OK!" ];then
echo "$TIME1 [INFO] physical backup is successed, Take it easy." >> $LOGFILE
else
echo "$TIME1 [ERROR] physical backup is failed, Please check." >> $LOGFILE
fi
else
TIME1=`date +"%Y-%m-%d %H:%M:%S"`
echo "$TIME1 [ERROR] Connect is failed, Please check." >> $LOGFILE
fi
find $BACKUPDIR -name "full_mysql_*.tar.gz" -ctime +7 -exec rm {} \;
正常输出：
2022-11-23 06:57:31 [INFO] physical backup is successed, Take it easy.
2022-11-23 06:58:08 [INFO] physical backup is successed, Take it easy.
2022-11-23 06:58:49 [INFO] physical backup is successed, Take it easy.
错误输出：
2022-11-23 07:00:13 [ERROR] Connect is failed, Please check.
2022-11-23 07:00:19 [ERROR] Connect is failed, Please check.
2022-11-23 07:00:21 [ERROR] Connect is failed, Please check.
2022-11-23 07:00:36 [ERROR] Connect is failed, Please check.
2022-11-23 07:00:37 [ERROR] Connect is failed, Please check.
3 、 MySQL 逻辑热备脚本（ W indows 版）
脚本代码：
@echo off
SETLOCAL ENABLEDELAYEDEXPANSION
set DISK=C:
set MySQLPATH=C:\Program Files\MySQL\MySQL Server 8.0\bin
set BACKUPDIR=C:\backup
set LOGFILE=%BACKUPDIR%\mysql_logicbak.log
set IPADDR=127.0.0.1
set USER=root
set PASSWORD=Abcd321#
set PORT=3308
set CONN=-h%IPADDR% -u%USER% -p%PASSWORD% -P%PORT%
%DISK%
cd %MySQLPATH%
for /f %%i in ('mysql %CONN% --silent --skip-column-names -e"select 1" 2^>nul') do @set stdopt1=%%i
timeout 1 >nul
:: echo %stdopt1%
set TIME1=%DATE:~0,4%%DATE:~5,2%%DATE:~8,2%%TIME:~0,2%%TIME:~3,2%%TIME:~6,2%
if "%stdopt1%"=="1" (
echo "%TIME1% [INFO] Connect is successed, Take it easy." >> %LOGFILE%
) else (
echo "%TIME1% [ERROR] Connect is failed, Please check." >> %LOGFILE%
exit 1
)
set BKPFILE=%BACKUPDIR%\dbs_mysql_%TIME1%_bkp.sql
mysqldump %CONN% --databases hzmc --routines --events --single-transaction --master-data=2 --flush-logs > %BKPFILE% 2>nul
timeout 2 >nul
:: for /f "delims=" %%i in (%BKPFILE%) do (set lastLine=%%~i)
:: for /f "tokens=3" %%i in ('echo %lastLine%') do @set stdopt2=%%i
find "Dump completed" %BKPFILE% && set stdopt2=%errorlevel%
timeout 1 >nul
:: echo %stdopt2%
if "%stdopt2%"=="0" (
echo "%TIME1% [INFO] Logical backup is successed, Take it easy." >> %LOGFILE%
) else (
echo "%TIME1% [ERROR] Logical backup is failed, Please check." >> %LOGFILE%
exit 1
)
forfiles /P %BACKUPDIR%\ /M dbs_mysql_*_bkp.sql /D -2 /C "cmd /c del @file"
ENDLOCAL
pause
正常输出：
"20221130192026 [INFO] Connect is successed, Take it easy."
"20221130192026 [INFO] Logical backup is successed, Take it easy."
"20221130192055 [INFO] Connect is successed, Take it easy."
"20221130192055 [INFO] Logical backup is successed, Take it easy."
"20221130192112 [INFO] Connect is successed, Take it easy."
"20221130192112 [INFO] Logical backup is successed, Take it easy."
错误输出：
"20221208111538 [ERROR] Connect is failed, Please check."
"20221208111545 [ERROR] Connect is failed, Please check."
"20221208111548 [ERROR] Connect is failed, Please check."
"20221208111550 [ERROR] Connect is failed, Please check."
"20221208111552 [ERROR] Connect is failed, Please check."
以上
3
种热备场景，脚本中均内置的备份成功关键字的判断，只有真正成功完成备份，才会在日志中显示备份成功，因此在备份可靠性方面是有保障的。