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
One way to do it:
# mysqldump -uroot -p --triggers --add-drop-trigger --no-create-info \
--no-data --no-create-db --skip-opt test > /tmp/triggers.sql
Open triggers.sql
file in your favorite editor and use Find and Replace
feature to change DEFINER
s. Save updated file.
Recreate triggers from the file
# mysql -u USER -p DATABASENAME < /tmp/triggers.sql