sql数据库定期同步并上传ftp脚本

@echo off

set USER=root  
set PASSWORD=root  
set DATABASE=**  

rem 备份文件存储路径 
set BACKUP_DIR=f:\DMP_BACKUP\dmp_backup
rem 备份文件目录  处理小时
set h=%time:~0,2%
set h=%h: =0%
set DUMPFILE=%date:~0,4%%date:~5,2%%date:~8,2%-%h%%time:~3,2%_dmp.sql 
set OPTIONS=-u%USER% -p%PASSWORD% %DATABASE% 
set DATE_TIME=%date:~0,4%-%date:~5,2%-%date:~8,2% %time:~0,2%:%time:~3,2%:%time:~6,2%



rem 判断备份文件存储目录是否存在，否则创建该目录  
if not exist %BACKUP_DIR%  (
     echo ------目录不存在 创建目录...
     mkdir %BACKUP_DIR%
     )

rem 记录日志
echo %date:~0,4%-%date:~5,2%-%date:~8,2% %time:~0,2%:%time:~3,2%:%time:~6,2%: Database Backup Begin...   >> %BACKUP_DIR%\log.txt 

rem 上一次备份文件的大小 用于预警

cd /d f:\DMP_BACKUP\dmp_backup\
for /f "tokens=*" %%f in ('dir *.sql /b /od /a-d') do (set f=%%f&set old_size=%%~zf)


echo ------上次备份文件:%f% ( %old_size% 字节) 
echo ------备份开始...


rem 使用mysqldump 命令备份制定数据库，并以格式化的时间戳命名备份文件 
rem 名字可能存在空格，所以加引号
mysqldump %OPTIONS% > %BACKUP_DIR%\%DUMPFILE%

rem 判断数据库备份是否成功?  失败%errorlevel%返回6
if %errorlevel% gtr 0 (               
                echo %date:~0,4%-%date:~5,2%-%date:~8,2% %time:~0,2%:%time:~3,2%:%time:~6,2%: Database Backup Fail... ...  >> %BACKUP_DIR%\log.txt 
        echo ------备份失败...
        ) else (echo %date:~0,4%-%date:~5,2%-%date:~8,2% %time:~0,2%:%time:~3,2%:%time:~6,2%: Database Backup success      Backup Address: %BACKUP_DIR%\%DUMPFILE%  

>> %BACKUP_DIR%\log.txt 
        echo ------备份成功...
        )

rem 判断备份数据的大小，理论上备份数据会越来越大
FOR /f "delims=" %%i in ("%BACKUP_DIR%/%DUMPFILE%") do (        
    set /a size_M=%%~zi/1048576   
    set /a size_b=%%~zi
)     


set /a change_size=%size_b%-%old_size%
echo ------此次备份文件:%DUMPFILE% ( %size_b% 字节) 增加了%change_size% 字节

echo %date:~0,4%-%date:~5,2%-%date:~8,2% %time:~0,2%:%time:~3,2%:%time:~6,2%: Database Backup Size         %size_M% MB (%size_b% Byte) change %change_size% Byte    >> 

%BACKUP_DIR%\log.txt 

echo.>> %BACKUP_DIR%\log.txt 

rem 删除30天前的备份数据
rem 指定天数
set DaysAgo=30

forfiles /p f:\DMP_BACKUP\dmp_backup\ /s /m *.* /d -%DaysAgo% /c "cmd /c del @file"
if %errorlevel% gtr 0 ( 
echo ------没有删除本地备份
)

echo ------开始上传ftp

echo open ftp.******.com>abc.txt
echo user username password >>abc.txt
echo cd dmp_backup
echo cd dmp_backup   
echo prompt off
echo mdelete log.txt
echo put "f:\DMP_BACKUP\dmp_backup\log.txt"  "dmp_backup\dmp_backup\log.txt" >> abc.txt
echo put "%BACKUP_DIR%\%DUMPFILE%"  "dmp_backup\dmp_backup\%DUMPFILE%" >> abc.txt
echo bye>>abc.txt
ftp -n -s:abc.txt
if %errorlevel% EQU 0 ( 
echo ------上传ftp成功
)
del abc.txt

pause

这个脚本很多功能略显鸡肋，有时间在修改一下。大家有什么问题可以留言。