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

Source: Internet
Author: User
Tags mysql export database

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"

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.