How to backup your MySQL database
Visit the MySQL site to find out more, and to download the latest version.
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).
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.
- Create a new file C:\MySQL_Backup.cmd
- Edit it as follows...
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
PING 220.127.116.11 -n 1 -w 5000 >NUL
Here's what's happening
- @echo off - This will clear the Command Prompt screen
- echo Running dump... - This will print this message on the screen.
- Our backup script will now run.
- Note the speech-marks around the password.
- echo Done! - Again this is to print a message.
- PING 18.104.22.168 -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.
- 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.