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.
|
1 2 3 4 5 6 7 8 9 10 |
@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 mysqluername=root SET mysqlpassword=somepassword SET database=dbname C:\xampp\mysql\bin\mysqldump.exe -uroot -pPASSWORD %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
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
That’s it, 🙂 feel to comment here if you any doubts.
16 comments
Skip to comment form ↓
abaid
November 16, 2016 at 3:58 pm (UTC 5.5) Link to this comment
thank you sir
MADHVESH
July 26, 2017 at 11:02 am (UTC 5.5) Link to this comment
Thanks you so much…
Sukhjeet Singh
September 4, 2017 at 5:11 pm (UTC 5.5) Link to this comment
Awesome Job
Gabriel Maole
September 11, 2017 at 11:23 am (UTC 5.5) Link to this comment
Great job. but about time, Personally I prefer to use ‘time \t’ approach, because when the time is less then 10, the sql file is not correctly named (neither time and sql extension)
kaaynaat shaikh
December 6, 2017 at 6:02 pm (UTC 5.5) Link to this comment
where to see backup file
admin
December 6, 2017 at 9:41 pm (UTC 5.5) Link to this comment
It’s where you specify on backupdir variable
here that variable (backupdir) is C:\xampp\htdocs\backup
vem
January 3, 2018 at 10:46 am (UTC 5.5) Link to this comment
hello, my db back up successfully but when I open it, no data.its 0KB.
admin
January 9, 2018 at 5:10 pm (UTC 5.5) Link to this comment
Was it successful when you run the commands manually ?
michael
January 24, 2018 at 3:34 pm (UTC 5.5) Link to this comment
my problem same, db back up successfully but when I open it, no data.its 0K
i try run the command manualy via task scheduller
Prince
May 19, 2018 at 10:09 pm (UTC 5.5) Link to this comment
The backup is successful but there is an empty data, there is nothing in the. Sql file.
Sam
March 16, 2018 at 5:01 am (UTC 5.5) Link to this comment
nice job … just bit clean up for last line
@echo off
set datetime=%date:~10,4%%date:~4,2%%date:~7,2%_%time:~0,2%%time:~3,2%%time:~6,2%
SET backupdir=C:\xampp\htdocs\backup
SET mysqluername=root
SET mysqlpassword=somepassword
SET database=dbname
“C:\xampp\mysql\bin\mysqldump.exe” -u %mysqluername% -p%mysqlpassword% %database%> %backupdir%\%database%_%datetime%.sql
codeview
August 20, 2018 at 2:59 am (UTC 5.5) Link to this comment
Thank you, Sam! This worked for me after the OP code produced an empty .sql file like most others noted.
sreenu aucc
July 10, 2018 at 10:56 am (UTC 5.5) Link to this comment
The backup is successful but there is an empty data, there is nothing in the. Sql file.
Trung Nguyen
July 24, 2018 at 3:40 pm (UTC 5.5) Link to this comment
My command perfect for everybody below:
@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=D:\CUT\OUTPUT_HDO
SET mysqluername=your_user
SET mysqlpassword=your_pass
SET database=database_name
C:\xampp\mysql\bin\mysqldump.exe -u %mysqluername% -p %mysqlpassword% -v %database% > %backupdir%\%database%_%mydate%_%mytime%_.sql
Balaji L
February 12, 2019 at 4:11 pm (UTC 5.5) Link to this comment
I am getting below error when imported backup sql file. but table data populated correctly. can you please tell why i am getting below error.
#1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] –datab’ at line 1
Michael
October 20, 2022 at 7:30 pm (UTC 5.5) Link to this comment
@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:\backup
SET mysqluername=username
SET mysqlpassword=password
SET database=database
C:\xampp\mysql\bin\mysqldump.exe -u %mysqluername% -p%mysqlpassword% -v %database% > %backupdir%\%database%_%mydate%_%mytime%_.sql
If you simply copy and paste these commands, you must fix the quotation marks as it seems this website replaces them with ‘pretty’ characters