About SRGArticlesRecruitment Information
About SRGArticlesRecruitment Information
HOME/Articles/A story about how replication suddenly stopped when using Amazon Aurora Version 2 (compatible with MySQL 5.7) and MySQL 5.7 on a VM

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

This is Oniumi (@fat47) from the Service Reliability Group (SRG) of the Technology Headquarters.
#SRG(Service Reliability Group) mainly provides cross-sectional support for the infrastructure of our media services, improving existing services, launching new ones, contributing to OSS, etc.
This article is located in SRGDBWGWe will publish materials regarding the database that the (DB Working Group) provides to the entire company.
I hope this helps in some way.
 
 

A three-line summary of what happened

💡
When setting up replication from [Aurora v2] to [MySQL5.7], if you submit an update query using a MySQL8.0 client to connect to an Aurora v2 cluster, a collation that does not exist in MySQL5.7 is automatically specified, and the replication stops with an error.
 

About the situation

As we were switching from our on-premise MySQL environment to Amazon Aurora,
I had created the following configuration:
After the switchover, we made sure to set up reverse replication so that we could switch back to the on-premises environment if any problems arose.
 
When using the latter configuration, MySQL 5.7 in the on-premises environment suddenly encountered an error and replication stopped.
 

cause

MySQL Client on the developer's device was unintentionally updatedMySQL8.0It was because I was using the system.
 
Since I had experienced a similar error when verifying an upgrade to MySQL 8.0, I had guessed that this error was related to MySQL 8.0.
 
utf8mb4_0900_ai_ci
If you actually connect to a MySQL 5.7 server from a MySQL 8.0 Client and check the collation settings, you will see the following and there will be no problems.
 
utf8mb4_0900_ai_ci
utf8mb4_0900_ai_ci
If you issue a query in this state, the following will be written to the binary log:
was written out, and because this 255 did not exist in the replica MySQL 5.7, an error occurred and replication stopped.

Reproduction environment

Version Information

Source side: Aurora5.7.mysql_aurora.2.11.0
Replica EC2MySQL 5.7.41
We set up binary log replication between Aurora v2 as the source and MySQL 5.7 installed on EC2 as the replica.

Steps to reproduce

[Aurora v2] Create a suitable table
 
Add records as appropriate
 
Execute UPDATE on the [Aurora v2] cluster from the MySQL5.7 client
 
[MySQL5.7 replica] Check if records have been updated
 
Execute UPDATE on [Aurora v2] cluster from MySQL8.0 client
 
[MySQL5.7 replica] When checking show slave status, a replication error occurs
 

Workaround

I don't think there are many opportunities to use this type of configuration,
First of all, all developers will use the MySQL 5.7 Client to connect to Aurora v2.
 
skip_character_set_client_handshake
Once you do this you can avoid this happening.
However, I would recommend using this setting only as a temporary measure during migration.
 

Reference URL

The following article was very helpful.
 
 

Conclusion

SRG is looking for people to work with us. If you are interested, please contact us here.