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?

RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536
dole doug
  • 391
  • 3
  • 6
  • 11

3 Answers3

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.

Derek Downey
  • 23,568
  • 11
  • 79
  • 104
RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536
2

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.

mustaccio
  • 28,207
  • 24
  • 60
  • 76
Krzysztof Dabrowski
  • 21
  • 1
0

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.

user2367418
  • 101