By: Mitchell Harper

February 27th, 2002

Reader Rating: 8.5

MySQL is one of the most popular database management systems for the development of interactive Websites that need to utilize persistent data sources. As with all other popular database management systems, MySQL offers several methods to backup your important data. In this article we'll look at how to backup your databases using the mysqldump utility that comes with MySQL. We'll review several examples using mysqldump, including the backup of your database to a file, another server, and even a compressed gzip file.

I'll assume that you have MySQL installed locally on a Windows, Unix or Linux machine, and that you have administrative privileges on that machine. I'll also assume that you've had at least a small amount of exposure to MySQL and the SQL language syntax.

The mysqldump Utility

What is mysqldump?

The mysqldump utility is a console-driven executable that lets us specify a host of options to backup a database to an external resource, such as a file, or even a completely different MySQL server running on the other side of the world!

I'm using the word "backup" rather loosely here, because MySQL doesn't actually backup our data per se. Rather, it creates a set of "CREATE TABLE" and "INSERT INTO" commands that can be executed against a MySQL server to re-create our database(s).

The mysqldump utility can usually be found in c:\mysql\bin on Windows operating systems, and in the /usr/local/mysql/bin directory on Unix/Linux systems where MySQL is installed. The mysqldump utility accepts several command-line arguments that you can use to change the way your databases are backed up.

In its simplest form, the mysqldump utility can be used like this:

mysqldump ---user [user name] ---password=[password]  
[database name] > [dump file]

Let's take a look at each of the arguments that can be passed to the mysqldump utility, as shown above:

--user [user name]: The ---user flag followed by a valid MySQL username tells MySQL the username of the account that we want to use to perform the database dump. MySQL user accounts are stored in the "user" table of the "mysql" database. You can view a list of users and their permissions for your MySQL server by inserting the following code at the MySQL command prompt:

use mysql;
select * from user;

--password=[password]: The password for the user account mentioned above.

[database name]: The name of the database that we would like the mysqldump utility to backup. Instead of specifying one single database name, we could use either --databases or --all-databases to backup every single database on our MySQL server.

> [dump file]: If you're familiar with DOS and batch files, then you'll know that the ">" symbol specifies that we're directing output to a stream, port, or file. For the mysqldump utility, we prepend a ">" to the filename to which we would like our database to be backed up. If no path is specified for the file, then it will be created in the current directory.

Now that we're versed in the basic arguments that can be passed to the mysqldump utility, let's take a look at five different ways to use the mysqldump utility to backup our databases.

Method 1

mysqldump ---user admin ---password=password mydatabase > sql.dump

In the example above, we're specifying that MySQL should check the grants for the user account of the "admin" user with a password of "password". I'm running MySQL on Windows 2000, and these are the default credentials for the admin user account. I've chosen to backup the database named "mydatabase" into the file sql.dump.

If you're not sure what the names of your databases are, then use the following command at the MySQL command prompt to list them:

show databases;

On my system, MySQL responded with this:
Here's a snippet of my database dump using the mysqldump utility as described in the example above:

#
# Table structure for table 'tbl_contactemails'
#

CREATE TABLE tbl_contactemails (
pk_ceId int(11) NOT NULL auto_increment,
ceEmail varchar(250) NOT NULL default '',
ceType int(11) default NULL,
PRIMARY KEY (pk_ceId),
UNIQUE KEY id (pk_ceId)
) TYPE=MyISAM;

#
# Dumping data for table 'tbl_contactemails'
#

INSERT INTO tbl_contactemails VALUES (18,'mitchell@devarticles.com',1);
INSERT INTO tbl_contactemails VALUES (17,'mitchell_harper@hotmail.com',1);
INSERT INTO tbl_contactemails VALUES (16,'mytch@dingoblue.net.au',1);

As you can see, the mysqldump command has taken the design of my tbl_contactemails table (which exists as part of the "mydatabase" database that I chose to back up) and turned it into a CREATE TABLE query, which (when imported back into MySQL) will re-create the tbl_contactemails table if it needs to.

Also notice the three INSERT INTO statements, which add rows of data to the tbl_contactemails table. I had three email addresses in my contact emails table, and when this data is restored, MySQL will execute these insert commands directly against my tbl_contactemails database, to add the rows back into the table.

Method 2

mysqldump --opt mydatabase > sql.dump

Specifying the --opt argument when we back up our database should theoretically give us the fastest possible dump for reading back into MySQL server (the "opt" stands for optimize). When we specify the --opt argument, the mysqldump utility creates a more sophisticated set of dump commands, which includes the "DROP TABLE IF EXISTS" statement. This deletes the table from the database if it already exists when the dump file is being used to restore the database. The dump also includes several table locking statements.

Here's a sample of the sql.dump file that was generated when I backed up the same database with the ---opt argument:

#  
# Table structure for table 'tbl_contactemails'  
#  
 
DROP TABLE IF EXISTS tbl_contactemails;  
CREATE TABLE tbl_contactemails (  
pk_ceId int(11) NOT NULL auto_increment,  
ceEmail varchar(250) NOT NULL default '',  
ceType int(11) default NULL,  
PRIMARY KEY (pk_ceId),  
UNIQUE KEY id (pk_ceId)  
) TYPE=MyISAM;  
 
