I`v got an issue from our development team to dump all triggers from prod db and add them to development db.
It`s trivial, except all triggers deletion. Everyone use magic, procedures, etc.
How to dump only triggers:
/usr/bin/mysqldump --single-transaction --triggers --routines --no-data --no-create-db --no-create-info
-u backup -pMEGASECUREPASSWORD
-h dbslave1-1.randomthemes.com database_name > /home/backup/mysql/database_name_triggers.sql
-u backup -pMEGASECUREPASSWORD
-h dbslave1-1.randomthemes.com database_name > /home/backup/mysql/database_name_triggers.sql
How to delete all triggers:
Fastest way (a little bit hindy 🙂 🙂 but working o.k.): Upload trigger names to file, apply regexp, delete
$mysql -e "SELECT trigger_schema, trigger_name from information_schema.triggers" > ./drop_all_triggers.sql
regexps (use vi, Luke):
:%s/\n/;\r/g
:%s/database_name/DROP TRIGGER IF EXISTS database_name/g
:%s/\t/./g
$mysql mydatabase < ./drop_all_triggers.sql
regexps (use vi, Luke):
:%s/\n/;\r/g
:%s/database_name/DROP TRIGGER IF EXISTS database_name/g
:%s/\t/./g
$mysql mydatabase < ./drop_all_triggers.sql
Recent Comments