Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to export MySQL database with triggers and procedures?

Tags:

mysql

How to create a MySQL database dump (.sql file) with all its triggers and procedures?

like image 310
Yugal Avatar asked Feb 22 '11 07:02

Yugal


People also ask

How will you take backup of triggers procedures and functions alone?

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.


3 Answers

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.

like image 101
Haim Evgi Avatar answered Oct 18 '22 00:10

Haim Evgi


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
like image 26
Usman Avatar answered Oct 17 '22 23:10

Usman


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
like image 9
Krishna Vedula Avatar answered Oct 18 '22 01:10

Krishna Vedula