Migrating from Amazon RDS MySQL without SUPERUSER privileges

Successfully creating and running a migration job with an Amazon RDS MySQL source or sources that don't allow SUPERUSER privileges can require a few additional steps.

Create the Amazon RDS MySQL migration job

  1. MySQL limits the source hostname definition to 60 characters. Amazon RDS databases hostnames will typically be longer than 60 characters. If this is the case for the database you are migrating, configure a DNS redirect to create a CNAME record that associates your domain name with the domain name of your Amazon RDS DB instance. You can read more about setting up DNS CNAME in the Cloud DNS documentation or in the AWS Route53 documentation.

  2. Binary logs must be stored on standard block storage and cannot be stored on Amazon S3.

  3. Creating a continuous migration job with a manual dump provided requires GTID to be enabled. GTID_MODE must be either ON, OFF, or OFF_PERMISSIVE. The GTID_MODE value of ON_PERMISSIVE isn't supported.

Run the migration job

To take the initial full dump, both for MySQL Amazon RDS and for sources with no SUPERUSER privilege, writes should be stopped at the source for approximately 20 seconds.

You can stop writes either by migrating from a read replica or by stopping writes directly on the source database.

To migrate from a read replica:

  1. Create a read replica in Amazon Web Services (AWS).

  2. Configure the Database Migration Service source connection profile with the connectivity information of the AWS read replica.

  3. After the status of the migration job changes to Starting | Waiting for source writes to stop, disable replication between the AWS primary and its read replica for approximately 20 seconds.

  4. After the status of the migration job changes to Running | Full dump in progress, re-enable replication to the read replica in AWS.

Instead of stopping writes by migrating from a read replica in AWS, you can stop writes directly on the source database. You can use a script to verify that all writing to the source database is stopped.

Indication of when to stop and resume writes is in the status and substatus of the migration job. The status changes can be tracked in the API, Console or directly in Cloud Monitoring:

  1. After the status changes to Starting | Waiting for source writes to stop, writing should be stopped to the source database. Database Migration Service identifies that the writing stopped, and the status changes to Running | Preparing the dump.

  2. After the status changes to Running | Full dump in progress, it's safe to resume writing to the source database.

Database Migration Service keeps trying to take the initial dump for approximately 20 minutes. If writes haven't been stopped, or if writes are resumed before the status update, then the process fails and returns an error describing the cause of the failure.