The mysqldump Program

The program mysqldump will send to the screen a text representation (a "dump") of database and table structures, and optionally all of the data in those databases/tables. This dump can easily be redirected to a text file with a shell command like:

     mysqldump ...parameters... > dump001.txt

The format of the dump will be commands that, when run, will recreate that database or table, as we’ll see below.

Dumping just the table structure is handy for when you want to recreate the structure, say on another machine. By dumping the data too, you have a complete backup of your database, or of just the table(s) you specify.

This is handy either just for your own archival purposes, or for sending to others. For example, I used mysqldump to dump my ‘library’ database table’s structure and all its data so that you can try out the examples in this book with it.

On the database we have been working with, which contains only a single table, we can do something like this:

mysqldump -u bookuser -p -d PerlBook

Once we ran the above command, we would get prompted for the password. Entering the password causes the following to be displayed on the screen.

# MySQL dump 8.16 # # Host: localhost    Database: PerlBook #-------------------------------------------------------- # Server version        3.23.47 # # Table structure for table ‘library’ # CREATE TABLE library (   isbn varchar(20) default NULL,   title varchar(255) default NULL,   author varchar(255) default NULL,   price varchar(20) default NULL,   format varchar(50) default NULL,   publisher varchar(255) default NULL,   pubdate varchar(50) default NULL,   notes text ) TYPE=MyISAM;

This code can be cut-and-pasted into the mysql> command line, and it creates a copy of the table. Printing it directly to the screen is not usually the best idea, so typically you do something like this:

mysqldump -u bookuser -p -d PerlBook > table_structure.txt 

The above command causes the preceding table information to be sent to a file called table_structure.txt. If you want the table structure plus all of the data in the table, simply remove the -d from the previous command. The -d means no data.

If someone sends you a file he or she has produced by using the mysqldump command and you want to add the file to a new database, you can simply pipe it into the database like this:

mysql –ubookuser –p DataBaseName < structure.txt

MySQL asks for the password; if it is entered correctly, all of the data in structure.txt is imported into the database that has been specified.

mysqldump is, in my opinion, under-appreciated. It can provide you with quick and simple backups, and it allows people to easily share database structures and data.

Note 

MySQL has many commands and utilities beyond the basic ones that were covered here. The documentation provided at the MySQL Web site is excellent, however. Use it if you want to gain an even greater understanding of how MySQL works.