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
Hi there,
What do you mean with ‘moneymove’? I was not able to decipher your meaning.
Thanks,
Jared Ready
Sorry. moneymove – my database. It was typo.
I correct string at post.
:%s/database_name/DROP TRIGGER IF EXISTS database_name/g
thank you very much! 🙂
nice but you can do it all in the sql statement without having to use regexp etc.
mysql -u root -p -N -e “SELECT concat(‘DROP TRIGGER IF EXISTS ‘, trigger_schema, ‘.’, trigger_name, ‘;’) from information_schema.triggers” > ./drop_all_triggers.sql
Thanks!