如何批量更改 MySQL 触发器 DEFINER
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18593746/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to bulk change MySQL Triggers DEFINER
提问by neildt
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.
我一直在我们的内部开发服务器上工作，并在我们的 MySQL 5.6.13 服务器上创建 MySQL 触发器。我现在遇到的问题是触发器（总共大约 200 个）是在内部服务器上使用 DEFINER= root@创建的%。
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
现在我想转移到实时生产服务器。但是，在我们的实时服务器上，我们不允许 root 用户进行此访问。因此，我如何批量更改所有触发器，使其显示为 DEFINER= root@localhost
回答by peterm
One way to do it:
一种方法：
1) Dump trigger definitions into a file
1) 将触发器定义转储到文件中
# mysqldump -uroot -p --triggers --add-drop-trigger --no-create-info \
--no-data --no-create-db --skip-opt test > /tmp/triggers.sql
2) Open triggers.sqlfile in your favorite editor and use Find and Replacefeature to change DEFINERs. Save updated file.
2)triggers.sql在您喜欢的编辑器中打开文件并使用Find and Replace功能更改DEFINERs。保存更新的文件。
3) Recreate triggers from the file
3) 从文件中重新创建触发器
# mysql < triggers.sql
回答by Xorax
Without using --add-drop-trigger option:
不使用 --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 ;/g' \
| perl -0777 -pe 's/(DEFINER[^`]+)'$currentUserDefiner'(`@`)'$currentHostDefiner'/'$newUserDefiner''$newHostDefiner'/gi' \
> out.sql
mysql -u root < out.sql
回答by ndberg
I know it's an old post, but perhaps it can help someone.
我知道这是一个旧帖子，但也许它可以帮助某人。
I use this sql query to generate a DROP and a CREATE command:
我使用这个 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";
I use this in my app when I take the production database to my development machine and go it with a foreach over all commands and recreate the triggers automatically. This gives me the option to automate it.
当我将生产数据库带到我的开发机器上并在所有命令上使用 foreach 并自动重新创建触发器时，我在我的应用程序中使用它。这使我可以选择使其自动化。
Example in PHP/Laravel:
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;
}
Hint: I have to do it with pdo because of the mysql buffer query problem, described here
提示：由于mysql缓冲区查询问题，我必须用pdo来做，描述here
The same I do for my views (Here you can use ALTER TABLE):
我对我的观点也是如此（在这里你可以使用 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']);
}
Hope it helps.
希望能帮助到你。
回答by David Vielhuber
Run this sql statement (replace tablename and triggername with the desired triggers):
运行此 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';
Then modify the result and execute it.
然后修改结果并执行。
回答by user3092412
回答by Damon
Another variation on the theme utilizing an option in MySQL Workbench is to dump the entire database using "mysqldump", open the dump in a capable editor, find replace with desired "DEFINER", then import with MyQSL Workbench with the "Dump Structure Option" (v6.2) selected on the import screen.
使用 MySQL Workbench 中的选项的主题的另一个变体是使用“mysqldump”转储整个数据库，在功能强大的编辑器中打开转储，找到所需的“DEFINER”替换，然后使用“转储结构选项”使用 MyQSL Workbench 导入(v6.2) 在导入屏幕上选择。
回答by Craig Jacobs
The accepted answer didn't work for me because most of my clients are hosted on servers with 5.5 and not much I can do about that, and the --add-drop-trigger function was added in 5.6
接受的答案对我不起作用，因为我的大多数客户端都托管在 5.5 的服务器上，对此我无能为力，并且在 5.6 中添加了--add-drop-trigger 功能
After banging my head on this on, I just went in to phpMyAdmin which exposes the definer on the trigger edit page and I simply edited the definer for the 20 or so triggers that were wrong - phpMyAdmin will drop and recreate the trigger with the new definer.
在对此深思熟虑后，我刚刚进入 phpMyAdmin，它在触发器编辑页面上公开了定义器，我只是编辑了 20 个左右错误触发器的定义器 - phpMyAdmin 将删除并使用新的定义器重新创建触发器.
Another option I found was to do a full mysqldump, edit the resulting file, use it to create a new database, then use a tool like Navicat to perform a structure sync back to the original database selecting only the desired triggers in the resulting compare list. This was a much longer process for me because I only had to edit 20 triggers, but if I had to update hundreds of triggers this would be faster.
我发现的另一个选择是做一个完整的 mysqldump，编辑结果文件，用它来创建一个新数据库，然后使用像 Navicat 这样的工具执行结构同步回原始数据库，只在结果比较列表中选择所需的触发器. 这对我来说是一个更长的过程，因为我只需要编辑 20 个触发器，但是如果我必须更新数百个触发器，这会更快。
回答by Sasha Pachev
I was able to update the trigger definer by shutting down MySQL server, then updating the table .TRG files (those are text files, look for definer and CREATE DEFINER in them), and restarting MySQL. I only needed to do it for one table, but if there were many, I would have written a sed script or something along those lines. If the server cannot go down, the following should work quite smoothly with little disruptions to the activity (disclaimer - I had not tested it):
我能够通过关闭 MySQL 服务器来更新触发器定义器，然后更新表 .TRG 文件（那些是文本文件，在其中查找定义器和 CREATE DEFINER），然后重新启动 MySQL。我只需要为一张桌子做这件事，但如果有很多，我会写一个 sed 脚本或类似的东西。如果服务器无法关闭，以下操作应该会非常顺利，对活动的干扰很小（免责声明 - 我没有测试过）：
for each table:
对于每个表：
- LOCK TABLE t WRITE
- FLUSH TABLE t
- do the sed magic on the TRG file
- UNLOCK TABLE t
- 锁定表 t 写入
- 冲洗台
- 对 TRG 文件执行 sed 魔法
- 解锁表
