Can mysqldump dump triggers and procedures?

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
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.

StackExchange Database Administrators Q#6420, answer score: 16