Skip to Main Content
MySQL Cookbook
book

MySQL Cookbook

by Paul DuBois
October 2002
Intermediate to advanced content levelIntermediate to advanced
1024 pages
27h 26m
English
O'Reilly Media, Inc.
Content preview from 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; ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

MySQL Reference Manual

MySQL Reference Manual

Michael Widenius, David Axmark, Kaj Arno
High Performance MySQL

High Performance MySQL

Jeremy D. Zawodny, Derek J. Balling
MySQL Stored Procedure Programming

MySQL Stored Procedure Programming

Guy Harrison, Steven Feuerstein

Publisher Resources

ISBN: 0596001452Catalog PageErrata