What I learned while migrating a customer MySQL installation to Amazon RDS

Hi, I recently had the experience of assisting with a migration of a customer MySQL installation to Amazon RDS (Relational Database Service). Amazon RDS is a great platform for hosting your MySQL installation and offers the following list of pros and cons:

You can scale your CPU, IOPS, and storage space separately by using Amazon RDS. Otherwise you need to take downtime and upgrade physical components of a rack-mounted server.

You lose SUPER privilege for regular users. Many SUPER-type statements and commands are provided for as a Stored Procedure.

It is easy to set up multiple read replicas (slaves in READ_ONLY=1 mode).

You can set up a secondary sychronous instance for failover in the event your primary instance fails.

While this article is written to be Amazon RDS-specific it also has implications for any sort of migration.

The only way to interface with RDS is through mysql client, which means loading data must be done using SQL. This means you need to use mysqldump or mydumper, which can be a large endeavour should your dataset be north of 500GB — this is a lot of single threaded activity! Think about not only how long dumping and loading will take, but also factor in how much time it will take for replication to catch up on the hours/days/weeks your dumping and loading procedure took. You might need to allocate more disk space and Provisioned IOPS to your RDS node in order to improve disk throughput, along with a change to innodb_flush_log_at_trx_commit, and sync_binlog.

RDS is set to UTC (system_time_zone=UTC) and this cannot be changed as in Parameter Groups you will see that default_time_zone is set as Modifiable=false. This can bite you if you are planning to use RDS as a slave for a short while before failing the application over to Amazon RDS. If you have configured binlog_format=STATEMENT on your master and you have TIMESTAMP columns, this will lead to differences in RDS data set for absolute values ‘2014-07-24 10:15:00’ vs NOW(). It is also a concern for the Developer who may not be explicitly declaring their MySQL connections to set an appropriate time zone. Often the best piece of advice can be to leave all database data in UTC no matter where the server is physically located, and deal with localization at the presentation layer.

Amazon RDS by default has max_allowed_packet=1MB. This is pretty low as most other configs are 16MB so if you’re using extended-insert (by default, you are), the size of each insert statement will be close to 16MB and thus can lead to errors related to “packet too big” on Amazon RDS side, thus failing out an import.

Amazon RDS does not support the SUPER privilege for regular users. For example, this becomes quite a challenge as many tools (Percona Toolkit) are authored to assume you have SUPER-level access on all nodes — simple tasks become vastly more complicated as you need to think of clever workarounds (I’m looking at you pt-table-sync!).

Triggers and views thus cannot be applied using the default mysqldump syntax which includes SQL DEFINER entries — these lines are there so that a user with SUPER can “grant” another user ability to execute the trigger/view. Your load will fail if you forget this.

Consider running your load with –force to the mysql client, and log to disk stderr/stdout so you can review errors later. It is painful to spend 4 days loading a 500GB database only to have it fail partially through because you forgot about SQL DEFINER issue..

Consider splitting the mysqldump into two phases: –no-data so you dump schema only, and then –data-only so you get just the rows. This way you can isolate faults and solve them along the way.

Skipping replication events is SLOW. You don’t have ability to do sql_slave_skip_counter (since this requires SUPER), instead you need to use an Amazon RDS function of mysql.rds_skip_repl_error. Sadly this Stored Procedure takes no argument and thus it only skips one event at a time. It takes about 2-3 seconds for each execution, so if you have a lot of events to skip, that’s a problem. Having to skip ANYTHING is indication that something went wrong in the process, so if you find yourself in the unenviable position of skipping events, know that pt-table-checksum should be able to give you an idea how widespread is the data divergence issue.

pt-table-sync doesn’t work against Amazon RDS as it is written to expect SUPER because it wants to do binlog_format=STATEMENT in session, but that’s not allowed. Kenny Gryp hacked me a version to just skip this check, and Kenny also reported it for inclusion in a future release of Percona Toolkit, but in the meantime you need to work around the lack of SUPER privilege.

pt-table-sync is SLOW against RDS. As pt-table-sync doesn’t log a lot of detail about where time is spent, I haven’t completely isolated the source of the latency, but I suspect this is more about network round trip than anything else.

