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; ...
Get MySQL Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.