mysql circular / master-master replication

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 Comment

  1. Pingback: multi-master mysql replication with servers on different continents – .pQd's log

Leave a Reply

Your email address will not be published. Required fields are marked *

 

(Spamcheck Enabled)