How to export MySQL Data Using the SELECT... into outfile statement: select... outfile

How to export MySQL Data Using the SELECT... into outfile statement: select... outfile

The simplest way to export table data to a text file is to export the query results of the SELECT... into outfile statement directly to a file on the server host.
The SELECT... into outfile statement for exporting data:

The syntax of this statement is combined with the end of the conventional select into outfile file name. The default output format is the same as load data. Therefore, the following statement exports the table tabs of tutorials_tbl, and the file ending with a line break to/tmp/tutorials.txt:

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

You can also change the output format and use options to indicate how to reference and separate columns and records. To export a table in CSV format of tutorial_tbl from the CRLF termination line, use the following statement:

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

SELECT... into outfile has the following attributes:

• The MySQL server directly creates an output file. Such a file name should specify that the file you want on the server host will be written. There are no similar statements in the LOCAL version of load data.
• You must have the FILE Permission of MySQL to execute the SELECT... INTO statement.
• The output file must not exist. This prevents MySQL from damaging files.
• You should retrieve files from the Logon account of the host on the server host or in some ways. Otherwise, SELECT... into outfile may be of no value.
• In Unix, it creates world-readable files and MySQL servers. This means that although the file can be read, it cannot be deleted.

Export table as raw data:

Use the mysqldump program to copy or back up tables and databases. It can write the original data file output by the table or recreate the records in the table for a group of INSERT statements.
To dump a table, you must specify an option on the "Data Files" tab to indicate the directory of the MySQL server file to be written.

Use the mysqldump program to copy or back up tables and databases. It can write the original data file output by the table, or re-create records in the table for a group of INSERT statements.
To dump a table, you must specify an option on the "Data Files" tab to indicate the directory of the MySQL server file to be written.

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

Export table content or definitions in SQL format:

Use the following command to export the SQL format of a table to a file:

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

This will create the file content 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=InnoDB;---- 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');

To dump multiple tables, their names are all database name parameters. To dump the entire database, do not specify the database after any table, as shown below:

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

Run the following command on all available database backup hosts:

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

Export table content or definitions in SQL format:

These methods can be used to implement Database Backup policies.
Copy a table or database to another host:

If you want to copy a table or database from a MySQL server, use the mysqldump Database Name and table name.

Run the following command on the source host. This will transfer the stored data warehouse to the dump.txt file:

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

Copy the complete database without a specific table name, as described above.

Now, use the following command to run the FTP dump.txt file on another host. Before running this command, make sure that the database name on the target server has been created.

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

Another method, instead of using an intermediate file, is to directly send the mysqldump output to a remote MySQL server over the network. If you can connect to the host where the cookbook database of the two servers is located, run the following command:

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

Half of the command mysqldump is connected to the local server and written to the dump output pipeline. Half of MySQL commands are connected to the remote MySQL Server otherhost.com. It reads each statement sent by the input manager and other host.com servers.