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:
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
Run this sql statement (replace tablename and triggername with the desired triggers):
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.
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
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:
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.
Example in 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
The same I do for my views (Here you can use 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.
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
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.
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.
Another approach is to use this Java utility. Note this is a work in progress as current version 1.0 clears some server variables. newdef
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.
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):
for each table:
