The goal of this configuration is to allow each server to operate autonomously for a period of time should it ever lose contact with the other 2 servers, while doing everything I can to make sure there will be no conflicting database updates once connectivity resumes. Each server will act as both a Master and a Slave with replication going in a circle. The binary log, which is written by the master and read by the slave, contains the name of the MySQL server which wrote it. Therefore a loop will never happen, as once the query comes back around to its original master, the master will know that it has already been executed.
I’ll attempt to prevent key conflicts by using the auto_increment_offset and auto_increment_increment configuration variables, to ensure AUTO_INCREMENT values will be unique to each server. This may also prove handy as I’ll be able to tell which server created a record, just by looking at the key value.
There is one remaining potential problem which I can’t guarantee won’t happen, that is the problem of the order of updates. Based on my lovely sketch of the replication circle, consider this scenario:
- nl.cwik.ch loses connectivity to us.cwik.ch, interrupting replication
- user A updates a post on nl.cwik.ch: UPDATE posts SET title=’Some new title’ WHERE id=123;
- the post does not get replicated to us.cwik.ch because the link is down
- later, user B updates the same post on us.cwik.ch: UPDATE posts SET title=’Some other title’ WHERE id=123;
- user B’s update gets replicated around the circle to nl.cwik.ch
- the connection between nl and us comes back up
- the original update statement from nl.cwik.ch gets replicated to us and nl servers
- we now have inconsistent data:
- title on nl: Some other title
- title on us: Some new title
- title on ie: Some new title
It’s an unlikely scenario, but plausible. One potential workaround would be to design your application such that records never get updated, but instead create a new record for every change. You can then always select the newest record based on a timestamp column, so in the above scenario all servers would show ‘Some other title’ as the newest blog post title.
Another possibility, should you consider the likelihood of this scenario limited for your application, would be to store a checksum of each row and compare the checksums across all the servers. Any inconsistencies could be flagged and/or an administrator notified.
I already installed mysql-server earlier, so let’s get down to the configuration!
Step 1: Enable the MySQL service and create a config file. I’ve posted my config as an example. This will need to be done on all 3 servers.
chkconfig mysqld on vi /etc/my.cnf (use my config as a starting point, or create your own - not forgetting to set server_id and auto_increment_offset appropriately) service mysqld start
Step 2: Log in to your new MySQL server using ‘mysql -u root’
Step 3: Set your root password and configure your replication user (do this on each server):
USE mysql DELETE FROM user WHERE user=''; UPDATE user SET password=PASSWORD('your new root password') WHERE user='root'; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'18.104.22.168' IDENTIFIED BY 'somethinglongandrandom'; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'22.214.171.124' IDENTIFIED BY 'somethinglongandrandom'; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'126.96.36.199' IDENTIFIED BY 'somethinglongandrandom'; FLUSH PRIVILEGES; SHOW MASTER STATUS; +---------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +---------------------+----------+--------------+------------------+ | mysql-bin-ie.000003 | 685 | | | +---------------------+----------+--------------+------------------+
Now on each server, use CHANGE MASTER to start slaving the next server in the circle, until each server is both master and slave. Use the log file name and position as shown in SHOW MASTER STATUS.
On nl.cwik.ch: CHANGE MASTER TO MASTER_HOST='ie.cwik.ch', MASTER_USER='repl', MASTER_PASSWORD='somethinglongandrandom', MASTER_LOG_FILE='mysql-bin-ie.000003', MASTER_LOG_POS=685; On us.cwik.ch: CHANGE MASTER TO MASTER_HOST='nl.cwik.ch', MASTER_USER='repl', MASTER_PASSWORD='somethinglongandrandom', MASTER_LOG_FILE='mysql-bin-nl.000003', MASTER_LOG_POS=685; On ie.cwik.ch: CHANGE MASTER TO MASTER_HOST='us.cwik.ch', MASTER_USER='repl', MASTER_PASSWORD='somethinglongandrandom', MASTER_LOG_FILE='mysql-bin-us.000003', MASTER_LOG_POS=685;
Step 4: Replication should now be up and running in a multi-master, circular configuration. Use ‘SHOW SLAVE STATUS\G’ on each server to check that it’s working properly. Check that Slave_IO_Running and Slave_SQL_Running both report Yes. Once it’s all working properly, create a test table to check that the AUTO_INCREMENT values are being created properly (with unique values per server):
mysql ie> USE test; mysql ie> CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, val VARCHAR(255) NOT NULL); mysql ie> INSERT INTO test (val) VALUES ('I am Ireland'); mysql nl> INSERT INTO test (val) VALUES ('I am the Netherlands'); mysql us> INSERT INTO test (val) VALUES ('I am the USA'); mysql ie> INSERT INTO test (val) VALUES ('I am Dublin'); mysql nl> INSERT INTO test (val) VALUES ('I am Amsterdam'); mysql us> INSERT INTO test (val) VALUES ('I am Chicago');
Check the table contents and note the id values:
mysql> SELECT * FROM test; +----+----------------------+ | id | val | +----+----------------------+ | 1 | I am Ireland | | 2 | I am the Netherlands | | 3 | I am the USA | | 11 | I am Dublin | | 12 | I am Amsterdam | | 13 | I am Chicago | +----+----------------------+ 6 rows in set (0.00 sec)
Looking good! Note the records created by the Irish server always end in 1, the Dutch server always end in 2 and the American server always will end in 3.