MySQL export data

MySQL export 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 php_tbl to the /tmp/tutorials.txt file:

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

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

SELECT a,b,a+b INTO OUTFILE '/tmp/result.text'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;

• 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 readable after it is created, 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 table as raw data

mysqldump is a utility used by mysql 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.

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

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

Export data in SQL format

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

$ mysqldump -u root -p php php_tbl > dump.txt
password ******

-- MySQL dump 8.23
--
-- Host: localhost    Database: php
---------------------------------------------------------
-- Server version       3.23.58
--
-- Table structure for table `php_tbl`
--
CREATE TABLE php_tbl (
  php_id int(11) NOT NULL auto_increment,
  php_title varchar(100) NOT NULL default '',
  php_author varchar(40) NOT NULL default '',
  submission_date date default NULL,
  PRIMARY KEY  (php_id),
  UNIQUE KEY AUTHOR_INDEX (php_author)
) TYPE=MyISAM;
--
-- Dumping data for table `php_tbl`
--
INSERT INTO php_tbl 
       VALUES (1,'Learn PHP','John Poul','2007-05-24');
INSERT INTO php_tbl 
       VALUES (2,'Learn MySQL','Abdul S','2007-05-24');
INSERT INTO php_tbl 
       VALUES (3,'JAVA Tutorial','Sanjay','2007-05-06');

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

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

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 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 *****

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