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