Chat Zalo Chat Messenger Phone Number Đăng nhập
MySQL - Database Export - Tutorialspoint
Blogs

MySQL – Database Export – Tutorialspoint

Follow Saigon Techsoluionts

The easiest way to export data from a table to a text file is to use the SELECT… An INTO OUTFILE statement that exports the result of a query directly to a file on the server host.

Exporting data with the SELECT …

INTO OUTFILE statement

The syntax of this statement combines a regular SELECT command with the INTO OUTFILE file name at the end. The default output format is the same as for the LOAD DATA command. Therefore, the following statement exports the tutorials_tbl table to /tmp/tutorials.txt as a tab-delimited line-break terminated file.

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

You can change the output format using various options to indicate how to quote and delimit columns and records. To export the tutorial_tbl table in CSV format with lines ending in CRLF, use the following code.

mysql> SELECT * FROM passwd INTO OUTFILE ‘/tmp/tutorials.txt’ -> FIELDS ENDING WITH ‘,’ ENCLOSED BY ‘”‘ -> LINES ENDING WITH ‘rn’;

The SELECT … INTO OUTFILE has the following properties:

the output file

  • is created directly by the MySQL server, so the file name should indicate where you want the file to be written to the server host. There is no LOCAL version of the statement analogous to the LOCAL version of LOAD DATA.

  • You must have the MySQL FILE privilege to run the SELECT … INTO Declaration.

  • The output file must not already exist. This prevents MySQL from hitting files that may be important.

  • You must have a logon account on the server host or some way to retrieve the file from that host. Otherwise, the SELECT … Most likely, the INTO OUTFILE command has no value to you.

  • Under UNIX, the file is created readable worldwide and is 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.

Exporting tables

as raw data

The mysqldump program is used to copy or back up tables and databases. You can write the table output as a raw data file or as a set of INSERT statements that recreate the records in the table.

To dump a table as a data file, you must specify a -tab option that indicates the directory, where you want the MySQL server to write the file.

For example, to dump the tutorials_tbl table in the TUTORIALS database to a file in the /tmp directory, use a command as shown below.

$ mysqldump -u root -p -no-create-info -tab=/tmp tutorials tutorials_tbl password ****** Exporting content or table definitions in SQL format

To export a table in SQL format to a file, use the command

shown below. $ mysqldump -u root -p TUTORIALS tutorials_tbl > dump.txt password ******

This will be a creation file that has content as shown below

. – 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 default date NULL, PRIMARY KEY (tutorial_id), UNIQUE KEY AUTHOR_INDEX (tutorial_author) ) TYPE = MyISAM; – – Dump data for table ‘tutorials_tbl’ – INSERT INTO tutorials_tbl VALUES (1,’Learn PHP’,’John Poul’,’2007-05-24′); INSERT IN 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, name them all followed by the database name argument. To dump an entire database, do not assign any table names after the database, as shown in the following code block.

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

To back up all available databases on the host, use the following code

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

all-databases option is available in MySQL version 3.23.12. This method can be used to implement a database backup strategy.

Copy tables or databases to another host

If you want to copy tables or databases from one MySQL server to another, use mysqldump with

the database name and table name. Run the following command on

the source host. This will dump the entire database into a .txt dump file.

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

You can copy the entire database without using a particular table name as explained above

.

Now, dump ftp.txt file on another host and use the following command. Before you run this command, make sure that you have created database_name on the destination server.

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

Another way to accomplish this without using an intermediary file is to send the output of the mysqldump directly over the network to the remote MySQL server. If you can connect to both servers from the host where the source database resides, use the following command (make sure you have access on both servers).

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

In mysqldump, half of the command connects to the local server and writes the dump output to the pipeline. The remaining half of the command connects to the remote MySQL server on the other-host.com. It reads the pipeline for input and sends each instruction to the other-host.com server.

Contact US