HOME/Technical article/This is a story about how replication suddenly stopped when using Amazon Aurora Version 2 (MySQL 5.7 compatible) and MySQL 5.7 on a VM.

This is a story about how replication suddenly stopped when using Amazon Aurora Version 2 (MySQL 5.7 compatible) and MySQL 5.7 on a VM.

2023/2/2 14:082024/9/20 9:02

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.

Summarize what happened in 3 lines Regarding the situation cause Reproduction environment Version Information Reproduction steps Workaround Reference URL In conclusion

Summarize what happened in 3 lines

💡

When replicating from [Aurora v2] to [MySQL 5.7], if you use a MySQL 8.0 client to send an update query to connect to the Aurora v2 cluster, a collation that does not exist in MySQL 5.7 is automatically specified, causing the replication to stop with an error.

Regarding the situation

As we proceeded with migrating from our on-premises MySQL environment to Amazon Aurora,

We had built the following configuration:

After the switchover, we implemented reverse replication so that we could revert to the on-premises environment in case of any problems.

When using the latter configuration, the on-premises MySQL 5.7 suddenly stopped replicating due to an error.

cause

The MySQL Client on the developer's terminal was unintentionally updated to the latest version.MySQL8.0This was because we were using the system.

I had encountered a similar error during testing when upgrading to MySQL 8.0, so I suspected it was related to MySQL 8.0.

Upgrading from MySQL 5.7 to 8.0 with rollback considerations - CyberAgent SRG #ca_srg

The Service Reliability Group (SRG) primarily provides cross-functional 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 publishes database-related materials provided company-wide by the DBWG (DB Working Group) within the SRG. This material was presented at the SRG Study, an internal training program...

https://ca-srg.dev/e49f49ddfbb14f909214a7d7111d4676#block-13f3f9e7267a4c89bf5b301f5de979f8

utf8mb4_0900_ai_ci

When I actually connected to a MySQL 5.7 server from a MySQL 8.0 client and checked the Collation settings, they looked like this, and there were no problems.

utf8mb4_0900_ai_ci

If you issue a query in this state, the binary log will

The data was written out, and since this 255 does not exist in the replica's MySQL 5.7, it caused an error and stopped replication.

Reproduction environment

Version Information

Source side: Aurora	5.7.mysql_aurora.2.11.0
Replica side EC2	MySQL 5.7.41

I used Aurora v2 as the source and created a replica of MySQL 5.7 installed on EC2. I will build binary log replication between these two environments.

Reproduction steps

Create a suitable table in [Aurora v2]

Add records as needed

Performing an UPDATE operation on an Aurora v2 cluster from a MySQL 5.7 client.

Check if the records have been updated on the [MySQL 5.7 replica].

Performing an UPDATE operation on an Aurora v2 cluster from a MySQL 8.0 client.

When checking the show slave status on the [MySQL 5.7 replica], a replication error occurs.

Workaround

I don't think there are many opportunities to use this kind of configuration,

First, all developers should use the MySQL 5.7 client to connect to Aurora v2.

skip_character_set_client_handshake

This phenomenon can be avoided for the time being.

However, I would recommend using this setting only as a temporary measure during the migration process.

Reference URL

The following article was very helpful.

Two Key Points to Consider When Building Replication from Aurora MySQL to Cloud SQL - ZOZO TECH BLOG

Hello, I'm Tajima, an engineer in the MA department. Previously, our colleague Shiozaki gave a presentation titled "Integrating Amazon Aurora Data with Google BigQuery in Real Time." This presentation introduced how to integrate Amazon Aurora MySQL data with Google BigQuery in real time. In order to achieve real-time data integration, Aurora

https://techblog.zozo.com/entry/replicate-from-aurora-mysql-to-cloud-sql

How to configure character encoding/collation in MySQL 5.7 and 8.0 - Miscellaneous Notes

Preface - Always be aware of whether parameter names are written with hyphens or underscores. - This should be configured before starting MySQL for the first time. It's not impossible to fix it later, but it's a pain. Also, I'm afraid to change the mysql/performance/sys schema with an alter statement (I've left some parts as they are...). Prerequisites - Consideration of settings in my.cnf (Linux)/parameter group (AmazonRDS) in MySQL 5.7/8.0

https://yoneyore.hatenablog.com/entry/2020/11/26/232128

In conclusion

SRG is looking for new team members. If you are interested, please contact us here.

Recruitment information - CyberAgent SRG #ca_srg

About SRG: SRG (Service Reliability Group) operates under the vision of "improving reliability across media businesses" and promotes the introduction of SRE into media businesses as a cross-functional SRE, working to improve reliability. Our work primarily revolves around the following three areas: Gathering and deploying technical know-how from each business.

https://ca-srg.dev/0d353a3a68ba4fb0a2e3443d7009a22b