How to perform schema changes on replica?

[Roman Semenov]

To avoid downtime I usually perform time consuming schema changes on replica:

1. Stop replication
2. Perform time consuming changes
3. Enable replication
4. Allow slave to catch up
5. Promote it to master

That's how I do it on Amazon RDS. But I wasn't able to do it on a Google SQL instance. When I try to do that on a slave, it complains that MySQL server is running with the --read-only option. I tried to promote replica to a standalone instance and configure it manually but `CHANGE MASTER` also fails with "Access denied; you need (at least one of) the SUPER privilege(s) for this operation".

So how do I alter tables without hours (or days) of downtime?

[George (Cloud Platform Support)]

Hello Roman, 

CloudSQL is a managed environment based on MySQL; this means that often used MySQL commands, in particular all those that require the SUPER privilege, are restricted and would not run. This is the meaning of the "access denied" message you received. 

A read replica is a copy of the master that reflects changes to the master instance in almost real time. You may gather more insight by reading the "Replication Options" document. The master does not reflect changes on replicas, but the other way round. You need to modify the master; this is what the managed environment allows. 

[Roman Semenov]

> You need to modify the master; this is what the managed environment allows

So I need to put my website down for maintenance for a full day or more just to change DB schema? And there are no alternatives? This sounds strange especially considering that on Amazon I can perform those changes without any downtime in a way described above.

[George (Cloud Platform Support)]

Hi Roman, 

Replicas in the case of CloudSQL are maintained as exact copies of the original, in real time, so they are not defined as the other replicas you refer to. If you decide to rather use cloning, the way these clones are described on the "Cloning Instances" page, you can get similar results as from steps 1 to 5 above. So instead of replicating, you clone the instance, you modify this cloned instance and, when ready, you switch your app's traffic to the newly configured instance. It should not take shutting down your application for a whole day, as your editing of the cloned instance is not affecting current functionality. You may take your time and only switch traffic when you are ready. 

[Roman Semenov]

I tried to do that. The problem is that after I change schema on the cloned instance there is no way to transfer changes that happened on master in that time (steps 3 and 4) because I can't define it as a slave to current master and allow it to catch up, because I don't have SUPER privileges. To clarify, I'm making only backwards compatible changes that don't cause desync between master and slave.

[Skyler Slade]

The way we handle this (and we did this with self-hosted MariaDB, before we migrated some databases to Cloud SQL), is to create a copy of the table we wish to modify with the new schema, perform the alter there, and use triggers to keep the old and new tables in sync. When the alters are complete and tables are in async, we use RENAME TABLE, which is atomic, to swap the two tables. This means we can make big alters without incurring downtime.

We're mostly a PHP shop, so we use a project called LHM to handle all of this orchestration for us: https://github.com/masom/lhm_php.

[Roman Semenov]

Yes this is one possible solution. There are some problems with it too though. For example if you have foreign keys in other tables they will keep referencing old table and it will mess up your data if you allow a table without constraints to slip into production. You either need to briefly take down your app, disable key checks, and drop the old table or rebuild foreign keys for new table, which can take a lot of time too.

There is a `pt-online-schema-change` tool in Percona toolkit for doing exactly that but I've tried to avoid it because it still introduces additional load and IOPS to master instance while it is copying and converting tables.

[George (Cloud Platform Support)]

As this is getting a bit too specialized and coding-related, it may be worthwhile mentioning that for programming or database administration advice, stackoverflow or serverfault my prove more appropriate, as experts in programming or system administration are ready to reply on those forums, focusing on technical matters. Groups are meant more for general discussion and opinion voicing.