9/10/2010

Mysql master-master replication

Today I’m going to write a short manual how to create failover mysql cluster.
What we need:

Server1 = master 1/slave2
Server2 = master2/slave2
Internal or external IPs (in this example I will use server1 _ip and server2_ip)
Replication user = replicau


Step-by-step how to:
Step 1.
On server 1 in my.cnf we need to add following lines

[mysqld]
#master config
Server-id =1
Log-bin = mysql-bin
binlog-do-db = test_db
… #we can add as many DB as we need to replicate
#slave config
replicate-do-db = test_db
… #we can add as many DB as we need to replicate
master-host = server2_ip
master-user= replicau
master-password =password_of_user_replicau
master-port = 3306


same lines we should add in my.cnf on server2, the only thing we should change is master-host parameter to use server1_ip instead of server2_ip and change server-id parameter
Step 2
Restart mysql servers
Step 3
On each server we need to create replication user:
Server1

mysql>GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replicau'@'server2_ip' IDENTIFIED BY 'password';
server2

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replicau'@'server1_ip' IDENTIFIED BY 'password';

Step 4
Add server2 as slave to server1
- we need to set read_only parameter as ON on server1 (master 1)

mysql> SET GLOBAL read_only = ON;
mysql> show master status;
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000006 | 7984 | | |
+------------------+----------+--------------+------------------+
1 row in set (0,00 sec)
on server2

mysql>slave stop;
mysql> CHANGE MASTER TO MASTER_HOST = "server2_ip", MASTER_USER = "replicau", MASTER_PASSWORD = "password_of_user_replication", MASTER_LOG_FILE = "mysql-bin.000006", MASTER_LOG_POS = 7984;
mysql> slave start;


on server1

mysql> SET GLOBAL read_only = OFF;

on server2 check connection status

mysql> show slave status;

if we do not receive any errors, everything is perfect 

Step 5
Do Step 4 for connecting server 1 as slave to server2.

Step 6
Check if everything is fine, if no try from step 1 

Step 7
It’s time to have a cup of coffee/tea of a bottle of beer

No comments: