MySQL export database, database table structure, stored procedures and functions "with"

I. Exporting a database

My MySQL installation directory is D:\Program files\mysql\mysql Server 5.5\bin\, the export file is expected to be placed in D:\sql\

Execute command in MySQL installation directory: Mysqldump-hhostname-uusername-ppassword databasename > D:\sql\databasename.sql

A warning was reported at the time of mysql5.7, but the data was exported successfully:

Now in a different way to solve the above problem:

Create a my.cnf file that I placed under the Mysqldump.exe sibling directory: C:\Program files\mysql\mysql Server 5.7\bin

3306=/tmp/mysql.sockdefault-character-Set = = =  '123456'

Re-export: (exam9 for database name )

5.7>mysqldump--defaults-extra-file=> E:/database.sql

Do not report a warning:

Second, export the database table structure

The first kind:(report the warning, but the execution succeeds!)

Execute command: mysqldump-hhostname-uusername-ppassword-d databasename > D:\sql\databasename.sql

5.7>mysqldump-uroot-p123456-d exam9 >e:/interface  can be insecure.

The second type: ( with the help of the cnf file above, no warning is reported )

5.7>mysqldump--defaults-extra-file=my.cnf-> e:/5.7>

Third, export database table structure and data

The first kind:(report the warning, but the execution succeeds!)

5.7>mysqldump-uroot-p123456-t exam9 >e:/interface  can be insecure.

The second type:( with the help of the cnf file above, no warning is reported )

5.7\bin>mysqldump--defaults-extra-file=-T exam9 >e:/5.7 \bin>

Iv. Exporting stored procedures and functions

1. Querying stored procedures and functions in the database

Method One:

Select ' Name ' from Mysql.proc where db = ' databasename ' and ' type ' = ' PROCEDURE '; Stored Procedures
Select ' Name ' from Mysql.proc where db = ' databasename ' and ' type ' = ' function '//functions

Method Two:

Show procedure status;
Show function status;

2. mysql export stored procedures and functions

Execute command: mysqldump-hhostname-uusername-ppassword-ntd-r databasename > Prorandfunc.sql

V. Summary

-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

MySQL export database, database table structure, stored procedures and functions "with"