The best MySQL export data Tutorial In 2024, In this tutorial you can learn Use SELECT ... INTO OUTFILE statements to export data,Export data as the original table,Export data in SQL format,The data sheet and copy of the database to another host,
MySQL you can use SELECT /en. INTO OUTFILE statements to export the data to a simple text file.
The following examples, we will export the data to a data table w3big_tbl /tmp/tutorials.txt file:
mysql> SELECT * FROM w3big_tbl -> INTO OUTFILE '/tmp/tutorials.txt';
You can set the output data format specified by the command options, the following examples are exported in CSV format:
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, the values separated by commas. This format is used by several programs.
SELECT a,b,a+b INTO OUTFILE '/tmp/result.text' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM test_table;
mysqldump mysql is used to turn the utility database is stored. It mainly produces a SQL script that contains the command to re-create the database from scratch necessary CREATE TABLE INSERT and so on.
Use mysqldump to export data requires --tab option to specify the directory specified in the export file, the object must be written.The following example will export the data table w3big_tbl to the / tmp directory:
$ mysqldump -u root -p --no-create-info \ --tab=/tmp w3big w3big_tbl password ******
Export SQL data to the specified file format, as follows:
$ mysqldump -u root -p w3big w3big_tbl > dump.txt password ******
Content files created by the above command as follows:
-- MySQL dump 8.23 -- -- Host: localhost Database: w3big --------------------------------------------------------- -- Server version 3.23.58 -- -- Table structure for table `w3big_tbl` -- CREATE TABLE w3big_tbl ( w3big_id int(11) NOT NULL auto_increment, w3big_title varchar(100) NOT NULL default '', w3big_author varchar(40) NOT NULL default '', submission_date date default NULL, PRIMARY KEY (w3big_id), UNIQUE KEY AUTHOR_INDEX (w3big_author) ) TYPE=MyISAM; -- -- Dumping data for table `w3big_tbl` -- INSERT INTO w3big_tbl VALUES (1,'Learn PHP','John Poul','2007-05-24'); INSERT INTO w3big_tbl VALUES (2,'Learn MySQL','Abdul S','2007-05-24'); INSERT INTO w3big_tbl VALUES (3,'JAVA Tutorial','Sanjay','2007-05-06');
If you need to export data for the entire database, you can use the following command:
$ mysqldump -u root -p w3big > database_dump.txt password ******
If you need to back up all the databases, you can use the following command:
$ mysqldump -u root -p --all-databases > database_dump.txt password ******
--all-databases option was added in MySQL 3.23.12 or later.
This method can be used to implement database backup strategy.
If you need to copy data to other MySQL server, you can specify the name of the database and data tables in the mysqldump command.
Execute the following command on the source host, back up data to dump.txt file:
$ mysqldump -u root -p database_name table_name > dump.txt password *****
If the full backup of the database, you do not need to use a specific table name.
If you need to back up the database into MySQL server, you can use the following command, use the following command you need to make sure the database has been created:
$ mysql -u root -p database_name < dump.txt password *****
你也可以使用以下命令将导出的数据直接导入到远程的服务器上,但请确保两台服务器是相通的,是可以相互访问的:</p> $ mysqldump -u root -p database_name \ | mysql -h other-host.com database_name
The above command is used in pipeline to export the data into the specified remote host.