MySQL Master-Master Replication

Create MySQL user

CREATE USER 'droaming'@'localhost' IDENTIFIED BY 'DroamG.123';
CREATE USER 'droaming'@'%' IDENTIFIED BY 'DroamG.123';

Drop Mysql User

DROP USER 'droaming'@'localhost';
DROP USER 'droaming'@'%';

Create Mysql Database and grant permission

CREATE DATABASE droaming;
GRANT ALL ON droaming.* TO 'droaming'@'localhost';

View MySQL users

SELECT User, Host, Password FROM mysql.user;

MySQL Master-Master Replication

SERVER 01

[mysqld]
datadir=/var/lib/mysql
#socket=/var/lib/mysql/mysql.sock
socket=/tmp/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log_bin=mysql-bin
binlog_do_db=gts
binlog_do_db=fw_store
server_id=1
relay_log = mysql-relay-bin

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

SERVER 02

[mysqld]
datadir=/var/lib/mysql
#socket=/var/lib/mysql/mysql.sock
socket=/tmp/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log_bin=mysql-bin
binlog_do_db=gts
binlog_do_db=fw_store
server_id=2
relay_log = mysql-relay-bin

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

Set replication

CREATE USER 'replicator'@'%' IDENTIFIED BY 'OmoBio@123#$';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%' IDENTIFIED BY 'OmoBio@123#$';


SHOW MASTER STATUS;


mysql> show master status; 
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      120 | droaming     |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

log into mysql as root

STOP SLAVE; 
CHANGE MASTER TO MASTER_HOST = '10.106.11.110', MASTER_USER = 'replicator', MASTER_PASSWORD = 'OmoBio@123#$', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 120;

CHANGE MASTER TO MASTER_HOST = '10.106.11.109', MASTER_USER = 'replicator', MASTER_PASSWORD = 'OmoBio@123#$', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 120; 

START SLAVE; 
SHOW MASTER STATUS;

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 |      154 | droaming     |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

STOP SLAVE; 
CHANGE MASTER TO MASTER_HOST = '10.12.97.7', MASTER_USER = 'replicator', MASTER_PASSWORD = 'Omo@Rep#123', MASTER_LOG_FILE = 'mysql-bin.000005', MASTER_LOG_POS = 154; 
START SLAVE;