0

new to RBR and am wondering how to remove a column in the middle of a table in an active-passive setup without breaking replication? Pre-RBR I would just have run the alter on one side and then failover writes. Running Percona 5.6 server in a scaled out environment.

3
  • You used SBR before? Did you do the ALTER on each machine (with repl turned off)?
    – Rick James
    Jun 6, 2017 at 2:12
  • Why would RBR be different? ALTER TABLE still replicates as a statement, even with BINLOG_FORMAT set to ROW. Jun 6, 2017 at 3:03
  • I was facing issues in replication in earlier 5.0* versions of Mysql in RBR while altering or rebuilding indeces. Like exceeding max_row_segments or allowed_packets like that. I guess such issues are fixed right now. If you want to test it by adding two slaves one being (TEST-A) slave of production but master of TEST-B and another (TEST-B) being slave of (TEST-A). Try to alter on TEST-A, it shouldn't break the replication between A and B.
    – Mannoj
    Jun 6, 2017 at 5:14

2 Answers 2

1

ALTER will go through RBR, so I don't see it as being much different.

As long as

  • you don't do SELECT *, the fetches should remain OK.
  • no queries touch the column you are removing any time near the action, write queries should not be an issue.

If you are trying to avoid the table being blocked a long time, I see some use for the 'failover' you mentioned (but did not adequately detail).

What version do you have? 5.6 rebuilds "in place" and permits concurrent DML. So, the 'failover' may be overkill.

3
  • Thanks Rick. I was worried that dropping the columns on the target slaves first would break replication if they were missing. But you are saying that as long as the app is not adding data to those columns hould be ok? I think one is a tinyint default 0 and the other is a varchar default null. I guess I can test on one slave first. These tables have millions of rows. Is a drop column with online ddl efficient enough to not cause replication lag?
    – JShean
    Jun 6, 2017 at 11:14
  • (first issue) Oh, I get your point -- RBR is more tuned to the exact set of columns than SBR. Still, if there is no overlap of touching that table vs the ALTER, it should work.
    – Rick James
    Jun 6, 2017 at 14:15
  • (second issue) I have not used DROP COLUMN since ALTER added the "in place" optimization, so I cannot answer your question. Do you have multi-threaded slave threading? Or are all the tables in a single DB, thereby nullifying multi-threading (in 5.6)?
    – Rick James
    Jun 6, 2017 at 14:17
0

Yes, unfortunately its a pretty large table in a single database. I will test on a non-public slave, see if it breaks. Trying to be cautious, but I feel better having read your response. Thanks!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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