How to export MySQL database with triggers and procedures?

How to create a MySQL开发者_运维知识库 database dump (.sql file) with all its triggers and procedures?

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 default

so 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

We can use -R instead of using --routine flag.

mysqldump -u mysqluser -R databasename > /tmp/databasename.sql