[mysql tutorial] MySQL export data

MySQL Exporting Data

In MySQL you can use the SELECT...INTO OUTFILE statement to simply export data to a text file.

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

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

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

You You can set the specified format of data output through command options. The following example is to 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, and each value is separated by commas. This format can be used by many programs. The

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 attributes:

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

SELECT...INTO OUTFILE 'file_name' form of SELECT can write the 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 logged into the server to retrieve files. Otherwise SELECT ... INTO OUTFILE will have no effect.

In UNIX, the file is created readable and the permissions are 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.

Export tables as raw data

mysqldump is a MySQL utility used to transfer storage databases. It mainly produces a SQL script that contains the commands CREATE TABLE INSERT, etc. necessary to re-create the database from scratch.

To use mysqldump to export data, you need to use the --tab option to specify the directory specified by the export file. The target must be writable.

The following example exports the data table tutorials_tbl to the /tmp directory:

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

Exports data in SQL format

Exports in SQL format Data to the specified file, as shown below:

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

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

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

--
-- Table structure for table `tutorials_tbl`
--

CREATE TABLE tutorials_tbl (
  tutorial_id int(11) NOT NULL auto_increment,
  tutorial_title varchar(100) NOT NULL default '',
  tutorial_author varchar(40) NOT NULL default '',
  submission_date date default NULL,
  PRIMARY KEY  (tutorial_id),
  UNIQUE KEY AUTHOR_INDEX (tutorial_author)
) TYPE=MyISAM;

--
-- Dumping data for table `tutorials_tbl`
--

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

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

$ mysqldump -u root -p TUTORIALS > 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-databases option is added in MySQL 3.23.12 and later versions.

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

Copy data tables and databases to other hosts
If you need to copy data to other MySQL servers, you can specify the database name and data table 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 back up the database completely, 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 using the following command:

$ 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 export Import the data to the specified remote host.

The above is the content of [mysql tutorial] MySQL export data. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!