@echo off
:: USE THIS SCRIPT AT YOUR OWN RISK!
:: Set some variables
set backupdir="C:\Backup\MySQL\Data"
set mysqldir="C:\Program Files\MySQL\MySQL Server 5.1"
set mysqldatadir="C:\ProgramData\MySQL\MySQL Server 5.1\data"
set logdir="C:\Backup\MySQL\Logs"
set dbuser=root
set dbpass=YOURROOTPASSWORDHERE
set zip="C:\gzip"
set externalserver=THEHOSTNAMEORIPOFYOURDEVSERVER
set externalserveruser=FTPUSERNAME
set externalserverpass=FTPPASSWORD
set externalserverfolder=/YOUR/BACKUP/FOLDER

set endtime=0


:DODIR

:: Do DIR of backupdir to establish network
DIR %backupdir%

:GETTIME

:: get the date and then parse it into variables
for /F "tokens=1-3 delims=/ " %%i in ('date /t') do (
set dd=%%i
set mm=%%j
set yy=%%k
)

:: get the time and then parse it into variables
for /F "tokens=5-8 delims=:. " %%i in ('echo.^| time ^| find "current" ') do (
set hh=%%i
set ii=%%j
set ss=%%k
)

:: If this is the second time through then go to the end of the file
if "%endtime%"=="1" goto END

:: Create the filename suffix
set fn=_%yy%-%mm%-%dd%_%hh%-%ii%-%ss%

:: Switch to the data directory to enumerate the folders
pushd %mysqldatadir%

:: Write to the log file
echo Beginning mysqldump Process > %logdir%\MySQL_LOG%fn%.txt
echo Start Time = %yy%-%mm%-%dd% %hh%:%ii%:%ss% >> %logdir%\MySQL_LOG%fn%.txt
echo --------------------------- >> %logdir%\MySQL_LOG%fn%.txt
echo. >> %logdir%\MySQL_LOG%fn%.txt

:: Loop through the data structure in the data dir to get the database names
for /d %%f in (*) do (

:: Create the backup sub-directory if it does not exist
if not exist %backupdir%\%%f\ (
echo Making Directory %%f
echo Making Directory %%f >> %logdir%\MySQL_LOG%fn%.txt
mkdir %backupdir%\%%f
) else (
echo Directory %%f Exists
echo Directory %%f Exists >> %logdir%\MySQL_LOG%fn%.txt
)

:: Delete previous backups
echo Deleting old backups
pushd %backupdir%\%%f
del *.gz

:: Run mysqldump on each database and compress the data by piping through gZip
echo Backing up database %%f%fn%.sql.gz
echo Backing up database %%f%fn%.sql.gz >> %logdir%\MySQL_LOG%fn%.txt
%mysqldir%\bin\mysqldump.exe --user=%dbuser% --password=%dbpass% --databases %%f --opt --quote-names --allow-keywords --complete-insert | %zip%\gzip.exe -9 > %backupdir%\%%f\%%f%fn%.sql.gz
echo Done...
echo Done... >> %logdir%\MySQL_LOG%fn%.txt
)

:: Write to the log file
echo. >> %logdir%\MySQL_LOG%fn%.txt
echo --------------------------- >> %logdir%\MySQL_LOG%fn%.txt
echo Analyzing, optimising and repairing databases
echo Analyzing, optimising and repairing databases >> %logdir%\MySQL_LOG%fn%.txt
%mysqldir%\bin\mysqlcheck.exe --all-databases --medium-check --auto-repair --force --use-frm --optimize --analyze --check-only-changed --user=%dbuser% --password=%dbpass%
echo Done...
echo Done... >> %logdir%\MySQL_LOG%fn%.txt

:: Go back and get the end time for the script
set endtime=1
goto :GETTIME

:END
:: Write to the log file
echo. >> %logdir%\MySQL_LOG%fn%.txt
echo --------------------------- >> %logdir%\MySQL_LOG%fn%.txt
echo MySQLDump Process Finished >> %logdir%\MySQL_LOG%fn%.txt
echo End Time = %yy%-%mm%-%dd% %hh%:%ii%:%ss% >> %logdir%\MySQL_LOG%fn%.txt
echo. >> %logdir%\MySQL_LOG%fn%.txt

:: Return to the scripts dir
popd

:: FTP backups to external server

:: Move to mysql data directory to get names of databases
pushd %mysqldatadir%

:: Loop through databases
for /d %%f in (*) do (

:: Move to backup directory for this database
pushd %backupdir%\%%f

:: Create the temporary FTP script file
> script.ftp ECHO %externalserveruser%
>>script.ftp ECHO %externalserverpass%
>>script.ftp ECHO cd %externalserverfolder%
>>script.ftp ECHO prompt n
>>script.ftp ECHO mput *.gz
>>script.ftp ECHO quit

:: FTP Database to external server
FTP -v -s:script.ftp %externalserver%

:: Delete temporary script file
DEL script.ftp

)