Hello! We are a writer team from Definer Inc. In this issue, you are wondering how to migrate MySQL from Cloud SQL to RDS. Let's take a look at the actual screens and resources to explain in detail.

Data migration is the process of transferring data from one system to another, and it's a critical aspect of many business operations. Some common use cases for data migration include system upgrades, cloud migration, and merging data from multiple systems. A successful data migration requires careful planning, a thorough understanding of the source and target systems, and effective testing and validation processes. This article provides helpful information and practices when you want to migrate MySQL from Cloud SQL to AWS RDS using AWS DMS.

AWS Database Migration Service (DMS) is a DB migration service provided by AWS for relational databases, NoSQL databases, and data warehouses. AWS Database Migration Service (AWS DMS) is a managed migration and replication service that helps move your database and analytics workloads to AWS quickly, securely, and with minimal downtime and zero data loss. AWS DMS supports migration between 20-plus database and analytics engines, such as Oracle to Amazon Aurora MySQL-Compatible Edition, MySQL to Amazon Relational Database (RDS) for MySQL, Microsoft SQL Server to Amazon Aurora PostgreSQL-Compatible Edition, MongoDB to Amazon DocumentDB (with MongoDB compatibility), Oracle to Amazon Redshift, and Amazon Simple Storage Service (S3). With AWS DMS, you can discover your source data stores, convert your source schemas, and migrate your data.   The following three terms are important components when using the Database Migration Service.   ・Endpoint Endpoint is the definition of the connection information to the database. There are two endpoints, one for the source DB and the other for the target DB. The specific connection information is different, depending on your data store, but in general you supply the following information when you create an endpoint: ・Replication Instance This is the instance that connects the source and target DBs and executes the data replication task. A replication instance also loads the data into the target data store. Most of this processing happens in memory. However, large transactions might require some buffering on disk. Cached transactions and log files are also written to disk.   ・Replication Task This is the execution unit of data replication and where data migration is actually performed. You use the task to migrate data from the source endpoint to the target endpoint, and the task processing is done on the replication instance. You specify what tables and schemas to migrate and any special processing, such as logging requirements, control table data, and error handling. Migration type options for migration task, as listed following: - Full load (Migrate existing data) : The specified table data is migrated from the source DB to the target DB as it is. If you can afford an outage long enough to copy your existing data, this option is a good one to choose. This option simply migrates the data from your source database to your target database, creating tables when necessary. - Full load + CDC (continuous replication): This option performs a full data load while capturing changes on the source. After the full load is complete, captured changes are applied to the target. Eventually, the application of changes reaches a steady state. At this point, you can shut down your applications, let the remaining changes flow through to the target, and then restart your applications pointing at the target. - CDC (Continuous replication): In some situations, it might be more efficient to copy existing data using a method other than AWS DMS. For example, in a homogeneous migration, using native export and import tools might be more efficient at loading bulk data. In this situation, you can use AWS DMS to replicate changes starting when you start your bulk load to bring and keep your source and target databases in sync. Without performing a full load, changes in the source DB are periodically read and the changed data is reflected in the target DB.

Let's start with data migration from Google Cloud to Amazon RDS using Database Migration Service (DMS). As a precondition, we assume that the source (source) Cloud SQL exists. Now, let's create a RDS in AWS before migrating.   (1) Creating a subnet group Create a subnet group to create RDS.   (2) Create RDS for migration destination   Create MySQL DB by the following command.

## Creation of DB subnet group
aws rds create-db-subnet-group \
    --db-subnet-group-name subg-test \
    --db-subnet-group-description test \
    --subnet-ids subnet-xxxxxxxx subnet-yyyyyyy


## Creating RDS
aws rds create-db-instance \
    --db-instance-identifier mysql-test \
    --db-instance-class db.t3.micro \
    --engine mysql \
    --engine-version 8.0.28 \
    --allocated-storage 20 \
    --master-username root \
    --master-user-password password \
    --backup-retention-period 3 \
    --vpc-security-group-ids sg-xxxxxx \
    --db-subnet-group-name subg-test                

 

Or you can simplify create AWS RDS on AWS console.

Choose RDS service -> in Database dashboard -> choose Create Database button -> then enter neccesary information.

Next, we will migrate the DB with DMS.   (1) Creating a replication instance of DMS Go to the DMS console and click "Replication Instance" -> "Create Replication Instance". Enter the name, instance class, and network information such as VPC and SecurityGroup. For Public Access, check the box if you need to connect to the replication instance from the source/target DB via the Internet. Wait a few minutes for the creation to complete.     (2) DMS endpoint creation Two endpoints must be created, one for the source DB and one for the target DB. In the DMS console, click "Endpoints" > "Create Endpoints." For the source DB endpoint, enter the Cloud SQL connection information. Since the target DB is RDS, check "Select RDS DB instance" and choose the RDS DB instance you created in the previous step.     (3) Creating a DB Migration Task In the DMS console, click "Database Migration Task" > "Create Task". Specify the endpoints and replication instances created earlier. For the data replication type, we selected "full load" this time.   When the status becomes "Load Complete", replication is complete. Log in to the DB and check the table list to confirm that the DB migration has succeeded.

## Login to MySQL
mysql -u ${username} -p ${password} -h ${host name}

## After logging in to MySQL, browse table list
show tables;                

What to consider when migrating MySQL from RDS to Cloud SQL ... Amazon RDS (Managed Relational Database)| AWS MySQL major version using Database Migration Service ... What to consider when building replication from Aurora MySQL to Cloud SQL ... Cloud SQL explained in depth! - G-gen Tech Blog Trouble with Migration Tasks with AWS Database Migration Service ...

・PrismScaler is a web service that enables the construction of multi-cloud infrastructures such as AWS, Azure, and GCP in just three steps, without requiring development and operation. ・PrismScaler is a web service that enables multi-cloud infrastructure construction such as AWS, Azure, GCP, etc. in just 3 steps without development and operation. ・The solution is designed for a wide range of usage scenarios such as cloud infrastructure construction/cloud migration, cloud maintenance and operation, and cost optimization, and can easily realize more than several hundred high-quality general-purpose cloud infrastructures by appropriately combining IaaS and PaaS.  

This article provides useful introductory information free of charge. For consultation and inquiries, please contact "Definer Inc".

・Definer Inc. provides one-stop solutions from upstream to downstream of IT. ・We are committed to providing integrated support for advanced IT technologies such as AI and cloud IT infrastructure, from consulting to requirement definition/design development/implementation, and maintenance and operation. ・We are committed to providing integrated support for advanced IT technologies such as AI and cloud IT infrastructure, from consulting to requirement definition, design development, implementation, maintenance, and operation. ・PrismScaler is a high-quality, rapid, "auto-configuration," "auto-monitoring," "problem detection," and "configuration visualization" for multi-cloud/IT infrastructure such as AWS, Azure, and GCP.