Backup and Restore Your MySQL Databases From the Command Line

In this tutorial I will show you how to backup and restore your databases using the command line MySQL tool.  I have also provided a really useful batch file, that will automatically rename and zip the backup files, which is absolutely perfect for scheduling to run automatically each day to ensure you maintain a set of backups.  This tutorial is probably most useful for those of you running your MySQL instances on windows dedicated servers, as you will have the correct administrator permissions on the box and remote access to setup task scheduler.

Maintaining good backups are often overlooked and many developers forget about it completely.  Personally I had had some bad past experiences with not making good backups in my early days as a developer and these days I always ensure I have a good backup strategy for all of my projects.  There is nothing worse than working for weeks on a project only to lose some of it due to a drive failure.  Also a key part of any backup strategy that is often overlooked is testing the restore process.  Make sure your files can be uncompressed and restored successfully, before you have to do it in a live data recovery situation.

I would also like to add, that there are other methods of backing up your data, many of which offer much better redundancy and change tracking than shown below.  If you are using MySQL in a business critical scenario, then this option may not be for you.  You should perform some analysis on what kind of backup strategy you need and decide the best way forward.

Prerequisites

1. MySQL Server (Windows Installation)
2. 7Zip (free file compression utility)

How To Backup Your Database From the Command Line

You will need to know your MySQL Server name and have a MySQL User account setup with the correct permissions to perform the backup (such as root).  MySQL offers the ability to dump the database contents in SQL format and the command line utility mysqldump is provided within your MySQL installation to do this.  This will work in any Windows installation of MySQL.

Note that the paths to the MySQL and 7zip software may vary on your own systems.

Batch File Code

d:

cd\backup

c:\progra~1\MySQL\MySQLS~1.0\bin\mysqldump -uroot -ppassword mydb > d:\backup\mydb.sql

c:\progra~1\7-Zip\7z a temp.zip *.sql

rename temp.zip data-%date:~0,2%%date:~3,2%%date:~-4%.zip

del *.sql

ftp -s:"backup.ftp"

The batch file does the following actions

1. change to the backup folder
2. dump the database to a .sql file
3. use 7zip to compress the .sql file into a .zip file
4. rename the zip file to a filename of the current date
5. remove the .sql file
6. ftp the files to a remote ftp server (ftp scripts are out of scope of tutorial)

As mentioned in the introduction, I have used windows task scheduler to launch the batch file overnight when the server traffic is low.   If you need to backup several database simply copy line 3 and paste into a new line below and just change the database name (in my example I used mydb as the database name)

How To Restore

Restoring is quite straightforward, as there is another command tool MySQL.exe which does the opposite of the above.  It takes a .sql file and imports it into an empty database.  

c:\progra~1\MySQL\MySQLS~1.0\bin\mysql mydb -uroot -ppassword < d:\backup\mydb.sql

I use this all the time to take snapshots of my production databases and copy them into my development environment and it works very well for my needs.  As I maintan one file per day, this gives me enough data to restore to a close enough point in time should my hardware suffer any failures, however I take a calculate risk that any data saved that day will be lost in the event of data loss.