Moving data from MySQL into Aurora/RDS

Migrating databases from your data center to Amazon RDS or Amazon Aurora can be fun. Here are some tips to help.

If you data set is larger than a few GB, use mydumper and myloader instead of a normal MySQL dump. It will go a lot faster because mydumper and myloader is multi-threaded. Using these tools does introduce some caveats though.

Aurora will not let the user doing the restore create definers for other users. If you try this you will get this misleading error:

ERROR 1227 (42000) at line 1902: Access denied; you need (at least one of) the SUPER privilege(s) for this

If you are migrating a schema with routines (functions or stored procedures), events, triggers, or views then you are going to have definers for the code in each of those. You can either change all the definers in your schemas before you dump so they are standardized or change them after the dump but before importing into AWS.

This is the process I am using to dump and restore from a client's data center to Aurora. My client has a long running query killer on their master servers so I generally perform the dumps on a read-only replica (slave). If I must perform the dump on the master, then I must disable the event that performs the query killing during the duration of the dump.

# 1. Create a dump of objects with no data excluding stored procedures, functions, events of views.
# 2. Restore dump on new server.
# 3. Re-create the users on new server but not the grants for any stored procedures, functions, or events. Remove/replace any grants which are not compatible with RDS/Aurora.
# 4. Create a dump of the the objects without data (a second time) but this time include stored procedures, functions, events and  views.
# 5. Get a list of the unique definers on the source system. Do a find and replace of all the unique definer users and replace with standard definer.
# 6. Restore the dump on the new server as the same user which is the definer.
# 7. Dump the grants from the source.
# 8. Remove/replace any grants which are not compatible with RDS/Aurora.
# 9. Apply all the grants including stored procedures, functions, events.
# 10. Do a full dump and restore of all data and all objects

The reason for this multi stop process is you cannot create objects which have a definer if the user has not been created yet. You cannot grant permissions to objects if the object has not been created yet. This is why you cannot always do a straight dump on a new machine. Restoring to RDS adds in more steps because the definers must be the same user which is doing the restore.

Here is a great white paper written by the Aurora team lead at Amazon:

https://d0.awsstatic.com/whitepapers/Migration/amazon-aurora-migration-handbook.pdf

What happened to deep db?

When I was at Percona Live last year, there seemed to be a lot of excitement around what DeepDB could do for MySQL users. Their sales people kept hounding me to try it out but I didn't have time. I saw some news articles that they have open sourced the technology and now are being called Deep Software Foundation (http://www.deepis.org).

They had a lot of good blog posts and I really want to see how this technology progresses. Here are some interesting blog posts:

http://dev.deepis.com.473elmp01.blackmesh.com/blog/innodb-writing-challenges
http://dev.deepis.com.473elmp01.blackmesh.com/insights/blog/understanding-science-databases
http://dev.deepis.com.473elmp01.blackmesh.com/insights/blog/reimaging-science-databases
http://dev.deepis.com.473elmp01.blackmesh.com/blog/mysql-keep-grumbling

References to news articles:

https://finance.yahoo.com/news/deep-information-sciences-goes-open-130000559.html

https://www.businesswire.com/news/home/20170502005138/en/Deep-Information-Sciences-Open-Source-Relaunches-Deep