MySQL Cookbook

Exporting Table Contents or Definitions in SQL Format

Problem

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

Solution

Use the mysqldump program without the --tab option.

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