Skip to content

Instantly share code, notes, and snippets.

@cedricmagne
Last active December 2, 2020 23:45
Show Gist options
  • Save cedricmagne/aad3277d5497a7cb39e5 to your computer and use it in GitHub Desktop.
Save cedricmagne/aad3277d5497a7cb39e5 to your computer and use it in GitHub Desktop.
Change TRIGGERS DEFINER in MySQL
##
## Create mysql triggers dump
## We add DROP TRIGGER IF EXISTS with option --add-drop-trigger
## This option is supported only by mysqldump as supplied with MySQL Cluster. It is not available when using MySQL Server 5.5.
## If you don't need password dont use -p option.
##
mysqldump -u <USERNANME> -p --routines --add-drop-trigger --no-create-info --no-data --no-create-db --skip-opt <database> > outputfile.sql
##
## Change old DEFINER with sed command
## Or use your prefered editor to do that
## Exemple : `olduser`@`%` -> `newuser`@`localhost`
##
sed -i -e 's/OLD_DEFINER/NEW_DEFINER/g' outputfile.sql
##
## Import all new triggers in the database
## If you don't need password dont use -p option.
##
mysql -u <USER> -p <DATABASE_NAME> < outputfile.sql
@NRiou
Copy link

NRiou commented Feb 2, 2016

The param --add-drop-trigger doesn't work with all mysql versions

@cedricmagne
Copy link
Author

This option is supported only by mysqldump as supplied with MySQL Cluster.
It is not available when using MySQL Server 5.5.
https://dev.mysql.com/doc/refman/5.5/en/mysqldump.html#option_mysqldump_add-drop-database

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment