MySql Master To Master Replication Installation On Linux

MySql Master To Master Replication Installation On Linux

Master To Master Replication installation & configuration.

In master to Master replication we need two MySql nodes and both will be master and slave to each other. Both nodes will perform read/write operation. Master to master replications provides high availability in the case of one node goes down then you can use second node as backup.

Here we assume we have two nodes with Centos6/Rhel6 fresh installation & update repository:

Node1:               mysql1.how2install.in (10.0.0.245)

Node2:               mysql2.how2install.in (10.0.0.246)

 Assume we have db1 database & we want to replicate it on both nodes.

Step1:—

Take root access and install mysql-server on both nodes.

mysql1.how2install.in (10.0.0.245)

[root@mysql1 ~]# hostname
mysql1.how2install.in
[root@mysql1 ~]#

[root@mysql1 ~]# yum install mysql-server -y

mysql2.how2install.in (10.0.0.246)

[root@mysql1 ~]# hostname
mysql1.how2install.in
[root@mysql1 ~]#

[root@mysql1 ~]# yum install mysql-server -y

Step2:—

Start mysqls service on both nodes.

mysql1.how2install.in (10.0.0.245)

[root@mysql1 ~]# service mysqld start
Starting mysqld: [ OK ]
[root@mysql1 ~]#

[root@mysql1 ~]# chkconfig mysqld on
[root@mysql1 ~]#

[root@mysql1 ~]# service mysqld status
mysqld (pid 6453) is running...
[root@mysql1 ~]#

mysql1.how2install.in (10.0.0.245)

[root@mysql2 ~]# service mysqld start
Starting mysqld: [ OK ]
[root@mysql2 ~]#

[root@mysql2 ~]# chkconfig mysqld on
[root@mysql2 ~]#

[root@mysql2 ~]# service mysqld status
mysqld (pid 6453) is running...
[root@mysql2 ~]#

Step3:—

 Password must be set on both nodes. If not set net set it by using below command.

mysql1.how2install.in (10.0.0.245) & mysql1.how2install.in (10.0.0.245) .Here am running only mysql1.how2isntall.in but you have to run in on both.

 [root@mysql1 ~]# mysql_secure_installation


Change the root password? [Y/n] Y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
... Success!



Remove anonymous users? [Y/n] Y
... Success!



Disallow root login remotely? [Y/n] n
... skipping.



Remove test database and access to it? [Y/n] Y
- Dropping test database...


Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] Y
... Success!

Cleaning up...



All done! If you've completed all of the above steps, your MySQL
installation should now be secure.

Thanks for using MySQL!


[root@mysql1 ~]#

Step4:—

Create slave user (slave) on both nodes and set password on it. This user has grant access to allow replication process, so we need to set replication right on this user.

Login into MySql server command prompt.

mysql1.how2install.in (10.0.0.245)

[root@mysql1 ~]# mysql -p
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 73
Server version: 5.5.32-log MySQL Community Server (GPL) by Remi


mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'10.0.0.246' IDENTIFIED BY 'password';

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'mysql2.how2intall.in' IDENTIFIED BY 'password';

mysql> FLUSH PRIVILEGES;

mysql>exit;

mysql2.how2install.in (10.0.0.246)

[root@mysql2 ~]# mysql -p
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 73
Server version: 5.5.32-log MySQL Community Server (GPL) by Remi


mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'10.0.0.245' IDENTIFIED BY 'password';

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'mysql1.how2intall.in' IDENTIFIED BY 'password';

mysql> FLUSH PRIVILEGES;

mysql>exit;

 

Step5:–

Make change in /etc/my.cnf  on both nodes it’s the main mysql configurations file. Before make any changes you should take its backup. When using master-master or multi-master replication together with auto-increment columns, you should use the auto_increment_offset and auto_increment_increment parameters on each server to make sure that there are no duplicate values assigned.

E.g

Server         Auto_increment_increment      Auto_increment_offset                  Value

mysq1         2                                                          1                                                                     1,3…

mysq2        2                                                          2                                                                    2,4…

 

mysql1.how2install.in (10.0.0.245)

[root@mysql1 ~]# vim /etc/my.cnf
[mysqld]

server-id = 1
replicate-do-db=db1

replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 1

relay-log = /var/lib/mysql/mysql-relay-bin
relay-log-index = /var/lib/mysql/mysql-relay-bin.index
log-error = /var/lib/mysql/mysql.err
master-info-file = /var/lib/mysql/mysql-master.info
relay-log-info-file = /var/lib/mysql/mysql-relay-log.info
log_bin = /var/lib/mysql/mysql-bin
expire_logs_days = 14
sync_binlog = 1

