How to write stored procedures to separate files with mysqldump?

Question

The mysqldump option --tab=path 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 --routines to the second command, they will not get their own files.

Answer 1

I created a script to output to a separate file.

https://gist.github.com/temmings/c6599ff6a04738185596

example: mysqldump ${DATABASE} --routines --no-create-info --no-data --no-create-db --compact | ./seperate.pl

File is output to the directory(out/).

$ tree . └── out ├── FUNCTION.EXAMPLE_FUNCTION.sql └── PROCEDURE.EXAMPLE_PROCEDURE.sql

Answer 2

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