Automatic Mysql DB Backup Scripts

 Automatic Mysql DB Backup Scripts With Compress and auto Deletion of Old Archive Files

:: UPDATE 3.30.2012  Added error logging to help trouble shoot databases backup errors.   --log-error="c:\MySQLBackups\backupfiles\dumperrors.txt"

::If the time is less than two digits insert a zero so there is no space to break the filename

set year=%DATE:~10,4%

set day=%DATE:~7,2%

set mnt=%DATE:~4,2%

set hr=%TIME:~0,2%

set min=%TIME:~3,2%

IF %day% LSS 10 SET day=0%day:~1,1%

IF %mnt% LSS 10 SET mnt=0%mnt:~1,1%

IF %hr% LSS 10 SET hr=0%hr:~1,1%

IF %min% LSS 10 SET min=0%min:~1,1%

set backupdate=%year%-%day%-%mnt%-%hr%-%min%

:: MySQl DB user

set dbuser=aaa

:: MySQl DB users password

set dbpass=yyy

:: Switch to the MySQL data directory and collect the folder names

pushd "C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.1\data"

:: Loop through the folders and use the fnames for the sql filenames, collects all databases automatically this way

echo "hello"

echo "Pass each name to mysqldump.exe and output an individual .sql file for each"

FOR /D %%F IN (*) DO (

"C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqldump.exe" --user=%dbuser% --password=%dbpass% --databases --log-error="c:\MySQLBackups\backupfiles\dumperrors.txt" %%F > "c:\MySQLBackups\backupfiles\%%F.%backupdate%.sql"

)

echo "Zipping all files ending in .sql in the folder"

"c:\MySQLBackups\zip\7zG.exe" a -tzip "c:\MySQLBackups\backupfiles\FullBackup.%backupdate%.zip" "c:\MySQLBackups\backupfiles\*.sql"

echo "Deleting all the files ending in .sql only"

del "c:\MySQLBackups\backupfiles\*.sql"

echo "Deleting zip files older than 30 days now"

Forfiles -p c:\MySQLBackups\backupfiles\ -s -m *.* -d -30 -c "cmd /c del /q @path"

echo "All done, pretty slick eh"