Unlike on Linux, when MySQL is running on Windows, most of sys-admins including myself 🙂 found that backup MySQL Databases on Windows is little bit hard. When trying to automate it, then it would definitely become challenge . However there are lots of free and commercial tools are available to automate MySQL backup process on windows. Here we are going to discus how to achieve same using simple windows batch script. Later we discuss automate the batch script using Windows task scheduler
01) Create batch file
Open notepad and save following script as batch file (.bat) and make sure to change all SET parameters as you need. If you install XAMPP then mysqldump.exe location would be similar as below otherwise you need to change it.
For /f “tokens=2-4 delims=/ ” %%a in (‘date /t’) do (set mydate=%%c-%%a-%%b)
For /f “tokens=1-2 delims=/:” %%a in (“%TIME%”) do (set mytime=%%a%%b)
C:\xampp\mysql\bin\mysqldump.exe -u %mysqlusername% -p%mysqlpassword% -v %database% > %backupdir%\%database%_%mydate%_%mytime%_.sql
02) Automate the MySQL Backup process
i) Open task scheduler
Open a command prompt. To open a command prompt, click Start , click All Programs , click Accessories , and then click Command Prompt .
At the command prompt, type Taskschd.msc .
For alternative options refer this :- https://technet.microsoft.com/en-us/library/cc721931.aspx
ii) Create New Task
Task Scheduler – Create Task
Click on “Create Task…” from right hand side. it will open up “create task” sub windows
please make sure to select “Run whether user is logged on or not” and tick “Run with highest privileges“. You may can change user but
recommend to select user with admin privileges , later you may need to provide credentials of that user.
iii) Schedule the time
From “Triggers” tab select how often back process should happen and it’s time
iv) Set backup process script
From the “Actions” tab select previously saved bat file.
v) Click “OK” and save the task