Follow us on Social Media:

MySQL Database Administrator

Boost Your Quick Knowledge in MySQL DBA Work, Concepts, Tips and Trick

Sunday, 21 January 2018

Replication - Master to Master

MASTER TO MASTER REPLICATION PROCESS

We are considering to configure Master-Master replication between two server (Server A and B).

In Both serevr A and Server B..

mysql> STOP SLAVE;
mysql> RESET SLAVE;

In Server A

In My.ini
-------
[mysqld]

log-bin=mysql-bin
server-id=1
binlog-ignore-db=[database name]
replicate_ignore_table=[database name].[table name]
auto_increment_increment= 2
auto_increment_offset   = 1  ***(This value should be same as server id)
sync_binlog=1
binlog_format=row  ***(Considering row based replication)
log_bin_trust_function_creators=1

---------------------------------------------------------------------------------------------------------------

In Server B

In My.ini
-------
[mysqld]
log-bin=mysql-bin
server-id=2
binlog-ignore-db=[database name]
replicate_ignore_table=[database name].[table name]
auto_increment_increment= 2
auto_increment_offset   = 2  ***(This value should be same as server id)
sync_binlog=1
binlog_format=row  ***(Considering row based replication)
log_bin_trust_function_creators=1

---------------------------------------------------------------------------------------------------------------

Create replication User in Server A and Server B

mysql> CREATE USER 'replicator1'@’%' IDENTIFIED BY 'repl';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@’%' IDENTIFIED BY 'repl';
mysql> FLUSH  PRIVILEGES;

mysql> SHOW MASTER STATUS;

---------------------------------------------------------------------------------------------------------------

In Server A

Check SHOW MASTER STATUS Server B

+------------------+----------+--------------+------------------+---------------
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB          |
+------------------+----------+--------------+------------------+---------------
| mysql-bin.000001 |      107 | example  | test, informatio            |
+------------------+----------+--------------+------------------+------- -------
1 row in set (0.00 sec)

mysql> STOP SLAVE;
mysql> CHANGE MASTER TO
    -> MASTER_HOST='172.29.56.202',
    -> MASTER_USER='replicator1',
    -> MASTER_PASSWORD='replicator1',
    -> MASTER_LOG_FILE='mysql-bin.000001',
    -> MASTER_LOG_POS=107;
mysql>
mysql>
query executed.
mysql> START SLAVE;

mysql> SHOW SLAVE STATUS \G;


---------------------------------------------------------------------------------------------------------------

In Server B

Check SHOW MASTER STATUS Server a

+------------------+----------+--------------+------------------+---------------
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB          |
+------------------+----------+--------------+------------------+---------------
| mysql-bin.000001 |      107 | example      | test, informatio        |
+------------------+----------+--------------+------------------+----------------
1 row in set (0.00 sec)

mysql> STOP SLAVE;
mysql> CHANGE MASTER TO
    -> MASTER_HOST='172.29.56.201',
    -> MASTER_USER='replicator1',
    -> MASTER_PASSWORD='replicator1',
    -> MASTER_LOG_FILE='mysql-bin.000001',
    -> MASTER_LOG_POS=107;

query executed.
mysql> START SLAVE;

mysql> SHOW SLAVE STATUS \G;


Now replication has been configured on both servers.

Now you can check if it's working properly by running "SHOW SLAVE STATUS \G" on both server .
?              Slave_IO_Running: Yes

?             Slave_SQL_Running: Yes


For any further clarification or facing issue , please let us know in comment or my Mail.


No comments:

Please Like and Subscribe us ....

Please visit the website on daily basis , Your New articles will publish at any moment. Please Subscribe us for getting Latest Updates...