#  
# Dumping data for table 'tbl_contactemails'  
#  
 
LOCK TABLES tbl_contactemails WRITE;  
INSERT INTO tbl_contactemails VALUES (18,'mitchell@devarticles.com',1),  
(17,'mitchell_harper@hotmail.com',1),(16,'mytch@dingoblue.net.au',1);  
UNLOCK TABLES;

Method 3

mysqldump --host=host1 --opt mydatabase | mysql --host=host2  
-C newdatabase

One excellent feature of the mysqldump utility is that it allows you to back up a database from one MySQL server to another with a single command. In the example above, I chose to backup the "mydatabase" database from the server named host1 to the server named host2. Both these servers must allow MySQL connections from wherever I run this command, or the procedure won't work. I have used the --C argument to tell the mysqldump utility to enforce data compression between my MySQL server and the destination server, if they both support it. Lastly, I have specified that all the tables from the "mydatabase" database on host1 should be created in a new database on the host2 server named "newdatabase".

In the example above I used host2 as the name of the remote MySQL server that I'll send the backup data to. You can replace this with the hostname or IP address of any other computer on your network or the Internet.

Note that there is one catch to using this method: the target database must already exist on the remote server. In our example, we would use the following command at the MySQL console application before we ran the mysqldump utility:

create database newdatabase;

Method 4

mysqldump ---user admin --password=password mydatabase |  
gzip > /usr/local/mydatabase.gz

Yet another great feature of MySQL is the ability to backup databases to gzipped (Linux's answer to ZIP) files. Note that this can be done on Unix/Linux servers only. As you can see in the example above, I have specified the name of the database I would like to backup (mydatabase), followed by a pipe, the insertion symbol, the "gzip" keyword, and finally the path to the gzip file that mysqldump should stream the backup to.

Method 5

mysqldump --no-data --databases mydatabase1 mydatabase2 mydatabase3    
> sql.dump

The mysqldump command above uses two new arguments: --no-data and ---databases. The ---no-data argument tells MySQL to only dump the structure of a database and not any actual data. The ---databases argument is used when we want to backup more than one database. In the example above, I have backed up three databases named "mydatabase1", "mydatabase2", and "mydatabase3" respectively. The dump will be saved into a file named sql.dump. Here's what an extract from the sql.dump file looks like:

#  
# Table structure for table 'tbl_tempzips'  
#  
 
CREATE TABLE tbl_tempzips (  
pk_zId int(11) NOT NULL auto_increment,  
zBlob longblob NOT NULL,  
PRIMARY KEY (pk_zId),  
UNIQUE KEY ID (pk_zId)  
) TYPE=MyISAM;  
 
#  
# Table structure for table 'tbl_topics'  
#  
 
CREATE TABLE tbl_topics (  
pk_tId int(11) NOT NULL auto_increment,  
tName varchar(50) NOT NULL default '',  
tType smallint(4) NOT NULL default '0',  
PRIMARY KEY (pk_tId),  
UNIQUE KEY ID (pk_tId)  
) TYPE=MyISAM;

Notice how there are no "INSERT INTO" queries in the dump, and only "CREATE TABLE" commands?

That wraps up our look at backing up MySQL databases using the mysqldump utility. As I mentioned earlier, there are quite a few other arguments that can be passed to the mysqldump utility. You should see the mysqldump reference page [1] over at MySQL.com for more information and examples of these arguments.

Restoring a backup

It's all fine and dandy to have used the mysqldump utility to export our database backups, but how do we actually read this data back into MySQL to re-construct the databases, tables and records that we initially backed up?

Quite easily, actually. All you have to do is call the MySQL console application with a couple of arguments. Here's how to pass the arguments to MySQL:

mysql [database name] < [backup file name]

These arguments should be fairly self-explanatory. The "[database name]" argument specifies the name of the database to restore, and the "[backup file name]" argument should specify the full path and filename to the file generated by mysqldump, such as sql.dump. You should also specify your MySQL username and password as usual.

So if I wanted to restore the database named mydatabase from the file named sql.dump, I'd run mysql like this:

mysql mydatabase < sql.dump

Conclusion

The mysqldump utility is one of many ways to backup your MySQL databases. It has a wide range of options, is flexible, fast, and can perform advanced backups to remote servers, flat files, or even backup your databases as well-formed XML files!

If you run a Website that uses a MySQL database, then you should make sure that your database is being backed up on a daily basis. If it isn't, then consider setting up a cron job (on Linux/Unix), or an NT service (on Windows) to perform regular backups using the mysqldump utility as described in this article.

I was involved with a site a while back where another programmer worked on the database, and I worked on the interface and usability. He forgot to create a database backup script and always "forgot" to get around to it. Ten months later that MySQL server caught on fire and all of the data (some 1,000,000 rows) was lost.

If only that programmer had taken an extra hour to create a backup script using the mysqldump utility, that site would still be around today. The lesson: it really does pay to take the extra time to make sure your database is being properly and effectively backed up to another server, or to a portable medium such as zip disk, tape, or removable hard drive.

[1] http://www.mysql.com/doc/m/y/mysqldump.html