How to dump the database with triggers and procedures?

Stored procedures and Triggers are first introduced with MySQL 5.0. So if you are still using MySQL older version’s upgrade it to MySQL 5.0 or higher version to use these features.

What is Stored Procedure ?

A stored procedure, by definition, is a segment of declarative SQL code which is stored in the database catalog and can be invoked later by a program, a trigger or even a stored procedure.

What is Triggers ?

Triggers are event-driven specialized procedures, they are stored in and managed by the database. A trigger is a SQL procedure that initiates an action on an event (Like INSERT, DELETE or UPDATE) occurs.

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:

Backup Stored Procedures and Routines

We need to specify --routines to take backup of stored procedures with data and tables.

The following command will take backup of entire database including stored procedures. For example, your database name is “mydb”.

mysqldump -u root -p --routines mydb > mydb.sql

To take backup of only Stored Procedures and Triggers (Exclude table and data ) use the following command.

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

To Take backup without triggers use following command

mysqldump -uroot -p --skip-triggers mydb> mydb.sql

Restore Procedures

To restore stored procedures in the database simply use the following command, But make sure you have taken backup properly before restoring it to avoid any data loss.

mysql -u root -p mydb < mydb.sql