There are a lot of benefits to migrating an on-premises database to Amazon Relational Database Service (RDS). I’ve found that RDS is much easier to operate than MySQL or PostgreSQL. RDS makes it easier to manage multiple databases, the interface is easy to use, it has a good API, and it maintains good uptime. But before you can enjoy these benefits, you have to do the actual work of migration. Let’s face it, that’s not necessarily easy or fun. Here are my eight tips for performing a live database migration from an on-premises database (namely, MySQL and PostgreSQL) to Amazon RDS.
Tip #1: Follow the Scotty/LaForge approach to project planning. Star Trek fans know that any time the captain gives an order, the engineers estimate that it will take much longer than the captain ultimately allows. Be like a Star Trek engineer and give yourself as much time as you can for a live database migration to account for the unforeseeable. If you think it will take two days, give yourself a week.
There will always be some kind of snag during a database migration: It might be with replication, it might be the schema, maybe overall compatibility, maybe you have a bunch of views or stored procedures that won’t work in Aurora or RDS. If the database is massive, the moment you realize you have to start over it only gets worse as your window gets smaller and the data continues to get bigger.
Tip #2: Back up everything. If you’re a DBA you should already be doing this. Do a full-disk backup, take snapshots of everything, and do a point-in-time restore. Remember, if it doesn’t exist in two places, it doesn’t exist at all.
Tip #3: Give yourself options. There are a lot of different ways to go about a database migration, and some work better than others depending on the situation. Keep yourself open to various options. For example, keep multiple copies of your .sql files and try migrating without a staging area. Or stage them in an EC2 instance or S3, or use a data pipeline. Set up a backup replication job in case issues come up with the first. Stash those binlogs for easy use and recall.
Tip #4: Use what works. mysqldump/workbench and pg_dump are your friends. More importantly, they work. Aside from the de facto management tools there are a number of migration utilities out there such as Flyway and Alembic. These give the added advantage of allowing for DDL and DML operations during migration, which can often be useful.
Tip #5: Put in the work upfront. The more work you put into formatting your existing environment to be increasingly mobile, the easier it will be to move. Find the time to make changes to production before the migration. Ideally, the migration is complete once the data is moved over – no changes needed. (If you keep those myisam tables and you go to Aurora, you’ll be sore by how much longer it will take.)
Tip #6: Meditate deeply on external and physical constraints, such as the source machine’s resources, your bandwidth and your network topography. Physical constraints are more of an issue if you’re coming from limited bandwidth on-premises. If you have 2Tb of data and a 6mb up/down pipe, make sure the migration is actually possible within the allotted time.
Tip #7: Embrace the tedium. People often ask me how to make a database migration as easy as possible. It should be a little tedious. If it’s too easy, folks get careless. Tedium ensures things go smoothly. Always err on the side of caution and control when doing a database migration. Take things slow.
Tip #8: Move complex tables one at a time. If your tables have a lot of stored procedures, triggers, views or other things associated with them, then you’re better off moving them one at a time. A lot of times stored procedures, triggers and views won’t migrate automatically or without re-formatting, especially if you’re migrating to Aurora. So don’t try to do any bulk table or bulk database moves. I like to keep sprocs with the table they’re associated with. If you have a table with a lot of stored procedures, move them one at a time.
If these tips were helpful to you, watch my recorded presentation, Living with Database Migrations. I provide more tips and tricks for managing a successful database migration to Amazon RDS.