@ECHO OFF
REM 请在计算机的控制面版中,将该脚本添加为计划任务,用于进行定期备份
REM 请d:\mysqldump.exe文件存在。备份机器无需安装Mysql,mysqldump.exe可在mysql的windows包中bin目录下找到,拷贝到备份机器。
REM 数据库配置
SET "Subject=shangxueyuan"
SET "userName=cbc-as"
SET "password=***"
SET "db1=cbc-as"
REM SET "db2=hfd2"
SET "IP=***"
REM 是否压缩备份文件(true/false)
SET "rar=false"
REM 压缩文件路径(winrar/7z)
SET "rarURL=winrar"
REM 备份文件上传FTP设置
SET "ftpUpload=false"
SET "ftpIP=***"
SET "ftpUser=singvi"
SET "ftpPW=sin263"
REM WIN7需要配置备份文件目录,否则保存在与任务计划程序taskeng相同的目录
REM "DISK=F:" "DIR=DbBackup"
SET "DISK="
SET "DIR="
REM 配置结束
COLOR 27
SET d=%date%
REM 年份月份目录生成
REM 取日期,支持输入格式:YYYY-MM-DD HH:mm:ss 和 YYYY/MM/DD HH:mm:ss
for /f "tokens=1 delims=\/- " %%j in ("%d%") do set d1=%%j
for /f "tokens=2 delims=\/- " %%j in ("%d%") do set d2=%%j
for /f "tokens=3 delims=\/- " %%j in ("%d%") do set d3=%%j
for /f "tokens=4 delims=\/- " %%j in ("%d%") do set d4=%%j
for /f "tokens=1 delims=: " %%j in ("%time%") do set t1=%%j
for /f "tokens=2 delims=: " %%j in ("%time%") do set t2=%%j
for /f "tokens=3 delims=:. " %%j in ("%time%") do set t3=%%j
REM 星期在日期前的情况
IF "%d1%"=="%d1:~0,3%" (
SET year=%d2%
SET /a tmpx=100+d3
SET /a tmpy=100+d4
) ELSE (
SET year=%d1%
SET /a tmpx=100+d2
SET /a tmpy=100+d3
)
REM 截取放在IF内时,不能正常截取
SET month=%tmpx:~1,2%
SET day=%tmpy:~1,2%
SET /a t1=100+t1
SET /a t2=100+t2
SET /a t3=100+t3
SET hour=%t1:~1,2%
SET minute=%t2:~1,2%
SET second=%t3:~1,2%
SET PATH_YEAR_MONTH=%year%/%month%/
SET fname=%year%-%month%-%day%_%hour%%minute%%second%
%DISK%
CD %DIR%
IF EXIST %year% (
REM ECHO 文件夹[%year%]已经存在
CD %year%
) ELSE (
REM ECHO 创建文件夹[%year%]
MD %year%
CD %year%
)
IF EXIST %month% (
REM ECHO 文件夹[%month%]已经存在
CD..
) ELSE (
REM ECHO 创建文件夹[%month%]
MD %month%
CD..
)
REM 目录生成结束
ECHO %Subject%需求管理系统 数据库自动备份
ECHO -------------------------------
ECHO 正在执行备份任务,请稍候...
REM 以下使用mysqldump进行备份,如果mysql安装目录未添加到系统路径,请在其前面指定安装目录,如: d:\mysql\bin\mysqldump
ECHO ***************************************************************************************************>>log.txt
ECHO %Subject%需求管理系统 数据库自动备份>>log.txt
ECHO ------------------------------->>log.txt
ECHO %date% %time% 正在进行备份...>>log.txt
ECHO %PATH_YEAR_MONTH%%db1%%fname%.sql
mysqldump -u%userName% -p%password% -h%IP% %db1% > %PATH_YEAR_MONTH%%db1%%fname%.sql
REM ECHO %PATH_YEAR_MONTH%%db2%%fname%.sql
REM mysqldump -u%userName% -p%password% -h%IP% --database %db2%> %PATH_YEAR_MONTH%%db2%%fname%.sql
ECHO 备份完毕 [%date% %time%]>>log.txt
IF "%rar%"=="true" (
ECHO %date% %time% 正在压缩备份文件...>>log.txt
%rarURL% u %PATH_YEAR_MONTH%%db1%%fname%.rar %PATH_YEAR_MONTH%%db1%%fname%.sql
REM %rarURL% u %PATH_YEAR_MONTH%%db2%%fname%.rar %PATH_YEAR_MONTH%%db2%%fname%.sql
ECHO 压缩完毕 [%date% %time%]>>log.txt
REM 压缩后删除原文件
CD %PATH_YEAR_MONTH%
DEL %db1%%fname%.sql
REM DEL %db2%%fname%.sql
CD..
CD..
)
SET FtpFile="TempAcc.txt"
IF "%ftpUpload%"=="true" (
>"%FtpFile%" ECHO %ftpUser%
>>"%FtpFile%" ECHO %ftpPW%
>>"%FtpFile%" ECHO bin
>>"%FtpFile%" ECHO MKDIR %year%
>>"%FtpFile%" ECHO CD %year%
>>"%FtpFile%" ECHO MKDIR %month%
>>"%FtpFile%" ECHO CD %month%
goto checkRAR
) else (
goto endFTP
)
:checkRAR
IF "%rar%"=="true" (
>>"%FtpFile%" ECHO SEND %PATH_YEAR_MONTH%%db1%%fname%.rar
>>"%FtpFile%" ECHO !ECHO %PATH_YEAR_MONTH%%db1%%fname%.rar 已上传^>^>uploadLog.txt
>>"%FtpFile%" REM ECHO send %PATH_YEAR_MONTH%%db2%%fname%.rar
>>"%FtpFile%" REM ECHO !ECHO %PATH_YEAR_MONTH%%db2%%fname%.rar 已上传^>^>uploadLog.txt
) else (
>>"%FtpFile%" ECHO send %PATH_YEAR_MONTH%%db1%%fname%.sql
>>"%FtpFile%" ECHO !ECHO %PATH_YEAR_MONTH%%db1%%fname%.sql 已上传^>^>uploadLog.txt
>>"%FtpFile%" REM ECHO send %PATH_YEAR_MONTH%%db2%%fname%.sql
>>"%FtpFile%" REM ECHO !ECHO %PATH_YEAR_MONTH%%db2%%fname%.sql 已上传^>^>uploadLog.txt
)
>>"%FtpFile%" ECHO bye
ECHO 正在上传文件...
ECHO %date% %time% 正在上传备份文件...>>log.txt
REM start /wait ftp -v -i -s:"%FtpFile%" %ftpIP%
FTP -s:"%FtpFile%" %ftpIP%
DEL /q "%FtpFile%"
ECHO 上传成功 [%date%%time%]>>log.txt
ECHO 文件已上传
:endFTP
ECHO 操作已完成![%date% %time%]>>log.txt
Echo. >>log.txt
Echo. >>log.txt
ECHO 执行完成!
REM PAUSE
REM 请在计算机的控制面版中,将该脚本添加为计划任务,用于进行定期备份
REM 请d:\mysqldump.exe文件存在。备份机器无需安装Mysql,mysqldump.exe可在mysql的windows包中bin目录下找到,拷贝到备份机器。
REM 数据库配置
SET "Subject=shangxueyuan"
SET "userName=cbc-as"
SET "password=***"
SET "db1=cbc-as"
REM SET "db2=hfd2"
SET "IP=***"
REM 是否压缩备份文件(true/false)
SET "rar=false"
REM 压缩文件路径(winrar/7z)
SET "rarURL=winrar"
REM 备份文件上传FTP设置
SET "ftpUpload=false"
SET "ftpIP=***"
SET "ftpUser=singvi"
SET "ftpPW=sin263"
REM WIN7需要配置备份文件目录,否则保存在与任务计划程序taskeng相同的目录
REM "DISK=F:" "DIR=DbBackup"
SET "DISK="
SET "DIR="
REM 配置结束
COLOR 27
SET d=%date%
REM 年份月份目录生成
REM 取日期,支持输入格式:YYYY-MM-DD HH:mm:ss 和 YYYY/MM/DD HH:mm:ss
for /f "tokens=1 delims=\/- " %%j in ("%d%") do set d1=%%j
for /f "tokens=2 delims=\/- " %%j in ("%d%") do set d2=%%j
for /f "tokens=3 delims=\/- " %%j in ("%d%") do set d3=%%j
for /f "tokens=4 delims=\/- " %%j in ("%d%") do set d4=%%j
for /f "tokens=1 delims=: " %%j in ("%time%") do set t1=%%j
for /f "tokens=2 delims=: " %%j in ("%time%") do set t2=%%j
for /f "tokens=3 delims=:. " %%j in ("%time%") do set t3=%%j
REM 星期在日期前的情况
IF "%d1%"=="%d1:~0,3%" (
SET year=%d2%
SET /a tmpx=100+d3
SET /a tmpy=100+d4
) ELSE (
SET year=%d1%
SET /a tmpx=100+d2
SET /a tmpy=100+d3
)
REM 截取放在IF内时,不能正常截取
SET month=%tmpx:~1,2%
SET day=%tmpy:~1,2%
SET /a t1=100+t1
SET /a t2=100+t2
SET /a t3=100+t3
SET hour=%t1:~1,2%
SET minute=%t2:~1,2%
SET second=%t3:~1,2%
SET PATH_YEAR_MONTH=%year%/%month%/
SET fname=%year%-%month%-%day%_%hour%%minute%%second%
%DISK%
CD %DIR%
IF EXIST %year% (
REM ECHO 文件夹[%year%]已经存在
CD %year%
) ELSE (
REM ECHO 创建文件夹[%year%]
MD %year%
CD %year%
)
IF EXIST %month% (
REM ECHO 文件夹[%month%]已经存在
CD..
) ELSE (
REM ECHO 创建文件夹[%month%]
MD %month%
CD..
)
REM 目录生成结束
ECHO %Subject%需求管理系统 数据库自动备份
ECHO -------------------------------
ECHO 正在执行备份任务,请稍候...
REM 以下使用mysqldump进行备份,如果mysql安装目录未添加到系统路径,请在其前面指定安装目录,如: d:\mysql\bin\mysqldump
ECHO ***************************************************************************************************>>log.txt
ECHO %Subject%需求管理系统 数据库自动备份>>log.txt
ECHO ------------------------------->>log.txt
ECHO %date% %time% 正在进行备份...>>log.txt
ECHO %PATH_YEAR_MONTH%%db1%%fname%.sql
mysqldump -u%userName% -p%password% -h%IP% %db1% > %PATH_YEAR_MONTH%%db1%%fname%.sql
REM ECHO %PATH_YEAR_MONTH%%db2%%fname%.sql
REM mysqldump -u%userName% -p%password% -h%IP% --database %db2%> %PATH_YEAR_MONTH%%db2%%fname%.sql
ECHO 备份完毕 [%date% %time%]>>log.txt
IF "%rar%"=="true" (
ECHO %date% %time% 正在压缩备份文件...>>log.txt
%rarURL% u %PATH_YEAR_MONTH%%db1%%fname%.rar %PATH_YEAR_MONTH%%db1%%fname%.sql
REM %rarURL% u %PATH_YEAR_MONTH%%db2%%fname%.rar %PATH_YEAR_MONTH%%db2%%fname%.sql
ECHO 压缩完毕 [%date% %time%]>>log.txt
REM 压缩后删除原文件
CD %PATH_YEAR_MONTH%
DEL %db1%%fname%.sql
REM DEL %db2%%fname%.sql
CD..
CD..
)
SET FtpFile="TempAcc.txt"
IF "%ftpUpload%"=="true" (
>"%FtpFile%" ECHO %ftpUser%
>>"%FtpFile%" ECHO %ftpPW%
>>"%FtpFile%" ECHO bin
>>"%FtpFile%" ECHO MKDIR %year%
>>"%FtpFile%" ECHO CD %year%
>>"%FtpFile%" ECHO MKDIR %month%
>>"%FtpFile%" ECHO CD %month%
goto checkRAR
) else (
goto endFTP
)
:checkRAR
IF "%rar%"=="true" (
>>"%FtpFile%" ECHO SEND %PATH_YEAR_MONTH%%db1%%fname%.rar
>>"%FtpFile%" ECHO !ECHO %PATH_YEAR_MONTH%%db1%%fname%.rar 已上传^>^>uploadLog.txt
>>"%FtpFile%" REM ECHO send %PATH_YEAR_MONTH%%db2%%fname%.rar
>>"%FtpFile%" REM ECHO !ECHO %PATH_YEAR_MONTH%%db2%%fname%.rar 已上传^>^>uploadLog.txt
) else (
>>"%FtpFile%" ECHO send %PATH_YEAR_MONTH%%db1%%fname%.sql
>>"%FtpFile%" ECHO !ECHO %PATH_YEAR_MONTH%%db1%%fname%.sql 已上传^>^>uploadLog.txt
>>"%FtpFile%" REM ECHO send %PATH_YEAR_MONTH%%db2%%fname%.sql
>>"%FtpFile%" REM ECHO !ECHO %PATH_YEAR_MONTH%%db2%%fname%.sql 已上传^>^>uploadLog.txt
)
>>"%FtpFile%" ECHO bye
ECHO 正在上传文件...
ECHO %date% %time% 正在上传备份文件...>>log.txt
REM start /wait ftp -v -i -s:"%FtpFile%" %ftpIP%
FTP -s:"%FtpFile%" %ftpIP%
DEL /q "%FtpFile%"
ECHO 上传成功 [%date%%time%]>>log.txt
ECHO 文件已上传
:endFTP
ECHO 操作已完成![%date% %time%]>>log.txt
Echo. >>log.txt
Echo. >>log.txt
ECHO 执行完成!
REM PAUSE