Jader Dias Jader Dias - 1 year ago 258
SQL Question

How to write stored procedures to separate files with mysqldump?

The mysqldump option

writes the creation script of each table in a separate file. But I can't find the stored procedures, except in the screen dump.

I need to have the stored procedures also in separate files.

The current solution I am working on is to split the screen dump programatically. Is there a easier way?

The code I am using so far is:

#save all routines to a single file
mysqldump -p$PASSWORD --routines --skip-dump-date --no-create-info --no-data --skip-opt $DATABASE > $BACKUP_PATH/$DATABASE.sql
#save each table to its file
mysqldump -p$PASSWORD --tab=$BACKUP_PATH --skip-dump-date --no-data --skip-opt $DATABASE

Even if I add
to the second command, they will not get their own files.

Answer Source

The mysqldump command does not support dumping stored procedures into individual files.

But, it is possible to do it using the mysql command.

mysql --skip-column-names --raw mydatabase -e "SELECT CONCAT('CREATE PROCEDURE `', specific_name, '`(', param_list, ') AS ') AS `stmt`, body_utf8 FROM `mysql`.`proc` WHERE `db` = 'mydatabase' AND specific_name = 'myprocedure';" 1> myprocedure.sql

For a more complete example, using Windows Batch, look into my answer on another question. MySQL - mysqldump --routines to only export 1 stored procedure (by name) and not every routine

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download