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?

  • It has always worked for me by default. Are you sure that the user has the required permissions? Commented May 6 at 22:02

3 Answers 3


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.

  • @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' Commented Oct 2, 2011 at 18:44
  • 1
    @RolandoMySQLDBA, Isn't --triggers dumped by default? What would be the difference between --no-data --routines --triggers vs --no-data --routines?
    – Pacerier
    Commented May 7, 2015 at 14:05
  • 3
    @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. Commented May 7, 2015 at 14:21
  • @RolandoMySQLDBA - What do -A -N do? Commented Jul 19, 2015 at 17:05
  • 1
    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 Commented Nov 13, 2015 at 9:38

In my case adding --triggers was not enough. It has turned out that the user I was using to dump didn't have such a privilege in the DB.


It is possible for the triggers to be copied successfully in a dump and restore but not be visible from a database tool run from a client computer. But visible from a database tool installed on the server hosting the database. At least that was my experience using SQLyog. I confirmed that the definition of the triggers were at the bottom of the dump file.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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