MySQL最典型和实用的热备脚本实战

来源：这里教程网时间：2026-03-01 16:57:33 作者：

对于 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 种热备场景，脚本中均内置的备份成功关键字的判断，只有真正成功完成备份，才会在日志中显示备份成功，因此在备份可靠性方面是有保障的。