Syncing updates to master in master/slave setup [semi-sync replication]

Question

Background info

I have two MySQL 5.5 servers set up in a Master/Slave configuration with Semi-synchronous replication.

The database is being used for a high-profile WordPress site.

Problem

It seems to work great except for one thing:

When the master is down and changes are made to the slave, they are not written back to the master when it comes back up.

What I've done so far

I tried the answer [here], but it broke my replication completely.

Questions

• Is there a more standard way of setting this up or is it generally not supported?
• Should I just disallow writes on the slave?
• Is there a simple way to notify the master of changes, and sync them upstream?

Answer 1

You are almost there. I guess you have missed the auto_increment_increment & auto_increment_offset variables.

Set auto increment increment to the total number of servers.

Set auto_increment_offset to a number between 1 and auto increment increment for each server.

example:

Server 1: auto increment increment = 2, auto_increment_offset = 1

Server 2: auto increment increment = 2, auto_increment_offset = 2