Cauwenbergh.be weblog

« | Home | »

Mysql Master Master Replication on CentOS 5

By Robin | juni 22, 2010

This tutorial describes how to set up a MySQL master-master replication. This is usefull is one of the servers drops down, the other can take over without any data loss, it will also be used as slave for the live one.

Step 1:
Install mysql on master 1 (slave 2) and slave 1 (master2).
#yum install mysql-server mysql

Master 1 (slave 2) ip: 10.0.0.1
Slave 1 (master 2) ip: 10.0.0.2

Step 2:
On Master 1, change /etc/my.cnf as follow:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
old_passwords=1
user=mysql
log-bin=mysql-bin
server-id=1

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Step 3:
Logon to mysql on master 1 (in our case the server with ip 10.0.0.1):
mysql> grant replication slave on *.* to ‘repl‘@’%’ identified by ‘WhatYouwant‘;

After this restart the mysql on master 1

We used the % but you can set here also 10.0.0.2 for a more secure server!

Step 4:
On slave 1 (in our case the server with ip 10.0.0.2) edit /etc/my.cnf as follow:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
old_passwords=1
server-id=2

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Step 5:
Logon to mysql master 1:
mysql> show master status;
+——————+———-+————–+——————+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000007 |     2013 |              |                  |
+——————+———-+————–+——————+
1 row in set (0.00 sec)

Step 6:

Logon to mysql on slave 1 and use the infomation from step 5:
mysql>CHANGE MASTER TO MASTER_HOST=’10.0.0.1′, MASTER_USER=’repl‘, MASTER_PASSWORD=’WhatYouWant‘, MASTER_LOG_FILE=’mysql-bin.000007‘, MASTER_LOG_POS=2013;
mysql>start slave;
mysql>show slave status\G

*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.1
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000007
Read_Master_Log_Pos: 2013
Relay_Log_File: mysqld-relay-bin.000003
Relay_Log_Pos: 2150
Relay_Master_Log_File: mysql-bin.000007
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 2013
Relay_Log_Space: 2150
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
1 row in set (0.00 sec)


Step 7:
Now we going to make slave 1 -> master 2 and master 1 -> slave 2 so the master-master replication will work!
On slave 1 (ip 10.0.0.2) we add a rule to /etc/my.cnf:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
old_passwords=1
server-id=2
log-bin

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Step 8:
On slave 1 (ip 10.0.0.2 in our example) create the replication in mysql:
mysql> grant replication slave on *.* to ‘repl’@’%’ identified by ‘WhatYouWant‘;

You can change the % to be more safe in 10.0.0.1!

mysql> show master status;
+——————-+———-+————–+——————+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————-+———-+————–+——————+
| mysqld-bin.000005 |      314 |              |                  |
+——————-+———-+————–+——————+
1 row in set (0.00 sec)

Step 9:
On master 1 (ip 10.0.0.1 in our example) add the user to mysql (use information from step 8):

mysql>CHANGE MASTER TO MASTER_HOST=’10.0.0.2′, MASTER_USER=’repl’, MASTER_PASSWORD=’WhatYouWant‘, MASTER_LOG_FILE=’mysqld-bin.000005‘, MASTER_LOG_POS=314;

Step 10:
Restart both mysql services on master 1 (slave 2) and slave 1 (master 2).

On master 1:
mysql> start slave;

On slave 1:
mysql> show master status;

On master 1:
mysql> show slave status\G

So now you can test on both servers if your config works! Hope you enjoy the tutorial and have a safe configuration! If you want safe hosting with this solution you can also take hosting by Netdistri bvba

Topics: Internet - SEO - Money, Linux | No Comments »

Comments