i’ve been playing for a while with mysql circular replication; here are some notes from setting it up.
my configuration:
i have two sites; asql0 and bsql0 are write servers to which backend saves information, while aslq0b is read-only slave that in case of emergency can be promoted to the master role.
my config – my.cnf:
[client] port = 3306 socket = /var/run/mysqld/mysqld.sock [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice = 0 [mysqld] slave_compressed_protocol=true slave-net-timeout = 15 master-connect-retry = 15 master-retry-count = 0 server-id=102 ### every server involved in the replication needs to have unique id auto_increment_increment=2 ### it all depends on your application logic, but in general this should be at least equal to the number of servers invovled in the circular replication auto_increment_offset=2 ### if we have just 2 servers one of them should have it set to 2, another to 3 to guarantee unique generation of autoincrement values log-slave-updates log_bin= /var/log/mysql/mysql-bin.log relay-log=mysqld-relay-bin skip_name_resolve character-set-server = utf8 innodb_additional_mem_pool_size = 16M innodb_buffer_pool_size = 128M innodb_file_io_threads = 4 innodb_thread_concurrency = 8 innodb_flush_log_at_trx_commit = 2 innodb_log_buffer_size = 2M innodb_log_file_size = 64M innodb_log_files_in_group = 2 innodb_file_per_table innodb_flush_method=O_DIRECT max_connections = 1000 wait_timeout = 10800 interactive_timeout = 10800 innodb_lock_wait_timeout = 120 innodb_file_per_table ignore_builtin_innodb plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so innodb_file_format=barracuda innodb_strict_mode=1 thread_stack = 256K general_log= /var/log/mysql/mysql.log user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp language = /usr/share/mysql/english skip-external-locking key_buffer = 16M max_allowed_packet = 16M thread_cache_size = 8 myisam-recover = BACKUP query_cache_limit = 1M query_cache_size = 16M [mysqldump] quick quote-names max_allowed_packet = 16M [isamchk] key_buffer = 16M
securely or not in my replication setup i use debian-sys-maint account; one needed change is to allow access to it from any hosts:
echo "update user set Host ='%' where User='debian-sys-maint'; flush privileges;" |mysql --defaults-file=/etc/mysql/debian.cnf mysql
to start the replication flowing between asql0 and asql0b [one way ‘classical’ replication] i
check master position on asql0:
echo "show master status\G"|mysql --defaults-file=/etc/mysql/debian.cnf
*************************** 1. row ***************************
File: mysql-bin.000006
Position: 494
Binlog_Do_DB:
Binlog_Ignore_DB:
tell asql0b to follow asql0:
echo "SLAVE STOP; RESET SLAVE; CHANGE MASTER TO MASTER_HOST='asql0',MASTER_USER='debian-sys-maint',MASTER_PASSWORD='whatever',MASTER_LOG_FILE='mysql-bin.000006',MASTER_LOG_POS=494,MASTER_CONNECT_RETRY=10; SLAVE START;" | mysql --defaults-file=/etc/mysql/debian.cnf
to start replication from asql0 to bsql0 i again check master status of asql0:
echo "show master status\G"|mysql --defaults-file=/etc/mysql/debian.cnf
*************************** 1. row ***************************
File: mysql-bin.000006
Position: 494
Binlog_Do_DB:
Binlog_Ignore_DB:
and on bsql0 i run the same command as on bsql0:
echo "SLAVE STOP; RESET SLAVE; CHANGE MASTER TO MASTER_HOST='asql0',MASTER_USER='debian-sys-maint',MASTER_PASSWORD='whatever',MASTER_LOG_FILE='mysql-bin.000006',MASTER_LOG_POS=494,MASTER_CONNECT_RETRY=10; SLAVE START;" | mysql --defaults-file=/etc/mysql/debian.cnf
to start replication flowing ‘back’ from bsql0 to asql0 on bsql0 i check master log position:
echo "show master status\G"|mysql --defaults-file=/etc/mysql/debian.cnf
*************************** 1. row ***************************
File: mysql-bin.000008
Position: 285
Binlog_Do_DB:
Binlog_Ignore_DB:
and on asql0 i run:
echo "slave stop; reset slave; CHANGE MASTER TO MASTER_HOST='bsql0',MASTER_USER='debian-sys-maint',MASTER_PASSWORD='whatever',MASTER_LOG_FILE='mysql-bin.000008',MASTER_LOG_POS=285,MASTER_CONNECT_RETRY=10; slave start;" | mysql --defaults-file=/etc/mysql/debian.cnf
from now on any sql command updating data on asql0/bsql0 should be replicated to all other involved machines. we can check it by executing for instance:
CREATE DATABASE test;
CREATE TABLE test.t(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, data varchar(10));
INSERT INTO test.t(data) values('a'), ('b'),('c');
SELECT * FROM test.t;
we can check status of every slave by running:
SHOW SLAVE STATUS\G
i’m aware of possible consequences of a split brain scenario when both sites are partitioned and some client first submits change to existing entity to one of site A and then fails over and sends another change to the site B. i can think of following ways to solve it
- save all previous versions of the entity in the database and change application logic to always pick the newest one
- have automated consistency check between sites and fix – manually or automatically – found problems based on the timestamp
both solutions require good time synchronisation between sites. for now i’ve set up hourly comparison between all involved servers using mk-table-checksum from maatkit:
mk-table-checksum h=acds0,u=debian-sys-maint,p=whatever h=bcds0,u=debian-sys-maint,p=whatever --databases mysql,test| mk-checksum-filter
few links:
http://www.cwik.ch/2011/03/setting-up-multi-master-circular-replication-with-mysql/
http://dev.mysql.com/doc/refman/5.1/ja/replication-topology-circular.html
http://onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html
http://capttofu.livejournal.com/1752.html
http://freenac.net/fr/techguide/multimaster
http://www.slideshare.net/datacharmer/advanced-mysql-replication-for-the-masses
handy commands:
disable logging of the queries to the binlog – handy when synchronising back servers out of sync:
SET sql_log_bin = 0;
telling the replication mechanism to skip one statement:
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; slave stop; slave start;
apparently master-connect-retry does not work the way i would expect, at least not in debian’s 5.1.49-3-log – i was getting “error reconnecting to master ‘debian-sys-maint@tprxy1:3306′ – retry-time: 180 retries: 0’ with the default 180sec value. but i solved it by using MASTER_CONNECT_RETRY option in the CHANGE MASTER TO command:
CHANGE MASTER TO MASTER_HOST='tprxy0',MASTER_USER='debian-sys-maint',MASTER_PASSWORD='WHATEVER', MASTER_LOG_FILE='mysql-bin.000025',MASTER_LOG_POS=106,MASTER_CONNECT_RETRY=15;
sidenote: in all my configuration i use actually ip addreses not dns names. i compare the content of the servers every 60 minutes to make sure they did not run out of sync; it’s done with table-checksum from maatkit/percona toolkit. i also monitor the slave lag [in both directions]. if there are inconsistencies/high slave lag – i get a notification and have ready scripts for semi-manual synchronization of the content [scripts are very much data specific and cannot be used in generic setups].
One thought on “mysql circular / master-master replication”