MySQL Export Data

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

Export Data Using SELECT ... INTO OUTFILE Statement

The following example exports the data from the table tutorialpro_tbl to the /tmp/tutorialpro.txt file:

mysql> SELECT * FROM tutorialpro_tbl 
    -> INTO OUTFILE '/tmp/tutorialpro.txt';

You can set the specified format for data output through command options. The following example exports data in CSV format:

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

In the example below, 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;

SELECT ... INTO OUTFILE Statement Attributes:

LOAD DATA INFILE is the inverse operation of SELECT ... INTO OUTFILE. 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.
The SELECT...INTO OUTFILE 'file_name' form of SELECT writes the selected rows into a file. The file is created on the server host, so you must have FILE permissions to use this syntax.
The output file cannot be an existing file to prevent data tampering.
You need an account to log in to the server to retrieve the file. Otherwise, SELECT ... INTO OUTFILE will not work.
On UNIX, the file is created with readable permissions owned by the MySQL server. This means you can read the file but may not be able to delete it.

Export Table as Raw Data

mysqldump is a utility for MySQL that is used to transfer databases. It primarily generates an SQL script containing the necessary commands to recreate the database, such as CREATE TABLE and INSERT.

To export data using mysqldump, you need to specify the directory for the export file using the --tab option, which must be writable.

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

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

Export Data in SQL Format

Export SQL formatted data to a specified file as follows:

$ mysqldump -u root -p tutorialpro tutorialpro_tbl > dump.txt
password ******

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

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

--
-- Table structure for table `tutorialpro_tbl`
--

CREATE TABLE tutorialpro_tbl (
  tutorialpro_id int(11) NOT NULL auto_increment,
  tutorialpro_title varchar(100) NOT NULL default '',
  tutorialpro_author varchar(40) NOT NULL default '',
  submission_date date default NULL,
  PRIMARY KEY  (tutorialpro_id),
  UNIQUE KEY AUTHOR_INDEX (tutorialpro_author)
) TYPE=MyISAM;

--
-- Dumping data for table `tutorialpro_tbl`
--

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

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

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

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.

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

Copy Tables and Databases to Another Host

If you need to copy 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 back up the entire database, you do not need to specify a particular table name.

To import the backed-up database into a MySQL server, use the following command, ensuring the database is already created:

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

You can also use the following command to directly import the exported data into a remote server, provided the two servers can communicate and 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 directly into the specified remote host.

❮ Mysql Where Clause Mysql Order By ❯