Home  >  Article  >  Database  >  mysql dump/restore stored procedures and triggers

mysql dump/restore stored procedures and triggers

不言
不言Original
2019-03-05 10:24:572806browse

MySQL5.0 first introduced stored procedures and triggers. Therefore, if you are still using an older version of MySQL you can upgrade it to MySQL 5.0 or higher to take advantage of these features. This article will introduce how to use the mysqldump command to dump stored procedures and triggers.

mysql dump/restore stored procedures and triggers

#What is a stored procedure?

By definition, a stored procedure is a declarative piece of SQL code stored in a database directory that can be later called by a program, trigger, or even a stored procedure.

What is a trigger?

Triggers are event-driven, specialized procedures that are stored in and managed by the database. Triggers are SQL procedures that initiate operations on the occurrence of an event, such as an insert, delete, or update.

When we simply execute mysqldump, it automatically backs up triggers but not stored procedures by default.

Backup stored procedures and routines

We need to specify --routines to backup stored procedures with data and tables.

The following command will back up the entire database, including stored procedures. For example, the database name is "mydb".

$ mysqldump -u root -p --routines mydb > mydb.sql

To back up only stored procedures and triggers (excluding tables and data), you need to use the following command.

$ mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt mydb > mydb.sql

Restore Procedure

To restore the stored procedures in the database, just use the following command, but make sure you have backed it up properly before restoring to avoid any data loss.

$ mysql -u root -p mydb < mydb.sql

The above is the detailed content of mysql dump/restore stored procedures and triggers. For more information, please follow other related articles on the PHP Chinese website!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn