MySQL - mysqldump --routines to only export 1 stored procedure (by name) and not every routine

Question

So we have a lot of routines that come out from exporting. We often need to get these out in CLI, make changes, and bring them back in. Yes, some of these are managed by different folks and a better change control is required, but for now this is the situation.

If I do:

mysqldump --routines --no-create-info --no-data --no-create-db

then great, I have 200 functions. I need to go through a file to find just the one or set I want.

Is there anyway to mysqldump routines that I want like there is for tables?

Answer 1

Another way to go about this would be the following. Do note, however, that you have to have root privileges in the target database in order to import rows into mysql.proc:

mysqldump --compact --no-create-info --where="db='yourdatabasename' AND type='PROCEDURE' AND name IN ('yoursp1', 'yoursp2')" --databases mysql --tables proc

Answer 2

To answer your exact question: no.

But this will probably give you what you want.

Take a look at SHOW CREATE PROCEDURE and SHOW CREATE FUNCTION:

http://dev.mysql.com/doc/refman/5.0/en/show-create-procedure.html

http://dev.mysql.com/doc/refman/5.0/en/show-create-function.html

Those commands allow you to dump the code for one routine at a time.

Answer 3

It is possible to dump a single function or procedure using the command that Ike Walker mentioned, but the SHOW CREATE PROCEDURE and SHOW CREATE FUNCTION commands don't allow to select only a few columns from the output.

Here is a example of a Windows batch command line to dump a single procedure, using the system table mysql.proc:

mysql --defaults-extra-file=myconfig.cnf --skip-column-names --raw --batch mydatabase -e "SELECT CONCAT('DELIMITER $$\nCREATE PROCEDURE `', specific_name, '`(', param_list, ') AS \n', body_utf8, ' $$\nDELIMITER ;\n') AS `stmt` FROM `mysql`.`proc` WHERE `db` = 'mydatabase' AND specific_name = 'myprocedure';" 1> myprocedure.sql

This will redirect the output of mysql into the file myprocedure.sql.

The --batch option tells the mysql client to remove the table borders from the output. The --skip-column-names option removes the column headers from the output. The --raw option tells MySQL to not escape special characters on the output, keeping new lines as is instead of replacing them with \n.

And if you want to dump ALL the procedures in different files, this example in batch should work:

dump-procedures.bat

@echo off

REM set the target database
set database=mydatabase

REM set the connection configuration file
set auth=--defaults-extra-file=myconfig.cnf

REM set the routine type you want to dump
set routine_type=PROCEDURE

set list_file=%routine_type%S.csv

if "%routine_type%"=="PROCEDURE" (
set ending=AS 
)
if "%routine_type%"=="FUNCTION" (
set ending=RETURNS ', `returns`, '
)

echo Dumping %routine_type% list to %list_file%
mysql %auth% --skip-column-names --raw %database% -e "SELECT routine_name FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = DATABASE() AND ROUTINE_TYPE = '%routine_type%';" 1> %list_file%

for /f "tokens=*" %%a in (%list_file%) do (
    echo Dumping %routine_type% %%a
    mysql %auth% --skip-column-names --raw --batch %database% -e "SELECT CONCAT('DELIMITER $$\nCREATE PROCEDURE `', specific_name, '`(', param_list, ') %ending% \n', body_utf8, ' $$\nDELIMITER ;\n') AS `stmt` FROM `mysql`.`proc` WHERE `db` = '%database%' AND specific_name = '%%a';" 1> %%a.sql
)

It works in 2 steps, where it first dumps the list of all procedures into the file procedures.csv, and then iterates in each line and uses the names of the procedures to dump each procedure in a different file.

In this example, I am also using the option --defaults-extra-file, where some configuration parameters are set in a different file, and allows to invoke the command without needing to type the password each time or writing the password inside the batch itself. I created a file with this content

myconfig.cnf

[client]
host=localhost
port=3306

user=myusername
password=mypassword

This solution also works with function, defining the routine_type variable to:

set routine_type=FUNCTION