MySQL 5 has introduced some newinteresting features, like storedprocedures and triggers.I will show in this small post how we can backup and restore these componentsusing mysqldump.

mysqldumpwill backup by default all the triggers but NOT the storedprocedures/functions. There are 2 mysqldump parameters that control thisbehavior:

–routines- FALSE by default

–triggers- TRUE by default

Thismeans that if you want to include in an existing backup script also thetriggers and stored procedures you only need to add the –routines commandline parameter:

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

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

mysqldump --routines --no-create-info  --no-data --no-create-db --skip--opt <database> > outputfile.sql

and thiswill save only the procedures/functions/triggers of the . If you need toimport them to another db/server you will have to run something like:

mysql  <database>  <  outputfile.sql

执行存储过程的用户需要两个权限.

grant  select on mysql.proc  to user@’’;

grant  execute on mysql.*  to user@’’