Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 6 down vote favorite
1

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

share|improve this question
+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) – RolandoMySQLDBA Jul 25 '11 at 15:49

1 Answer

up vote 5 down vote accepted

This should do it for you:

mysqldump -h... -u... -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
                      --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).

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
share|improve this answer
1  
I tried this and had to add the '-t' option to not get the create table statements. – Derek Downey Jul 25 '11 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 !!! – RolandoMySQLDBA Jul 25 '11 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 – Derek Downey Jul 25 '11 at 16:43

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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