Facebook Twitter Instagram
    TecAdmin
    • Home
    • FeedBack
    • Submit Article
    • About Us
    Facebook Twitter Instagram
    TecAdmin
    You are at:Home»Databases»MySQL»How to Backup/Restore MySQL Stored Procedures & Triggers

    How to Backup/Restore MySQL Stored Procedures & Triggers

    By RahulJune 16, 20222 Mins Read

    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.

    Advertisement

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

    What is Stored Procedure?

    A stored procedure, by definition, is a segment of declarative SQL code that 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 create a backup of the 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 (excluding table structure 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

    MySQL stored procedures triggers
    Share. Facebook Twitter Pinterest LinkedIn Tumblr Email WhatsApp

    Related Posts

    (Resolved) MySQL connection error: certificate verify failed

    How to Connect Python with MySQL Database

    Installing MySQL 8.0 on Amazon Linux 2

    How To Install MySQL 8 on Amazon Linux 2

    View 3 Comments

    3 Comments

    1. Lance on August 15, 2018 6:42 pm

      Same question as Ankit. Please elaborate.

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

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

      Reply
      • Paul Freeland on September 6, 2019 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.

        Reply

    Leave A Reply Cancel Reply

    Advertisement
    Recent Posts
    • How to Split Large Archives in Linux using the Command Line
    • System.out.println() Method in Java: A Beginner’s Guide
    • Split Command in Linux With Examples (Split Large Files)
    • Test Your Internet Speed from the Linux Terminal
    • 11 Practical Example of cat Command in Linux
    Facebook Twitter Instagram Pinterest
    © 2023 Tecadmin.net. All Rights Reserved | Terms  | Privacy Policy

    Type above and press Enter to search. Press Esc to cancel.