0

I have a master-master configuration in MySQL with two servers. One server should stay live on the network to serve requests (call it server A) and the other should be taken offline to push new code changes (server B).

My idea originally was that after running STOP SLAVE on both servers, that server B could be shut down, updated, and even have a new database schema put in. After this, I thought that I could simply START SLAVE on server B and have the entire database from server A replicated/mirrored back over to server B. However, this is not the case. Restarting the slave and doing a CHANGE MASTER TO (...) and syncing up the log files does not replicate old changes like I want it to: it only replicates new writes from that point on.

I am looking for a way to bring server B up to speed with the latest database from server A, and then have server B continue to replicate changes in a master-master setup. Then I can continue the sequence of server upgrades by doing the same process but keeping server B online only.

Any solutions which require locking the tables will not work since I need to do this change without any downtime. Is this possible?

0

Slow ALTER in Master-Master

In Master-Master, each server is acting both as a Master and a Slave to the other server.

STOP SLAVE -- When you later do START SLAVE, the stuff that was performed on the Master will be applied to the Slave. If you did, say, a big ALTER on a big table, that ALTER will run, taking a long time. This is probably the "downtime" you are worried about?

The alternative is to do the lengthy process twice.

  1. Stop client usage of B.
  2. On B, turn off writing to the binlog
  3. On B, do the ALTER(s), etc.
  4. On B, turn on writing to the binlog
  5. Wait for replication to catch up. (It was probably blocked by the ALTER.)
  6. Flip -- that is stop talking to A and start talking to B.
  7. On A, (steps 2,3,4,5) binlog off, ALTER, binlog on, catch up

Alternatives

In Galera, this is "RSU" (as opposed to "TOI").

See also pt-online-schema-change -- this tool (percona.com) allows for doing time consuming maintenance on a table with essentially no downtime.

5.7 can now do many ALTERs with ALGORITHM=INPLACE, hence little or no downtime.

Putting the burden on the Code

Keep in mind that some code changes involve schema changes. Sometimes they can be done in a "backward compatible" manner, thereby obviating stuff like this.

In the extreme, you might need to double up the code. That is, have it capable of handing the old schema and the new schema at the same time. This complicates the code, but eliminates coordination problems between code and schema. (The code should eventually be cleaned up to throw out the 'old' variant.)

That last way becomes especially important when you have Sharding, multiple clients, or other cases of "lots of servers to update".

  • I am currently already doing that 7 step process. I think my question was more along the lines of, if server B has nothing (all tables are truncated) and server A holds all the data and it is being written to by clients, is there a way to 'mirror' over all the changes back to server B without taking server A offline. – nwrobel Jan 19 '16 at 1:50
  • 1
    Replication does not provide an automatic transfer of data. It only transfers things that were just now inserted/updated/deleted. – Rick James Jan 19 '16 at 2:53
  • Okay, thanks for the clarification. So what sort of tools/tactics do you suggest if the two masters become out of sync? Say a server gets shut down for an hour and is now missing entries and/or has entries which are old. How would you bring that server back up to speed with the live database? I guess that is closer to my question. – nwrobel Jan 19 '16 at 22:04
  • After re-establishing Master-Slave, replication should "catch up" and be in sync after that. What Engine are you using? What binlog format (ROW/MIXED/STATEMENT)? – Rick James Jan 20 '16 at 0:50
  • Check the percona toolkit -- pt-table-checksum and pt-table-sync – Rick James Jan 20 '16 at 0:52

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.