Query, export, and import stored procedures in mysql

 

1. query stored procedures in a database

Method 1 (view the content of a specified Stored Procedure ):

Select body from mysql. proc where name = 'Procedure _ name ';

 

Method 2 (view all stored procedures ):

Show procedure status;

 

Ii. Export and Import MySQL stored procedures. Usage:

1. Export

[Root @ localhost bin] # mysqldump-uroot-p-hlocalhost-P3306-n-d-t-R DBName> procedure_name. SQL

Parameter description:

-N: -- no-create-db

-D: -- no-data

-T: -- no-create-info

-R: -- routines Dump stored routines (functions and procedures)

 

Mysqldump is a client tool used to back up databases or migrate data between different databases. The backup content contains the SQL statement used to create a live load table:

Main parameters:

1. connection options

-U, -- user = name

-P, -- password = name

-H, -- host = name

-P, -- port = #

2. Output content options

-- Add-drop-database

-- Add-drop-table

-N; -- no-create-db

-D; -- no-data

-T; -- no-create-info

3. output format options

-- Compact

-C -- complete-insert

-T (data backup in the specified data table is a simple data file and a table creation SQL file)

Note: The xx.sqltable file is created by the linux rootuser, And the xx.txt file is created by the linux mysql user. Therefore, the storage path of these two files must ensure that the mysql user has the permission to read and write the created files.

-- Fields-terminated-by = name (domain separator)

-- Fields-enclosed-by = name (domain quote)

-- Fields-optionally-enclosed-by = name (optional characters for domain reference)

-- Fields-escaped-by = name (Escape Character)

4. Character Set options

-- Default -- character-set = xx

5. Other options

-F -- flush-logs (refresh the log before backup)

-L -- lock-tables (apply a read lock to all tables)

2. Import

Mysql-hhostname-uusername-ppassword databasename <backupfile. SQL

This article is from the "from cisco to linux" blog