MySQL – Database Export

MySQL – Database Export

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

Exporting Data with the SELECT ... INTO OUTFILE Statement

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

mysql> SELECT * FROM tutorials_tbl
-> INTO OUTFILE '/tmp/tutorials.txt';

You can change the output format using various options to indicate how to quote and delimit columns and records. To export the employees table in a CSV format with CRLF-terminated lines, use the following code.

mysql> SELECT * FROM passwd INTO OUTFILE '/tmp/tutorials.txt'
-> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
-> LINES TERMINATED BY 'rn';

The SELECT ... INTO OUTFILE has the following properties −

  • The output file is created directly by the MySQL server, so the filename should indicate where you want the file to be written on the server host. There is no LOCAL version of the statement analogous to the LOCAL version of LOAD DATA.
  • You must have the MySQL FILE privilege to execute the SELECT ... INTO statement.
  • The output file must not already exist. This prevents MySQL from clobbering files that may be important.
  • You should have a login account on the server host or some way to retrieve the file from that host. Otherwise, the SELECT ... INTO OUTFILE command will most likely be of no value to you.
  • Under UNIX, the file is created world readable and is owned by the MySQL server. This means that although you will be able to read the file, you may not be able to delete it.
    The output file is created directly by the MySQL server, so the filename should indicate where you want the file to be written on the server host. There is no LOCAL version of the statement analogous to the LOCAL version of LOAD DATA.
    You must have the MySQL FILE privilege to execute the SELECT ... INTO statement.
    The output file must not already exist. This prevents MySQL from clobbering files that may be important.
    You should have a login account on the server host or some way to retrieve the file from that host. Otherwise, the SELECT ... INTO OUTFILE command will most likely be of no value to you.
    Under UNIX, the file is created world readable and is owned by the MySQL server. This means that although you will be able to read the file, you may not be able to delete it.

    Exporting Tables as Raw Data

    The mysqldump program is used to copy or back up tables and databases. It can write the table output either as a Raw Datafile or as a set of INSERT statements that recreate the records in the table.
    To dump a table as a datafile, you must specify a --tab option that indicates the directory, where you want the MySQL server to write the file.
    For example, to dump the products table from the myd database to a file in the /tmp directory, use a command as shown below.

    $ mysqldump -u root -p --no-create-info
    --tab=/tmp mydb products_table
    password ******

    Exporting Table Contents or Definitions in SQL Format

    To export a table in SQL format to a file, use the command shown below.

    $ mysqldump -u root -p mydb products > dump.txt
    password ******

    This will a create file having content as shown below.

    -- MySQL dump 8.23
    --
    -- Host: localhost    Database: mydb
    ---------------------------------------------------------
    -- Server version       3.23.58
    --
    -- Table structure for table `products`
    --
    > CREATE TABLE `products` (
    ->   `supplier_ids` LONGTEXT NULL DEFAULT NULL,
    ->   `id` INT(11) NOT NULL AUTO_INCREMENT,
    ->   `product_code` VARCHAR(25) NULL DEFAULT NULL,
    ->   `product_name` VARCHAR(50) NULL DEFAULT NULL,
    ->   `description` LONGTEXT NULL DEFAULT NULL,
    ->   `standard_cost` DECIMAL(19,4) NULL DEFAULT '0.0000',
    ->   `list_price` DECIMAL(19,4) NOT NULL DEFAULT '0.0000',
    ->   `reorder_level` INT(11) NULL DEFAULT NULL,
    ->   `target_level` INT(11) NULL DEFAULT NULL,
    ->   `quantity_per_unit` VARCHAR(50) NULL DEFAULT NULL,
    ->   `discontinued` TINYINT(1) NOT NULL DEFAULT '0',
    ->   `minimum_reorder_quantity` INT(11) NULL DEFAULT NULL,
    ->   `category` VARCHAR(50) NULL DEFAULT NULL,
    ->   `attachments` LONGBLOB NULL DEFAULT NULL,
    ->   PRIMARY KEY (`id`),
    ->   INDEX `product_code` (`product_code` ASC))
    --
    -- Dumping data for table `products`
    --
    MariaDB [mydb]> INSERT INTO `products_table` (`supplier_ids`, `id`, `product_code`, `prd_name`, `description`, `standard_cost`, `list_price`, `reorder_level`, `target_level`, `quantity_per_unit`, `discontinued`, `minimum_reorder_quantity`, `category`, `attachments`) VALUES ('10', 3, 'NWTCO-3', 'Northwind Traders Syrup', NULL, 7.5, 10, 25, 100, '12 - 550 ml bottles', 0, 25, 'Condiments', '');
    Query OK, 1 row affected (0.04 sec)
    MariaDB [mydb]> INSERT INTO `products_table` (`supplier_ids`, `id`, `product_code`, `prd_name`, `description`, `standard_cost`, `list_price`, `reorder_level`, `target_level`, `quantity_per_unit`, `discontinued`, `minimum_reorder_quantity`, `category`, `attachments`) VALUES ('10', 5, 'NWTO-5', 'Northwind Traders Olive Oil', NULL, 16.0125, 21.35, 10, 40, '36 boxes', 0, 10, 'Oil', '');
    Query OK, 1 row affected (0.04 sec)

    To dump multiple tables, name them all followed by the database name argument. To dump an entire database, don't name any tables after the database as shown in the following code block.

    $ mysqldump -u root -p TUTORIALS > database_dump.txt
    password ******

    To back up all the databases available on your host, use the following code.

    $ mysqldump -u root -p --all-databases > database_dump.txt
    password ******

    The --all-databases option is available in the MySQL 3.23.12 version. This method can be used to implement a database backup strategy.

    Copying Tables or Databases to Another Host

    If you want to copy tables or databases from one MySQL server to another, then use the mysqldump with database name and table name.
    Run the following command at the source host. This will dump the complete database into dump.txt file.

    $ mysqldump -u root -p database_name table_name > dump.txt
    password *****

    You can copy complete database without using a particular table name as explained above.
    Now, ftp dump.txt file on another host and use the following command. Before running this command, make sure you have created database_name on destination server.

    $ mysql -u root -p database_name  LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl;
  • If the LOCAL keyword is not present, MySQL looks for the datafile on the server host using the looking into absolute pathname, which fully specifies the location of the file, beginning from the root of the filesystem. MySQL reads the file from the given location.
  • By default, LOAD DATA assumes that datafiles contain lines that are terminated by linefeeds (newlines) and that data values within a line are separated by tabs.
  • To specify a file format explicitly, use a FIELDS clause to describe the characteristics of fields within a line, and a LINES clause to specify the line-ending sequence. The following LOAD DATA statement specifies that the datafile contains values separated by colons and lines terminated by carriage returns and new line character.
    mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl
    -> FIELDS TERMINATED BY ':'
    -> LINES TERMINATED BY 'rn';
  • The LOAD DATA command assumes the columns in the datafile have the same order as the columns in the table. If that is not true, you can specify a list to indicate which table columns the datafile columns should be loaded into. Suppose your table has columns a, b, and c, but successive columns in the datafile correspond to columns b, c, and a.
    You can load the file as shown in the following code block.

    mysql> LOAD DATA LOCAL INFILE 'dump.txt'
    -> INTO TABLE mytbl (b, c, a);

    Importing Data with mysqlimport

    MySQL also includes a utility program named mysqlimport that acts as a wrapper around LOAD DATA, so that you can load the input files directly from the command line.
    To load data from the dump.txt into mytbl, use the following command at the UNIX prompt.

    $ mysqlimport -u root -p --local database_name dump.txt
    password *****

    If you use mysqlimport, command-line options provide the format specifiers. The mysqlimport commands that correspond to the preceding two LOAD DATA statements looks as shown in the following code block.

    $ mysqlimport -u root -p --local --fields-terminated-by = ":"
    --lines-terminated-by = "rn"  database_name dump.txt
    password *****

    The order in which you specify the options doesn't matter for mysqlimport, except that they should all precede the database name.
    The mysqlimport statement uses the --columns option to specify the column order −

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

    Handling Quotes and Special Characters

    The FIELDS clause can specify other format options besides TERMINATED BY. By default, LOAD DATA assumes that values are unquoted and interprets the backslash () as an escape character for the special characters. To indicate the value quoting character explicitly, use the ENCLOSED BY command. MySQL will strip that character from the ends of data values during input processing. To change the default escape character, use ESCAPED BY.
    When you specify ENCLOSED BY to indicate that quote characters should be stripped from data values, it is possible to include the quote character literally within data values by doubling it or by preceding it with the escape character.
    For example, if the quote and escape characters are " and , the input value "a""b"c" will be interpreted as a"b"c.
    For mysqlimport, the corresponding command-line options for specifying quote and escape values are --fields-enclosed-by and --fields-escaped-by.

MySQL – Useful Resources (Prev Lesson)
(Next Lesson) MySQL – Useful Functions
', { 'anonymize_ip': true });