MySQL – Backup Stored Procedures, Functions and Triggers

By default, mysqldump will backup all the triggers but NOT the stored procedures and functions.

There are 2 parameters that control this action:

--routines (FALSE by default)
--triggers (TRUE by default)

If you want to include the stored procedures and triggers, you need to add the –routines in your backup command as follows.

This command will backup the entire database including stored procedures.

mysqldump -u USERNAME -p --routines DBName > outputfile.sql

But if you wanna backup just the stored procedures and triggers (excluding table and data), use the following command.

mysqldump -u USERNAME -p --routines --no-create-info --no-data --no-create-db --skip-opt lm_cia > outputfile.sql

You can also put routines=true in the [mysqldump] section of your my.cnf

Cheers!

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter
Bookmark the permalink.

Leave a Reply

Leave a Reply

Your email address will not be published. Required fields are marked *