如何批量更改MySQL触发器DEFINER [英] How to bulk change MySQL Triggers DEFINER

问题描述

我一直在内部开发服务器上工作，并在MySQL 5.6.13服务器上创建MySQL触发器.我现在遇到的问题是在内部服务器上使用DEFINER = root @ %创建触发器(总共约200个).

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.

现在，我想移至实时生产服务器.但是，在我们的实时服务器上，我们不允许root用户访问.因此，如何批量更改所有触发器，使其显示为DEFINER = root @ localhost

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

推荐答案

一种方法:

1)将触发器定义转储到文件中

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)在您喜欢的编辑器中打开triggers.sql文件，然后使用Find and Replace功能更改DEFINER.保存更新的文件.

2) Open triggers.sql file in your favorite editor and use Find and Replace feature to change DEFINERs. Save updated file.

3)从文件中重新创建触发器

3) Recreate triggers from the file

# mysql < triggers.sql

这篇关于如何批量更改MySQL触发器DEFINER的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！