14

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?

Improve this question

2 Answers 2

Reset to default
16

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`
do
    mysqldump -u... -p... --no-data --no-create-info --routines dbname > ${DB}-routines.sql
    mysqldump -u... -p... --no-data --triggers dbname > ${DB}-schema-triggers.sql
done

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

Improve this answer
7
  • @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, 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
    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.
    – RolandoMySQLDBA
    May 7, 2015 at 14:21
  • @RolandoMySQLDBA - What do -A -N do?
    – MontyPython
    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
    – AlexandruSerban
    Nov 13, 2015 at 9:38
0

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.

Improve this answer

Your Answer

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

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