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.

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

Using 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;

Note the following quapties 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 pnes terminated by pnefeeds (newpnes) and data values separated by tabs.

Use the FIELDS clause to exppcitly specify formatting of fields on a pne. Use the LINES clause to specify pne ending. Review an example below.

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

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

The mysqpmport tool acts as a LOAD DATA wrapper allowing the same operations from the command pne.

Load data as follows −

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

Specify formatting as follows −

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

Use the --columns option to specify column order −

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

Using 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

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.”