MySQL Replication Failed From GCP to AWS Aurora

kumar22011993 · 01-02-2024 12:03 AM

Hi Team,
I have GCP MySQL server and i was migrate GCP to AWS Aurora Database and replication was enabled by both the end. Replication is working fine as expected, recently i am facing one issue like below

Worker 1 failed executing transaction 'fcf5f0fb-724c-11ed-b414-42410a400006:9602812' at master log mysql-bin.000049, end_log_pos 71426786; Error 'Unknown authorization ID `cloudsqlsuperuser`@`%`' on query. Default database: ''. Query: 'GRANT 'cloudsqlsuperuser' TO'username'@'%''

How can i resolve the replication issue? Can someone help me to overcome this issue.

Thanks,

Krishnakumar K

robertcarlos

Hi @kumar22011993,

Welcome to Google Cloud Community!

There are certain migration considerations that you may need to check first before migrating to and from Cloud SQL. Please be advised that this is on based on Cloud SQL's documentation and this may be different for AWS.

Aurora uses the default character set server latin1 (until MySQL v 5.7). This is different from the Cloud SQL for MySQL’s default configuration for databases, tables, stored procedures, and functions, which are created with utf8 as the default character set during a migration. This can result in situations that lead to performance problems.
- If you were using the default character set in Aurora (until v 5.7) which is latin1, then the default character set should be changed from utf8 to latin1 in Cloud SQL before importing the data.
- Another solution could be to change everything to utf8, however, in this case users should test the complete application and dataset as changes to the character set can result in unexpected data representations.
Aurora MySQL 5.7 has many collations (for example utf8mb4_0900_ai_ci for character set utf8mb4) which are currently only available in Cloud SQL for MySQL 8.0. If you are using any such collations and import the data in Cloud SQL for MySQL 5.7, you will get an error message like "Error 'Character set '#255' is not a compiled character set". The solution is to change those collations to available collations in MySQL version 5.7.
Cloud SQL for MySQL doesn’t support the MyISAM engine unlike Aurora. It's advisable to convert all the tables into InnoDB before starting the Migration from Aurora to Cloud SQL. If there exists a table in an engine other than InnoDB, users can change the table’s engine using “ALTER” command:

mysql> alter table table_1 engine='Innodb' ;
Query OK, 0 rows affected (2.89 sec)
Records: 0  Duplicates: 0  Warnings: 0

In Aurora users can set up multiple readers behind a single endpoint, however, Cloud SQL for MySQL users doesn’t have this feature out of the box. Every read-replica in Cloud SQL for MySQL has its own IP and users need to use something like ProxySQL to split the traffic among multiple read replica instances. Here is a guide demonstrating how to use the ProxySQL to route the traffic among multiple read repli....
Aurora doesn’t have a change buffer. For more details read Change Buffer.
Query cache may impact performance. Read more about query cache.
Replication mechanism may impact performance.
Global transaction identifier (GTID) based replication. Read more about Limitations in GTID.

You can check out the following documentations that may help with your migration:

Hope this helps.