MySQL export data, mysql export data

MySQL export data, mysql export data

You can useSELECT... INTO OUTFILEStatement to export data to a text file.

Use the SELECT... into outfile statement to export data

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

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

You can use Command Options to set the specified format of data output. The following example shows the CSV format for export:

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 a comma. 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 attributes:

• Load data infile is the inverse operation of SELECT... into outfile. SELECT syntax. To write DATA from a database to a file, SELECT... into outfile is used. To read the file back to the database, load data infile is used.
• SELECT... into outfile 'file _ name' can write the selected row INTO a file. This FILE is created on the server host. Therefore, you must have the FILE permission to use this syntax.
• The output cannot be an existing file. Prevents file data tampering.
• You need an account to log on to the server to retrieve files. Otherwise, SELECT... into outfile does not play any role.
• In UNIX, the file is readable after being created, and its permissions are owned by the MySQL server. This means that although you can read the file, you may not be able to delete it.

Export table as raw data

Mysqldump is a utility used by mysql to store databases. It mainly generates an SQL script, including the CREATE TABLE INSERT command required to recreate the database from scratch.

 

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

 

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

$ mysqldump -u root -p --no-create-info \            --tab=/tmp cnblogs cnblogs_tblpassword ******

Export data in SQL format

Export data in SQL format to a specified file as follows:

$ mysqldump -u root -p cnblogs cnblogs_tbl > dump.txtpassword ******

The file created by the preceding command is as follows:

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

To export the data of the entire database, run the following command:

$ mysqldump -u root -p cnblogs > database_dump.txtpassword ******

To back up all databases, run the following command:

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

The -- all-databases option is added to MySQL 3.23.12 and later versions.

This method can be used to implement Database Backup policies.

Copy data tables and databases to other hosts

To copy data to another MySQL server, you can specify the Database Name and data table in the mysqldump command.

Run the following command on the source host to back up data to the dump.txt file:

$ mysqldump -u root -p database_name table_name > dump.txtpassword *****

If the database is fully backed up, no specific table name is required.

To import the backup database to the MySQL server, run the following command to confirm that the database has been created:

$ mysql -u root -p database_name < dump.txtpassword *****

You can also use the following command to directly import exported data to a remote server, but make sure that the two servers are connected and can access each other: </p> $ mysqldump-u root-p database_name | mysql-h other-host.com database_name

The preceding command uses an MPS queue to import exported data to a specified remote host.