@echo off
REM This script backups MySQL Databases to remote timestamped target file mysql_databases.sql.gz.
REM Please change the variables in step (1) first before running the script.

REM (0) set up timestamp string
for /f "tokens=2 delims==" %%a in ('wmic OS Get localdatetime /value') do set "dt=%%a"
set "YY=%dt:~2,2%" & set "YYYY=%dt:~0,4%" & set "MM=%dt:~4,2%" & set "DD=%dt:~6,2%"
set "HH=%dt:~8,2%" & set "Min=%dt:~10,2%" & set "Sec=%dt:~12,2%"
set "ISO_TIMESTAMP=%YYYY%-%MM%-%DD%T%HH%:%Min%:%Sec%"

REM (1) set up all backup variables
set JAVA_HOME=\path\to\JavaHome
set COMMANDER_HOME=\path\to\crossftp-all-bin
set MYSQL_USER=username
set MYSQL_PASS=password
set SOURCE_FILE_PATH=C:\Windows\Temp\mysql_databases.sql.%ISO_TIMESTAMP%
set TARGET_SITE_ID=ID
set TARGET_FILE_PATH=/path/to/backup/mysql_databases.sql.%ISO_TIMESTAMP%

REM (2) dump all MySQL databases
echo Dump MySQL databases to file %SOURCE_FILE_PATH%
REM use this command for a database server on localhost. add other options if need be.
mysqldump.exe --opt --user=%MYSQL_USER% --password=%MYSQL_PASS% --host=localhost --port=3306 --all-databases --result-file="%SOURCE_FILE_PATH%"

REM (3) backup the MySQL database dump file
echo Backup %SOURCE_FILE_PATH% to remote file %TARGET_FILE_PATH%
START /D %COMMANDER_HOME% /B /WAIT commander.bat --action copy --sourceSite MyComputer --sourceFile %SOURCE_FILE_PATH% --targetSite %TARGET_SITE_ID% --targetFile %TARGET_FILE_PATH%

REM (4) cleanup tempoary dump file
del %SOURCE_FILE_PATH%

REM (5) show user the result
echo Finished backup %SOURCE_FILE_PATH% to remote file %TARGET_FILE_PATH%
