![\"\"](\"https:\/\/kudzia.eu\/b\/wp-content\/uploads\/mysqlcircularreplication-e1308997592444.png\" "\\"mysqlcircularreplication\\"")
\ni have two sites; asql0 and bsql0 are write<\/i> servers to which backend saves information, while aslq0b is read-only slave that in case of emergency can be promoted to the master role.<\/p>\n
i’ve been playing for a while with mysql circular replication; here are some notes from setting it up.
\n
\nmy configuration:<\/p>\n
\ni have two sites; asql0 and bsql0 are write<\/i> servers to which backend saves information, while aslq0b is read-only slave that in case of emergency can be promoted to the master role.<\/p>\n
my config – my.cnf:<\/p>\n
\r\n\r\n[client]\r\nport = 3306\r\nsocket = \/var\/run\/mysqld\/mysqld.sock\r\n\r\n[mysqld_safe]\r\nsocket = \/var\/run\/mysqld\/mysqld.sock\r\nnice = 0\r\n\r\n[mysqld]\r\n\r\nslave_compressed_protocol=true\r\nslave-net-timeout = 15\r\nmaster-connect-retry = 15\r\nmaster-retry-count = 0\r\n\r\nserver-id=102 ### every server involved in the replication needs to have unique id\r\n\r\n\r\nauto_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\r\nauto_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\r\nlog-slave-updates\r\nlog_bin= \/var\/log\/mysql\/mysql-bin.log\r\nrelay-log=mysqld-relay-bin\r\n\r\n\r\nskip_name_resolve\r\ncharacter-set-server = utf8\r\ninnodb_additional_mem_pool_size = 16M\r\ninnodb_buffer_pool_size = 128M\r\ninnodb_file_io_threads = 4\r\ninnodb_thread_concurrency = 8\r\ninnodb_flush_log_at_trx_commit = 2\r\ninnodb_log_buffer_size = 2M\r\ninnodb_log_file_size = 64M\r\ninnodb_log_files_in_group = 2\r\ninnodb_file_per_table\r\ninnodb_flush_method=O_DIRECT\r\n\r\nmax_connections = 1000\r\nwait_timeout = 10800\r\ninteractive_timeout = 10800\r\n\r\ninnodb_lock_wait_timeout = 120\r\ninnodb_file_per_table\r\nignore_builtin_innodb\r\nplugin-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\r\ninnodb_file_format=barracuda\r\ninnodb_strict_mode=1\r\n\r\nthread_stack = 256K\r\ngeneral_log= \/var\/log\/mysql\/mysql.log\r\n\r\nuser = mysql\r\npid-file = \/var\/run\/mysqld\/mysqld.pid\r\nsocket = \/var\/run\/mysqld\/mysqld.sock\r\nport = 3306\r\nbasedir = \/usr\r\ndatadir = \/var\/lib\/mysql\r\ntmpdir = \/tmp\r\nlanguage = \/usr\/share\/mysql\/english\r\nskip-external-locking\r\n\r\nkey_buffer = 16M\r\nmax_allowed_packet = 16M\r\nthread_cache_size = 8\r\n\r\nmyisam-recover = BACKUP\r\n\r\nquery_cache_limit = 1M\r\nquery_cache_size = 16M\r\n\r\n\r\n\r\n[mysqldump]\r\nquick\r\nquote-names\r\nmax_allowed_packet = 16M\r\n\r\n\r\n[isamchk]\r\nkey_buffer = 16M\r\n\r\n<\/pre>\nsecurely or not in my replication setup i use debian-sys-maint account; one needed change is to allow access to it from any hosts:<\/p>\n
\r\necho "update user set Host ='%' where User='debian-sys-maint'; flush privileges;" |mysql --defaults-file=\/etc\/mysql\/debian.cnf mysql\r\n<\/pre>\nto start the replication flowing between asql0 and asql0b [one way ‘classical’ replication] i<\/p>\n
check master position on asql0:<\/p>\n
\r\necho "show master status\\G"|mysql --defaults-file=\/etc\/mysql\/debian.cnf\r\n*************************** 1. row ***************************\r\n File: mysql-bin.000006\r\n Position: 494\r\n Binlog_Do_DB:\r\nBinlog_Ignore_DB:\r\n<\/pre>\ntell asql0b to follow asql0:<\/p>\n
\r\necho "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\r\n<\/pre>\nto start replication from asql0 to bsql0 i again check master status of asql0:<\/p>\n
\r\necho "show master status\\G"|mysql --defaults-file=\/etc\/mysql\/debian.cnf\r\n*************************** 1. row ***************************\r\n File: mysql-bin.000006\r\n Position: 494\r\n Binlog_Do_DB:\r\nBinlog_Ignore_DB:\r\n<\/pre>\nand on bsql0 i run the same command as on bsql0:<\/p>\n
\r\necho "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\r\n<\/pre>\nto start replication flowing ‘back’ from bsql0 to asql0 on bsql0 i check master log position:<\/p>\n
\r\necho "show master status\\G"|mysql --defaults-file=\/etc\/mysql\/debian.cnf\r\n*************************** 1. row ***************************\r\n File: mysql-bin.000008\r\n Position: 285\r\n Binlog_Do_DB:\r\nBinlog_Ignore_DB:\r\n<\/pre>\nand on asql0 i run:<\/p>\n
\r\necho "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\r\n<\/pre>\nfrom 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:<\/p>\n
\r\nCREATE DATABASE test;\r\nCREATE TABLE test.t(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, data varchar(10));\r\nINSERT INTO test.t(data) values('a'), ('b'),('c');\r\nSELECT * FROM test.t;\r\n<\/pre>\nwe can check status of every slave by running:<\/p>\n
\r\nSHOW SLAVE STATUS\\G\r\n<\/pre>\ni’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<\/p>\n
both solutions require good time synchronisation between sites. for now i’ve set up hourly comparison between all involved servers using mk-table-checksum<\/a> from maatkit<\/a>:<\/p>\n few links:<\/p>\n http:\/\/www.cwik.ch\/2011\/03\/setting-up-multi-master-circular-replication-with-mysql\/<\/p>\n http:\/\/dev.mysql.com\/doc\/refman\/5.1\/ja\/replication-topology-circular.html<\/p>\n http:\/\/onlamp.com\/pub\/a\/onlamp\/2006\/04\/20\/advanced-mysql-replication.html<\/p>\n http:\/\/capttofu.livejournal.com\/1752.html<\/p>\n http:\/\/freenac.net\/fr\/techguide\/multimaster<\/p>\n http:\/\/www.slideshare.net\/datacharmer\/advanced-mysql-replication-for-the-masses<\/p>\n handy commands:<\/p>\n disable logging of the queries to the binlog – handy when synchronising back servers out of sync:<\/p>\n telling the replication mechanism to skip one statement:<\/p>\n\r\nmk-table-checksum h=acds0,u=debian-sys-maint,p=whatever h=bcds0,u=debian-sys-maint,p=whatever --databases mysql,test| mk-checksum-filter\r\n<\/pre>\n
\r\nSET sql_log_bin = 0;\r\n<\/pre>\n
\r\nSET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; slave stop; slave start;\r\n<\/pre>\n