MySQL Cookbook, 2nd Edition

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 Exporting Tables as Text Files, mysqldump causes the MySQL server to write tables as text datafiles on the server host when it’s invoked with the --tab option. If you omit the --tab , the server formats the table rows as the INSERT statements and returns them to mysqldump, which writes the output on the client host. The output also can include 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; Copying Tables or Databases to Another Server 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 > states.txt

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

-- MySQL dump 10.10 -- -- Host: localhost Database: cookbook -- ------------------------------------------------------ -- Server version 5.0.27-log -- -- Table structure for table `states` -- CREATE TABLE `states` ( `name` varchar(30) NOT NULL, `abbrev` char(2) NOT NULL, ...