Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Is there any way of making a mysqldump which will save all the triggers and procedures from a specified db?

Some time ago I read that mysqldump will also save my triggers, but it doesn't look like it. My second related question is how can I check in a sql file if triggers exists?

share|improve this question

I normally do not separate triggers from the tables they were meant for. I dump like this:

mysqldump -u... -p... --no-data --routines --triggers dbname > DBSchema.sql

Check for presence of routines and triggers like this:

SELECT COUNT(1) FROM mysql.proc;

SELECT COUNT(1) FROM information_schema.triggers;

SELECT * FROM information_schema.triggers\G

If you want to get this done to all DBs in the MySQL Instance, do this:

mysql -u... -p... -A -N -e"SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN ('information_schema','mysql')" > /tmp/dblist.txt
for DB in `cat /tmp/dblist.txt`
    mysqldump -u... -p... --no-data --no-create-info --routines dbname > ${DB}-routines.sql
    mysqldump -u... -p... --no-data --triggers dbname > ${DB}-schema-triggers.sql

That way, stored procedures go in a routines dump for the DB, while the schema and triggers go in another dump.

share|improve this answer
@dole Rolando's right on how to dump triggers/procedures. If you want to see if they exist in a sql file, open it and search for 'CREATE TRIGGER' calls. If in *nix, grep 'CREATE TRIGGER' – Derek Downey Oct 2 '11 at 18:44
@RolandoMySQLDBA, Isn't --triggers dumped by default? What would be the difference between --no-data --routines --triggers vs --no-data --routines? – Pacerier May 7 '15 at 14:05
@Pacerier There have been situations where I didn't want triggers and I would use --skip-triggers on such occasions (such as setting up slaves that didn't need the triggers). As a constant reminder to myself of the possibility of skipping options, I always use certain flags in mysqldumps (--routines, --triggers) even if they are default. So, it's just a personal preference. If you trust that a default setting will remain a default from version to version and never deal with adhoc situations, then you express the mysqldumps as needed as long as the resulting mysqldump is correct for you. – RolandoMySQLDBA May 7 '15 at 14:21
@RolandoMySQLDBA - What do -A -N do? – MontyPython Jul 19 '15 at 17:05
I have used regular expressions to prepend DROP trigger statements: Replace this: (\/*\!50003 CREATE*\/ \/*\!50017 DEFINER\=some_text_here*\/\ /*\!50003 TRIGGER (.*?) ) With this: DROP TRIGGER /*!50032 IF EXISTS */ $2;;\n$1 – AlexandruSerban Nov 13 '15 at 9:38

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.