Diary of A Webmaster Part 4 - Backing Up With MySQLDump

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 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.