I've been working on our internal development server and creating MySQL Triggers on our MySQL 5.6.13 server. The problem I now have is the Triggers (around 200 in total) were created with as DEFINER=root
@%
on the internal server.
Now I want to move to the live production server. However on our live server we don't allow this access for user root. Therefore how can I bulk change all my Triggers, so that it reads DEFINER=root
@localhost
The DEFINER clause specifies the MySQL account to be used when checking access privileges at trigger activation time. If the DEFINER clause is present, the user value should be a MySQL account specified as ' user_name '@' host_name ' , CURRENT_USER , or CURRENT_USER() .
Triggers in different schemas can have the same name. As of MySQL 5.7. 2, it is possible to define multiple triggers for a given table that have the same trigger event and action time. For example, you can have two BEFORE UPDATE triggers for a table.
A MySQL trigger is a stored program (with queries) which is executed automatically to respond to a specific event such as insertion, updation or deletion occurring in a table. There are 6 different types of triggers in MySQL: 1.
One way to do it:
1) Dump trigger definitions into a file
# mysqldump -uroot -p --triggers --add-drop-trigger --no-create-info \
--no-data --no-create-db --skip-opt test > /tmp/triggers.sql
2) Open triggers.sql
file in your favorite editor and use Find and Replace
feature to change DEFINER
s. Save updated file.
3) Recreate triggers from the file
# mysql < triggers.sql
Without using --add-drop-trigger option:
currentUserDefiner='root'
currentHostDefiner='localhost'
newUserDefiner='user'
newHostDefiner='localhost'
db='myDb'
mysqldump -u root --triggers --no-create-info --no-data --no-create-db --skip-opt $db \
| perl -0777 -pe 's/(\n\/\*.+?50003 TRIGGER `([^`]+)`)/\nDROP TRIGGER \2;\1/g' \
| perl -0777 -pe 's/(DEFINER[^`]+)'$currentUserDefiner'(`@`)'$currentHostDefiner'/\1'$newUserDefiner'\2'$newHostDefiner'/gi' \
> out.sql
mysql -u root < out.sql
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With