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
Tags: mysqldump
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.
13th January 2009, 05:38
Thanks
Really Nice one. Going to save a lot of time of mine
3rd February 2009, 12:08
Many thanks for the command. very useful.
11th February 2009, 21:30
Exactly what I was looking for. Thanks!
26th March 2009, 10:53
Thanks A Lot………
28th April 2009, 11:23
Just I need, perfect.
Thanks a lot (moltes gràcies).
19th June 2009, 14:38
Gracias, mysqldump –routines > outputfile.sql me sirvio de mucho para exportar las funciones cuando genero mis respaldos
Gracias
20th August 2009, 12:50
[...] does not dump stored procedures by default, so I needed to restore them from somewhere else. Now I know better, I need to include the parameter “–routines” to [...]
10th December 2009, 02:30
Any solution for
mysqldump: has insufficient privileges to SHOW CREATE PROCEDURE
“!
I have give the user all privileges including Create & Alter Routine,I don’t see a SHOW CREATE PROCEDURE option. Mysql version 5.0.85-community.
I have a similar issue with VIEWS, so I’ll take input on this one too, but more interested in the SProc issue.
Thanks! Joe
10th December 2009, 05:41
I think there is no other way in mysql 5.0.x; In mysql5.1 there should be updated privileges for procedures and triggers. hth.
8th January 2010, 15:09
Thanks for the hint!
8th January 2010, 15:13
Thanks Marius … I had the admin upgrade us to 5.1.40 and I can now dump both Stored Procs and Views. Thanks for the help and a great article!
Joe
31st March 2010, 03:40
Thanks u for the command
8th April 2010, 03:52
Nice post! Helped me a lot. Thanks.
29th April 2010, 20:36
You’re the one!
29th April 2010, 22:50
Thanks man, I had no idea about the –routines flag!
14th June 2010, 10:46
Thanx dude.
It helped me a lot!!
25th August 2010, 09:09
Thanks a lot, Marius. Your post has saved my day.
7th September 2010, 11:54
This version of MySQL doesn’t yet support ‘multiple triggers with the same action time and event for one table’
Use –skip-triggers also. To avoid above error. Because, triggers are default loaded in mysqldump.
7th October 2010, 02:01
Thanks
20th October 2010, 02:03
hi I have trouble in dumping also stored procedure from a remote dbase on a LNX server. I need to install it locally. Can I have more details on how running the below.
mysql> mysqldump -routine -uroot -padmin -dbasename > backup.sql
it appears via SSH get stuck. I do not know where the .sql file is located in the LNX server folders.
is this possible to do it trough phpmyadmin control panel?
hope you can give me a guidance.
thanks
20th October 2010, 09:44
@viktor: if you run this remotely you will have to use the host parameter (-h) for mysqldump
hth,
- Marius -
20th October 2010, 22:11
hi Marius
I’m running it through SSH (Putty) , dbase to dump ins on a linux server.
where do I have to place -h?
as below:
mysql> mysqldump -routine -uroot -padmin -dbasename -h> backup.sql
please just confirm , thanks again
20th October 2010, 23:21
@viktor: oh, I understood you run it on a remote server (where you add -h hostname). Still since you seem to connect with ssh to the server you run this command locally on that machine, hence you don’t need the -h. Again what is the problem? run the command without the pipe and see what it outputs, or look in the sql file you are creating. It might just take a lot of time to dump the db…
20th October 2010, 23:35
hi marius
it appears the problem I cannot connecto t the DBASE.
I log in via SSH with a certain server user and p.
however user abnd psw of the dbase are different.
I get this error see below
[adminxxxxxxx@hostname ~]$ mysqldump -r -uRoot -padmin – mydbr –result-file = mydbr.sql;
mysqldump: Got error: 1045: Access denied for user ‘adminpxxxxxx’@'localhost’ (using password: YES) when trying to connect
21st October 2010, 10:49
@viktor: the mysql user/pass/access (u -> user; p -> pass) is not working correctly from the error you are seeing.
21st October 2010, 13:22
marius
I know password is not accepted. I do not know why.
again I do have psw for log in into Lnx server which is different from the one for log in into the dbase I have to dump (MYDBR).
I’m fully ocnfused and I’ve lost so many hours on this , please give me support.
is there any other way to dump stored procedur ein mysql dbase together with data without going through the SSH . PHPMYADMIN does not perform such backup or dumps for what I know.
please advise
thanks
viktor
thanks
21st October 2010, 14:02
@viktor: I think the easiest think you can do is to create a new mysql user (mysql users are different than your system users) that has FULL privileges. You can probably do this with phpmyadmin; then try again; it should work just fine. I am not very familiar with phpmyadmin to tell if/how you can do the same with it. I always use command line as it is faster and easier to automate.
hth.
22nd October 2010, 00:25
all users I have have full privileges in mysql but they cannot log in into the lnx server via SSH as I need to do for running mysqldump. this is the problem. I’ve also addedd Main SSH user to the mysql with full privileges, but again it appears access when mysql dump run is denied.
hope you can give me the solution
i’m really frustrated.
thanks
22nd October 2010, 08:37
dear Marius
I fixed the user apparently.
I dumped the dbase, however I do not know where..
mysqldump –routines -uadminxxx -pPASSWORD –databases mydbr -h localhost;
if I’m going to specify the result-file=/localhost/dump.sql for instance I get message cannot write or open that location or aonyone else. all permissions are 777.
If you can please help me.! last support
22nd October 2010, 09:00
@viktor: just output it to a location you are sure you can write on, like /tmp/dump.sql
22nd October 2010, 09:20
I tried but in the root or in any other folder of this fu–ing linux server I cannot write despite I have 777 permits.
I tried on XAMPP locally and it works fine.
what I have to do =?
see below
[adminxxxxx@shanahaneng ~]$ mysqldump –routines -uadmxxxxxx -pxxxxxx –databases -r /private/mydbr.sql
mysqldump: Can’t create/write to file ‘/private/mydbr.sql’ (Errcode: 2)
22nd October 2010, 09:35
@viktor: as I said use /tmp where you should have permissions (-r /tmp/mydbr.sql)
23rd October 2010, 00:16
thansk marius
I have issue in changing or adding users in my lnx control panel so I cannot figure out how adding other users with 777 permissions.
I need to check wir h server owner manual is very poor.
thanks again I’ll be back
20th January 2011, 01:21
[...] Link: Dumping MySQL Stored Procedures, Functions and Triggers [...]
19th May 2011, 10:35
thanks for the great tip! worked like a charm.
10th June 2011, 04:32
http://dev.mysql.com/doc/refman/5.0/en/show-create-procedure.html
20th June 2011, 02:52
thansk a lot
19th July 2011, 20:51
[...] Dumping MySQL Stored Procedures, Functions and Triggers mysqldump will backup by default all the triggers but NOT the stored procedures/functions. There are 2 mysqldump parameters that control this behavior: [...]
8th December 2011, 04:15
Thanks for the blog entry, it is good search-engine bait.
If you always want to catch routines then it’s probably best to add some default options for mysqldump by using the site-wide or per-user mysql config files:
eg. ~/.my.cnf
[mysqldump]
password=
user=
routines=true
13th December 2011, 22:29
Great article , it was very helpful for me.
9th January 2012, 14:58
[...] Dumping SP and triggers Like this:LikeBe the first to like this post. [...]