MySQL database FAQ: How do I backup (back up) a MySQL database?
I can’t speak about backing up MySQL databases that are modified twenty-four hours a day seven days a week, but on all the MySQL databases I currently work with, there are always times when I can guarantee that there won’t be any SQL INSERTs, DELETEs, or UPDATEs occurring, so I find that it’s really easy to perform a MySQL backup using the mysqldump command-line utility program. Here’s how it works.Back to top
Create a MySQL backup with mysqldump
I was just working on a Drupal database, and decided that I wanted to make a backup of Drupal’s MySQL database before I did something that might screw up the database (converting a WordPress database to a Drupal database). So, I made a backup of my current MySQL Drupal database using the following
mysqldump --opt --user=root --password drupaldb > drupaldb-20090505.sql
Note that in this command,
drupaldb is the name of my database, not the password. The
--password argument just tells MySQL to prompt me for the password, because I don’t want to type it right here on the command line, where it would be visible to anyone who can see the history of my commands. So, immediately after I enter that command, I’m prompted to enter the MySQL
root user password, like this:
Enter password: _
Once I enter that password, my
drupaldb database is dumped to a file in the current directory named
A slightly different mysqldump syntax
Note that you can also enter your
mysqldump command as shown next, which is equivalent to the previous command:
mysqldump --opt -u root -p drupaldb > drupaldb-20090505.sql
In both of these examples, the
20090505 portion of the filename just indicates today’s date, May 5, 2009. (Happy Cinco de Mayo. :)
MySQL backup - The general form of mysqldump
That was one specific example of the
mysqldump command. The general form of the backup command is shown here:
mysqldump --opt -u username -p database-name > backup-file-name
root, if you know the
rootpassword, or it can be the name of the MySQL user that owns your database.
database-nameis the name of your database, which in my case was
backup-file-nameis the name of the backup file you want to create. This will be a plain text file that the
mysqldumpcommand creates for you.
MySQL database backup shell script
I just created a new MySQL database backup (dump) shell script to automate MySQL backups. In addition to creating this shell script, I've added new MySQL command line options not shown in this example. If you need to use additional mysqldump command line options, see that article for more information.
Always test your backups
As noted above, the backup file that is created by
mysqldump is a plain text file, which you can view in a text editor. I encourage you to review that file, just to get comfortable with the format of the commands, and to understand more deeply how the process works.
But much better than that, I recommend taking that backup file to another MySQL server and making sure you can restore it on that server. I learned this lesson a long time ago: If you can't restore a backup file, it's not going to do much good for you(!).
Of course once you have made a MySQL backup you'll also want to know how to restore your backup. For information on that process, see my MySQL restore - How to restore a MySQL database backup.Back to top