Menu
Menu

mysql master-master replication

Posted in linux, server, srvbox on 2013/10/24
Tags: , , , , , , ,

This is a short note on howto quickly deploy a master-master mysql replication on 2 servers.

After installing mysql on both of them, the important changes to configurations (/etc/mysql/my.cnf) are:


bind-address = 0.0.0.0 # required for replication over vpn, make sure you block 3306 port on external IP in your firewall
# it could also stay 127.0.0.1 with appriopriate ssh tunnels.
skip_name_resolve
server-id = 1
auto-increment-increment = 2
auto-increment-offset = 1
log_bin = /var/log/mysql/mysql-bin.log
relay_log = /var/log/mysql/mysql-relay-bin.log
expire_logs_days = 10
max_binlog_size = 100M
log_slave_updates = 1

On the second server it only differs by:


server-id = 2
auto-increment-increment = 2
auto-increment-offset = 2

Next step is to grant access for replication on both servers respectively:


# on 1st master
mysql(root)>grant replication slave on *.* TO 'replication_user'@'master2' identified by 'replication_password';
# on 2nd master
mysql(root)>grant replication slave on *.* TO 'replication_user'@'server1' identified by 'replication_password';

CAUTION! When you pick up a replication_password be aware of MySQL Bug #43439
and make sure your password is shorter than 32 characters.

Configure and enable slave functionality on both servers:


# get the master status on the 1st server:
mysql(root)>show master status;
# use the resulted values on the 2nd master:
mysql(root)>change master to MASTER_HOST = 'server1', MASTER_USER = 'replication_user', MASTER_PASSWORD = 'replication_password', MASTER_LOG_FILE = 'mysql-bin.000008', MASTER_LOG_POS = 123;
# start the slave on server2
mysql(root)>start slave;

# check status of the slave
mysql(root)>show slave status \G

# do the same replacing the order of the servers

# get the master status on the 2st server:
show master status;
# use the resulted values on the 1nd master:
mysql(root)>change master to MASTER_HOST = 'server2', MASTER_USER = 'replication_user', MASTER_PASSWORD = 'replication_password', MASTER_LOG_FILE = 'mysql-bin.000005', MASTER_LOG_POS = 237;

# start the slave on server1
mysql(root)>start slave;

# check status of the slave
mysql(root)>show slave status \G

Test it. Create database on one of the servers, and check if you can see it on the other. Do some insert/deletes/updates. Enjoy the master-master replication.

Comments are closed.