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

 

Share on Facebook




About Pragnesh Karia

Pragnesh Karia, Open Source Enthusiast, Software Professional, Software Developer, Technical Lead ,Magento, Joomla ,Joomla LMS , Moodel LMS ,PHP ,Mysql, Ajax, Javascript, Jquery, Linux, Fan of Open Sources , Annet Technologies , SEO Analyst , Mootools