Using mysqldump to Back Up Your MySQL Database

September 29, 2010

In my professional IT career I've rarely experienced greater rage and frustration than when realizing that an important file or directory has been inadvertently deleted. The feeling is exponentially more nauseating when the data is related to an important client or other mission-critical project, particularly when that data cannot be easily recreated. Unfortunately, such data is plentiful in corporate environments, whether its sales records, carefully managed customer contact data, or archived support forum messages.

Whether you host your website and database internally or you use a hosting provider, it is of paramount concern that you institute a rigorous backup policy in order to avoid such disastrous consequences. If you're using MySQL, several easily implementable options are at your disposal. In this article I'll introduce you to one such option by showing you how to backup your database using MySQL's native mysqldump client.

Introducing mysqldump

MySQL is bundled with a number of command-line utilities, among them mysql (a general purpose utility for interacting with MySQL in many ways, including database schemas and data, users, and configuration variables), mysqladmin (useful for carrying out various administrative tasks), and mysqldump. The mysqldump utility is useful for backing up both your database schemas and data, allowing you to not only perform a blanket backup of all databases residing on a server, but also to selectively backup a single database or even specific tables within a database.

Chances are that when MySQL was installed on your server, the system path was modified so that you will have global command-line access to the clients. Open a terminal window and execute the following command:

%>mysqldump Usage: mysqldump [OPTIONS] database [tables] OR mysqldump [OPTIONS] 
--databases [OPTIONS] DB1 [DB2 DB3...] OR mysqldump [OPTIONS] --all-databases [OPTIONS] 
For more options, use mysqldump --help

The usage instructions which follow identify the different ways in which the mysqldump client can be invoked. For instance, to backup all database schemas and data to a file named backup092210.sql execute the following variation:

%>mysqldump -u root -p --all-databases > backup092210.sql Enter password:

Note how you need to specify a user possessing read access privileges to all databases (in this case, root), in addition to supplying a password at the ensuing prompt. Also, if you're following along with the tutorial by executing these commands on your own development server, take a moment to examine the contents of the backup file. You'll see that it consists of a series of SQL statements which successively drop and recreate each table, followed by a series of insertion statements which recreate the associated data. Additionally, because in the above example we've backed up all databases, you'll also find that the backup file will attempt to create each database if it does not exist, and then switch to that database (via the USE command) before creating the tables and data associated with that database.

To backup a single database named wiki to a file named wiki-backup092210.sql, execute the following command:

%>mysqldump -u root -p wiki > wiki-backup092210.sql Enter password:

Finally, to backup solely the users table in a database named wiki to a file named wikiusers-backup092210.sql, execute the following command:

%>mysqldump -u root -p wiki users > wikiusers-backup092210.sql Enter password:

Refining the Backup Contents

You might occasionally only be interested in backing up a database's schema, or conversely only its data. To backup the schema, pass the --no-data option to mysqldump:

%>mysqldump -u root -p --no-data wiki > wiki-backup092210.sql

To backup only the data, use the --no-create-info option:

%>mysqldump -u root -p --no-create-info wiki > wiki-backup092210.sql

Automating the Backup Process

As the previous examples demonstrated, it's easy to execute the mysqldump command with just a few keystrokes. However with everything else going on during a typical workday, it is practically guaranteed that you will eventually shirk the duty of consistently executing even this simple command. Therefore you'll want to automate the process, a task easily accomplished using the cron utility (available on all Unix-like operating systems). To automate the backup, create an appropriately-named file such as nightly-backup.sh which looks like this:

#!/bin/sh mysqldump -uroot -psecret wiki > /home/backup/sql/wiki-backup-`date +%m%d%Y`.sql

If you execute this script, it will result in the wiki database being backed up to a file named in accordance with the date in which the backup occurred, for instance wiki-backup-092210.sql. Also note how the backup user's name root) and password secret) are passed to mysqldump by appending each to the associated option flag -u and -p, respectively). Because of the obvious security implications, which could arise from a third-party viewing the contents of this file, take care to ensure that its permissions are set appropriately.

Next you'll want to assign this script to a cron job via crontab. To do so, execute the following command:

%>crontab -e

This will open the currently logged-in user's crontab file, creating one if it doesn't already exist. Within this file you'll add the following line to ensure that the backup script runs daily at 3am:

0 3 * * * /home/backup/scripts/nightly-backup.sh

If you're new to crontab syntax, the line's preceding arguments are probably confusing. The five arguments point to the script's minute, hour, day of month, month, and day of week in which the script should execute. Therefore to execute a script every Tuesday at 4:45am, you'll assign the arguments 45 4 * * 3.

With the line inserted, save the file and the task will be immediately scheduled for execution at the specified time. Be sure to check the designated directory the next morning to ensure everything is working properly.

Advanced Backup Solutions

As I mentioned at the beginning of this article, mysqldump is just one of MySQL's 'numerous backup solutions. Notably, I recommend looking into performing incremental backups using MySQL's binary log or even copying data from the "master" MySQL server to a secondary "slave" server using what's referred to as replication.

Conclusion

As this article demonstrated, it is incredibly easy to implement a simple MySQL backup solution, accomplished in mere minutes. If you don't already have an acceptable solution in place, take a moment to do so right now. I guarantee the few minutes you spend doing so will save you dozens of hours, if not your job, should your server falter!

About the Author

Jason Gilmore is founder of the publishing and consulting firm WJGilmore.com. He is the author of several popular books "Easy PHP Websites with the Zend Framework", "Easy PayPal with PHP", and "Beginning PHP and MySQL, Fourth Edition". Follow him on Twitter at @wjgilmore.