Importing MySQL Databases to MySQL Backed Aurora PART 1

Importing MySQL Databases to MySQL Backed Aurora is on the face of it quite simple. But I ran into some issues.

There are a number of options that allow you to migrate an existing database from local MySQL instance. In this context, by local, I mean a MySQL instance you manage your self, whether that is a single server running a LAMP stack or a dedicated database server as part of a more complex architecture.

If you’re already taking backups, please tell me you are, then you might be using mysqldump tarballing that up and putting it somewhere safe.

Importing your backup into RDS from that is pretty simple, all you need to do is spin up your rds instance. Then from your workstation or an EC2 instance and from there you can import your SQL dump file by connecting to your RDS cluster and importing the database like this:

$ mysql -h cluster.endpoint -P 3306 -u db_cluster_master_user -p
mysql> source backup.sql;

This is fine for smaller databases, but what if you’re database is larger? Doing the above will take a fair bit of time especially if you’re trying to use a T.n instance to do the import as you’re limited on the bandwidth you can utilise during the transfer.

Fortunatly there is a better way to do this import, as Amazon allow you to create new clusters from a backup stored in an S3 bucket.

This series of posts will guide you through preparing you database and getting a cluster up and running.

Preparing Your Database

The Users

There are a few housekeeping tasks that you need to undertake before you begin. RDS is, in essance, a managed database instance. That means that there are most likely permissions you have for some of your users that will cause your import to fail.

• SUPER
• SHUTDOWN
• FILE
• CREATE TABLE SPACE

Are all prohibited and if any user in your mysql.user table has them then the RDS import will fail

You can get a list of users, along with the commands you need to run to revoke the extra permissions by running

SELECT CONCAT("'",user,"'@'",host,"'") as 'user',
CONCAT("REVOKE SUPER, SHUTDOWN, FILE, CREATE TABLESPACE ON *.* FROM '",user,"'@'",host,"';") as 'query' from mysql.user 
WHERE HOST NOT IN  ('localhost','127.0.0.1') 
AND (Super_Priv='Y' OR Shutdown_priv='Y' OR File_priv='Y' OR Create_tablespace_priv='Y');

This will check your users for incompatible grants:

+--------------+---------------------------------------------------------------------------+
| user         | query                                                                     |
+--------------+---------------------------------------------------------------------------+
| 'john'@'%'   | REVOKE SUPER, SHUTDOWN, FILE, CREATE TABLESPACE ON *.* FROM 'john'@'%';   |
| 'george'@'%' | REVOKE SUPER, SHUTDOWN, FILE, CREATE TABLESPACE ON *.* FROM 'george'@'%'; |
| 'paul'@'%'   | REVOKE SUPER, SHUTDOWN, FILE, CREATE TABLESPACE ON *.* FROM 'paul'@'%';   |
| 'ringo'@'%'  | REVOKE SUPER, SHUTDOWN, FILE, CREATE TABLESPACE ON *.* FROM 'ringo'@'%';  |
+--------------+---------------------------------------------------------------------------+

With the above you can now revoke the forbidden permissions, at which point you’re ready to take a new backup of your database for importing.

A Gotcha

As you use your database in the real world, users will be added and dropped, tables dropped and changed and sometimes things aren’t tidied up properly. One thing that might be left behind is a user who has a GRANT on a table that no longer exists. If that is the case, then your import will appear to take a very long time to complete, or silently fail.

Make sure that any VALID grants are to entities that actually exist in your Database.ß