Mariadb 简明教程

MariaDB - Backup Loading Methods

在本章中，我们将了解各种备份加载方法。从备份还原数据库是一个简单但有时非常漫长的过程。

In this chapter, we will learn about various backup loading methods. Restoring a database from a backup is a simple and sometimes terribly long process.

加载数据有三种选择：LOAD DATA 语句、mysqlimport 和简单的 mysqldump 还原。

There are three options in loading data: the LOAD DATA statement, mysqlimport, and a simple mysqldump restore.

Using LOAD DATA

LOAD DATA 语句用作批量加载器。查看其中加载文本文件的用例示例 −

The LOAD DATA statement functions as a bulk loader. Review an example of its use that loads a text file −

mysql> LOAD DATA LOCAL INFILE 'products_copy.txt' INTO TABLE empty_tbl;

注意 LOAD DATA 语句的以下特点 −

Note the following qualities of a LOAD DATA statement −

Use the LOCAL keyword to prevent MariaDB from performing a deep search of the host, and use a very specific path.
The statement assumes a format consisting of lines terminated by linefeeds (newlines) and data values separated by tabs.
Use the FIELDS clause to explicitly specify formatting of fields on a line. Use the LINES clause to specify line ending. Review an example below.

mysql> LOAD DATA LOCAL INFILE 'products_copy.txt' INTO TABLE empty_tbl
   FIELDS TERMINATED BY '|'
   LINES TERMINATED BY '\n';

The statement assumes columns within the datafile use the same order of the table. If you need to set a different order, you can load the file as follows −

mysql> LOAD DATA LOCAL INFILE 'products_copy.txt' INTO TABLE empty_tbl (c, b, a);

Using MYSQLIMPORT

mysqlimport 工具充当一个 LOAD DATA 封装器，允许从命令行执行相同的操作。

The mysqlimport tool acts as a LOAD DATA wrapper allowing the same operations from the command line.

按如下方式加载数据 −

Load data as follows −

$ mysqlimport -u root -p --local database_name source_file.txt

按如下方式指定格式 −

Specify formatting as follows −

$ mysqlimport -u root -p --local --fields-terminated-by="|" \
   --lines-terminated-by="\n" database_name source_file.txt

使用 — columns 选项指定列顺序 −

Use the --columns option to specify column order −

$ mysqlimport -u root -p --local --columns=c,b,a \
   database_name source_file.txt

Using MYSQLDUMP

使用 mysqldump 还原需要此简单语句将转储文件加载回主机 −

Restoring with mysqldump requires this simple statement for loading the dump file back into the host −

shell> mysql database_name < source_file.sql

SPECIAL CHARACTERS AND QUOTES

在 LOAD DATA 语句中，可能无法正确解释引号和特殊字符。该语句假定未加引号的值，并将反斜杠视为转义字符。使用 FIELDS 子句指定格式。用“ENCLOSED BY”指向引号，这会导致从数据值中剥离引号。用“ESCAPED BY”更改转义。

In a LOAD DATA statement, quotes and special characters may not be interpreted correctly. The statement assumes unquoted values and treats backslashes as escape characters. Use the FIELDS clause to specify formatting. Point to quotes with “ENCLOSED BY,” which causes the stripping of quotes from data values. Change escapes with “ESCAPED BY.”