How to write stored procedures to separate files with mysqldump?

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$PASS开发者_如何学JAVAWORD --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.

---

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

---

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

---

I think the answer is: it is not possible without post-processing

---

This writes table definitions (not SPs) fwiw:

mysqldump -u<username> -p<password> -T<destination-directory> --lock-tables=0 <database>

One snag I ran into was, make sure you put enough permissions on . I just did chmod 777 on it.

A note on this--MySQL will write out the table structures in .sql files, and the data in .txt files. I wish it would just do it normal, thanks.