Mysql Multi-Master
My notes on what I did to create a mysql multi-master setup w/ newly-install mysql servers.
On both servers:
GRANT REPLICATION SLAVE ON *.* TO slave_user IDENTIFIED BY ‘********’; flush privileges;
vim my.cnf and make the following changes:
bind-address = 0.0.0.0 # we have to allow outside connections, use firewall to allow only the other server’s connections
Now, before proceeding, let’s check that each master can connect as the slave to the other master.
on both servers:
# /etc/init.d/mysql restart
# mysql -u slave_user -p -h XX.XX.XX.XX
where XX.XX.XX.XX is the *other* mysql server’s IP.
If all goes well, proceed. Otherwise debug your connectivity issues (usually iptables or switch firewall).
vim my.cnf again and add these under the [mysqld] section:
server-id = 1 # each server gets it’s own id
log-bin = /var/lib/mysql/bin.log
log-slave-updates
log-bin-index = /var/lib/mysql/bin-log.index
relay-log = /var/lib/mysql/relay.log
relay-log-info-file = /var/lib/mysql/relay-log.info
relay-log-index = /var/lib/mysql/relay-log.index
auto_increment_increment = 10 # this is the maximum number of servers you think you’ll ever have – set it to low and you’ll pay the price when you need more
auto_increment_offset = 1 # the first server is 1, the next server is 2, etc
master-host = XX.XX.XX.XX # the ip of the other master
master-user = slave_user
master-password = ********** # password used above
slave_compressed_protocol=1 # might as well use compression on the transmission – esp if the servers are on separate networks
now, restart both servers. Once they are running use ‘show slave status;’ to see if they are running correctly, if not, try ‘stop slave; reset slave; start slave;’ on both to see if that helps them figure themselves out.
Once you see both slave_io_running and slave_sql_running are both yes, try created databases and dropping databases on both boxes and make sure the other sees the related change.
Leave a Comment