By nakhli

2011-07-25 14:55:41 8 Comments

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


@RolandoMySQLDBA 2011-07-25 15:30:09

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).
  -t, --no-create-info   Do not write CREATE TABLE statements that create each 
                         dumped table.


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

@Derek Downey 2011-07-25 16:21:17

I tried this and had to add the '-t' option to not get the create table statements.

@RolandoMySQLDBA 2011-07-25 16:30:48

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 !!!

@Derek Downey 2011-07-25 16:43:45

No problem. In my case, I just wanted stored functions/procedures for a specific database, and not triggers. So it worked out well

Related Questions

Sponsored Content

4 Answered Questions

[SOLVED] Verify all Stored Procedures

1 Answered Questions

MySQL - Backup new and modified entries only

  • 2014-11-26 19:23:45
  • Warren M. Nocos
  • 126 View
  • 0 Score
  • 1 Answer
  • Tags:   mysql backup

1 Answered Questions

[SOLVED] Dump of a mysql table on import replaced existing records

2 Answered Questions

[SOLVED] DROP PROCEDURE IF EXISTS not included in mysqldump

1 Answered Questions

[SOLVED] Can mysqldump dump triggers and procedures?

1 Answered Questions

[SOLVED] MySQLDump wrong dump

2 Answered Questions

[SOLVED] MySQL: Securing Access using Stored Procedures

1 Answered Questions

[SOLVED] How to retrieve stored procedures from raw data

2 Answered Questions

[SOLVED] How do I dump only the data for all tables using mysqldump

  • 2013-02-05 06:25:15
  • Sam
  • 29447 View
  • 9 Score
  • 2 Answer
  • Tags:   mysql mysqldump

1 Answered Questions

[SOLVED] MySQL Dump all rountines except one

Sponsored Content