Dumping MySQL Stored Procedures, Functions and Triggers

MySQL  has introduced some new interesting features, like stored procedures and triggers. I will show in this small post how we can backup and restore these components using mysqldump.

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.

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:

#

mysqldump <other mysqldump options> –routines outputfile.sql

Let’s assume we want to backup ONLY the stored procedures and triggers and not the mysql tables and data (this can be useful to import these in another db/server that has already the data but not the stored procedures and/or triggers), then we should run something like:

#

mysqldump –routines –no-create-info –no-data –no-create-db –skip-opt <database> > outputfile.sql

and this will save only the procedures/functions/triggers of the . If you need to import them to another db/server you will have to run something like:

#

mysql <database> < outputfile.sql

MySQL 5 has introduced some new interesting features, like stored procedures and triggers. I will show in this small post how we can backup and restore these components using mysqldump.

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:

1
mysqldump <other mysqldump options> --routines outputfile.sql

Let’s assume we want to backup ONLY the stored procedures and triggers and not the mysql tables and data (this can be useful to import these in another db/server that has already the data but not the stored procedures and/or triggers), then we should run something like:

1
mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt <database> > outputfile.sql

and this will save only the procedures/functions/triggers of the . If you need to import them to another db/server you will have to run something like:

1
mysql <database> < outputfile.sql

Related Posts

  • 37
    There are many difference between function and stored procedure. In this article we will discuss about the difference between function and procedures in context of SQL Server. Stored Procedures are pre-compile objects which are compiled for first time and its compiled format is saved which executes (compiled code) whenever it…
    Tags: procedure, procedures, stored
  • 36
    Stored Procedures are pre-compile objects which are compiled for first time and its compiled format is saved which executes (compiled code) whenever it is called. But Function is compiled and executed every time when it is called. For more about stored procedure and function refer the articles Different types of…
    Tags: procedure, stored, procedures
  • 32
    What is mysqldump The mysqldump client is a utility that performs logical backups, producing a set of SQL statements that can be run to reproduce the original schema objects, table data, or both. It dumps one or more MySQL database for backup or transfer to another SQL server. The mysqldump command can also generate output…
    Tags: mysqldump, mysql, database, backup

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: