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

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.

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.

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 ontwitter).

  1. 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
  2. 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:

  1. mysql> grant replication slave on *.* to ‘replication’@192.168.16.5 identified by ‘slave’;

And start master:

  1. mysql> start master;

Master 1 changes to /etc/my.cnf:

  1. [mysqld]
  2. datadir=/var/lib/mysql
  3. socket=/var/lib/mysql/mysql.sock
  4. old_passwords=1
  5. log-bin
  6. binlog-do-db=<database name>  # input the database which should be replicated
  7. binlog-ignore-db=mysql            # input the database that should be ignored for replication
  8. binlog-ignore-db=test
  9. server-id=1
  10. [mysql.server]
  11. user=mysql
  12. basedir=/var/lib
  13. [mysqld_safe]
  14. err-log=/var/log/mysqld.log
  15. pid-file=/var/run/mysqld/mysqld.pid

Slave 1 changes to /etc/my.cnf:

  1. [mysqld]
  2. datadir=/var/lib/mysql
  3. socket=/var/lib/mysql/mysql.sock
  4. old_passwords=1
  5. server-id=2
  6. [mysql.server]
  7. user=mysql
  8. basedir=/var/lib
  9. [mysqld_safe]
  10. err-log=/var/log/mysqld.log
  11. 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:

  1. mysql> show master status;
  2. +————————+———-+————–+——————+
  3. | File                   | Position | Binlog_Do_DB | Binlog_Ignore_DB |
  4. +————————+———-+————–+——————+
  5. |mysqld-bin.000012 |      106 | adam         |                  |
  6. +————————+———-+————–+——————+
  7. 1 row in set (0.00 sec)

On Slave 1:

  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:

  1. mysql> start slave;
  2. mysql> show slave statusG;
  3. *************************** 1. row ***************************
  4.                Slave_IO_State: Waiting for master to send event
  5.                   Master_Host: 192.168.16.5
  6.                   Master_User: slave
  7.                   Master_Port: 3306
  8.                 Connect_Retry: 10
  9.               Master_Log_File: mysqld-bin.000012
  10.           Read_Master_Log_Pos: 1368129
  11.                Relay_Log_File: mysqld-relay-bin.000005
  12.                 Relay_Log_Pos: 605530
  13.         Relay_Master_Log_File: mysqld-bin.000012
  14.              Slave_IO_Running: Yes
  15.             Slave_SQL_Running: Yes
  16.               Replicate_Do_DB:
  17.           Replicate_Ignore_DB:
  18.            Replicate_Do_Table:
  19.        Replicate_Ignore_Table:
  20.       Replicate_Wild_Do_Table:
  21.   Replicate_Wild_Ignore_Table:
  22.                    Last_Errno: 0
  23.                    Last_Error:
  24.                  Skip_Counter: 0
  25.           Exec_Master_Log_Pos: 1368129
  26.               Relay_Log_Space: 1367083
  27.               Until_Condition: None
  28.                Until_Log_File:
  29.                 Until_Log_Pos: 0
  30.            Master_SSL_Allowed: No
  31.            Master_SSL_CA_File:
  32.            Master_SSL_CA_Path:
  33.               Master_SSL_Cert:
  34.             Master_SSL_Cipher:
  35.                Master_SSL_Key:
  36.         Seconds_Behind_Master: 0
  37. Master_SSL_Verify_Server_Cert: No
  38.                 Last_IO_Errno: 0
  39.                 Last_IO_Error:
  40.                Last_SQL_Errno: 0
  41.                Last_SQL_Error:
  42.   Replicate_Ignore_Server_Ids:
  43.              Master_Server_Id: 1
  44. 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:

  1. mysql> grant replication slave on *.* to ‘replication’@192.168.16.4 identified by ‘slave’;

And start master:

  1. mysql> start master;

Master 2 changes to /etc/my.cnf:

  1. [mysqld]
  2. datadir=/var/lib/mysql
  3. socket=/var/lib/mysql/mysql.sock
  4. old_passwords=1
  5. log-bin
  6. binlog-do-db=<database name>  # input the database which should be replicated
  7. binlog-ignore-db=mysql            # input the database that should be ignored for replication
  8. binlog-ignore-db=test
  9. server-id=2
  10. [mysql.server]
  11. user=mysql
  12. basedir=/var/lib
  13. [mysqld_safe]
  14. err-log=/var/log/mysqld.log
  15. pid-file=/var/run/mysqld/mysqld.pid

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

  1. [mysqld]
  2. datadir=/var/lib/mysql
  3. socket=/var/lib/mysql/mysql.sock
  4. old_passwords=1
  5. log-bin
  6. binlog-do-db=<database name>  # input the database which should be replicated
  7. binlog-ignore-db=mysql            # input the database that should be ignored for replication
  8. binlog-ignore-db=test
  9. server-id=1
  10. [mysql.server]
  11. user=mysql
  12. basedir=/var/lib
  13. [mysqld_safe]
  14. err-log=/var/log/mysqld.log
  15. 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:

  1. mysql> show master status;
  2. +————————+———-+————–+——————+
  3. | File                  | Position | Binlog_Do_DB | Binlog_Ignore_DB |
  4. +————————+———-+————–+——————+
  5. |mysqld-bin.000012 |      106    | adam            |                  |
  6. +————————+———-+————–+——————+
  7. 1 row in set (0.00 sec)

On Slave 2:

  1. 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:

  1. auto_increment_increment= 2
  2. auto_increment_offset   = 1

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

  1. auto_increment_increment= 2
  2. auto_increment_offset   = 2
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s