innodb_log_file_size is hardcoded to 128MB in Amazon RDS, you can’t change this. innodb_log_files_in_group is not even showing up in Parameter Groups view but SHOW GLOBAL VARIABLES reports as 2. So you’re cookin’ on 256MB, if your writes are heavy this may become a bottleneck with little workaround available in MySQL.

CHANGE MASTER isn’t available in RDS. You define RDS as a slave by calling a stored procedure where you pass the appropriate options such as CALL mysql.rds_set_external_master.

For those of you wondering about the SUPER-privilege, I was fortunate that Bill Karwin from Percona’s Support team took the time to review my post and suggested I dig into this deeper, turns out that Amazon didn’t hack MySQL to remove the SUPER privilege, but instead run the Stored Procedures with security_type of DEFINER:

Shell

1

2

3

4

5

6

7

8

9

mysql>select db,name,type,language,security_type,definer from proc where name='rds_external_master'G

Related

Michael joined Percona as a Consultant in 2012 after having worked with high volume stock photography websites and email service provider platforms. With a foundation in Systems Administration, Michael enjoys working with SAN technologies and high availability solutions. A Canadian, Michael currently lives in Costa Rica with his wife, two children, and two dogs.

Hi Fadi, I tried to present both the pros and cons to RDS, my first list of bullets are some of the great features that RDS delivers, which include: dynamic scaling of CPU, IOPS, and space without downtime, automated backups, upgrades, failure detection, and a trivially easy method for setting up additional read replicas + master failover. Those are some fantastic pros!

You’re correct though that my list are more about the challenges faced as the point of this post is to illustrate those areas that someone doing a migration should be thinking about and how to work around them.

I don’t use phpMyAdmin unfortunately so I don’t know for sure whether it would be supported on RDS or not. Let us know if you find an answer to this.

Another effect of the small innodb_log_file_size is that you can’t insert large BLOB/TEXT content. The log files must be 10x larger than the largest content you insert to an InnoDB table. Since you’re limited to 256MB of log file space, that means you can’t insert a blob or text greater than 25MB. This might be a deal-breaker for a given application.

I was at a talk about RDS at the recent AWS Summit in April 2015, and I asked if they had any plans to make the log file size configurable. The speaker said no, no plans, and suggested that we should store large blobs outside the database, and store a filename in the database.

Hi @Bill, actually you can now change innodb_log_file_size in RDS, at least for 5.6 . Check under Parameter Groups, the variable is now listed as isModifiable=true and the range of allowed values are 1M-275G.

25M blob/text is pretty big but not unheard of, you’re right. Fortunately since you can now change the variable we can minimise the impact of the limitation you’ve outlined.

Thank you very much for such informative and useful article. I have been struggling with percona connectivity with amazon rds since last few weeks. I did get the password for rdsadmin@localhost. However, I still cannot connect to the rds instance using that username and password because rdsadmin is allowed to connect only on localhost. How do I use rdsadmin to connect via percona? Do you have some example that you can share?

You don’t want to use the rdsadmin account for anything, this is reserved for Amazon to run their own monitoring and other routines within your instance. You are better off creating multiple other users, perhaps one for your application, one for monitoring, and one for your own access — each will require different levels of permissions.

What is it you mean with “Percona connectivity with Amazon RDS” — are you using RDS as a slave or master to a Percona Server instance?

I have been trying to use Percona XtraBackup is an open-source hot backup utility for MySQL to do my backups and restore over bunch of Amazon RDS instances. However, the users that I have dont have super privileges. Hence I wanted to see how we can make XtraBackup to work.

To restore a backup from XtraBackup you would need access to the filesystem, which you don’t get from using RDS. I’m not sure what your circumstances are, but if you are unable to stop your current DB and are looking to restore from a backup (say, to setup replication for instance), you’re best bet is to do so in EC2. Once fully restored in EC2, you can then stop that DB and do a MySQL dump from EC2 into RDS. I should note that if you can stop the DB that is currently running, you would not even need EC2 in that case. Hope this helps.

Thanks for this information! Also, great inputs by other contributors on this page.
I have a question:
I am looking to connect my nodejs application, which is deployed in OpenStack, to a MySQL DB instance in Amazon RDS. Any pointers as to what I need to configure on Amazon RDS side or on OpenStack side to enable this connection ?