How to make it possible to roll back after migrating MySQL from on-premises to an AWS environment.
This is Onikai (@fat47) from the Service Reliability Group (SRG) of the Technology Division.
#SRGThe Service Reliability Group primarily provides comprehensive support for the infrastructure surrounding our media services, focusing on improving existing services, launching new ones, and contributing to open-source software (OSS).
This article is located within SRG.DBWGThe DB Working Group is releasing database-related materials that it provides to the entire company.
I hope this is of some help.
overviewenvironmentSource informationDestinationOverall flowDetailed instructionsStop replication from the on-premises environment to the AWS Aurora environment.Reset Aurora replication information.Check the binlog position in Aurora.Obtain the certificate bundle to connect to Aurora.Change the master on the on-premises MySQL instance to make Aurora the master.Points to noteWhen connecting to Aurora with a MySQL client, do not use the MySQL 8.0 client.A TLS connection to Aurora requires a certificate bundle provided by AWS.In conclusion
overview
the other day,Perform MySQL replication between an on-premises environment and Amazon Aurora over the internet.I published an article with the following content.
The plan was to establish replication between the on-premises environment and Amazon Aurora and then migrate to AWS.
During the actual migration, it's necessary to consider how to revert to the on-premises environment if problems arise.
This article will show you how to quickly roll back to your Amazon Aurora environment by setting it up as the master and your on-premises environment as the slave.
environment
Source information
Server OS: CentOS 7
Database: MySQL 5.7.41
Destination
AWS Aurora
This is the same environment as in the previous article.
This scenario assumes a transition from the source on-premises environment to Aurora immediately after performing service maintenance.
We will create a system that allows us to roll back from Aurora to the on-premises environment.
This procedure assumes that the application has stopped writing to MySQL.
Overall flow
- Stop replication from the on-premises environment to the AWS Aurora environment.
- Reset Aurora replication information.
- Check the binlog position in Aurora.
- Obtain the certificate bundle to connect to Aurora.
- Change the master on the on-premises MySQL instance to make Aurora the master.
Detailed instructions
Stop replication from the on-premises environment to the AWS Aurora environment.
Switching to the AWS environment will stop replication between the on-premises environment and the Aurora environment.
Aurora replication stopped
I'll double-check to make sure it's actually stopped.
Reset Aurora replication information.
The replication information remaining in Aurora is no longer needed, so we will reset it.
It's all clean now.
Check the binlog position in Aurora.
To set up Aurora to on-premises replication, I'll make a note of the Aurora binlog position.
Check binlog positions in Aurora
Obtain the certificate bundle to connect to Aurora.
An AWS certificate is required to establish a TLS connection with Aurora.
アジアパシフィック (東京)ap-northeast-1-bundle.peChange the master on the on-premises MySQL instance to make Aurora the master.
Run CHANGE MASTER on the on-premises MySQL. Specify the LOG_FILE and Position values you noted down earlier.
Please note that if a failover or similar event occurs on the Aurora side, replication with the on-premises system will stop.
We will start replication and verify it.
Points to note
When connecting to Aurora with a MySQL client, do not use the MySQL 8.0 client.
Aurora v2 (MySQL 5.7) incorporates some MySQL 8.0 features through its own implementation.
The issue is that when this is replicated to an on-premises MySQL 5.7 environment, replication stops due to an error.
More details are available in previous articles.
A TLS connection to Aurora requires a certificate bundle provided by AWS.
When I built the replication with the on-premises system as the master, I used a self-signed certificate,
If Aurora is the master instance, you cannot use a self-signed certificate, so use the certificate bundle provided by AWS.
In conclusion
This time, we introduced a method for rolling back to the previous state if something goes wrong during the MySQL migration to AWS.
Ideally, thorough pre-testing and verification would prevent any pruning from occurring, but it's important to have procedures in place to allow for pruning in case of unforeseen circumstances.
SRG is looking for new team members.
If you are interested, please contact us here.