Backing Up Your MySQL Databases With MySQLDump
(Page 1 of 3 )

MySQL includes several ways to backup your important data. In this article Mitchell shows us how to backup our databases using the mysqldump utility that comes with MySQL. He looks at several examples including how to backup your database to a file, another server, and even a compressed gzip file.MySQL is one of the most popular database management systems to use when developing interactive web sites that need to utilize persistent data sources. As with all other popular database management systems, MySQL also includes several ways to backup your important data.

In this article I'm going to show you how to backup your databases using the mysqldump utility that comes with MySQL. We will look at several examples of using MySQL dump, including how to backup your database to a file, another server, and even a compressed gzip file.

This article assumes that you have MySQL installed locally on a Windows, Unix or Linux machine and have administrative privileges on that machine. This article also assumes that you have at least a small amount of exposure to MySQL and the SQL language syntax.

Backing Up Your MySQL Databases With MySQLDump - The mysqldump utility
(Page 2 of 3 )

What is mysqldump?

The mysqldump utility is a console driven executable that allows us to 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 be found in c:\mysql\bin on Windows operating systems, and in the /usr/local/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 using the following code at the MySQL console application:

      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 will know that the ">" symbol specifies that we are directing output to a stream, port, or file. For the mysqldump utility, we prepend a ">" to the filename we would like our database to be backed up to. If no path is specified for the file, then it will be created in the current directory.

Now that we're versed with 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.

Example 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 have 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 console application to list them:

show databases;

On my system, MySQL responded with this:

The list of database that exist on my MySQL server

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.




Backing Up Your MySQL Databases With MySQLDump - More examples
(Page 3 of 3 )

Example 2:

mysqldump --opt mydatabase > sql.dump

Specifying the --opt argument when backing 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 to delete 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;

Example 3:

mysqldump --opt mydatabase | mysql --host=localhost -C newdatabase

One excellent feature of the mysqldump utility is that it supports backing up a database from one MySQL server to another with just one command. In the example above, I have chosen to backup the "mydatabase" database to the server named localhost (which is the net bios name of my local machine). 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 of the tables from the "mydatabase" database should be created in a new database on the remote server named "newdatabase".

In the example above I have used localhost as the name of the remote MySQL server to send the backup data to. You can replace this with the net bios name of any other computer on your network, or the I.P. address of any local or remote computer if you want to send the database to a MySQL server over your network or the Internet.

There's one catch to using this method: You must have already created the database on the remote server. In our example we are simply backing up the "mydatabase" database on the same machine, so we would use the following command at the MySQL console application before we ran the mysqldump utility:

create database newdatabase;