Dumping MySQL Stored Procedures, Functions and Triggers

MySQL 5 has introduced some new interesting features, like stored procedures and triggers.
I will show in this small post how we can backup and restore these components using mysqldump.

mysqldump will backup by default all the triggers but NOT the stored procedures/functions. There are 2 mysqldump parameters that control this behavior:

–routines - FALSE by default
–triggers - TRUE by default

This means that if you want to include in an existing backup script also the triggers and stored procedures you only need to add the –routines command line parameter:

mysqldump <other mysqldump options> --routines > outputfile.sql

Let’s assume we want to backup ONLY the stored procedures and triggers and not the mysql tables and data (this can be useful to import these in another db/server that has already the data but not the stored procedures and/or triggers), then we should run something like:

mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt <database> > outputfile.sql
and this will save only the procedures/functions/triggers of the <database>. If you need to import them to another db/server you will have to run something like:

mysql <database> < outputfile.sql

Random Posts

7 Responses to “Dumping MySQL Stored Procedures, Functions and Triggers”

1
Roger
10th August 2007, 03:12
Thanks for your post,It’s more timely.
2
Flower
16th August 2007, 10:26
Thanks
3
Luz
19th August 2007, 00:36
hi i enjoyed the read
4
Julian
13th September 2007, 15:07
nice solution, but it doesn’t export the DETERMINISTIC, NO SQL or READ SQL DATA directives. when i try to restore the dump on a replicated server i get an “Error 1418 (HY000): This function has none of DETERMINISTIC …”
i cannot use the set log_bin_trust_function_creators=1; because it’s a production server and they wont accept this kind of statement in the script.
any solutions ?
5
Eric
23rd February 2008, 20:06
Thanks for this. I forgot the -r on a huge db dump and your code to export ONLY the sps was a lifesaver.
6
Ash (philippines)
10th July 2008, 10:53
Thanks a lot. I spent half-an-hour recreating my stored proc/funcs from source files after dumping ito devt server, until I found out this post.

Regards!
7
Patrice
30th July 2008, 01:51
remove –skip-opt to get drop statements to override exisiting procedures with no pain