How to bulk change MySQL Triggers DEFINER

Question

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

Answer 1

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.

Answer 2

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

Answer 3

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

Answer 4

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.

Answer 5

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.

Answer 6

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