MariaDB backup loading method

In this chapter, we'll look at the various backup loading methods. R estoring a database from a backup is a simple and sometimes very long process.

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

Use LOAD DATA

The LOAD DATA statement is used as a bulk loader. S ee examples of the use of loading text files -

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

Note the following quality of the LOAD DATA statement:

• Using the LOCAL keyword prevents MariaDB from performing a deep search on the host and using very specific paths.

• The statement assumes a format that includes lines terminated by line breaks (line breaks) and data values separated by tabs.

• Use the FIELDS clause to explicitly specify the format of fields on a line. U se the LINES clause to specify the end of the line. C heck out the example below.

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

• The statement assumes that the columns in the data file use the same order as the table. I f 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);

Use MYSQLIMPORT

The mysqlimport tool is used as a LOAD DATA wrapper, allowing the same operation to be performed from the command line.

The loading data is as follows -

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

The specified format is as follows -

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

Use the --columns option to specify the column order -

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

Use MYSQLDUMP

Using mysqldump restore requires this simple statement to load the dump file back to the host -

shell> mysql database_name < source_file.sql

Special characters and quotes

In LOAD DATA statements, quotation marks and special characters may not be interpreted correctly. T he statement takes an unsealed value and treats the backslash as an escape character. U se the FIELDS clause to specify the format. P oint to quotation marks with "ENCLOSED BY BY", which will cause the quotation marks to be removed from the data value. U se ESCAPED BY to change escape.