MySQL Circular Replication

Question

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?

Answer 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.