Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

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.

share|improve this answer
    
@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 '11 at 18:44
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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