[AWS] Migrate Auror Mysql DB clusters
[AWS] Migrate Auror Mysql DB clusters
Requirements and options
Requirements
Overview:
We have an Aurora MySQL DB cluster and we want to migrate it to another one in the same account but a different VPC.
Criterias to consider:
- least downtime as possible.
- least complexity as possible.
- least possible to error. DB cluster information:
Number instances: 1 writer + 1 reader
Engine version: 8.0.mysql_aurora.3.05.2
All Table size ( data_length + index_length ): ~ 8Gb
Number Rows: ~ 14M
VolumeBytesUsed: ~10.0Gb
Note:
VolumeBytesUsed = data_length + index_length + free_length + data_cannot_reclaim
Ref: https://aws.amazon.com/about-aws/whats-new/2020/10/amazon-aurora-enables-dynamic-resizing-database-storage-space/
https://serverfault.com/questions/873699/why-is-my-volume-bytes-used-always-increasing-on-my-amazon-aurora-cluster
Options We have some options to migrate RDS Aurora MySQL cluster:
Take a snapshot and then restore the snapshot in a different VPC. ( Point-in-time recovery also uses snapshots but requires the transaction log as well ) Create a Aurora clone in a different VPC. Using DMS AWS service. Set up replication using binary logging using MySQL. Using mysqldump tool. Modify the Amazon RDS DB instance to use the new subnet group in new VPC ( only work for Amazon RDS MySQL and not for Amazon Aurora ). Backtracking an Aurora DB cluster ( not suitable because it does not create a new DB cluster ). https://repost.aws/knowledge-center/rds-vpc-aurora-cluster
We will review one by one option now.
Option 1. Aurora cluster snapshot
https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/aurora-restore-snapshot.html
Steps to do:
Step Using Difficult level Time
- Creating a DB cluster snapshot AWS Web console Very easy 5 min
- Restoring from snapshot Terraform. Easy 38 min Total time 43 min Terrafrom example: https://github.com/moneyforward/services-terraform/pull/21560/files
Note 1:
Important: If you attempt to restore a snapshot to a deprecated DB engine version, an immediate upgrade to the latest engine version will occur. Additionally, Extended Support charges might apply if the version is on Extended Support or has reached the end of standard support. Note 2:
Point-in-time recovery feature also uses snapshots but requires the transaction log as well.
It will take the same time or even longer than restoring from a snapshot.
Ref:
PDTP backing up and restoring an Aurora DB
https://repost.aws/knowledge-center/rds-point-in-time-restore-issues Example of point-in-time recover:
https://github.com/moneyforward/services-terraform/pull/20706/files
Option 2. Aurora cloning
Ref:
https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Aurora.Managing.Clone.html
Overview:
Steps to do:
Step Using Difficult level Time
- Cloning Terraform. Easy 20 min Total time: 20 min Terraform example:
Note 1:
Can't be specified restore-to-time, must use UseLatestRestorableTime
https://docs.aws.amazon.com/cli/latest/reference/rds/restore-db-cluster-to-point-in-time.html
Note 2:
No problem if you delete the source DB cluster after cloning. https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Aurora.Managing.Clone.html#Aurora.Managing.Clone.Deleting
Option 3. Using DMS AWS service. Overview:
Steps to do:
https://docs.aws.amazon.com/dms/latest/userguide/CHAP_GettingStarted.html
Note 1:
Recommend from AWS Premium Support:
Thank you for contacting AWS Premium Support. It was a pleasure assisting you today. Please find below a summary of our conversation today.
You contacted us as you want to migrate your RDS Aurora MySQL cluster to a different VPC within the same AWS account.
\
Instead, for Aurora clusters you have the following options as mentioned in the below blog:
[+] How can I change the VPC of an Amazon Aurora for MySQL or PostgreSQL cluster? - https://repost.aws/knowledge-center/rds-vpc-aurora-cluster
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1. Create a clone of the cluster in a different VPC
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
....
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2. Take a snapshot and then restore the snapshot in a different Subnet group.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
....
~~~~~~~~~~~~~~~~~~~~~~~
3. Replication to new cluster
~~~~~~~~~~~~~~~~~~~~~~~
\
We also discussed that the 2nd option is the least complex and you can minimize data loss by performing it during your maintenance window when the application does not make any writes on the db.
The 1st option is faster than the 2nd option, whereas the 3rd option will be the most complex of the three options.
....
DMS will also not be suitable for this use case due to its complexity.
We shouldn't use DMS for this case.
Option 3: DMS
AWS DMS doesn't automatically create secondary indexes, foreign keys, user accounts, and so on, in the target database.
To migrate secondary objects from your database, use the database's native tools if you are migrating to the same database engine as your source database.
https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Troubleshooting.html#CHAP_Troubleshooting.General.MissingSecondaryObjs
Note 3:
AWS DMS doesn't perform schema or code conversion.
If you want to convert an existing schema to a different database engine, you can use AWS SCT.
If your source and target endpoints are on the same database engine, you can use tools such as Oracle SQL Developer, MySQL Workbench, or PgAdmin4 to move your schema.
https://docs.aws.amazon.com/dms/latest/userguide/CHAP_BestPractices.html#CHAP_BestPractices.SchemaConversion
Option 4. Replication using binary logging using MySQL Ref:
https://aws.amazon.com/blogs/database/performing-major-version-upgrades-for-amazon-aurora-mysql-with-minimum-downtime/
Overview:
Steps to do:
-
Replication between Aurora and MySQL or between Aurora and another Aurora DB cluster (binary log replication) - https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Replication.MySQL.html
-
Promoting a read replica to be a DB cluster - https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Replication.CrossRegion.html#AuroraMySQL.Replication.CrossRegion.Promote
Note 1:
Performance degradation for the database write operations. The use of a MySQL binary log may cause performance degradation for the database write operations, and more storage and I/O load due to the additional binlog writes and synchronization. It can also impact the database recovery process due to the recovery of the binlog file for all uncommitted transactions during the database crash recovery. We strongly recommend testing the application in a non-production environment using the MySQL binary log with a production representative workload before turning the binary log on in the production environment.
https://aws.amazon.com/blogs/database/performing-major-version-upgrades-for-amazon-aurora-mysql-with-minimum-downtime/
Option 5. Using mysqldump tool
https://dev.mysql.com/doc/refman/8.4/en/mysqldump.html
Note 1: mysqldump is a single-threaded backup process. Performance for taking a backup is good for small databases, but it can become inefficient when the backup size is larger than 10 GB.
https://docs.aws.amazon.com/prescriptive-guidance/latest/migration-large-mysql-mariadb-databases/mysqldump-and-mysqlpump.html#limitations-mysqlpump-mysqldump
Note 2: You can receive the many unexpected errors when using mysqldump. https://repost.aws/knowledge-center/mysqldump-error-rds-mysql-mariadb
DONE.