How to create a MySQL database dump (.sql
file) with all its triggers and procedures?
By default, mysqldump will backup all the triggers but NOT the stored procedures and functions. If you want to include the stored procedures and triggers, you need to add the –routines in your backup command as follows.
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 defaultso in mysqldump
command , add --routines
like :
mysqldump <other mysqldump options> --routines > outputfile.sql
See the MySQL documentation about mysqldump arguments.
May be it's obvious for expert users of MYSQL but I wasted some time while trying to figure out default value would not export functions. So I thought to mention here that --routines param needs to be set to true to make it work.
mysqldump --routines=true -u <user> my_database > my_database.sql
I've created the following script and it worked for me just fine.
#! /bin/sh
cd $(dirname $0)
DB=$1
DBUSER=$2
DBPASSWD=$3
FILE=$DB-$(date +%F).sql
mysqldump --routines "--user=${DBUSER}" --password=$DBPASSWD $DB > $PWD/$FILE
gzip $FILE
echo Created $PWD/$FILE*
and you call the script using command line arguments.
backupdb.sh my_db dev_user dev_password
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With