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. It is a very convenient way to backup and restore the sql file directly by exporting it. This article mainly introduces how to export and import sql files through the cmd command to the MySQL database.

Export

1. Export database

Contains data objects and data

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

2. Export database table structure

Only database tables Structure

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

3. Export stored procedures and functions

• 1. Query the stored procedures and functions in the database

  Method 1:

  select `name` from mysql.proc where db = 'databasename' and `type` = 'PROCEDURE'; -- 存储过程
  select `name` from mysql.proc where db = 'databasename' and `type` = 'FUNCTION'; -- 函数

  Method 2:

  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 结构(--no-data:不导出任何数据，只导出数据库表结构)


-t 数据(--no-create-info:只导出数据，而不添加CREATE TABLE 语句)


-n (--no-create-db:只导出数据，而不添加CREATE DATABASE 语句）


-R (--routines:导出存储过程以及自定义函数)


-E (--events:导出事件)


--triggers (默认导出触发器，使用--skip-triggers屏蔽导出)


-B (--databases:导出数据库列表，单个库时可省略）


--tables 表列表（单个表时可省略）
①同时导出结构以及数据时可同时省略-d和-t
②同时 不 导出结构和数据可使用-ntd
③只导出存储过程和函数可使用-R -ntd
④导出所有(结构&数据&存储过程&函数&事件&触发器)使用-R -E(相当于①，省略了-d -t;触发器默认导出)
⑤只导出结构&函数&事件&触发器使用 -R -E -d

Import

1. First create an empty database

mysql>create database test;

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

    mysql>use test;

(2) Set the database encoding

    mysql>set names utf8;

(3) Import data (note the path of the sql file)

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

Method 2:

mysql -hhostname -uusername -ppassword abc < /home/justin/test.sql;

Related recommendations:

php use Export and import MySQL database using command line, command line mysql

php Export and import MySQL database using command line, command line mysql_PHP tutorial

MYSQL Database import and export command_MySQL

The above is the detailed content of Mysql database export and import sql database file commands. For more information, please follow other related articles on the PHP Chinese website!