我一直在为我们的内部开发服务器工作，并在我们的MySQL 5.6.13服务器上创建MySQL触发器。 我现在遇到的问题是，这些触发器（总共约200个）是在内部服务器上以DEFINER=root@%的身份创建的。
现在我想转移到活动生产服务器。 然而，在我们的活动服务器上，我们不允许用户root使用此访问权限。 那么我该如何批量更改所有的触发器，以便它们读取DEFINER=root@localhost？
8个回答
71
一种实现方法：
1）将触发器定义转储到文件中
# mysqldump -uroot -p --triggers --add-drop-trigger --no-create-info \
--no-data --no-create-db --skip-opt test > /tmp/triggers.sql
2）打开您喜欢的编辑器中的triggers.sql文件，使用查找和替换功能更改DEFINER。保存已更新的文件。
3）根据文件重新创建触发器。
# mysql < triggers.sql
- peterm
6
6
运行此 SQL 语句（将 tablename 和 triggername 替换为所需触发器）：
SELECT CONCAT('DROP TRIGGER IF EXISTS `', trigger_name, '`;', CHAR(13), 'DELIMITER $$', CHAR(13), 'CREATE TRIGGER `', TRIGGER_NAME, '` ', ACTION_TIMING, ' ', EVENT_MANIPULATION, ' ON ', EVENT_OBJECT_SCHEMA, '.', EVENT_OBJECT_TABLE, ' FOR EACH ROW ', ACTION_STATEMENT, '$$', CHAR(13), 'DELIMITER ;') AS command
FROM information_schema.triggers
WHERE EVENT_OBJECT_SCHEMA = 'tablename'
AND TRIGGER_NAME = 'triggername';
然后修改结果并执行它。
- David Vielhuber
1
2实际上，不是表名而是数据库名。 - Diego Murakami
4
不使用--add-drop-trigger选项：
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
- Xorax
3
我知道这是一个旧帖子了，但也许它可以帮助某些人。
我使用这个SQL查询语句生成DROP和CREATE命令：
SELECT CONCAT("DROP TRIGGER ", trigger_name, ";", " CREATE TRIGGER ", TRIGGER_NAME, " AFTER ", EVENT_MANIPULATION, " ON ", EVENT_OBJECT_SCHEMA, ".", EVENT_OBJECT_TABLE, " FOR EACH ROW ", ACTION_STATEMENT, ";") AS sqlCommand FROM information_schema.triggers WHERE EVENT_OBJECT_SCHEMA = "yourdatabase";
当我将生产数据库带到我的开发机器上，并使用foreach重建所有命令并自动重新创建触发器时，我在我的应用程序中使用它。这使我可以自动化这个过程。
PHP/Laravel示例：
$this->info('DROP and CREATE TRIGGERS');
$pdo = DB::connection()->getPdo();
$sql = 'SELECT CONCAT("DROP TRIGGER ", trigger_name, ";", " CREATE TRIGGER ", TRIGGER_NAME, " AFTER ", EVENT_MANIPULATION, " ON ", EVENT_OBJECT_SCHEMA, ".", EVENT_OBJECT_TABLE, " FOR EACH ROW ", ACTION_STATEMENT, ";") AS sqlCommand FROM information_schema.triggers WHERE EVENT_OBJECT_SCHEMA = "mydatabase";';
$stmt = $pdo->prepare($sql, [PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true]);
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
$stmt->closeCursor();
foreach($result as $rs){
$pdo = DB::unprepared($rs['sqlCommand']);
break;
}
提示：由于mysql缓冲查询问题，我必须使用pdo来执行它，这个问题在此处有描述。
对于我的视图，我也是这样做的（这里可以使用ALTER TABLE）：
$pdo = DB::connection()->getPdo();
$sql = 'SELECT CONCAT("ALTER DEFINER=`homestead` VIEW ", table_name," AS ", view_definition,";") AS sqlCommand FROM information_schema.views WHERE table_schema="mydatabase";';
$stmt = $pdo->prepare($sql, [PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true]);
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
$stmt->closeCursor();
$this->info('View definer changed');
foreach($result as $rs){
$pdo = DB::unprepared($rs['sqlCommand']);
}
希望这有所帮助。
- ndberg
3
打断“BEFORE”触发器。其他方面不错。 - Laloutre
1很晚才加入讨论，但我会更改查询语句如下：
SELECT CONCAT("DROP TRIGGER ", trigger_name, ";", " CREATE TRIGGER ", TRIGGER_NAME, " ",ACTION_TIMING," ", EVENT_MANIPULATION, " ON ", EVENT_OBJECT_SCHEMA, ".", EVENT_OBJECT_TABLE, " FOR EACH ROW ", ACTION_STATEMENT, ";") AS sqlCommand FROM information_schema.triggers WHERE EVENT_OBJECT_SCHEMA = "yourdatabase";这使得“BEFORE”触发器可以正常工作。 - Stargazing Worm不得不进一步修改它，使上述文本中的
";" 变为 ";;"。然后我将所有语句都包含在 DELIMITER ;; 中，并在末尾添加了 DELIMITER ;。 - Matt1
另一种利用MySQL Workbench选项的主题变体是使用“mysqldump”转储整个数据库，用功能强大的编辑器打开转储文件，查找所需的“DEFINER”进行替换，然后在MySQL Workbench中选择“Dump Structure Option”（v6.2）并在导入屏幕上导入。
- Damon
1
接受的答案对我没有用，因为我的大多数客户都托管在5.5服务器上，我无能为力，而 --add-drop-trigger函数是在5.6中添加的
在遇到这个问题后，我进入了phpMyAdmin，该软件在触发器编辑页面上公开了定义者，我只需编辑20个左右错误的触发器的定义者即可 - phpMyAdmin将删除并重新创建具有新定义者的触发器。
我发现的另一个选择是进行完整的mysqldump，编辑生成的文件，使用它创建一个新的数据库，然后使用Navicat之类的工具将结构同步回原始数据库，在结果比较列表中仅选择所需的触发器。对于我来说，这是一个更长的过程，因为我只需要编辑20个触发器，但如果我必须更新数百个触发器，则会更快。
- Craig Jacobs
2
1当然，您可以编辑由转储创建的文件并添加“drop trigger if exists name-of-triger;” - bartonlp
是的，但那需要打更多的字。如果我没有一个好的数据库比较工具，那就只能这样做了。 - Craig Jacobs
0
我能够通过关闭MySQL服务器，然后更新表格中的.TRG文件（这些是文本文件，在其中查找definer和CREATE DEFINER），最后重新启动MySQL来更新触发器定义者。我只需要为一个表格执行此操作，但如果有很多表格，我会编写一个sed脚本或类似的东西。如果服务器不能关闭，则以下操作应该可以平稳地进行，对活动干扰很小（免责声明-我没有测试过）：
对于每个表格：
- 锁定表格t WRITE
- 刷新表格t
- 在TRG文件上执行sed魔法
- 解锁表格t
- Sasha Pachev
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接
sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/'- ColinM--skip-opt才能使其正常工作。只是为了防止有人遇到同样的问题。但这是非常有用的答案，而且在2021年仍然有效。 - Huzaifa Mustafa