Automatically Backup MySQL Databases on Windows

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.

@echo off

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)

SET backupdir=C:\xampp\htdocs\backup

SET mysqlusername=your_user

SET mysqlpassword=your_pass

SET database=database_name

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

https://www.tectut.com/2016/04/automatically-backup-mysql-databases-on-windows/