MySQL master master replication

PDF

MySQL master-master replication (or multi master replication) offers a high availability failover solution. Other then in a master-slave replication setup you can write to all member master servers. Every master server is also a slave at the same time. Although all master server can write I would suggest to use only one active master server for writes and the other master be a passive master for reads and become active if the other server fails. With some monitoring scripts the passive master can become active within a fraction of a second.

Now let's setup a master-master replication. First on both servers we need to create an account granted ‘REPLICATION SLAVE’. As the username and password is stored in a plain-text file it is advisable to create a user that only has privilages for the replication:

GRANT REPLICATION SLAVE ON *.* TO 'user'@'host' IDENTIFIED BY 'pass';
FLUSH PRIVILEGES;
Next we have to edit /etc/my.cnf on the first master:
[mysqld]
server-id = 1
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 1
master-connect-retry = 60
log-bin = /var/log/mysql/mysql-bin
log-bin-index = /var/log/mysql/bin-log.index
log-error = /var/log/mysql/error.log
relay-log = /var/log/mysql/relay.log
relay-log-index = /var/log/mysql/relay-log.index
expire_log_days = 14
This same file /etc/my.cnf we have to edit on the second master with just some slight diffences:
[mysqld]
server-id = 2
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 2
master-connect-retry = 60
log-bin = /var/log/mysql/mysql-bin
log-bin-index = /var/log/mysql/bin-log.index
log-error = /var/log/mysql/error.log
relay-log = /var/log/mysql/relay.log
relay-log-index = /var/log/mysql/relay-log.index
expire_log_days = 14
read-only = 1
If it is a clean setup without any existing databases we can start the MySQL processes on both servers. On the active master we start the MySql daemon:
/ect/init.d/mysql start
and login to the mysql console
mysql --user=root -p
and will give the commands:
CHANGE MASTER TO MASTER_HOST='master2.mydomain.com',
MASTER_USER='user',
MASTER_PASSWORD='pass',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=0;
start slave;
On the passive master we will start the MySql daemon:
/ect/init.d/mysql start
and login to the mysql console
mysql --user=root -p
and will give the commands:
CHANGE MASTER TO MASTER_HOST='master1.mydomain.com',
MASTER_USER='user',
MASTER_PASSWORD='pass',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=0;
start slave;
 

Please login first before adding a comment.

Search






You are here: Home Howtos and FAQs MySql MySQL master master replication