Databases Reference
In-Depth Information
mysql>
LOAD DATA INFILE '/tmp/t1.txt'
->
INTO TABLE test.t1
->
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
->
LINES TERMINATED BY '\n';
Here's an informal test we did to demonstrate the backup and restore speed difference
between SQL files and delimited files. We adapted some production data for this test.
The table we're dumping from looks like the following:
CREATE TABLE load_test (
col1 date NOT NULL,
col2 int NOT NULL,
col3 smallint unsigned NOT NULL,
col4 mediumint NOT NULL,
col5 mediumint NOT NULL,
col6 mediumint NOT NULL,
col7 decimal(3,1) default NULL,
col8 varchar(10) NOT NULL default '',
col9 int NOT NULL,
PRIMARY KEY (col1, col2)
) ENGINE=InnoDB;
The table has 15 million rows and uses about 700 MB on disk.
Table 15-1
compares
the performance of the two backup and restore methods. You can see there's a large
speed difference in the restore times for the test.
Table 15-1. Backup and restore times for SQL and delimited dumps
Method
Dump size
Dump time
Restore time
SQL dump
727 MB
102 sec
600 sec
Delimited dump
669 MB
86 sec
301 sec
The
SELECT INTO OUTFILE
method has some limitations, though:
• You can back up only to a file on the machine on which the MySQL server is
running. (You can roll your own
SELECT INTO OUTFILE
by writing a program that
reads a
SELECT
result and writes it to disk, which is an approach we've seen some
people take.)
• MySQL must have permission to write to the directory where the file is written,
because the MySQL server—not the user running the SQL command—is what
writes the file.
• For security reasons, you can't overwrite an existing file, no matter what the file's
permissions are.
• You can't dump directly to a compressed file.
• Some things, such as nonstandard character sets, are hard to get right in either the
export or the import step.
