Shulou(Shulou.com)06/01 Report--

Import and export data using into outfile and load data infile

The advantage of this method is that the exported data can set its own format, and the exported data is pure data, there is no table information, you can directly import another different table with the same database, which is more flexible compared to mysqldump.

The following mysql command exports the data from select's mytable table to the / home/db_bak2012 file.

Select * from mytable where statusThe 0 and nameplates contains' 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, which is equivalent to the mysqlimport command. The following mysql command imports 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

Export 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) the 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, just export structure-add-drop-table adds 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, commonly use source command

Mysql-u root-p

Mysql > use database

Mysql > set names utf8; (confirm the code first. If you don't set it, the garbled code may occur. Note that it 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 export multiple libraries in batch, so we can add-- databases or-B, as follows:

Mysqldump-uroot-p-- databases test mysql # separated by spaces

Sometimes we may need to back up all the libraries in the database, so we can use-all-databases, as shown in the following statement:

Mysqldump-uroot-p-all-databases

(7) conditional derivation

The mysqldump command has a-- where/-w parameter, which is used to set the conditions for data export, using basically the same way as the where in the SQL query command. With it, we can export the part of the data you need from the database.

The format of the command is as follows:

Mysqldump-u user name-p password database name table name-where= "filter criteria" > export file path

Example:

Export data with id greater than 100 from the test_ data table of the test database to the file / tmp/test.sql

Mysqldump-uroot-p123456 test test_data-- where= "id > 100" > / tmp/test.sql

When importing

Mysql-u root-p 123456 test test_data < test.sql

Reference:

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.