MySQL Export Data

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

Using SELECT ... INTO OUTFILE Statement to Export Data

SELECT...INTO OUTFILEis the syntax in MySQL used to export query results to a file.

SELECT...INTO OUTFILEAllows you to write the query results into a text file. Basic usage:

SELECT column1, column2, ...
INTO OUTFILE 'file_path'
FROM your_table
WHERE your_conditions;

Parameter description:

column1, column2, ...: The columns to be selected.
'file_path': Specify the path and name of the output file.
your_table: The table to be queried.
your_conditions: The query condition.

The following is a simple example:

Example

SELECT id, name, email
INTO OUTFILE '/tmp/user_data.csv'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
FROM users;

In the above SQL statement, we selected the id, name, and email columns from the users table and wrote the results to the /tmp/user_data.csv file. FIELDS TERMINATED BY ',' specifies the separator between columns (comma), and LINES TERMINATED BY '\n' specifies the separator between rows (newline).

Note that executingSELECT...INTO OUTFILErequires the corresponding permission, and the directory of the output file must be a location where the MySQL server can write.

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

mysql> SELECT * FROM example_tbl 
    -> INTO OUTFILE '/tmp/example.txt';

You can use command options to set the specific format for data output. The following example exports data in CSV format:

mysql> SELECT * FROM passwd INTO OUTFILE '/tmp/example.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 inverse operation of SELECT ... INTO OUTFILE, using SELECT syntax. To write data from a database to a file, use SELECT ... INTO OUTFILE; to read the file back into the database, use LOAD DATA INFILE.
A SELECT in the form of SELECT ... INTO OUTFILE 'file_name' can write the selected rows to a file. The file is created on the server host, so you must have the FILE privilege to use this syntax.
The output cannot be an existing file. This prevents file data from being tampered with.
You need an account logged into the server to retrieve the file. Otherwise, SELECT ... INTO OUTFILE will have no effect.
In UNIX, the file is readable after it is created, with permissions owned by the MySQL server. This means that even though you can read the file, you may not be able to delete it.

mysqldump Exporting Tables as Raw Data

mysqldump is a command-line tool provided by MySQL for backing up and exporting databases.

mysqldumpIt is a utility used by mysql for dumping databases. It mainly produces an SQL script that contains the commands necessary to recreate the database from scratch, such as CREATE TABLE, INSERT, etc.

Usingmysqldumpto export data, you need to use the--taboption to specify the directory where the exported files will be saved. This target must be writable.

Basic usage of mysqldump:

mysqldump -u username -p password -h hostname database_name > output_file.sql

Parameter description:

-u: Specify the MySQL username.
-p: Prompt for the password.
-h: Specify the MySQL hostname.
database_name: The name of the database to be exported.
output_file.sql: The file where the exported data is saved.

The following example exports the example_tbl table to the /tmp directory:

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

mysqldump Example

1. Export the entire database

Export the mydatabase database to the mydatabase_backup.sql file:

mysqldump -u root -p mydatabase > mydatabase_backup.sql

2. Export a specific table

If you only want to export a certain table from the database, you can use the following command:

mysqldump -u username -p password -h hostname database_name table_name > output_file.sql

Or:

mysqldump -u root -p mydatabase mytable > mytable_backup.sql

3. Export the database structure

If you only want to export the database structure without including the data, you can use the--no-data option:

mysqldump -u username -p password -h hostname --no-data database_name > output_file.sql

4. Export a compressed file

You can compress the exported data to reduce the file size. For example, using gzip:

mysqldump -u username -p password -h hostname database_name | gzip > output_file.sql.gz

Export Data in SQL Format

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

$ mysqldump -u root -p EXAMPLE example_tbl > dump.txt
password ******

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

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

--
-- Table structure for table `example_tbl`
--

CREATE TABLE example_tbl (
  example_id int(11) NOT NULL auto_increment,
  example_title varchar(100) NOT NULL default '',
  example_author varchar(40) NOT NULL default '',
  submission_date date default NULL,
  PRIMARY KEY  (example_id),
  UNIQUE KEY AUTHOR_INDEX (example_author)
) TYPE=MyISAM;

--
-- Dumping data for table `example_tbl`
--

INSERT INTO example_tbl 
       VALUES (1,'Learn PHP','John Poul','2007-05-24');
INSERT INTO example_tbl 
       VALUES (2,'Learn MySQL','Abdul S','2007-05-24');
INSERT INTO example_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 EXAMPLE > 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 was added in MySQL 3.23.12 and later versions.

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

Copying Tables and Databases to Other Hosts

If you need to copy data to another MySQL server, you can specify the database name and 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 backing up the entire database, there is no need to use specific table names.

If you need to import the backed-up database into the MySQL server, you can use the following command. When using the following command, you need to confirm that the database has already been created:

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

You can also use the following command to directly import the exported data to a remote server, but please ensure that the two servers are connected and can access each other:

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

The above command uses a pipe to import the exported data to the specified remote host.

Other Extensions

MySQL Programming Language