4.2.33. MySQL export data

In MySQL, you can use SELECT…INTO OUTFILE Statement to simply export data to a text file.

Use SELECT… INTO OUTFILE statement to export data ¶

In the following example, we export the datasheet runoob_tbl data to the / tmp/runoob.txt file:

mysql> SELECT * FROM runoob_tbl
    -> INTO OUTFILE '/tmp/runoob.txt';

You can set the specified format of data output through the command options. The following example is the export CSV format:

mysql> SELECT * FROM passwd INTO OUTFILE '/tmp/runoob.txt'
    -> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
    -> LINES TERMINATED BY '\r\n';

In the following example, a file is generated, with values separated by commas. This format can be used by many programs.

SELECT a,b,a+b INTO OUTFILE '/tmp/result.text'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;

SELECT… The INTO OUTFILE statement has the following properties: ¶

LOAD DATA INFILE is SELECT. Reverse operation of INTO OUTFILE, SELECT syntax. To write data from a database to a file, use SELECT… INTO OUTFILE, to read the file back to the database, use LOAD DATA INFILE.
SELECT in the form of SELECT…INTO OUTFILE ‘file_name’ can write selected lines to a file. The file is created on the server host, so you must have FILE permissions to use this syntax.
The output cannot be an existing file. Prevent file data from being tampered with.
You need to have an account that logs in to the server to retrieve files. Otherwise, SELECT… INTO OUTFILE won’t do anything.
In UNIX, the file is created to be readable and the permissions are owned by the MySQL server. This means that although you can read the file, you may not be able to delete it.

Export tables as raw data ¶

Mysqldump is the utility that mysql uses to transfer the database. It mainly produces a SQL script that contains commands such as CREATE TABLE INSERT that are necessary to recreate the database from scratch.

Exporting data using mysqldump requires the– tab option to specify the directory specified by the export file, which must be writable.

The following example exports the datasheet runoob_tbl to the / tmp directory:

$ mysqldump -u root -p --no-create-info \
            --tab=/tmp RUNOOB runoob_tbl
password ******

Export data in SQL format ¶

Export the data in SQL format to the specified file, as follows:

$ mysqldump -u root -p RUNOOB runoob_tbl > dump.txt
password ******

The file created by the above command is as follows:

-- MySQL dump 8.23
--
-- Host: localhost    Database: RUNOOB
---------------------------------------------------------
-- Server version       3.23.58

--
-- Table structure for table `runoob_tbl`
--

CREATE TABLE runoob_tbl (
  runoob_id int(11) NOT NULL auto_increment,
  runoob_title varchar(100) NOT NULL default '',
  runoob_author varchar(40) NOT NULL default '',
  submission_date date default NULL,
  PRIMARY KEY  (runoob_id),
  UNIQUE KEY AUTHOR_INDEX (runoob_author)
) TYPE=MyISAM;

--
-- Dumping data for table `runoob_tbl`
--

INSERT INTO runoob_tbl
       VALUES (1,'Learn PHP','John Poul','2007-05-24');
INSERT INTO runoob_tbl
       VALUES (2,'Learn MySQL','Abdul S','2007-05-24');
INSERT INTO runoob_tbl
       VALUES (3,'JAVA Tutorial','Sanjay','2007-05-06');

If you need to export data from the entire database, you can use the following command:

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

If you need to back up all databases, you can use the following command:

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

– the all-databases option is added in MySQL 3.23.12 and later.

This method can be used to realize the backup strategy of the database.

Copy data tables and databases to other hosts ¶

If you need to copy the data to another MySQL server, you can specify the database name and table in the mysqldump command.

Backup the data to the dump.txt file by executing the following command on the source host:

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

If you fully back up the database, you do not need to use a specific table name.

If you need to import the backed-up database into the MySQL server, you can use the following command to verify that the database has been created:

$ mysql -u root -p database_name < dump.txt
password *****

You can also import the exported data directly to a remote server using the following command, but make sure that the two servers are connected and accessible to each other:

$ mysqldump -u root -p database_name \
       | mysql -h other-host.com database_name

Pipes are used in the above command to import the exported data to the specified remote host.