MySQL Master - Master (Select, Insert, Update, Delete for both)

Question

I'm looking for a way to have 2 databases running; 1 at the office and 1 at a data center. When at the office, employees would connect locally but when outside the office they would connect remotely to the data center. Both databases would be fully synchronized. That means that an employee could log in and update a record on the data center and that change would replicate instantly to the office server (or vice-versa). So, either user could edit the same record.

So, the typical scenario of auto_increment_offset etc. won't work because, in this case, each server has to be able to update the same record.

Am I missing something obvious? I can't think of a viable way to handle this. 2 users on 1 db can modify the same records so there HAS to be a way to do the same in this type of setup. I just can't think of one.

Answer 1

You can not do this reliably with asynchronous replication. Consider this simple example:

site A: update foo set bar = 1
site B: update foo set bar = 2

Some time later (milliseconds or hours depending) the transactions are replicated to each site:

site A: update foo set bar = 2
site B: update foo set bar = 1

foo.bar will now be 2 on site A and 1 on site B. There is nothing in MySQL that will detect this or prevent it from happening.

Either partition the problem so the masters "own" different parts of the data or elect a primary master for all updates. Or implement some global locking mechanism.

With one database things are completely different since there is synchronous locking going on internally.

Answer 2

Here's how we did it:

In master-master configuration there are 2 masters and 2 slaves. Both are masters and slaves at the same time:

master1 => slave1[master2]
[slave1]master1 <= slave2

enable binlogging on master server - my.cnf file under [mysqld] section:

log-bin=mysql-bin.log
relay_log=relay-bin.log
server-id = 1
log-slave-updates
auto_increment_increment = 10
auto_increment_offset = 1

You may need to add:
skip-slave-start option too, to prevent from start slave at database startup.

Note:
Each server should use different server-id auto_increment_offset should differs

Auto increment options:
auto_increment_increment controls the increment between successive AUTO_INCREMENT values. auto_increment_offset determines the starting point for AUTO_INCREMENT column values.

With auto_increment_increment = 10 and auto_increment_offset=5, auto_inc values would be 5, 15, 25..

Dedicated user:
GRANT REPLICATION SLAVE ON . TO 'replication'@'%' IDENTIFIED BY 'some_password';

Create consistent, binary backup of MySQL database (needed to setup a replication) using LVM but the same steps will apply to ZFS/UFS snapshot and other popular techniques:

connect to MySQL database on master (server1) and run "FLUSH TABLES WITH READ LOCK". It might take few second to complete flush operation and it will lock all tables! It is imporant to keep it in mind, because such operation may impact in production load in some cases. It's better to schedule replication setup to peak-off hours.

Create LVM snapshot (server1): lvcreate -L10G -s -n mysqlbackup /dev/vg/lvm_mysql_partition

Get information about master position. Using previously created connection to database run : SHOW MASTER STATUS; to get all information about current binlog, position and so on.

Copy-paste output somewhere - for future usage.

Uunlock all tables: UNLOCK TABLES;

Mount previously created filesystem: mount /dev/vg/mysqlbackup (this will allow you to access shapshot of data created).

Now you can just copy this data to the second server directly to data dir. Before you start up your database add previously mentioned parameters to my.cnf file, changing value of server-id.

Now, with master information copied somewhere and database files propagated to slave you can remove snapshot: lvremove -f /dev/vg/mysqlbackup on master (server1).

Now, you can log into server2 to check for permissions (depends on which user was used to copy files you'll need to correct it) and after that, startup MySQL instance. Database should start, perform InnoDB recovery (if you're signed it) and after a bit, you will be able to log into using command line client. Because of skip-slave-start your slave will not start by default.

Now, on both servers you need to configure replication thread by setting up master hostname, master port, password, user and info about position:

CHANGE MASTER TO master_host='IP_addr_of_server1', master_port=3306, master_user='replication', master_password='some_password', master_log_file='info_from_MASTER_STATUS', master_log_pos='info_from_MASTER_STATUS';

Now you can start replication: START SLAVE;

Check to make sure: SHOW SLAVE STATUS\G (on new machine)

Seconds_Behind_Master might not be equal to 0 because of changes done to database after you release all tables but with replication working slave should catch master pretty fast.

After your slave server catches up with master, start master-master (server1-server2) setup. Lock all tables on server2 ( FLUSH TABLES WITH READ LOCK;) and run SHOW MASTER STATUS;. On server1 set-up replication by CHANGE MASTER TO.. of course you need to change master_host to valid IP address, master_log_file and master_log_pos values. After that, UNLOCK TABLES on server2 and START SLAVE on server1.

Now you should have master-master-slave configuration with master-master replication between server1 and server2.