Loading ...
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
10th August 2007, 03:12
Thanks for your post,It’s more timely.
16th August 2007, 10:26
Thanks
19th August 2007, 00:36
hi i enjoyed the read
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 ?
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.
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!
30th July 2008, 01:51
remove –skip-opt to get drop statements to override exisiting procedures with no pain
17th September 2008, 06:22
Thanks . It helps me a lot.
Can u pls tell me I how come I will tk only few SP backups [Exclude all tables and other SPs]
8th November 2008, 04:44
good job! help me a lot. thanks!
22nd November 2008, 18:03
We are having problem with stored function when restoring the backup from database1 onto database2. The restore process has an error of unknown user/define.
Our database1 and database2 have different users thus the definer, which is user1, in the stored function created in database1 is different from the user, which is user2, in database2.
What can we do in mysqldump to make the stored function do not restrict the user?
Thanks.
Tehhuei
23rd November 2008, 21:50
@Tehhuei: I don’t believe mysqldump has such an option. I would suggest you to ask this question on the mysql forums or even post a bug/feature request.
8th December 2008, 23:18
Marius: do you have an opinion about triggers@mysql - dos and donts type of information is what i am looking for and that’s how stumbled upon your blog post.
9th December 2008, 09:13
Prasana: nothing special; with 5.0 you need super privileges to create triggers; special care needs to be taken if used with replication in special cases (like for ex. if only one db is replicated and triggers are changing more dbs, etc.); clean code should not cause problems normally.