«

»

Apr 21

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.

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

That’s it, :-)¬† feel to comment here if you any doubts.

11 comments

Skip to comment form

  1. abaid

    thank you sir

  2. MADHVESH

    Thanks you so much…

  3. Sukhjeet Singh

    Awesome Job

  4. Gabriel Maole

    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)

  5. kaaynaat shaikh

    where to see backup file

    1. admin

      It’s where you specify on backupdir variable
      here that variable (backupdir) is C:\xampp\htdocs\backup

  6. vem

    hello, my db back up successfully but when I open it, no data.its 0KB.

    1. admin

      Was it successful when you run the commands manually ?

    2. michael

      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

      1. Prince

        The backup is successful but there is an empty data, there is nothing in the. Sql file.

  7. Sam

    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

Leave Your Thought Here