MySQL Export Data

In MySQL, you can useSELECT...INTO OUTFILEstatement to simply export data to a text file.

using the SELECT ... INTO OUTFILE statement to export data

In the following example, we will export the data table w3Data of codebox_tbl is exported to /tmp/w3The codebox.txt file contains:

mysql> SELECT　*　FROM w3codebox_tbl　
　　　　-> INTO OUTFILE '/tmp/w3codebox.txt';

You can set the specified format for data output through command options, the following example is to export CSV format:

mysql> SELECT　*　FROM passwd INTO OUTFILE '/tmp/w3codebox.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;

The SELECT ... INTO OUTFILE statement has the following properties:

• LOAD DATA INFILE is the reverse operation of SELECT ... INTO OUTFILE, SELECT syntax. To write a database's data to a file, use SELECT ... INTO OUTFILE, and to read the file back into the database, use LOAD DATA INFILE.

• A SELECT...INTO OUTFILE in the form of 'file_name' can write the selected rows to a file. This file is created on the server host, so you must have FILE permissions to use this syntax.

• The output cannot be an existing file. This prevents file data from being tampered with.

• You need to have an account to log in to the server to retrieve the file. Otherwise, SELECT ... INTO OUTFILE will not work.

• In UNIX, the file is readable after it is created, 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 the table as raw data

mysqldump is a utility used by mysql for transferring database storage. It mainly generates a SQL script that contains the necessary commands such as CREATE TABLE, INSERT, etc., to recreate the database from scratch.

To export data using mysqldump, you need to use 　--Using the tab option to specify the directory of the exported file, the target must be writable.

The following example will export the data table w3codebox_tbl exported to /In the tmp directory:

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

Export data in SQL format

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

$ mysqldump　-u root　-p w3codebox w3codebox_tbl > dump.txt
password　******

The content of the file created by the above command is as follows:

--　MySQL dump　8.23
--
--　Host: localhost Database: w3codebox
---------------------------------------------------------
--　Server version　　　　　　　3.23.58
--
--　Table structure for table `w3codebox_tbl`
--
CREATE TABLE w3codebox_tbl (
　　w3codebox_id int(11) NOT NULL auto_increment,
　　w3codebox_title varchar(100) NOT NULL default '',
　　w3codebox_author varchar(40) NOT NULL default '',
　　submission_date date default NULL,
　　PRIMARY KEY (w3codebox_id),
　　UNIQUE KEY AUTHOR_INDEX (w3codebox_author)
) TYPE=MyISAM;
--
--　Dumping data for table `w3codebox_tbl`
--
INSERT INTO w3codebox_tbl　
　　　　　　　VALUES (1Learn PHP2007-05-24);
INSERT INTO w3codebox_tbl　
　　　　　　　VALUES (2Learn MySQL2007-05-24);
INSERT INTO w3codebox_tbl　
　　　　　　　VALUES (3JAVA Tutorial2007-05-06);

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

$ mysqldump　-u root　-p w3codebox > 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　******

--all-The databases option is in MySQL 3.23.12 and later versions added.

This method can be used to implement a database backup strategy.

Copy 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 name in the mysqldump command.

Execute the following command on the source host to back up the data to the dump.txt file:

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

If you need to make a complete backup of the database, there is no 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. You need to confirm that the database has been created when using the following command:

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

You can also use the following command to directly import the exported data into the remote server, but make sure that the two servers are connected and can be accessed by each other:

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

The pipe was used in the above commands to import the exported data into the specified remote host.