Mysqldump Export Database Parameters detailed description

Mysqldump is a utility that MySQL uses to store databases. It primarily produces a SQL script that contains the commands necessary to recreate the database from scratch, create TABLE insert, and so on.

Let's look at the various examples of mysqldump exports in detail:

1 exporting the structure of a database

mysqldump-d dbname-uroot-p > Dbname.sql

2 exporting the structure of multiple databases

Mysqldump-d-B dbname1 dbname2-uroot-p > Dbname.sql

3 exporting data from a database (without structure)

Mysqldump-t dbname-uroot-p > Dbname.sql

4 Exporting data from multiple databases (with no structure)

Mysqldump-t-B dbname1 dbname2-uroot-p > Dbname.sql

5 Exporting the structure and data of a database

Mysqldump dbname-uroot-p > Dbname.sql

6 exporting the structure and data of multiple databases

Mysqldump-b dbname1 dbname2-uroot-p > Dbname.sql

7 exporting the structure of a table in a database

mysqldump-d dbname1 tablename-uroot-p > Tablename.sql

8 exporting the structure of multiple tables in a database

Mysqldump-d-B dbname1--tables tablename1 tablename2-uroot-p > Tablename.sql

9 exporting data from a single table in a database (no structure included)

Mysqldump-t dbname1 tablename-uroot-p > Tablename.sql

10 exporting data from multiple tables in a database (with no structure)

Mysqldump-t-B dbname1--tables tablename1 tablename2-uroot-p > Tablename.sql

11 exporting the structure and data of a table in a database

Mysqldump dbname1 tablename-uroot-p > Tablename.sql

12 exporting the structure and data of multiple tables in a database

Mysqldump-b dbname1--tables tablename1 tablename2-uroot-p > Tablename.sql

Stored Procedures & Function operations

1 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 dbname-u root-p > dbname. sql

2 Export Events only

MYSQLDUMP-E-ndt dbname-u root-p > dbname. sql

3 do not export triggers (triggers are –triggers exported by default, export triggers using –skip-triggers masking)

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

Import the exported data into the database

Mysql-u Root-p
Use dbname;
source Dbname.sql   summary: -d structure (--no-data: Do not export any data, export only database table structure)

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

-n (--no-create-db: Export only data without adding a CREATE DATABASE statement)

-r (--routines: Exporting stored procedures and custom functions)

-E (--events: Export event)

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

-b (--databases: Exporting a list of databases, omitting a single library)

--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

Mysqldump Export Database Parameters detailed description