Using MySQL triggers and views in Amazon RDS

PREVIOUS POST
NEXT POST

I recently had an opportunity to migrate a customer from a physical server into Amazon’s RDS environment. In this particular case the customers’ platform makes extensive use of MySQL triggers and views.  I came across two significant issues that prevented me from following Amazon’s documentation, which basically states “use mysqldump” but doesn’t call out a specific method of dealing with MySQL triggers and views.

Amazon Relational Database Service (Amazon RDS) is a great platform if you’re looking for complete hands-off management of your MySQL environment, but comes at a cost in the area of flexibility, i.e. you don’t have SUPER privilege and this brings up additional challenges.

  1. You need to ensure you set log_bin_trust_function_creators=1 ( by default this is off, 0).
  2. You need to clean up your mysqldump syntax.

#1 is easy, you simply make a configuration change within the Amazon RDS GUI on the node’s Parameter Group to set log_bin_trust_function_creators=1 and then a restart of your Amazon RDS node.  The restart is required since without the SUPER privilege you lose access to changing DYNAMIC variables on the fly.
#2 is a little more complex.  If you go with vanilla mysqldump (from say a 5.5 mysqldump binary) on a schema that has triggers and views, you will see error 1227, something like this:

You’re seeing this message because MySQL in Amazon RDS doesn’t provide the SUPER privilege, and thus you cannot set up a trigger or view to run as a different user — only a user with SUPER can do that.

mysqldump will generate syntax for a trigger like this:

and for a view like this:

The problem is in the “DEFINER” lines.

Here’s one method that worked for me:

  1. Identify all the DEFINER lines in your schema. I found it helpful to dump out a –no-data and then weed through that to get a unique list of the DEFINER lines
  2. Create a sed line for each unique DEFINER line (see my example in a moment)
  3. Include this sed line in your dump/load script

Here’s what my sed matches looked like:

Note: the example above won’t directly work due to WordPress “helpfully” stripping my text… you need to escape the forward slashes and asterisks.

A big caveat: this method is akin to a brute force method of getting your data into Amazon RDS — you’ve lost the elegance & security of running your triggers and views as separate defined users within the database — they are all now going to run as the user you loaded them in as. If this is a show-stopper for you, contact Percona and I’d be happy to take on your case and develop a more comprehensive solution.  :)

Now all that’s left is to integrate this into your dump flow.  Something like this should work:

I hope this helps someone!

PREVIOUS POST
NEXT POST

Comments

  1. says

    SQLyog and SJA has the ability to omit (‘strip out’) DEFINER clauses everywhere, refer http://blog.webyog.com/2014/03/12/sqlyog-mysql-gui-11-4-released/

    “In Copy database, Export as SQL-Dump, Scheduled Backup and Schema Sync there is now an option to ignore/omit DEFINER-clause for database objects where it applies. Note that the target server will then create DEFINER as current [SQLyog] user when creating the object.” ([I am bracing [SQLyog] here as a dump generated with SQlyog may be imported with any client, of course).

  2. says

    Excellent, thanks for sharing this – we’ve just migrated a MySql DB to RDS and were encountering the slightly cryptic error messages about SUPER privilege.

    We use dbForge Studio and, fortunately, when exporting a CREATE SCHEMA document, there’s an option to remove the DEFINER clauses throughout, saving me much wedding!

  3. says

    Here’s the export / import with the sed.

    mysqldump -h hostname -u username -ppassword –single-transaction –quick database_name | sed -e ‘s/\/\*[^*]*DEFINER=[^*]*\*\///’ | mysql -h hostname -u username -ppassword database_name

  4. says

    @ David Gitman, your sed filter worked like a dream. Hopefully this can be added to the initial article so people don’t have to scroll way down to find it.

Leave a Reply

Your email address will not be published. Required fields are marked *