Automatically backup mysql database using a batch file

In this tutorial I’m going to show you how you can backup a mysql database using a batch file. Okay! I’ll tell you the truth, I basically made a similar tutorial maybe 5 months ago:

How to automate mysql database backup

Ways on how to backup mysql database

You may want to read on that first, especially the latter one. Because I don’t really want to repeat taking screenshots on how to use the task scheduler on Windows. Why you ask? Because It freezes on me.

Okay, I’ll make this quick. Here’s the code that you will need:

@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 /t')

do (set mytime=%%a%%b)

mysqldump -u root -p1234 modal_db -B>

c:wampdb_backupsmodal_dbdb_name%mydate%_%mytime%.sql

You only need the last 2 lines to backup your database into the directory that you specified. But the first 3 lines, identifies the current date and time for you. So you’ll even know what time the backup was created based on the filename.
There are few things you need to modify though:

database user – root

path - c:wampdb_backupsmodal_db

password – 1234

database name – modal_db

If all those matches your current configuration. And wampserver is up and running. You can just launch the batch file (.bat) and the mysql dump file will be created. Just use the power of windows task scheduler to automate this task.

Your output filename will look like this:

db_name2011-06-15_0905.sql

2 thoughts on “Automatically backup mysql database using a batch file

  1. Hi Wern,
    I have a problem in that when I run the batch file it asks for the password then I have to hit return before script continues, MySql database doesn’t have a password set?

    Is there any way around this
    Regards
    Harrogateweb

    • That’s a built in security feature, it ask you for a password if the password is left blank on the batch file. So the best way to get through this is to give mysql database a password. You can do this through phpmyadmin or the mysql console. Then just edit the config.inc.php file inside C:wampappsphpmyadmin3.3.9 and change this line:
      $cfg[‘Servers’][$i][‘password’] = ‘yourpasswordhere’;
      Then restart all services in the wampserver tray icon. If you’re using it.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s