How to backup your MySQL database

mysqlOf course having a backup of your important MySQL data is very important. This article introduces a basic method for automating the backup of your MySQL databases.

Visit the MySQL site to find out more, and to download the latest version.


Getting started

To get started, you will need to know where MySQL is installed on your Windows Server (eg. C:\Program Files\MySQL\MySQL Server 5.1).

 

Database Dump

We will now create our first dump of a database. In a Command Prompt, run the following...

"C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqldump" -u UserName --password=***** --result-file="C:\DB_Backups\backup.%DATE:~0,2%.sql" DatabaseName

This command breaks down as follows...

  • "path-to-MySQL-Installation\mysqldump" - This is the MySQL Dump command used for backing up.
  • -u UserName - This is your MySQL username
  • --password=***** - Replace the ***** with your MySQL password
  • --result-file="C:\DB_Backups\backup.%DATE:~0,2%.sql" - The resultant file will look like backup.27.sql, if today's date is the 27th.

Using this command you will be able to create a backup for every day of the month, which means you'll be able to go back upto 30 days.

 

Create a batch file

Using the command that was created above, we can now create a batch file.

  1. Create a new file C:\MySQL_Backup.cmd
  2. Edit it as follows...

@echo off

echo Running dump...

"C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqldump" -u username --password="*****" --result-file="C:\DB_Backups\backup.%DATE:~0,2%.sql" DatabaseName

echo Done!

PING 1.1.1.1 -n 1 -w 5000 >NUL

Here's what's happening

  1. @echo off - This will clear the Command Prompt screen
  2. echo Running dump... - This will print this message on the screen.
  3. Our backup script will now run.
  4. Note the speech-marks around the password.
  5. echo Done! - Again this is to print a message.
  6. PING 1.1.1.1 -n 1 -w 5000 >NUL - This will allow the last message to be visible for 5 seconds.

 

Automating the backup

Linux servers have cron, and Windows servers have at.  For this Windows example we will run a backup at 2am every weekday.

  1. In a Command Prompt run the following...

at 02:00 /every:M,T,W,Th,F,S,Su C:\MySQL-Backup.cmd

You should get a message saying "Added a new job, with JobID=x".  In the Command Prompt, type at to get a summary of existing scheduled tasks.  To find out more about scheduling, have a look at the AT Command for Windows.

 

Thanks for visiting.