SET sql_log_bin = 0 in RDS MYSQL 5.6

Question

I have moved into RDS MYSQL 5.6 and now I have a problem. I have 5 read replicas and a/z master.

I am trying to alter a table and want to change a column value and also on some tables I want to add a new column. Although this operation is non blocking on MYSQL 5.6 but when it gets replicated to the slaves it causes slaves to lag for entire duration of it, I cannot afford slave lag as we are 24/7 operations.

I tried doing SET sql_log_bin = 0; to skip the query to the slave but RDS do not allow this, otherwise I could have done slave (one each) --> master approach and eventually changing on master.

Anyone has any advise how to go on about this in RDS MYSQL 5.6

Answer 1

What we have done in a similar scenario (using Aurora RDS for master and slaves) is to use an intermediate small EC2 instance with a standard MySQL acting as a filter. This instance is a relay-slave (secondary master) between our main master and final slaves with a configuration similar to this.

We came to this solution dued to the lack of SUPER privilege and replication configuration options in Aurora RDS: You can not set sql_log_bin or define replication filters in RDS.

The problem here is with DDL statements, they are replicated to slaves although you have replication filters by db or tables in the slave. So a possible workaround is:

1. Create an auxiliary schema in master.
2. Set a db filter in mysql.cnf of secondary master, so it only adds to its binlog changes in desired schema: binlog-do-db=<myschema>
3. If you need to add or modify columns of one of your tables in main master, first set the auxiliary schema as default: use auxiliary-schema;
4. Modify the table indirectly, like alter table <myschema>.<table> ...

This way, DDL statements are applied in secondary master, but not stored in its binlog and therefore not send to the slaves.

Answer 2

You cannot get around this with MySQL RDS, ... EVER !!!

• Can we disable binary logging for one specific session or user in AWS RDS
• MySQL Documentation says it requires SUPER even at the session level
• The Scale-Out Blog: Replicating from MySQL to Amazon RDS says

We will take a recent replicator build that contains the RDS changes, which are documented in Issue 425. Use 2.0.7 build 177 or later. The main improvement is to add a non-privileged slave mode that avoids invoking any of the operations forbidden by Amazon. Among other things Tungsten normally uses commands like 'SET SESSION SQL_LOG_BIN=0' to suppress writing to the binlog when it applies data on a slave. This command requires SUPER privilege, hence causes problems for underprivileged RDS logins

SUGGESTION

You need to move your database cluster to Amazon EC2 where you have the ability to change the SUPER Privilege at will.

Otherwise, if you insist on staying with RDS, you will have to do the following:

• Spin up 5 more read replicas
• Failover all read operation to those 5 new replicas
• Monitor the ALTER TABLE on each of the current replicas
• When ALTER TABLE command finishes on the 5 current replicas and the ALTER TABLE command starts occurring on any of the 5 new replicas, failover all read operations back to the 5 current replicas.
• Spin down the 5 new replicas

GIVE IT A TRY !!!