Summary of MySQL import and export methods

Import and export data using into outfile and load data infile

The advantage of this method is that the exported data can be self-defined format, and the export is pure data, there is no table information, you can directly import another database with the different tables, compared to mysqldump more flexible.

The following MySQL command exports the data from the MyTable table of Select to the/home/db_bak2012 file.

SELECT * FROM MyTable where status!=0 and name!= "into outfile '/home/db_bak2012 ' fields terminated by ' | ' enclosed by ' ' ' lines terminated by ' \ r \ n ';

Reference:
Https://dev.mysql.com/doc/refman/5.7/en/select-into.html

To import the data you just backed up, you can use the load data infile method, equivalent to the Mysqlimport command, and the following MySQL command to import the exported data into the Mytable_bak table:

Load data infile '/home/db_bak2012 ' into table Mytable_bak fields terminated by ' | ' enclosed by ' "' lines terminated by ' \ r \ n ';

Reference:
Https://dev.mysql.com/doc/refman/5.7/en/load-data.html
Https://dev.mysql.com/doc/refman/5.7/en/mysqlimport.html

Exporting a database using mysqldump

Let's look at a few common use cases:

(1) Export the entire database data and table structure
Mysqldump-u user name-p database name > exported file name
Mysqldump-u wcnc-p SMGP_APPS_WCNC > Wcnc.sql

(2) export a table
Mysqldump-u user name-P database name Table name > exported file name
Mysqldump-u wcnc-p SMGP_APPS_WCNC users> wcnc_users.sql

(3) export structure does not export data
Mysqldump--opt-d database name-u root-p > Xxx.sql
Mysqldump-u Wcnc-p-D--add-drop-table SMGP_APPS_WCNC >d:\wcnc_db.sql
#-d do not export data only export structure--add-drop-table add a drop table before each CREATE statement

(4) Export data does not export structure

MYSQLDUMP-T database name-uroot-p > Xxx.sql

(5) Import database, common Source command

Mysql-u root-p
Mysql>use Database
Mysql>set names UTF8; (First confirm the encoding, if not set may appear garbled, note is not UTF-8)
Mysql>source./wcnc_db.sql

(6) Multi-Library export
The above example is only the most basic, sometimes we may need to batch export multiple libraries, we can add--databases or-B, the following statement:

Mysqldump-uroot-p--databases test MySQL #空格分隔

There are times when we may need to back up all the libraries in the database, we can use-all-databases, the following statement:

Mysqldump-uroot-p-all-databases

(7) Conditional export

The mysqldump command has a--where/-w parameter that sets the criteria for data export, which is basically the same as where in the SQL query command, and with it, we can export the part of the data you need from the database.

The command format is as follows:

Mysqldump-u User name-p password database name table name--where= "filter Condition" > Export file path

Example:

Export data with IDs greater than 100 to/tmp/test.sql in this file from the Test_data table in the test database

mysqldump-uroot-p123456 test test_data--where= "ID >" >/tmp/test.sql

At the time of import

Mysql-u root-p 123456 Test Test_data < Test.sql

Reference:
Https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html

This article is from the SQL Server deep Dive blog, so be sure to keep this source http://ultrasql.blog.51cto.com/9591438/1924898

Summary of MySQL import and export methods