Using mysqldump to Back Up Your MySQL Database
September 29, 2010
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:
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
Note how you need to specify a user possessing read access privileges to all databases (in this case,
To backup a single database named
Finally, to backup solely the
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
To backup only the data, use the
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
If you execute this script, it will result in the
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:
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
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.