Whether you host your website and database internally or you use a hosting provider, it is of paramount importance that you institute a rigorous backup policy. If you’re using MySQL, several easily implementable options are at your disposal. This article introduces one such option by showing you how to backup your database using MySQL’s native mysqldump client.
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.
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
%>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
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
are passed to mysqldump by appending each to the associated option
-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:
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.
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.