mysql2.how2install.in (10.0.0.246)

 

[mysqld]

server-id = 2
replicate-do-db=db1


replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 2

relay-log = /var/lib/mysql/mysql-relay-bin
relay-log-index = /var/lib/mysql/mysql-relay-bin.index
log-error = /var/lib/mysql/mysql.err
master-info-file = /var/lib/mysql/mysql-master.info
relay-log-info-file = /var/lib/mysql/mysql-relay-log.info
log_bin = /var/lib/mysql/mysql-bin
expire_logs_days = 14
sync_binlog = 1

Step6:–

Login into mysql & specify master host address & credential on both nodes.

mysql1.how2install.in (10.0.0.245)

mysql>CHANGE MASTER TO MASTER_HOST='10.0.0.246', MASTER_PORT=3306, MASTER_USER='slave', MASTER_PASSWORD='password';

mysql2.how2install.in (10.0.0.246)

mysql>CHANGE MASTER TO MASTER_HOST='10.0.0.245', MASTER_PORT=3306, MASTER_USER='slave', MASTER_PASSWORD='password';

Step7:–           

Restart mysqld service on both nodes. if you got any error during restart mysqld services then don’t next.

mysql1.how2install.in (10.0.0.245) 

[root@mysql1 ~]# service mysqld restart 
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
[root@mysql1 ~]#

mysql2.how2install.in (10.0.0.246)

[root@mysql2 ~]# service mysqld restart
Stopping mysqld:                                            [  OK  ]
Starting mysqld:                                            [  OK  ] 
[root@mysql2 ~]#

 Step8:–

Stop slave service on both nodes.

mysql1.how2install.in (10.0.0.245)

mysql> STOP SLAVE;

 mysql2.how2install.in (10.0.0.246)

mysql> STOP SLAVE;

Step9: 

Check master status on node1(mysql1.how2install.in)

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

mysql>

Step10:

Login  into mysql2.how2install.in and make this server as a slave of mysql1.how2intall.in.

Here you have to mention the MASTER_LOG_FILE  & MASTER_LOG_POS    value which you got from mysql1.how2intall.in (mysql> SHOW MASTER STATUS;)

mysql2.how2install.in (10.0.0.246)

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

mysql>

mysql> CHANGE MASTER TO MASTER_HOST='10.0.0.245', MASTER_USER='slave', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000014', MASTER_LOG_POS=107;

mysql>START SLAVE;

mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.245
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000014
Read_Master_Log_Pos: 107
Relay_Log_File: mysql-relay-bin.000035
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000014
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: db1
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: 107
Relay_Log_Space: 555
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.00 sec)

mysql>exit;

 

Slave_IO_Running and Slave_SQL_Running  must be Yes in the output.

Step11:

Login login into mysql1.how2install.in and make this server as a slave of mysql2.how2intall.in.

Here you have to mention the MASTER_LOG_FILE  & MASTER_LOG_POS     value which you got from mysql2.how2intall.in (mysql> SHOW MASTER STATUS;)

mysql> CHANGE MASTER TO MASTER_HOST='10.0.0.246', MASTER_USER='slave', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000016', MASTER_LOG_POS=862;

mysql>START SLAVE;

mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.246
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000016
Read_Master_Log_Pos: 862
Relay_Log_File: mysql-relay-bin.000038
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000016
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: db1
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: 862
Relay_Log_Space: 409
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: 2
1 row in set (0.00 sec)

mysql>

 

Slave_IO_Running and Slave_SQL_Running  must be Yes in the output

Step12:

We have replicate db1 database & right now its now available in both nodes. We for testing we create it on mysql1.howinstall.in node and it should be automatically create on mysql2.how2isntall.in, If the replication working fine. So let’s check it.

mysql1.how2install.in (10.0.0.245)

mysql> create database db1;
Query OK, 1 row affected (0.01 sec)


mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| mysql |
| performance_schema |
+--------------------+
8 rows in set (0.00 sec)

mysql>


mysql2.how2install.in (10.0.0.246)

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| mysql |
| performance_schema |
+--------------------+
8 rows in set (0.00 sec)

mysql>

 

Here you can see the db1 automatically create on node2 so our db1 database replication working fine.

{#moods_dlg.WellDone}

 

Leave a Reply

Time limit is exhausted. Please reload the CAPTCHA.