Summary of MySQL import and export methods

Source: Internet
Author: User
Tags import database mysql import

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.