1

I have this scenario where I have 4 MySQL servers (A,B,C,D), as this:

  1. Server (A) is always online, and serves as the main website.
  2. Servers (B,C and D) are online on random periods of the day, and not concurrently.
  3. Each of the servers can make changes to the tables, whether the state online or offline, the changes should be applied to the other servers.

I've followed this tutorial and everything went okay, until this happened: two servers were offline, the replication stopped, and I needed to reset the slave service on all servers from phpMyAdmin Control Panel in order to get the replication work again.

I need a solution that works automatically. How can I achieve that?

  • 1
    You have something worse that "circular" replication. I don't see a way to achieve your goals. If you are willing to direct all writes to A, then there is hope, using A=Master, B,C,D are Slaves. – Rick James Aug 17 '15 at 2:26
  • Can you explain what you mean that server can make changes to tables whether state is online or offline? Specifically in your scenario how does a server make changes to tables when it is offline? – Derek Downey Aug 19 '15 at 16:07
  • @DerekDowney I mean that some servers are working locally (offline) and in some point they go online to exchange data with the main website server – MoeZir Aug 20 '15 at 13:45
1

The tutorial you followed was mine and was the accepted answer.

However, the other answer given by AaronBrown had a very strong caveat.

Instead of having a single point of failure, now you have three.

What if one of the servers goes down? Now replication is broken everywhere:

How do you replace it easily and align the binary log positions? You can't without doing some very complicated and error prone binary log analysis.

So, now you have to restore all three servers from a backup. Wait, was that backup taken at the same moment in time? Nope, because there's no way to make a transactional backup across multiple servers. So, you're left with broken replication and attempting to fix it with pt-table-checksum and pt-table-sync...or rebuilding the entire cluster from scratch.

In your particular case, I adamantly advise against using pt-table-sync. Why ? Running pt-table-sync will create a script that you can run on the Slave so that the Slave will end up looking like its Master. THAT WILL DELETE DATA FROM THE SLAVE THAT ORIGINATED FROM THAT SLAVE IN ITS ROLE AS A MASTER !!! This is why I upvoted AaronBrown's answer and I wrote a comment on AaronBrown's answer as to why I liked his answer stating

I have to agree with your answer because I have had to do just what you are saying with some clients. In some cases, if you do not remember to precede the output of pt-table-sync with "SET SQL_LOG_BIN=0;", unintended changes may cascade and you end up chasing your tail like a crazed dog. Error prone, indeed !!!

WHAT TO DO NEXT ...

To sync each node, you will have to perform the following:

From ServerA (Master) to ServerB (Slave)

STEP 01) On the Master, create dumpfile with binary log disabled in your session as first line

STEP 02) mysqldump from Master without overwriting (using --insert_ignore)

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
DUMPFILE=/path/to/MasterDumpFile.sql
MYSQLDUMP_OPTIONS="--single-transaction --no-create-info --insert-ignore"
SQL="SELECT schema_name FROM information_schema.schemata"
SQL="${SQL} WHERE schema_name NOT IN ('information_schema','mysql','performance_schema')"
DBLIST=`mysql ${MYSQL_CONN}`
echo "SET sql_log_bin = 0;" > ${DUMPFILE}
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} >> ${DUMPFILE}

STEP 03) Move the dumpfile to the Slave

STEP 04) Execute the DumpFile on the Slave

STEP 05) Repeat Steps 1-4 for ServerB (Master) and ServerC (Slave)

STEP 06) Repeat Steps 1-4 for ServerC (Master) and ServerD (Slave)

STEP 07) Repeat Steps 1-4 for ServerD (Master) and ServerA (Slave)

When done, all nodes should look identical.

Sorry I don't have an automatic solution, but this is the next best thing.

| improve this answer | |
  • Thanks RolandoMySQLDBA for your response, I just need to know if I have to do this steps every time the servers go offline or is it only for the first setup ? – MoeZir Aug 20 '15 at 13:50

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.