Beginners guide to MySQL and MariaDB

The exporting a table data into a text file is using SELECT...INTO OUTFILE statement that exports a query result directly into a file on server host.

Exporting Data with SELECT ... INTO OUTFILE Statement:

The syntax for this statement combines a regular SELECT with INTO OUTFILE filename at end. The default output format is same as for LOAD DATA, so following statement exports the book_tbl table into /tmp/books.txt as a tab-delimited, linefeed-terminated file: 

mysql> SELECT * FROM book_tbl
           INTO OUTFILE '/tmp/books.txt';

The SELECT ... INTO OUTFILE has following properties: 

• The output file is created directly by MySQL server, so filename should indicate where you want file to be written on server host.
• The output file must not already exist. This will prevent MySQL from clobbering files that will be important. 

Exporting Tables as Raw Data:

The mysqldump program allow to copy or back up tables and databases. It will write table output either as a raw data file or as a set of INSERT statements that recreate records in table. 

To dump a table as a data file, user must specify a --tab option that indicates directory, where you want MySQL server to write file. 

$ mysqldump -u root -p --no-create-info 
            --tab=/tmp my_db_test book_tbl

You will have to enter user password and press enter.

Importing Data with LOAD DATA:

MySQL have LOAD DATA statement that acts as a bulk data loader. 

mysql> LOAD DATA LOCAL INFILE 'books.txt' INTO TABLE books_tbl; 

If LOCAL keyword is not present, MySQL checks for datafile on server host using looking into absolute pathname fully specifies location of the file, beginning from root of file system.

By default, LOAD DATA assumes data files contain lines that are terminated by linefeeds (newlines) and data values within a line are separated by tabs. 

mysql> LOAD DATA LOCAL INFILE 'dumpemysql.txt' INTO TABLE mytbl
             FIELDS TERMINATED BY ':'
             LINES TERMINATED BY '\r\n';

More on Importing and  Exporting can be read from the articles below

https://dev.mysql.com/doc/refman/5.1/en/load-data.html

https://dev.mysql.com/doc/refman/5.0/en/select-into.html

Here is a Quick video tutorial from Youtube that you can view next

Source: Youtube
Author: B Robets

This concludes our series on Quick introduction to MySQL. This document should only server as a starting point. There is so much more to learn. Practise is the key. We hope you enjoyed this series. Any improvement requests can be added using comments section.