backup.bat@echo off
rem create date
set mydate=%date:~0,4%%date:~5,2%%date:~8,2% rem save path of backup files and log's file
set savePath=e:\bat\ rem log's path
set logPath=E:\bat\ rem database's name
set databaseName=test rem backup's user and pwd
set user=root
set pwd=123456 rem mySQL Server's ip
set host=localhost @echo ===========================================================================>> %logPath%backup.log
@echo backup time:%mydate% %time:~0,5%>> %logPath%backup.log
@echo backup file path:%savePath%%mydate%.sql>> %logPath%backup.log
@echo backup database: %databaseName%>>%logPath%backup.log
set starttime=%time:~0,11%
@echo start time: %starttime%>> %logPath%backup.log rem lock tables,engine:innodb
MySQLdump -h %host% -u%user% -p%pwd% --single-transaction --default-character-set=sjis %databaseName% > %savePath%%mydate%.sql rem lock tables,engine:not innodb
rem MySQLdump -h %host% -u%user% -p%pwd% --default-character-set=sjis %databaseName% > %savePath%%mydate%.sql rem not lock tables
rem MySQLdump -h %host% -u%user% -p%pwd% --skip-opt --add-drop-table --default-character-set=sjis %databaseName% > %savePath%%mydate%.sql set endtime=%time:~0,11%
@echo end time: %endtime% >> %logPath%backup.log type %savePath%%mydate%.sql|find "-- Dump completed"
if %errorlevel% EQU 0 (
@echo congratulations ,backup success >> %logPath%backup.log
) else (
@echo sorry,backup fail,please check >> %logPath%backup.log @echo ===========================================================================>> %logPath%backup.log
recovery.bat @echo off
rem create data
set mydate=%date:~0,4%%date:~5,2%%date:~8,2% rem backup file's path
set filePath=E:/bat/20100528.sql rem log's path
set logPath=E:/bat/ rem database name
set databaseName=test rem backup's name and pwd
set user=root
set pwd=123456 rem mySQL Server's ip
set host=localhost @echo ===========================================================================>>%logPath%recovery.log
@echo recovery time:%mydate% %time:~0,5%>> %logPath%recovery.log
@echo recovery file: %filePath%>> %logPath%recovery.log
@echo backup database: %databaseName%>>%logPath%recovery.log rem start time
set starttime=%time:~0,11%
@echo start time: %starttime%>> %logPath%recovery.log MySQL -h %host% -u%user% -p%pwd% %databaseName% --default-character-set=sjis -q< %filePath% rem end time
set endtime=%time:~0,11%
@echo end time: %endtime% >> %logPath%recovery.log
@echo ===========================================================================>>%logPath%recovery.log 注: mysqldump備份時,視圖也會被備份,但是procedure,function不會備份,因為這些在MySQL庫中,只能備份數據庫