How to use MYSQL delimiters to back up and restore databases

This article mainly introduces how to use MYSQL delimiters to back up and restore the database, the article is very detailed, has a certain reference value, interested friends must read it!

In the second edition of "High performance Mysql", you can see the comparison between SQL files and delimiter files in backup and restore time.

Mode file size export time restore time

SQL dump 727M 102s 600s

Delimited dump 669M 86s 301s

It seems that the export of the delimiter file is nearly 20% faster than the sql file, and the restore speed is twice as fast.

Record and summarize the grammar:

Backup: SELECT INTO OUTFILE

SELECT * FROM hx_9enjoy INTO OUTFILE / tmp/9enjoy.txt

Tab split fields are used by default, and records use

Split. You can customize three parameters.

SELECT * FROM hx_9enjoy INTO OUTFILE / tmp/9enjoy_hx.txt FIELDS TERMINATED BY, OPTIONALLY ENCLOSED BY "LINES TERMINATED BY

Even if used, split the field, double quotation marks limit the field content

Split the record.

You can also specify record output for certain conditions:

SELECT * FROM hx_9enjoy WHERE id < 100INTO OUTFILE / tmp/9enjoy_100.txt

Restore: LOAD DATA INFILE

Default:

LOAD DATA INFILE / tmp/9enjoy.txt INTO TABLE hx_9enjoy

If there is a specified delimiter, add the description of the delimiter:

LOAD DATA INFILE / tmp/9enjoy.txt INTO TABLE hx_9enjoy FIELDS TERMINATED BY, OPTIONALLY ENCLOSED BY "LINES TERMINATED BY

Matters needing attention

1. You must have permission to access the directory where the generated file is generated

two。 For security reasons, an existing file cannot be overwritten, regardless of how permissions are assigned to the file.

3. You cannot export a compressed file directly.

The above is all the contents of the article "how to back up and restore a database using MYSQL delimiters". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!