Migrating a Large Database into IBM Cloud Databases for MySQL

2 min read

Are you looking to migrate your MySQL database, but are worried that you may have too much data to make the process seamless?

Fortunately, IBM Cloud® Databases makes the data migration process painless—even with a relatively large database—so long as you follow a couple of guiding principles. 

Migrate efficiently and keep production separate

Know your tooling and development environment

There are a few popular tools for backing up and restoring your MySQL database. If you aren't interested in the native MySQL backup client utility mysqldump, check out the open source MYSQL backup tool mydumper and its companion program myloader.

Keep your libraries and dependencies updated

If you're using open-source software, check to see that you have the most recent stable version. Then, check its dependencies to ensure that your environment is prepped for a successful migration.

Don't interrupt the import process

To ensure that your SSH session stays open, use a terminal multiplexer tool like Screen or Tmux, which allows users to open multiple virtual terminals on a remote machine.

Keep production separate from migration

Your production server is already under stress, so don't add to it by asking it to handle your data migration, as well. To reduce stress, provision a new server to run the migration. It's also a good idea to raise your innodb_io_capacity. For instance, a recent client who migrated a nearly 2 TB database into IBM Cloud Databases for MySQL set theirs to 20,000. 

Make sure you've got a little extra space and don't migrate all at once

Make room for the relay logs—keep a minimum of 15% operational overhead disk space available on a read replica

You've successfully backed up and imported your database. But wait—your source database was 1.9 TB, so why is the backup 2.2 TB? This is because read replicas store bin logs received from the source as relay logs. To bring it to the same state as the source, the read replica then replays the transactions from the relay logs. If there are large amounts of write activity on the source database, these relay logs can accumulate, as the read replica generates the logs that it needs to keep itself in sync. Additionally, if many complex select queries are run on the read replica, temporary tables can be generated, especially if the queries are unoptimized.

How do you eat an elephant? One bite at a time

When migrating a large database, do so in small chunks (300 GB, or less), especially if you have read replicas deployed. Migrate one batch of data, wait for the read replicas to catch up, then verify the data migration. Rinse and repeat.

Get started with IBM Cloud Databases for MySQL

To provision your own deployment on IBM Cloud Databases, check out IBM Cloud Databases for MySQL, our serverless, cloud database service that is fully integrated into the IBM Cloud environment.

Be the first to hear about news, product updates, and innovation from IBM Cloud