MySql Master Master Replication

I have been wanting to setup MySQL Master Master configuration, for a project I have been working on lately, for a while and finally got my hands dirty and got it done. Its quite easy and there are tons of tutorials out there describing how to do it. Let me point out that there are many ways of achieving this. This is just my experience and not really a full fledged tutorial.

The main reason for doing this is for HA

Here is what I had
1. Clean database (no previous data to deal with)
2. Two clean instances of MySQL 5.0.27 running on separate machines (Host A and Host B)
3. Host A is running 6.0.6 and Host B is running Fedora 5.0

Step 1 (Setup the system)

1. Make sure you are able to ping Host B from Host A and vice-versa.
2. Make sure that you are able to reach port 3306 on Host B (or whatever port your MySQL is configured to run on) from Host A and vice-versa. If there is a firewall configured then make sure to allow the port MySQL is running on. (ex: MySQL -h hostB -u username -p password)
3. It is recommended to have same versions of MySQL running on both the machines, if possible. Otherwise you’ll have to run some additional steps (not described here) to create this configuration.

Step 2 (Configure Master on Host A)

Edit my.cnf and add the following (On Ubuntu 6.0.6 this file is located at /etc/mysql) and add the following

[mysql]
….

log-bin=/var/log/mysql/mysql-bin.log
binlog-do-db= # The database you want to replicate
replicate-do-db= # The database you want to replicate
binlog-ignore-db=mysql # Ignore db from replication
replicate-ignore-db=mysql # Ignore db from replication

server-id=1 # This is very important
…

Step 3 (Create Slave Account on Host A)

Create a replication slave account for Host B on Host A.
mysql> grant replication slave on *.* to ‘repusername’@host_B_IP identified by ‘slavepass’;

This allows slave process on Host B to connect to the master on Host A to initiate the replication process.

Note: You can replace “host_B_IP” with your domain name or your subdomain like “example.org” or “subdomain.example.org” if the IP of host B is not static.

Step 4 (Create Slave on Host B)

Edit my.cnf and add the following (On Fedora 5.0 this file is located at /etc) and add the following

[mysqld]
…

server-id=2 # This is very important

master-host = host_A_IP # The IP address of the master host
master-user = repusername # The username to use to connect to master
master-password = slavepass # The password to use to connect to master
master-port = 3306 # The port where the master is running, default is 3306

…

Now we have a master slave configuration master being Host A and slave being Host B.

Step 5 (Verify Master Slave Configuration)

Now lets make sure that the master slave configuration really works. To do that restart both the database instances.

[hostA@ ubuntu]> /etc/init.d/mysql restart
[hostB@ fedora]> /etc/init.d/mysqld restart

On host B run the following command

mysql> start slave;
mysql> show slave status\G;

*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: HOST A IP
Master_User: repusername
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: hostAmysql01-bin.000009
Read_Master_Log_Pos: 4
Relay_Log_File: hostAmysql02-relay-bin.000015
Relay_Log_Pos: 3630
Relay_Master_Log_File: hostAmysql01-bin.000009
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: 4
Relay_Log_Space: 3630
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: 1519187

1 row in set (0.00 sec)

Make sure that the text highlighted shows “Yes”. If not its time to debug. The first place to start will be /var/log/mysqld.log or /var/log/mysql/mysql.log (depending on the settings). See if something is obvious. Some of the obvious errors are “Access denied”, “unable to reach host”. These error messages are exactly not as mentioned here but if you google the error codes you’ll find it quickly.

On host A run the following command

mysql> show master status;
+————————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+————————+———-+————–+——————+
|hostAmysql01-bin.000008 | 98 | replicationdb | |
+————————+———-+————–+——————+
1 row in set (0.00 sec)

Step 6 (Create Master on Host B)

Edit my.cnf and add the following (On Ubuntu 6.0.6 this file is located at /etc/mysql) and add the following

[mysqld]
…

server-id=2 # This is very important

master-host = host_A_IP # The IP address of the master host
master-user = repusername # The username to use to connect to master
master-password = slavepass # The password to use to connect to master
master-port = 3306 # The port where the master is running, default is 3306

# The following makes Host B act as a master
log-bin=/var/log/mysql/mysql-bin.log
binlog-do-db= # The database you want to replicate
replicate-do-db= # The database you want to replicate
binlog-ignore-db=mysql # Ignore db from replication
replicate-ignore-db=mysql # Ignore db from replication

…

Step 7 (Create Slave Account on Host B)

Create a replication slave account for Host A on Host B.
mysql> grant replication slave on *.* to ‘repusername’@host_A_IP identified by ‘slavepass’;

Step 8 (Make Host A the slave)

Edit my.cnf and add the following (On Ubuntu 6.0.6 this file is located at /etc/mysql) and add the following

[mysql]
….

log-bin=/var/log/mysql/mysql-bin.log
binlog-do-db= # The database you want to replicate
replicate-do-db= # The database you want to replicate
binlog-ignore-db=mysql # Ignore db from replication
replicate-ignore-db=mysql # Ignore db from replication

server-id=1 # This is very important

# This makes Host A a slave
master-host = host_B_IP # The IP address of the master host
master-user = repusername # The username to use to connect to master
master-password = slavepass # The password to use to connect to master
master-port = 3306 # The port where the master is running, default is 3306

…

Step 9 (Verify Master Master Configuration)

Now lets make sure that the master master configuration really works. To do that restart both the database instances.

[hostA@ ubuntu]> /etc/init.d/mysql restart
[hostB@ fedora]> /etc/init.d/mysqld restart

On host A run the following command

mysql> start slave;
mysql> show slave status\G;

Make sure that the following is true
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

mysql> show master status;

Repeat the same on Host B.

Step 10 (Confirm replication)

Now run your related DDL / DML queries and see them replicated on the slave. Try from both the instances. Enjoy!!!

Resources

1. MySQL Replication
   

Advertisement