MySQL exports data

MySQL exports data

You can use SELECT in MySQL... I NTO OUTFILE statement to simply export data to a text file.

Using SELECT ... The INTO OUTFILE statement exports data

In the following example, we export w3cschool_tbl data from the data table to the /tmp/tutorials .txt file:

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

You can use the command options to format the specified data output, as in the following instance, the export 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, with the 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 ... I NTO OUTFILE inverse operation, SELECT synth. I n order to write data from a database to a file, use SELECT ... I NTO OUTFILE, in order to read the file back to the database, use LOAD DATA INFILE.
• SELECT... I NTO OUTFILE 'file_name' form SELECT can write selected rows into a file. T he file is created on the server host, so you must have FILE permissions to use this syntax.
• The output cannot be an existing file. P revent file data from being tampered with.
• You need to have an account number that logs on to the server to retrieve the files. O therwise SELECT ... INTO OUTFILE does not play any role.
• In UNIX, the file is readable after it is created and the permissions are owned by the MySQL server. T his means that although you can read the file, you may not be able to delete it.

Export the table as raw data

Mysqldump is MySQL's utility for restorting databases. It mainly produces an SQL script that contains the command CREATE TABLE INSERT, etc. necessary to recreate the database from the beginning.

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

The following examples export data tutorials_tbl to the /tmp directory:

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

Export data in SQL format

Export SQL format data to the specified file as follows:

$ mysqldump -u root -p W3CSCHOOL w3cschool_tbl > dump.txt
password ******

The above command creates the following file:

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

--
-- Table structure for table `w3cschool_tbl`
--

CREATE TABLE w3cschool_tbl (
  w3cschool_id int(11) NOT NULL auto_increment,
  w3cschool_title varchar(100) NOT NULL default '',
  w3cschool_author varchar(40) NOT NULL default '',
  submission_date date default NULL,
  PRIMARY KEY  (w3cschool_id),
  UNIQUE KEY AUTHOR_INDEX (w3cschool_author)
) TYPE=MyISAM;

--
-- Dumping data for table `w3cschool_tbl`
--

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

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

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

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

$ 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 implement a backup strategy for the database.

Copy data tables and databases to other hosts

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

Perform 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 fully back up the database, you do not need to use a specific table name.

If you need to import a backed-up database into a MySQL server, you can use the following command, which you need to verify that 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 uses a pipeline to import exported data to a specified remote host.