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"