Automatic compressed mysql backups using batch file and windows task scheduler

[irongut_av]

If you do not have an automated MySQL backup on windows and would like to set them up, here is a good starting point:


Create a batch file (example: db_backup.bat) and open it up in your favorite editor. Paste the following contents below, replacing the directory structure, dbname, user and password as needed.

@echo off
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 "fullstamp=%YYYY%-%MM%-%DD%_%HH%-%Min%-%Sec%"

"C:\Program Files\MariaDB 10.0\bin\mysqldump.exe" -uroot -ppassword peq > C:\eqemu\backups\peq_%fullstamp%.sql

"C:\Program Files (x86)\WinRAR\rar.exe" a -df C:\eqemu\backups\peq_%fullstamp%.rar C:\eqemu\backups\peq_%fullstamp%.sql

Save the file and test it. If the directory location is correct, as well as your MySQL credentials, you should have a compressed backup of your database in the EQEMU/backups folder. Note: the .sql file generated by the MySQL dump will be deleted after being compressed because of the -df option for rar.exe

Next, open up the windows task scheduler and create a new task that calls this batch file. Set the schedule preference (daily, hourly, etc) and starting time.