Making copies of database content is a common web development task. Sometimes you need to move database tables from a development server to a production server. Other times you might just be making a backup.

In most of these cases, the mysqldump client, which was designed specifically as a database backup program, is an excellent way to dump a database or several databases and transfer the contents to another SQL server or a backup file.

The mysqldump client may be run from the command line of a UNIX-like system or in a shell script. In this article I am going to describe how to use mysqldump to make a backup of a particular database. I will also show you how to add the mysqldump statement to a shell script and set it to create a compressed backup every day.

mysqldump Syntax

The mysqldump client is typically called by typing the client’s name, some options, a database name, more options, and some output information.

I think it helps to see what this actually looks, so here is a complete mysqldump statement.

mysqldump -u root -ppassword magentotest --ignore-table=magentotest.core_config_data -e | gzip > mysqlback/mysql_081311.sql.gz

Notice that I start by specifying the client.

mysqldump

I then provide the username, using the short option -u followed by a space and the username, which in this case is “root.”

-u root

Next, I include an option for the password. This is not required for running the command directly from the prompt, but if I want to run this command from a shell script and have it make automatic backups, I will need it. The format I used is to type the option -p (without any spaces) and then the actual password. Alternatively, I could have typed two hyphens, the word “password,” an equal sign, and the actual password like this: –password=password.

-ppassword

Next, I specify the database that I want to copy, “magentotest” in this example. I used this database last week for my review of the Magento ecommerce platform, so I thought it would serve as a good example for making database backups.

magentotest

If I want to skip any tables in the database, I can do that with the –ignore-table= option. This probably would not make sense for a backup, so you will notice that I am going to drop it from our code after this explanation. But I wanted you to see this option, since it is very helpful for synchronizing databases from one server to another.

--ignore-table=magentotest.core_config_data

The -e option stands for extended insert and should make the dump and restore process faster.

-e

The next section tells mysqldump to output the resulting copy as a gzip (compressed) file in the “mysqlback” directory.

| gzip > mysqlback/mysql_081311.sql.gz

If I run this from the command line, and then look at the contents of the “mysqlback” directory, I learn that my database copy was indeed made.

The mysqldump client copied the database and output it as requested.

Take another look at the mysqldump statement — this time without the specific database and file names — and make sure that you understand what it says.

mysqldump -u username -ppassword database_name -e | gzip > path_to_file/file_name.sql.gz

By the way, you can find a complete list of mysqldump options by typing “man mysqldump” at the command prompt of a UNIX-like server’s terminal.

Servers with MySQL installed include a manual for mysqldump, listing its various options.

Automating a mysqldump Backup

Now that you can make a copy of a database, I want to show you one possible way to automate the process. I am going to do this using a shell script. This script might be stored in a “scripts” directory on your server or in the same directory as your backup. Since I am using the “bash” shell, you will see bash commented at the top.

#! /bin/bash
curdate=`date +"%s"`
mysqldump -u root -ppassword magentotest -e | gzip > mysqlback/mysql_$curdate.sql.gz

In the script, “curdate” is a variable. I have set it to equal the current UNIX time stamp (also called POSTIX time), which is the number of seconds since January 1, 1970. This gives me a unique identifier to place in my file name, so that I can make frequent backups without deleting or overwriting earlier backups. You should notice that when I reference the “curdate” variable in the mysqldump statement, it is proceeded by a “$.” Finally, notice also that the UNIX function _date + “%s” _ is wrapped in backticks — not single quotes.

With the script completed, I needed to adjust the file permissions to make it executable. This took a simple command-line statement.

sudo chmod 755 backup.sh

When I run this script from the command line now, it will make a backup of the specified database. Notice that I include the path, which is just the parent directory in this example.

mysqlback/backup.sh

If I look in the “mysqlback” directory, I can see the copy complete with the time stamp in its name.

The shell script successfully makes a copy of the database. The time stamp in the filename makes it unique.

The final task is to schedule the script to run daily, perhaps at 2 a.m., using a cron job. I recently wrote about the crontab here, in Using Cron Jobs to Schedule Scripts. So without much explanation, I will just demonstrate how to schedule backup.sh.

First open the crontab on your server.

crontab -e

Next, add a line that looks like the following.

0  2 * * * mysqlback/backup.sh

Save the changes and your database will be backed up daily at 2 a.m.

Summing Up

The mysqldump client is a good database backup program that can be used in combination with a shell script and a cron job to automatically back up your databases. There are also plenty of other options, so experiment with mysqldump. If your tables are MyISAM, you may also want to try mysqlhotcopy.

Search Terms

MySQL, command line

Related Articles

• Establish a MySQL Connection in PHP
• Create a Virtual LAMP Development Server
• Using Cron Jobs to Schedule Scripts