db

[root@clusterserver2 ~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.1.20 clusterserver1.rmohan.com clusterserver1
192.168.1.21 clusterserver2.rmohan.com clusterserver2
[root@clusterserver2 ~]#

wget https://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm

[root@clusterserver2 software]# rpm -ivh mysql57-community-release-el7-8.noarch.rpm
warning: mysql57-community-release-el7-8.noarch.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing…                          ################################# [100%]
Updating / installing…
1:mysql57-community-release-el7-8  ################################# [100%]

wo*fk9,yVb!y

EIJQW-y6:hhe

set password for root@localhost=password(‘Test@123’);

Master1
server_id           = 1
log_bin             = /var/log/mysql/mysql-bin.log
log_bin_index       = /var/log/mysql/mysql-bin.log.index
relay_log           = /var/log/mysql/mysql-relay-bin
relay_log_index     = /var/log/mysql/mysql-relay-bin.index
expire_logs_days    = 10
max_binlog_size     = 100M
log_slave_updates   = 1
auto-increment-increment = 2
auto-increment-offset = 1

GRANT ALL ON sonar.* TO ‘sonar’@’172.27.59.54’ IDENTIFIED BY ‘sonar’;

create user ‘replicator’@’%’ identified by ‘Test@123’;
grant replication slave on *.* to ‘replicator’@’%’ identified by ‘Test@123’;

SHOW MASTER STATUS;
stop slave;
CHANGE MASTER TO MASTER_HOST = ‘192.168.1.21’, master_port=3306, MASTER_USER = ‘replicator’, MASTER_PASSWORD = ‘Test@123’, MASTER_LOG_FILE = ‘mysql-bin.000007’, MASTER_LOG_POS = 1058;
start slave;

Master2

server_id           = 2
log_bin             = /var/log/mysql/mysql-bin.log
log_bin_index       = /var/log/mysql/mysql-bin.log.index
relay_log           = /var/log/mysql/mysql-relay-bin
relay_log_index     = /var/log/mysql/mysql-relay-bin.index
expire_logs_days    = 10
max_binlog_size     = 100M
log_slave_updates   = 1
auto-increment-increment = 2
auto-increment-offset = 2

stop slave;
CHANGE MASTER TO MASTER_HOST = ‘192.168.1.20’,master_port=3306, MASTER_USER = ‘replicator’, MASTER_PASSWORD = ‘Test@123’, MASTER_LOG_FILE = ‘mysql-bin.000005’, MASTER_LOG_POS = 2964;
start slave;

show master status \G
show slave status \G

You can try this:

Slave: stop slave;
Master: flush logs

Master: show master status; — take note of the master log file and master log position

Slave: CHANGE MASTER TO MASTER_LOG_FILE=’log-bin.00000X?, MASTER_LOG_POS=106;
Slave: start slave;

SELECT user, host FROM mysql.user;
SELECT USER(), CURRENT_USER();
SHOW VARIABLES LIKE ‘skip_networking’;

create user ‘replicator’@’%’ identified by ‘Test@123’;
grant replication slave on *.* to ‘replicator’@’%’ identified by ‘Test@123’;
grant replication slave on *.* to ‘replicator’@’192.168.1.20’ identified by ‘Test@123’;
grant replication slave on *.* to ‘replicator’@’192.168.1.21’ identified by ‘Test@123’;
GRANT ALL ON replicator.* TO ‘replicator’@’192.168.1.21’ IDENTIFIED BY ‘Test@123’;
GRANT ALL ON replicator.* TO ‘replicator’@’192.168.1.20’ IDENTIFIED BY ‘Test@123’;
grant all privileges on *.*  to root@”192.168.1.%” identified by ‘Test@123’ with grant option;
FLUSH PRIVILEGES;

Lest start with the advantage for Master-Master replication: allows data to be copied from either server to the other one, it adds redundancy and increases efficiency when dealing with accessing the data, high availability . Now about Disadvantage : cost ( instead of using one machine you will be using two machine with high speed connection).

Requirements: two servers ( you can use one server but it is not recommended unless its for testing)

How to setup: lets say you have machines one m1 with IP 1.1.1.1 and machine two m2 with IP 2.2.2.2

1) login to M1 you need to edit the cnf file for it by adding or comment\un-comment the following:

server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
binlog_do_db            = MyDB     — if you want the replication for one db, comment this if you want to replicate all the SID
# bind-address            = 127.0.0.1

then restart the SID for M1 and log in to MySQL using the command

mysql -uroot -P[port] -p[password]

2) create a replica user using the following command

create user ‘replicator’@’M2’ identified by ‘password’; — note you can use the IP instead of the host name

3) Next grant the replication privliges to the replica user using:

grant replication slave on *.* to ‘replicator’@’M2’;

4) check the master status to get the log id ** after finish configuration the server one M1 you need to move to server 2 M2 (the same thing you did for server one but you need to make sure to change the host names as needed):

5) edit the cnf file but make sure the server id it not the same as M1

server-id               = 2
log_bin                 = /var/log/mysql/mysql-bin.log
binlog_do_db            = MyDB     — if you want the replication for one db, comment this if you want to replicate all the SID
# bind-address            = 127.0.0.1

6) restart the service

7) create a replication user and provide it with the replication privliges

create user ‘replicator’@’M1’ identified by ‘password’;
grant replication slave on *.* to ‘replicator’@’M1’;

now lets configure the master for M2:

slave stop;

CHANGE MASTER TO MASTER_HOST = ‘1.1.1.1’, MASTER_USER = ‘replicator’, MASTER_PASSWORD = ‘password’, MASTER_LOG_FILE = ‘mysql-bin..xxxxx’, MASTER_LOG_POS = xxx;
slave start;

8) now check the master status

9) then go back to the M1 and start the load balance:

slave stop;

CHANGE MASTER TO MASTER_HOST = ‘2.2.2.2’, MASTER_USER = ‘replicator’, MASTER_PASSWORD = ‘password’, MASTER_LOG_FILE = ‘mysql-bin.xxxxx’, MASTER_LOG_POS = xxx;
slave start;