@ECHO OFF

REM name: mysql_backup.bat
REM description: mysql backup and upload
REM version: v1.0.0
REM author: Shi Zhuolin
REM E-mail: shizhuolin@hotmail.com shizhuolin@qq.com

REM the directory for story your backup file

SET backup_dir="D:\mysql_backup\files"

REM your mysql login information

SET db_host=
SET db_user=
SET db_passwd=

REM your ftp login information

SET ftp_host=
SET ftp_user=
SET ftp_passwd=
SET ftp_path=/

REM mysql, mysqldump, winrar and some other bin's path 

SET MYSQL="C:\Program Files\MySQL\MySQL Server 5.5\bin\mysql.exe"
SET MYSQLDUMP="C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqldump.exe"
SET WINRAR="C:\Program Files\WinRAR\Rar.exe"

SET FTPCMD=%backup_dir%\ftpcmd.txt
SET FTPLOG=%backup_dir%\upload.log

SET STRDATE=%date:~10,4%-%date:~4,2%-%date:~7,2%
SET STRTIME=%time:~0,2%%time:~3,2%%time:~6,2%

FOR /F "tokens=*" %%a IN ('"systeminfo | find /i "zh-cn""') DO (
	SET STRDATE=%date:~0,10%
	SET STRTIME=%time:~0,2%%time:~3,2%%time:~6,2%
)

SET PREFIX=%STRDATE%-%STRTIME%-

IF "%ftp_host%" NEQ "" (
	ECHO open %ftp_host%>%FTPCMD%
	ECHO user %ftp_user% %ftp_passwd%>>%FTPCMD%
	ECHO prompt off>>%FTPCMD%
	ECHO binary>>%FTPCMD%
	ECHO cd %ftp_path%>>%FTPCMD%
)

FOR /F "tokens=*" %%a IN ('"%MYSQL% -h%db_host% -u%db_user% -p%db_passwd% -e"show databases""') DO (
    IF "%%a" NEQ "information_schema" (
 		IF "%%a" NEQ "performance_schema" (
			IF "%%a" NEQ "Database" (
				SET database=%%a
				SET filesql=%backup_dir%\%PREFIX%%%a.sql
				SET filerar=%backup_dir%\%PREFIX%%%a.rar
				CALL :backup
 			)
		)
	)
)

IF "%ftp_host%" NEQ "" (
	ECHO bye>>%FTPCMD%
    ftp -n<%FTPCMD%>>%FTPLOG%
)

GOTO :EOF

:backup
    %MYSQLDUMP% -h%db_host% -u%db_user% -p%db_passwd% %database%>%filesql%
	IF exist %WINRAR% (
	    %WINRAR% a -k -df -ep %filerar% %filesql%
		IF "%ftp_host%" NEQ "" (
			ECHO put %filerar%>>%FTPCMD%
		)
	) ELSE (
		IF "%ftp_host%" NEQ "" (
			ECHO put %filesql%>>%FTPCMD%
		)
	)
    GOTO :EOF