| To dump a database, we use mysqldump; to restore the database, we use mysql command:
$ mysqldump -u test -p <database> > database.sql
$ mysql --verbose --user=test
--password=test <database> < database.sql
The issue is: mysqldump will backup by default all the triggers but NOT the stored procedures/functions.
Suppose we have some of procedures/functions, the above mysqldump/mysql are not enough to dump all.
To solve it, there are 2 mysqldump parameters could control this behavior:
- – routines – FALSE by default
routines mean procedures/functions, it remarks that by default, all the procedures and functions do not dump.
- – triggers – TRUE by default
by defult, all triggers do dump with schema and data.
This means that if we want to dump ALL,
we only need to add the –routines command line parameter:
mysqldump --user=test --password=test
--routines > procedures.sql
The above will dump all database structure/data as well as procedures/functions.
Let’s assume we ONLY want to backup the stored procedures/functions, not the mysql tables and data
(this can be useful to import these into 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
--verbose <database> > procedures.sql
By using this, we can operate the stored procedures and functions alone or with whole database schema and data for the purpose of database transfer.