It’s increasingly common to run databases in the cloud. Whether you run a self-managed instance or use a managed service, it saves much of the trouble of running it on-premises. Amazon’s AWS offers three major options, trading off control against convenience and availability.
This article will look at three ways of running MySQL on AWS and cover the advantages and disadvantages of each. Then it will look at what’s involved in migrating a database to AWS. While migration is never trivial, AWS provides tools that make it easier than you might think.
Three MySQL alternatives on AWS
If you want to run a MySQL database on AWS, you have these alternatives:
- Running a self-managed MySQL server.
- Using Amazon Relational Database Service (RDS) to get MySQL as a service.
- Using the Amazon Aurora database engine, which offers nearly complete MySQL compatibility.
Self-managed MySQL
If you’re used to running your own MySQL server and want full control, you can set up an instance on EC2. It’s much like running an on-premises database, except that you don’t need to maintain your own hardware.
Full control means full responsibility. Amazon provides the infrastructure; otherwise it’s much like having an on-premises database. You need to handle setup, updates, and backups. Scaling is easier than with your own hardware, but you have to decide when and how to move to a more powerful server or increase the number of instances.
It’s a low-cost option, and if you manage it well, you can get high performance. You can customize whatever you want, including encryption, replication, and the storage engine. If your IT team has strong expertise on MySQL servers, this option can work.
MySQL under RDS
Not everyone wants to put a lot of effort into running a customized database server. A managed alternative is the AWS Relational Database Service. RDS can run several database engines, including MySQL, PostgreSQL, Oracle, and MariaDB. You’re still running MySQL, but the service automates provisioning, setup, backups, and software updates.
The RDS Management Console and CLI make administering the database easy. Scaling up is a simple operation. You can choose between general-purpose SSD storage and provisioned IOPS storage, which is optimized for I/O intensive workloads. To improve performance under heavy demands for reading the database, you can create read replicas which mirror the main database.
To maximize security, the database firewall doesn’t allow any access by default. You need to configure it with the appropriate authorizations. AWS Identity and Access Management (IAM) is available for this purpose.
Aurora MySQL
MySQL wasn’t designed to optimize cloud performance. If you want the best performance without giving up MySQL, AWS Aurora could be your best choice. It’s not MySQL but a proprietary Amazon database engine. However, it’s wire-compatible and can be treated as a MySQL server for nearly all purposes. It also offers compatibility with PostgreSQL. Aurora tends to be more expensive than MySQL under RDS, but the improved performance can make up for it.
You access Aurora through RDS, so you gain the same advantages as a MySQL server running under RDS. In addition, Aurora is optimized for cloud performance and availability. It claims to be five times faster than MySQL. Its storage grows automatically as you need it. You start with 10 GB, and it grows in 10 GB increments to as much as 64 TB.
Aurora gives you high availability with no single point of failure. It stores six copies of all its data. As long as at least four copies are running, it maintains read and write access. With three copies it still provides read access. Peer-to-peer replication fixes any corruption found in one of the copies.
There are a few compatibility issues. Aurora uses the InnoDB storage engine and doesn’t support the older MyISAM engine. Check the update notes for current compatibility issues.
Migrating a MySQL database to AWS
Migrating a production database is always a significant task, but Amazon provides ways to make it easier.
One approach is to make a snapshot and copy it to the new host. This is straightforward and provides the most reliable copy, but it may be necessary to freeze the database for however long the copying takes. For a database in the terabyte range, it can take many hours. Taking it offline for that long may not be practical. An alternative is to keep the source database live and then sync it to the new one after the copy is finished.
A more robust approach uses the AWS Data Migration Service (DMS). You can use it to migrate to any of the three kinds of database hosting. The process takes about 12 hours per terabyte, but there’s no downtime. The source database can stay active until the switchover at the end. Any changes that happen in it during the migration get copied to the destination.
Setting up a DMS migration isn’t complicated. You can initiate it from the AWS management console. It’s worth checking Amazon’s best migration practices, though. Some uses of indexes, triggers, and constraints can slow down the migration or even make it fail.
DMS doesn’t migrate indexes, stored procedures, users, and other non-table data. There could be a large amount of manual work in making the new database exactly like the old one. Using the same indexes may not even be the right way to get top performance, given the difference in the environment. LOBs and BLOBs present some special issues, especially if they’re extra large (more than 32 KB). You may have to manually migrate the largest ones. A migration with DMS can require a lot of manual effort and perhaps some redesign.
Which is the best choice for you?
Most AWS customers will prefer a managed database solution to running their own MySQL server. However, if you have reasons for using a self-managed server, it can work.
If you’re moving from an on-premises or self-managed instance to one that Amazon will manage, RDS with MySQL is the more conservative solution. You still have a MySQL server, and many routine management tasks are off your hands.
If you need the highest performance, Aurora with MySQL compatibility could be the right choice. It’s a little more expensive but not as costly as most commercial database services.
We can help you to make the right choice and migrate your database smoothly. Contact us for details.
