MySQL master-slave and master-master replication. Step by step configuration instructions.

If you are looking for the options to csale your MySQL installation you may be also interested in MySQL partitioning and subpartitioning. It may improve each node speed and capacity parameters.

One may say that there are a lot of MySQL replication manuals, but latest versions of MySQL server have changed the way how configuration should be applied. Most of the manuals do not reflect these changes. I want to describe some other aspects of configurations also. As far as there are a lot of good manuals about replication, I think there is no need to dove into details what is the replication. Just want to mention that this technique is usually used for load balancing on database servers. If you have a lot of read requests (most common for web applications) master-slave replication should suit your needs well. In this case you will do write transactions on master host and read requests on slave hosts, because data is populated from master to slave much faster than from slaves to master and to other slaves.

mysql master-slave replication

But sometimes you might have more write requests or may have other (application related) reasons to start another type of replication. You can see it on the next fugure and that is so called  master-master replication.

mysql master-master replication

In this article I will describe simple master-slave architecture with 2 hosts and simple master-master replication with the same 2 hosts. Our final goal is to configure master-master replication, what includes several sub-steps, so lets  start. Sure you should configure network services on both systems. For example:

Master 1/Slave 2 ip: 192.168.16.4
Master 2/Slave 1 ip : 192.168.16.5

Iptables rules for MySQL replication

It will be good practice to allow connections only from other nodes envolved into the replication and deny from other. By the way this will work good for some other services that are allowed to communicate nly with the known hosts. You can define port range like 1025:3306 (I am going to write more about iptables soon, so follow my blog on twitter).

iptables -A INPUT -p tcp -s 192.168.16.4 --sport 3306 -d 192.168.16.5 --dport 3306 -m state --state NEW,ESTABLISHED -j ACCEPT
iptables -A OUTPUT -p tcp -s 192.168.16.5 --sport 3306 -d 192.168.16.4 --dport 3306 -m state --state ESTABLISHED -j ACCEPT

MySQL master-slave replication

Basically master-master replication consists of two master-slave replications. Now we will configure master-slave replication from the first server to the second one.

Create relication user on Master 1:

mysql> grant replication slave on *.* to 'replication'@192.168.16.5 identified by 'slave';

And start master:

mysql> start master;

Master 1 changes to /etc/my.cnf:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
old_passwords=1

log-bin
binlog-do-db=<database name>  # input the database which should be replicated
binlog-ignore-db=mysql            # input the database that should be ignored for replication
binlog-ignore-db=test

server-id=1

[mysql.server]
user=mysql
basedir=/var/lib


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

Slave 1 changes to /etc/my.cnf:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
old_passwords=1

server-id=2

[mysql.server]
user=mysql
basedir=/var/lib

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

Important! Pay attention that you should not configure master-host, master-user, master-password, master-port via my.cnf on slave server now.

On Master 1:

mysql> show master status;
+------------------------+----------+--------------+------------------+
| File                   | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------------+----------+--------------+------------------+
|mysqld-bin.000012       |      106 | adam         |                  |
+------------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

On Slave 1:

mysql> CHANGE MASTER TO MASTER_HOST='192.168.16.4', MASTER_USER='replication', MASTER_PASSWORD='password', MASTER_PORT=3306,MASTER_LOG_FILE='mysqld-bin.000012', MASTER_LOG_POS=106, MASTER_CONNECT_RETRY=10;

Attention! This will configure slave and server will remember settings, so this replaces my.cnf settings in latest versions of MySQL server.

Start slave on Slave 1:

mysql> start slave;
mysql> show slave statusG;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.16.5
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mysqld-bin.000012
          Read_Master_Log_Pos: 1368129
               Relay_Log_File: mysqld-relay-bin.000005
                Relay_Log_Pos: 605530
        Relay_Master_Log_File: mysqld-bin.000012
             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: 1368129
              Relay_Log_Space: 1367083
              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
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
1 row in set (0.02 sec)

 

Above highlighted rows must be indicate related log files and  Slave_IO_Running and   Slave_SQL_Running: must be to YES.

MySQL master-master replication

Master-master replication is actually two master-slave replications. This allows to make read and write transactions on both servers, as data propagation from master to slave goes very fast oposit to data propagation from slave to master which requires much more time. So, to create master-master replication we should now configure Master 2 - Slave 2 replication.

Create a replication slave account on Master 2 for Master 1/Slave 2:

mysql> grant replication slave on *.* to 'replication'@192.168.16.4 identified by 'slave';

And start master:

mysql> start master;

Master 2 changes to /etc/my.cnf:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
old_passwords=1

log-bin
binlog-do-db=<database name>  # input the database which should be replicated
binlog-ignore-db=mysql            # input the database that should be ignored for replication
binlog-ignore-db=test

server-id=2

[mysql.server]
user=mysql
basedir=/var/lib


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

Slave 2 / Master 1 changes to /etc/my.cnf:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
old_passwords=1

log-bin
binlog-do-db=<database name>  # input the database which should be replicated
binlog-ignore-db=mysql            # input the database that should be ignored for replication
binlog-ignore-db=test

server-id=1

[mysql.server]
user=mysql
basedir=/var/lib

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

Important! And again you should not configure master-host, master-user, master-password, master-port via my.cnf on slave server now.

On Master 2:

mysql> show master status;
+------------------------+----------+--------------+------------------+
| File                   | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------------+----------+--------------+------------------+
|mysqld-bin.000012       |      106 | adam         |                  |
+------------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

On Slave 2:

mysql> CHANGE MASTER TO MASTER_HOST='192.168.16.5', MASTER_USER='replication', MASTER_PASSWORD='password', MASTER_PORT=3306,MASTER_LOG_FILE='mysqld-bin.000012', MASTER_LOG_POS=106, MASTER_CONNECT_RETRY=10;

Attention! As I have already mentioned in the previous section this will configure slave and server will remember settings, so this replaces my.cnf settings in latest versions of MySQL server.

MySQL master-master replication and autoincrement indexes

If you are using master-slave replication, than most likely you will design your application the way to write to master and read from slave or several slaves. But when you are using master-master replication you are going to read and write to any of master servers. So, in this case the problem with autoincremental indexes will raise. When both servers will have to add a record (different one each server simultaneously) to the same table. Each one will assign them the same index and will try to replicate to the salve, this will create a collision. Simple trick will allow to avoid such collisions on MySQL server.

On the Master 1/Slave 2 add to /etc/my.cnf:

auto_increment_increment= 2
auto_increment_offset   = 1

On the Master 2/Slave 1 add to /etc/my.cnf:

auto_increment_increment= 2
auto_increment_offset   = 2

In my next articles I am going to complete describibg different approaches of database load balancing and some ways to reduce database server load. So, follow my blog on twitter.

 
Posted by:
Enjoyed this post? Share and Leave a comment below, thanks! :)