Master Slave configuration in cloud SQL Mysql

[tobyjames51]

Hi,

I'm trying to create a master slave configuration between two databases (Master being HDD and slave a new SSD database instance). The requirement is to sync the data in master to the slave database until the master database is decommissioned. This operation is to reduce the downtime required for data migration when the DB switch happens.

I tried executing the CHANGE MASTER command. But getting error: ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation. 

How can I perform this task without using the read replica option?

[ms4446]

In Cloud SQL for MySQL, users lack direct access to the SUPER privilege, which is required to execute the CHANGE MASTER command. As a result, setting up replication between a master on an HDD instance and a slave on an SSD instance without using read replicas necessitates an alternative approach.

To begin, the initial data synchronization can be performed using the mysqldump tool. By taking a consistent snapshot of the master database with the --single-transaction flag, data can be dumped and then restored on the SSD-based slave instance. This method ensures minimal data inconsistencies during the dump process.

For ongoing synchronization, enabling binary logging on the master is crucial. This allows for the capture of all subsequent data changes. Additionally, configuring Global Transaction Identifiers (GTID) by enabling gtid_mode and enforce_gtid_consistency on both the master and slave instances is recommended. This setup can be managed through the Google Cloud SQL instance flags in the console, as direct access to MySQL configuration files like my.cnf is restricted in Cloud SQL.

If GTID is not preferred, an alternative manual syncing process involves using mysqldump --master-data to capture binary log coordinates, followed by using mysqlbinlog to apply these logs incrementally to the slave. Automating this process with scripts or cron jobs is advisable to ensure continuous data synchronization.

Before finalizing the migration and decommissioning the HDD master, it's essential to thoroughly test the failover process. Ensuring that the slave instance is consistent and that applications can seamlessly connect to it is critical to a successful migration. Additional considerations such as planning for potential downtime during the initial dump, ensuring adequate network bandwidth, and setting up monitoring for the replication process should also be taken into account.

While the lack of direct access to SUPER privileges in Google Cloud SQL requires a more manual approach to replication setup, careful planning, the use of GTID, and automation can effectively minimize downtime and ensure data consistency during the migration from an HDD to an SSD-based instance.

[virinchi-t]

Hi 

I see that you want to replicate to slave all the data and then decommission master. 
Another approach can be , create a read replica for current CloudSQL instance. This way replication is always happening. When you want to decommission old  master. You can simply "promote" your read replica to be master. 

[jaia]

Hello,

Thank you for your engagement regarding this issue. We haven’t heard back from you regarding this issue for sometime now. Hence, I'm going to close this issue which will no longer be monitored. However, if you have any new issues, Please don’t hesitate to create a new issue. We will be happy to assist you on the same.

Regards,
Jai Ade