I need to dump only the stored procedures : no data, no table creation. How can I do this using mysqldump?
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.
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
answered Jul 25 '11 at 15:30
-
11I tried this and had to add the '-t' option to not get the create table statements. 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 !!! 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 Jul 25 '11 at 16:43
mysqldump: Got error: 23: "Out of resources when opening file"I had the--single-transactionoption to resolve this error. And if youy only want the routine, you can add --no-create-infoto avoid the create table statement.