• Home
  • Ubuntu 18.04
    • Whats New?
    • Upgrade Ubuntu
    • Install Java
    • Install Node.js
    • Install Docker
    • Install Git
    • Install LAMP Stack
  • Tutorials
    • AWS
    • Shell Scripting
    • Docker
    • Git
    • MongoDB
  • Funny Tools
  • FeedBack
  • Submit Article
  • About Us
TecAdmin
Menu
  • Home
  • Ubuntu 18.04
    • Whats New?
    • Upgrade Ubuntu
    • Install Java
    • Install Node.js
    • Install Docker
    • Install Git
    • Install LAMP Stack
  • Tutorials
    • AWS
    • Shell Scripting
    • Docker
    • Git
    • MongoDB
  • Funny Tools
  • FeedBack
  • Submit Article
  • About Us

MySQL Dump/Restore Stored Procedures and Triggers

Written by Rahul, Updated on October 9, 2018

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

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

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

References: Stored Procedures , Triggers

Share it!
Share on Facebook
Share on Twitter
Share on LinkedIn
Share on Reddit
Share on Tumblr
Share on Whatsapp
Rahul
Rahul
Connect on Facebook Connect on Twitter

I, Rahul Kumar am the founder and chief editor of TecAdmin.net. I am a Red Hat Certified Engineer (RHCE) and working as an IT professional since 2009..

3 Comments

  1. Avatar Lance Reply
    August 15, 2018 at 6:42 pm

    Same question as Ankit. Please elaborate.

  2. Avatar Ankit Arora Reply
    May 22, 2017 at 12:32 pm

    Why should we backup Stored Procedures, Triggers and Routines?
    What’s the need?
    Please elaborate.

    • Avatar Paul Freeland Reply
      September 6, 2019 at 9:08 am

      If you want a database backup that you can restore on another machine, then you’ll need to ensure you’ve included the definitions for Stored procs, Triggers and Routines/Functions in the backup. Data and table structure are included by default.

Leave a Reply Cancel reply

Popular Posts

  • How to Install Python 3.9 on CentOS/RHEL 7 & Fedora 32/31 0
  • How To Install VNC Server on Ubuntu 20.04 1
  • How To Install NVM on macOS with Homebrew 0
  • (Solved) apt-add-repository command not found – Ubuntu & Debian 0
  • How to Install .NET Core on Debian 10 0
© 2013-2020 Tecadmin.net. All Rights Reserved | Terms  | Privacy Policy