Delimited-Text Format Backups with mysqldump utility
Without –tab option
MYSQLDUMP writes SQL statements to the standard output as default. Output consists of DDL statements to create dumped objects (databases, tables, stored routines, and so forth), and DML statements to load data into tables like insert. It will generate output as one file which will used to reproduced the current state of database.
With –tab option
MYSQLDUMP produces two output files for each dumped table.
One file is used for tab-delimited text, one line per table row. This file is named tbl_name.txt in the output directory.
Second file has CREATE TABLE statement for the table to mysqldump, which writes it as a file named tbl_name.sql in the output directory.
Example of tab option
For a table named t1, the files are named t1.sql and t1.txt.
The .sql file contains a CREATE TABLE statement for the table.
The .txt file contains the table data, one line per table row.
Backup with delimited format
-- Backup the database in /tmp directory
mysqldump --tab=/tmp db1
-- Backup the database with specific option as used needed for other ETL process:
mysqldump --tab=/tmp --fields-terminated-by=, --fields-enclosed-by='"' --lines-terminated-by=0x0d0a db1
Restore or Reload Delimited file
Restore and reload the delimited file into the database.
-- First Way:
shell> mysql db1 mysqlimport db1 t1.txt
-- Second way run from mysql commands for import data:
mysql> USE db1;
mysql> LOAD DATA INFILE 't1.txt' INTO TABLE t1;
-- First way with options:
mysqlimport --fields-terminated-by=, --fields-enclosed-by='"' --lines-terminated-by=0x0d0a db1 t1.txt