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

This article will help you to how to make a dump of Stored procedures and triggers using mysqldump .

What is Stored Procedure ?

A stored procedure, by definition, is a segment of declarative code which is stored in the 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 of triggers but it will not stored procedures by default.

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  -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

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

Change the stored procedure definer

To see the definers:

show procedure status;

You can change them like this:

UPDATE `mysql`.`proc` p SET definer = 'YournewDefiner' WHERE definer='OldDefinerShownBefore'

For example:

 UPDATE `mysql`.`proc` p SET definer = '[email protected]%' WHERE definer='[email protected]%'

Be careful, because this will change all the definers for all databases.

Print Friendly, PDF & Email

Comments

comments

Bài viết liên quan