Exporting Table Contents or Definitions in SQL Format

10.16.1 Problem

You want to export tables or databases as SQL statements to make them easier to import later.

10.16.2 Solution

Use the mysqldump program without the --tab option.

10.16.3 Discussion

As discussed in Recipe 10.15, mysqldump causes the MySQL server to write tables as raw datafiles on the server host when it's invoked with the --tab option. If you omit the --tab, the server formats the table records as the INSERT statements and returns them to mysqldump. You can also generate the CREATE TABLE statement for each table. This provides a convenient form of output that you can capture in a file and use later to recreate a table or tables. It's common to use such dump files as backups or for copying tables to another MySQL server. This section discusses how to save dump output in a file; Recipe 10.17 shows how to send it directly to another server over the network.

To export a table in SQL format to a file, use a command like this:

% mysqldump cookbook states > dump.txt

That creates an output file dump.txt that contains both the CREATE TABLE statement and a set of INSERT statements:

# MySQL dump 8.16
#
# Host: localhost Database: cookbook
#--------------------------------------------------------
# Server version 3.23.46-log

#
# Table structure for table 'states'
#

CREATE TABLE states (
 name varchar(30) NOT NULL default '',
 abbrev char(2) NOT NULL default '',
 statehood date default NULL,
 pop bigint(20) default NULL,
 PRIMARY KEY (abbrev)
) TYPE=MyISAM;

#
# Dumping data for table 'states'
#

INSERT INTO states VALUES ('Alaska','AK','1959-01-03',550043);
INSERT INTO states VALUES ('Alabama','AL','1819-12-14',4040587);
INSERT INTO states VALUES ('Arkansas','AR','1836-06-15',2350725);
INSERT INTO states VALUES ('Arizona','AZ','1912-02-14',3665228);
INSERT INTO states VALUES ('California','CA','1850-09-09',29760021);
INSERT INTO states VALUES ('Colorado','CO','1876-08-01',3294394);
...

To dump multiple tables, name them all following the database name argument. To dump an entire database, don't name any tables after the database. If you want to dump all tables in all databases, invoke mysqldump like this:

% mysqldump --all-databases > dump.txt

In this case, the output file also will include CREATE DATABASE and USE db_name statements at appropriate places so that when you read in the file later, each table will be created in the proper database. The --all-databases option is available as of MySQL 3.23.12.

Other options are available to control the output format:

--no-create-info

Suppress the CREATE TABLE statements. Use this option when you want to dump table contents only.

--no-data

Suppress the INSERT statements. Use this option when you want to dump table definitions only.

--add-drop-table

Precede each CREATE TABLE statement with a DROP TABLE statement. This is useful for generating a file that you can use later to recreate tables from scratch.

--no-create-db

Suppress the CREATE DATABASE statements that the --all-databases option normally produces.

Suppose now that you've used mysqldump to create a SQL-format dump file. How do you import it the file back into MySQL? One common mistake at this point is to use mysqlimport. After all, it's logical to assume that if mysqldump exports tables, mysqlimport must import them. Right? Sorry, no. That might be logical, but it's not always correct. It's true that if you use the --tab option with mysqldump, you can import the resulting datafiles with mysqlimport. But if you dump a SQL-format file, mysqlimport won't process it properly. Use the mysql program instead. The way you do this depends on what's in the dump file. If you dumped multiple databases using --all-databases, the file will contain the appropriate USE db_name statements to select the databases to which each table belongs, and you need no database argument on the command line:

% mysql < dump.txt

If you dumped tables from a single database, you'll need to tell mysql which database to import them into:

% mysql  db_name  < dump.txt 

Note that with this second import command, it's possible to load the tables into a different database than the one from which they came originally. You can use this fact, for example, to create copies of a table or tables in a test database to use for trying out some data manipulating statements that you're debugging, without worrying about affecting the original tables.