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
  • 182,700
  • 33
  • 317
  • 520
dole doug
  • 391
  • 3
  • 6
  • 11

2 Answers2

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,440
  • 11
  • 78
  • 104
RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
  • @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 02 '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 07 '15 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 07 '15 at 14:21
  • @RolandoMySQLDBA - What do -A -N do? – MontyPython Jul 19 '15 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 '15 at 09:38
  • Thanks. This was really helpful. I thought my basic dump was doing the job, and it wasn't. Everything was so mangled. – Ken Ingram Oct 04 '19 at 09:55
  • Found more infor on dumping GRANTS: mysql -u user -ppassword --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ',QUOTE(user),'@',QUOTE(host),';') FROM mysql.user WHERE user<>''" | mysql -u user -ppassword --skip-column-names -A | sed 's/$/;/g' > MySQLUserGrants.sql – Ken Ingram Oct 04 '19 at 22:21
1

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
  • 25,896
  • 22
  • 57
  • 72
Krzysztof Dabrowski
  • 11
  • 1