1

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

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.

0

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

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 !!!

  • that still do not solve the problem of how to do it on master as with mixed replication master will write this to binlogs and that will get replicated as a statement. doing it on slaves is not an issue we can do it one by one. but from master alter statement will get replicated which we do not want. – roger moore Jul 9 '15 at 8:46
  • 1
    There is only one thing left to do: Get away from RDS and switch to EC2 ASAP because you are simply not going get around this on RDS. With EC2, you have more control of MySQL under such circumstances. This is why it is crucial to decide between RDS and EC2. With regard to my answer, it is a last resort but is operational feasible. – RolandoMySQLDBA Jul 9 '15 at 9:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.