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?


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' Oct 2 '11 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
    May 7 '15 at 14:05
  • 2
    @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. May 7 '15 at 14:21
  • @RolandoMySQLDBA - What do -A -N do? 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 Nov 13 '15 at 9:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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