如何导出带触发器和过程的 MySQL 数据库？

如何创建一个 MySQL 数据库转储(.sql文件)及其所有触发器和过程？

123820 次浏览
小开
最佳答案

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

提交答案