Backing up MySQL stored procedures, functions and triggers

MySQL 5 introduced some interesting new features, like stored procedures and triggers. In this short note I’ll show how to back up and restore these using mysqldump.

By default, mysqldump backs up all triggers, but NOT stored procedures/functions. There are 2 parameters that tell MySQLDump what to do:

• routines - FALSE by default
• triggers - TRUE by default

If you want your existing backup script to also include triggers and stored procedures, all you need to do is add --routines as a command-line parameter:

mysqldump -routines db_name > file_name.sql

If we want to back up ONLY the stored procedures and triggers, not the MySQL tables and data (useful for importing them into another database/server that already has the data but not the stored procedures and/or triggers), we should run something like:

mysqldump -routines -no-create-info -no-data -no-create-db -skip-opt db_name > file_name.sql

Restoring is very simple:

mysql db_name < file_name.sql

You can view functions and procedures by running these commands:

SHOW PROCEDURE STATUS;  
SHOW FUNCTION STATUS;

Or like this:

select name from mysql.proc;

The following command returns the SQL that creates the function:

show create function db_name.function_name;