MySQL mysqldump Import/Export structure & data & Stored Procedures & functions & events & Triggers

Source: Internet
Author: User

———————————————-Library Operation ———————————————-
1.① Exporting a library structure

mysqldump-d dbname-u root-p > Xxx.sql

② Exporting Multiple library structures

Mysqldump-d-B dbname1 dbname2-u root-p > Xxx.sql

2.① Exporting a library data

Mysqldump-t dbname-u root-p > Xxx.sql

② Exporting Multiple library data

Mysqldump-t-B dbname1 dbname2-u root-p > Xxx.sql

3.① Export a library structure and data

Mysqldump dbname1-u root-p > Xxx.sql

② export Multiple library structures and data

Mysqldump-b dbname1 dbname2-u root-p > Xxx.sql



———————————————-Table Operation ———————————————-
4.① Export a table structure

mysqldump-d dbname1 tablename1-u root-p > Xxx.sql

② Exporting multiple table structures

Mysqldump-d-B dbname1--tables tablename1 tablename2-u root-p > Xxx.sql

5.① Exporting a table data

Mysqldump-t dbname1 tablename1-u root-p > Xxx.sql

② Exporting multiple table data

Mysqldump-d-B dbname1--tables tablename1 tablename2-u root-p > Xxx.sql

6.① Export a table structure and data

mysqldump dbname1 tablename1-u root-p > Xxx.sql

② Export multiple table structures and data

Mysqldump-b dbname1--tables tablename1 tablename2-u root-p > Xxx.sql



———————————— – Stored Procedure & function Operations ————————————-
7. Export only stored procedures and functions (do not export structure and data, you need to use-D at the same time to export the structure)

Mysqldump-r-ndt dbname1-u root-p > Xxx.sql



———————————————-Event Action ———————————————-
8. Export only Events

MYSQLDUMP-E-ndt dbname1-u root-p > Xxx.sql



————————————— – Trigger Action —————————————— –
9. Do not export triggers (triggers are –triggers exported by default, export triggers using –skip-triggers masking)

mysqldump--skip-triggers dbname1-u root-p > Xxx.sql



————————————————————————————————
10. Import

Mysql-u root-p
Use game;
SOURCE Xxx.sql



————————————————————————————————
To summarize:

-D structure (--no-data: No data is exported, only database table structure is exported)

-T data (--no-create-info: Export only data without adding a CREATE TABLE statement)

-N (--no-create-db: Export only data without adding the Create DATABASE statement)

-R (--routines: Export stored procedures and custom functions)

-E (--events: Export event)

--triggers (default export trigger, export using--skip-triggers mask)

-B (--databases: Export Database list, omit when a single library is available)

--tables table list (can be omitted when a single table)

① both the-D and-t at the same time when the structure and data are exported
② also do not export structure and data can be used-NTD
③ only export stored procedures and functions can use-R-NTD
④ Export All (structure & data & Stored Procedures & functions & events & Triggers) using-R-E (equivalent to ①, omitting-d-t; trigger default export)
⑤ only export Structures & functions & events & triggers using-R-E-D

PS: If you can use the relevant tools, such as the official MySQL Workbench, import and export is very convenient, such as. (Of course, for security, the general situation is to shield the external operation rights, so the need to use the command is more)

MySQL mysqldump Import/Export structure & data & Stored Procedures & functions & events & Triggers

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.