Stored procedures and Triggers are first introduces 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.
This article will help you to how to make dump of Stored procedures and triggers using mysqldump command.
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.
When we simply execute mysqldump, It automatically takes backup of triggers but it will not backup stored procedures by default.
Example of Backup Stored Procedures and Routines
We need to specify –routines to take backup of stored procedures with data and tables.
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 ( No data and tables ) use following command.
# mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt mydb > mydb.sql
Restore Stored Procedures
To restore stored procedures in database simply use 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
References: Stored Procedures , Triggers
Why should we backup Stored Procedures, Triggers and Routines?
What’s the need?
Please elaborate.