50

I need to dump only the stored procedures : no data, no table creation. How can I do this using mysqldump?

2
  • 1
    +1 for this question because there are many developers in the DB world that like cataloging stored procedures outside of MySQL in version control software that would like to do this (I'm not a fan of doing so, myself) Commented Jul 25, 2011 at 15:49
  • In my case this command give me an error mysqldump: Got error: 23: "Out of resources when opening file" I had the --single-transaction option to resolve this error. And if youy only want the routine, you can add --no-create-info to avoid the create table statement.
    – bedomon
    Commented Jun 26, 2014 at 12:21

1 Answer 1

57

This should do it for you:

mysqldump -h yourhost -u username -p -n -d -t --routines --triggers --all-databases > MySQLStoredProc.sql

  -n, --no-create-db     Suppress the CREATE DATABASE ... IF EXISTS statement 
                         that normally is output for each dumped database if
  -h                     host replace yourhost with name of host
  -u                     username, replace username with username
  -p                     when added will ask for password. 
                         --all-databases or --databases is given.
  -d, --no-data          No row information.
  --triggers             Dump triggers for each dumped table.
                         (Defaults to on; use --skip-triggers to disable.)
  -R, --routines         Dump stored routines (functions and procedures).
  -t, --no-create-info   Do not write CREATE TABLE statements that create each 
                         dumped table.

CAVEAT

It would be much better not to separate the stored procedures from the database so that specific stored procedures will be created in the database it was meant for. The same goes for triggers. This would be preferrable:

mysqldump -h... -u... -p... -d --routines --triggers --all-databases > MySQLStoredProc.sql
3
  • 13
    I tried this and had to add the '-t' option to not get the create table statements. Commented Jul 25, 2011 at 16:21
  • Forgot that one, too bad I can't upvote comments. I updated the first mysqldump command to include it. The second one should be left out to associate every trigger to its base table. Thanks again, @DTest !!! Commented Jul 25, 2011 at 16:30
  • No problem. In my case, I just wanted stored functions/procedures for a specific database, and not triggers. So it worked out well Commented Jul 25, 2011 at 16:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.