MySQL database export and import SQL database file commands

When using a MySQL database, it is often necessary to back up and restore the database, and it is very convenient to export the SQL file backup and directly execute the SQL file recovery. This article focuses on how to export and import SQL files to the MySQL database through the cmd command.

Export

I. Exporting a database

Contains Data objects and data

Mysqldump-hhostname-uusername-ppassword databasename >/home/justin/databasename.sql

Second, export the database table structure

database table Structure only

mysqldump-hhostname-uusername-ppassword-d databasename >/home/justin/databasename.sql

Iii. 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 Procedure select ' name ' from Mysql.proc where db = ' databasename ' and ' type ' = ' FUNCTION '; --function

  Method Two:

  Show procedure status;show function status;

• 2. mysql export stored procedures and functions

  Mysqldump-hhostname-uusername-ppassword-ntd-r databasename >/home/justin/prorandfunc.sql

Parameter description

-D structure (--no-data: Do not export any data, export only database table structure)-T data (--no-create-info: Export data only, not add create TABLE statement)-N (--no-create-db: Export only data, Instead of adding the Create DATABASE statement)-R (--routines: Export stored procedure and custom function)-E (--events: Export event)--triggers (default export trigger, export using--skip-triggers mask)-B ( --databases: Export Database list, single library can be omitted)--tables table list (can be omitted when a single table) ① export structure as well as data simultaneously omit-D and-t② do not export structure and data can use the-ntd③ only export stored procedures and functions can use the-R- Ntd④ Export All (structure & data & Stored Procedures & functions & events & Triggers) using-R-E (equivalent to ①, omitting-d-t; trigger default export) ⑤ export only structure & function & event & trigger using-R -e-d

Import

1, first build empty database

Mysql>create database test;

2. Import the database
Method One:
(1) Select database

    Mysql>use test;

(2) Setting the database encoding

    Mysql>set names UTF8;

(3) Import data (note the path to the SQL file)

    mysql>source/home/justin/test.sql;

Method Two:

Mysql-hhostname-uusername-ppassword ABC