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!