Contact Support : +64 220 256 070

Aimvm
Blog

How to configure Mysql replication.

Posted on: August 16, 2015

This tutorial will teach you on how to setup Mysql replication between servers.

In this tutorial we assume two servers A and B. First we will configure server A and based on that we will configure server B.

 

Run the following on Mysql of server A.

grant replication slave on *.* TO [replication_username]@’[private IP of serverB]’ identified by '[some password]';

You have created the replication user. Edit the /etc/my.cnf file and add the following entries and restart mysql:

bind-address = 0.0.0.0
server-id = 1
log-bin = mysql-bin
binlog-ignore-db = “mysql”

On server B do the above two steps for that of serverA.

Now check the Master status of server A. 

mysql> SHOW MASTER STATUS;
+------------------+--------------------------+------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+--------------------------+------------------+
| mysql-bin.000010 |        10 |              | mysql            |
+------------------+--------------------------+------------------+
1 row in set (0.00 sec)

Now Go to server B and configure slave based on the above values.

mysql> SLAVE STOP;
mysql> CHANGE MASTER TO MASTER_HOST='[private-IP-of-serverA]',
MASTER_USER='[replication-username]',
MASTER_PASSWORD='[replication-password]',
MASTER_LOG_FILE='[file-listed-on-master-status]',
MASTER_LOG_POS=[log-position-listed–on-master-status];
mysql> START SLAVE;

Now check whether slave is running or not by using following commands.

mysql> SHOW SLAVE STATUS\G

Get the Master status of server B.

mysql> SHOW MASTER STATUS;
+------------------+--------------------------+------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+--------------------------+------------------+
| mysql-bin.000011 |        11 |              | mysql            |
+------------------+--------------------------+------------------+
1 row in set (0.00 sec)

 

Go to server A and configure slave using these values.

mysql> CHANGE MASTER TO MASTER_HOST='[private-IP-of-serverB]',
MASTER_USER='[replication-username]',
MASTER_PASSWORD='[replication-password]',
MASTER_LOG_FILE='[file-listed-on-master-status]',
MASTER_LOG_POS=[log-position-listed–on-master-status];
mysql> START SLAVE;

Finally check whether slave is replicating.

SHOW SLAVE STATUS\G

Search Blog