Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to bulk change MySQL Triggers DEFINER

Tags:

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

like image 574
neildt Avatar asked Sep 03 '13 13:09

neildt


People also ask

What is Definer in MySQL trigger?

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

Can we have multiple triggers in the same table MySQL?

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.

How many triggers can be applied to a table in MySQL?

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.


2 Answers

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 DEFINERs. Save updated file.

3) Recreate triggers from the file

# mysql < triggers.sql
like image 113
peterm Avatar answered Oct 18 '22 08:10

peterm


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
like image 35
Xorax Avatar answered Oct 18 '22 09:10

Xorax