MySQL export data

MySQL you can use SELECT ... INTO OUTFILE statements to export the data to a simple text file.

Use SELECT ... INTO OUTFILE statements to export data

The following examples, we will export the data to a data table w3big_tbl /tmp/tutorials.txt file:

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

You can set the output data format specified by the command options, the following examples are exported in CSV format:

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

In the following example, a file is generated, the values separated by commas. This format is used by several 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 ... INTO OUTFILE statement has the following properties:

LOAD DATA INFILE SELECT ... INTO OUTFILE is the inverse operation, SELECT syntax. In order to write data to a database in a file, use SELECT ... INTO OUTFILE, to read the file back into the database, use LOAD DATA INFILE.
SELECT ... INTO OUTFILE 'file_name' form of SELECT lines can be selected write a file. The file is created on the server host, so you must have the FILE privilege to use this syntax.
The output is not a file that already exists. File prevent data from being tampered.
You need to have a login account server to retrieve the file. Otherwise SELECT ... INTO OUTFILE will not play any role.
In UNIX, after the file is created is readable privileges owned by MySQL server. This means that although you can read the file, but may not be deleted.

Export data as the original table

mysqldump mysql is used to turn the utility database is stored. It mainly produces a SQL script that contains the command to re-create the database from scratch necessary CREATE TABLE INSERT and so on.

Use mysqldump to export data requires --tab option to specify the directory specified in the export file, the object must be written.

The following example will export the data table w3big_tbl to the / tmp directory:

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

Export data in SQL format

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

$ mysqldump -u root -p w3big w3big_tbl > dump.txt
password ******

Content files created by the above command as follows:

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

--
-- Table structure for table `w3big_tbl`
--

CREATE TABLE w3big_tbl (
  w3big_id int(11) NOT NULL auto_increment,
  w3big_title varchar(100) NOT NULL default '',
  w3big_author varchar(40) NOT NULL default '',
  submission_date date default NULL,
  PRIMARY KEY  (w3big_id),
  UNIQUE KEY AUTHOR_INDEX (w3big_author)
) TYPE=MyISAM;

--
-- Dumping data for table `w3big_tbl`
--

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

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

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

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

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

--all-databases option was added in MySQL 3.23.12 or later.

This method can be used to implement database backup strategy.

The data sheet and copy of the database to another host

If you need to copy data to other MySQL server, you can specify the name of the database and data tables in the mysqldump command.

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

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

If the full backup of the database, you do not need to use a specific table name.

If you need to back up the database into MySQL server, you can use the following command, use the following command you need to make sure the database has been created:

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

你也可以使用以下命令将导出的数据直接导入到远程的服务器上，但请确保两台服务器是相通的，是可以相互访问的：</p>
$ mysqldump -u root -p database_name \
       | mysql -h other-host.com database_name

The above command is used in pipeline to export the data into the specified remote host.

Previous: MySQL and SQL injection