{"id":2847,"date":"2018-04-08T10:12:01","date_gmt":"2018-04-08T09:12:01","guid":{"rendered":"https:\/\/kudzia.eu\/b\/?p=2847"},"modified":"2018-04-08T11:24:10","modified_gmt":"2018-04-08T10:24:10","slug":"multi-master-mysql-replication-with-servers-on-different-continents","status":"publish","type":"post","link":"https:\/\/kudzia.eu\/b\/2018\/04\/multi-master-mysql-replication-with-servers-on-different-continents\/","title":{"rendered":"multi-master mysql replication with servers on 3 different continents"},"content":{"rendered":"

at work i’m using mysql replication quite extensively. first it was a straightforward one-way replication that has been rock-solid for us since 2009. in 2012, for another type of data, we’ve started using master-master setup. initially the servers were in different European countries, eventually the secondary site was moved to North America while primary one remained in Europe. i’ve described the setup in this post<\/a>.<\/p>\n

\"\"<\/p>\n

clients periodically select preferred site based on the lowest response time. their request generate simple read\/write queries; updates get asynchronously replicated between both sites. if you google about similar setup you’ll find a lot of warnings – this architecture might lead to inconsistency when contradicting updates arrive nearly at the same time to both sites. in our case it’s highly unlikely. also, we have extensive monitoring and alerting based on pt-table-checksum and a simple automated conflict resolution script. it has worked very well for us and gave multi-year 100% service availability also in a few events of network outages affecting either of the datacenters. <\/p>\n

in 2017 we’ve expanded this setup and added a third site – in South East Asia. it was done to bring the client-facing application servers closer to our new users in that region and decrease the UI latency. users like responsive interfaces. as with the original setup from 2012 i was reluctant at first – i expected to see plenty of inconsistencies. it did not happen – so far it worked flawlessly. here’s the new architecture:<\/p>\n

\"\"<\/p>\n

in this setup asql0 is using multi-source replication<\/a> – a mechanism that is available since mysql 5.7. it fetches and replies binlogs from both bsql0 and csql0; thanks to the log-slave-updates<\/i> directive it also acts as replication master for asql0b that collects all updates done to asql0, bsql0, csql0.<\/p>\n

configuration needed for this setup:<\/p>\n

asql0 – the ‘central’ node – my.cnf:<\/p>\n

\r\nserver-id               = 10 # must be unique across all servers connected in the replication graph\r\nlog_bin                 = \/var\/log\/mysql\/mysql-bin.log\r\nauto_increment_increment=3 # thanks to this and one below we avoid conflicting auto-increment ids when inserts occur around the same time on different replication masters\r\nauto_increment_offset=3    # this one must be different for each master, in my case it's 3 for asql0, 4 for bsql0, 5 for csql0\r\nlog-slave-updates          # allows updates done on bsql0 to reach csql0 and asql0b\r\nbind-address=*             \r\nmaster-info-repository=TABLE # more durable way of storing the replication position\r\nrelay-log-info-repository=TABLE\r\nbinlog_format=STATEMENT   # ROW would be nice but i don't want to give up on the pt-table-checksum which requires statement based replication\r\n<\/pre>\n
bsql0 – my.cnf:<\/p>\n
\r\nserver-id               = 11\r\nlog_bin                 = \/var\/log\/mysql\/mysql-bin.log\r\nauto_increment_increment=3\r\nauto_increment_offset=4\r\nbind-address=*\r\nbinlog_format=STATEMENT\r\n<\/pre>\n
csql0 – my.cnf:<\/p>\n
\r\nserver-id               = 12\r\nlog_bin                 = \/var\/log\/mysql\/mysql-bin.log\r\nauto_increment_increment=3\r\nauto_increment_offset=5\r\nbind-address=*\r\nbinlog_format=STATEMENT\r\n<\/pre>\n
to connect bsql0, csql0, asql0b to the master i had to restore on them master’s backup that included binlog position and issue on then:<\/p>\n
find bsql0 master position – on bsql0:<\/p>\n
\r\nshow master status;\r\n<\/pre>\n
let asql0 connect to bsql0:<\/p>\n
\r\nchange master to master_host='bsql0',master_user='repl',master_password='repl',master_log_file='mysql-bin.000003',master_log_pos=154 for channel 'btoa';\r\n<\/pre>\n
similarly for csql0 – find out its current master position and let asql0 connect o it:<\/p>\n
\r\nchange master to master_host='csql0',master_user='repl',master_password='repl',master_log_file='mysql-bin.000003',master_log_pos=154 for channel 'ctoa';\r\n<\/pre>\n
then let bsql0 to replicate from asql0; bing log position taken from asql0 backup that was earlier restored on bsql0 – on bsql0:<\/p>\n
\r\nchange master to master_host='asql0',master_user='repl',master_password='repl',master_log_file='mysql-bin.000003',master_log_pos=154 ;\r\n<\/pre>\n
and on csql0 – similarly:<\/p>\n
\r\nchange master to master_host='asql0',master_user='repl',master_password='repl',master_log_file='mysql-bin.000003',master_log_pos=154 ;\r\n<\/pre>\n
if you’ve reached that far you’ve probably already realized that asql0 is a single point of failure. during the outage of asql0 transactions from bsql0 will not reach csql0 and vice-versa. we can tolerate it for a few minutes but not hours. our solution:<\/p>\n