GTID_MODE=OFF replication restriction relaxation

[OscarV]

I am attempting to migrate a legacy on-prem MariaDB 10.7 database to CloudSQL MySQL 8.0.

To achieve this, I have created an intermediary MySQL 5.7 database to bridge the version compatibility.

The replication setup looks like this: MariaDB 10.7 -> MySQL 5.7 -> CloudSQL MySQL 8.0

Due to GTID incompatibilities between MariaDB and MySQL, I am required to use binary log / anonymous transactions during replication.

This setup works; I have a CloudSQL instance replicating from my MySQL instance where GTID_MODE=OFF, which I setup using Type 1: Continuous migration and managed dump from here.

As expected, after doing this migration the CloudSQL instance also runs with GTID_MODE=OFF.

CloudSQL introduces a restriction whereby replicas cannot be created inside CloudSQL if running in GTID_MODE=OFF mode. I understand why Google have added this restriction, as they could not be as flexible with creating replicas/promotion/failover if they permitted anonymous transactions inside CloudSQL.

However, in MySQL 8.0 a new feature ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS was added. This allows a MySQL instance to replicate from anonymous transactions and convert these into GTID transactions.

I believe CloudSQL could be more flexible to use-cases like mine if when replicating from an on-prem instance with GTID_MODE=OFF it operated in mode GTID_MODE=ON_PERMISSIVE along with ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS=LOCAL.

This would mean that the CloudSQL replica from my on-prem database would become the "GTID boundary", and internally to CloudSQL you could use only GTID-based replication, which would likely address the internal reasons you chose to only support GTIDs. From my perspective, there is no reason not to support this configuration.

Has such a feature been considered by CloudSQL? It would make my migration path significantly easier.

[ms4446]

Your strategy for migrating a legacy MariaDB database to CloudSQL MySQL 8.0, utilizing an intermediary MySQL 5.7 database, presents a potential workaround to the GTID incompatibility challenge between MariaDB and MySQL. The issue you've encountered with CloudSQL's requirement for GTID_MODE to be enabled for creating replicas is a known limitation.

The introduction of the ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS feature in MySQL 8.0 offers a promising avenue to overcome this hurdle. This feature enables replication from sources without GTID support to replicas that utilize GTIDs, potentially broadening CloudSQL's support for migrations from legacy databases like yours.

However, CloudSQL does not currently support ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS or the operation in GTID_MODE=ON_PERMISSIVE for these specific scenarios. CloudSQL's architecture aims to simplify and secure MySQL configurations by abstracting them from the user, streamlining operations. While this design philosophy supports a wide array of use cases, it may limit the flexibility needed for more complex migration scenarios like the one you are facing.

To advocate for the inclusion of this feature and enhance CloudSQL's migration capabilities, consider undertaking the following steps:

• Submit a Feature Request: Highlight the broader benefits of enabling ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS for Cloud SQL users. By sharing your specific use case with Google Cloud's development team through their Issue Tracker, you can increase the visibility of this request. The more community support this feature receives, the more likely it is to be considered for implementation.

• Explore Workarounds: Alongside the feature request process, continue to explore potential workarounds that may enable you to achieve your migration goals within the existing constraints of Cloud SQL.

Your feedback and proactive advocacy are crucial to the ongoing development of CloudSQL. By highlighting the need for advanced migration support, you are helping to shape a service that better accommodates the complex requirements of its user base.

[OscarV]

Thank you, I raised a feature request here: https://issuetracker.google.com/issues/323695986