[root@clusterserver2 ~]# cat \/etc\/hosts
\n127.0.0.1\u00a0\u00a0 localhost localhost.localdomain localhost4 localhost4.localdomain4
\n::1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 localhost localhost.localdomain localhost6 localhost6.localdomain6
\n192.168.1.20 clusterserver1.rmohan.com clusterserver1
\n192.168.1.21 clusterserver2.rmohan.com clusterserver2
\n[root@clusterserver2 ~]#<\/p>\n
wget https:\/\/dev.mysql.com\/get\/mysql57-community-release-el7-8.noarch.rpm<\/p>\n
[root@clusterserver2 software]# rpm -ivh mysql57-community-release-el7-8.noarch.rpm
\nwarning: mysql57-community-release-el7-8.noarch.rpm: Header V3 DSA\/SHA1 Signature, key ID 5072e1f5: NOKEY
\nPreparing…\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ################################# [100%]
\nUpdating \/ installing…
\n1:mysql57-community-release-el7-8\u00a0 ################################# [100%]<\/p>\n
wo*fk9,yVb!y<\/p>\n
EIJQW-y6:hhe<\/p>\n
set password for root@localhost=password(‘Test@123’);<\/p>\n
Master1
\nserver_id\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = 1
\nlog_bin\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = \/var\/log\/mysql\/mysql-bin.log
\nlog_bin_index\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = \/var\/log\/mysql\/mysql-bin.log.index
\nrelay_log\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = \/var\/log\/mysql\/mysql-relay-bin
\nrelay_log_index\u00a0\u00a0\u00a0\u00a0 = \/var\/log\/mysql\/mysql-relay-bin.index
\nexpire_logs_days\u00a0\u00a0\u00a0 = 10
\nmax_binlog_size\u00a0\u00a0\u00a0\u00a0 = 100M
\nlog_slave_updates\u00a0\u00a0 = 1
\nauto-increment-increment = 2
\nauto-increment-offset = 1<\/p>\n
GRANT ALL ON sonar.* TO ‘sonar’@’172.27.59.54’ IDENTIFIED BY ‘sonar’;<\/p>\n
create user ‘replicator’@’%’ identified by ‘Test@123’;
\ngrant replication slave on *.* to ‘replicator’@’%’ identified by ‘Test@123’;<\/p>\n
SHOW MASTER STATUS;
\nstop slave;
\nCHANGE 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;
\nstart slave;<\/p>\n
Master2<\/p>\n
server_id\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = 2
\nlog_bin\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = \/var\/log\/mysql\/mysql-bin.log
\nlog_bin_index\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = \/var\/log\/mysql\/mysql-bin.log.index
\nrelay_log\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = \/var\/log\/mysql\/mysql-relay-bin
\nrelay_log_index\u00a0\u00a0\u00a0\u00a0 = \/var\/log\/mysql\/mysql-relay-bin.index
\nexpire_logs_days\u00a0\u00a0\u00a0 = 10
\nmax_binlog_size\u00a0\u00a0\u00a0\u00a0 = 100M
\nlog_slave_updates\u00a0\u00a0 = 1
\nauto-increment-increment = 2
\nauto-increment-offset = 2<\/p>\n
stop slave;
\nCHANGE 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;
\nstart slave;<\/p>\n
show master status \\G
\nshow slave status \\G<\/p>\n
You can try this:<\/p>\n
Slave: stop slave;
\nMaster: flush logs<\/p>\n
Master: show master status; \u2014 take note of the master log file and master log position<\/p>\n
Slave: CHANGE MASTER TO MASTER_LOG_FILE=\u2019log-bin.00000X?, MASTER_LOG_POS=106;
\nSlave: start slave;<\/p>\n
SELECT user, host FROM mysql.user;
\nSELECT USER(), CURRENT_USER();
\nSHOW VARIABLES LIKE ‘skip_networking’;<\/p>\n
create user ‘replicator’@’%’ identified by ‘Test@123’;
\ngrant replication slave on *.* to ‘replicator’@’%’ identified by ‘Test@123’;
\ngrant replication slave on *.* to ‘replicator’@’192.168.1.20’ identified by ‘Test@123’;
\ngrant replication slave on *.* to ‘replicator’@’192.168.1.21’ identified by ‘Test@123’;
\nGRANT ALL ON replicator.* TO ‘replicator’@’192.168.1.21’ IDENTIFIED BY ‘Test@123’;
\nGRANT ALL ON replicator.* TO ‘replicator’@’192.168.1.20’ IDENTIFIED BY ‘Test@123’;
\ngrant all privileges on *.*\u00a0 to root@”192.168.1.%” identified by ‘Test@123’ with grant option;
\nFLUSH PRIVILEGES;<\/p>\n
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).<\/p>\n
Requirements: two servers ( you can use one server but it is not recommended unless its for testing)<\/p>\n
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<\/p>\n
1) login to M1 you need to edit the cnf file for it by adding or comment\\un-comment the following:<\/p>\n
server-id\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = 1
\nlog_bin\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = \/var\/log\/mysql\/mysql-bin.log
\nbinlog_do_db\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = MyDB\u00a0\u00a0\u00a0\u00a0 — if you want the replication for one db, comment this if you want to replicate all the SID
\n# bind-address\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = 127.0.0.1<\/p>\n
then restart the SID for M1 and log in to MySQL using the command<\/p>\n
mysql -uroot -P[port] -p[password]<\/p>\n
2) create a replica user using the following command<\/p>\n
create user ‘replicator’@’M2’ identified by ‘password’; — note you can use the IP instead of the host name<\/p>\n
3) Next grant the replication privliges to the replica user using:<\/p>\n
grant replication slave on *.* to ‘replicator’@’M2’;<\/p>\n
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):<\/p>\n
5) edit the cnf file but make sure the server id it not the same as M1<\/p>\n
server-id\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = 2
\nlog_bin\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = \/var\/log\/mysql\/mysql-bin.log
\nbinlog_do_db\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = MyDB\u00a0\u00a0\u00a0\u00a0 — if you want the replication for one db, comment this if you want to replicate all the SID
\n# bind-address\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = 127.0.0.1<\/p>\n
6) restart the service<\/p>\n
7) create a replication user and provide it with the replication privliges<\/p>\n
create user ‘replicator’@’M1’ identified by ‘password’;
\ngrant replication slave on *.* to ‘replicator’@’M1’;<\/p>\n
now lets configure the master for M2:<\/p>\n
slave stop;<\/p>\n
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;
\nslave start;<\/p>\n
8) now check the master status<\/p>\n
9) then go back to the M1 and start the load balance:<\/p>\n
slave stop;<\/p>\n
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;
\nslave start;<\/p>\n","protected":false},"excerpt":{"rendered":"
[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 ~]#<\/p>\n
wget https:\/\/dev.mysql.com\/get\/mysql57-community-release-el7-8.noarch.rpm<\/p>\n
[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%]<\/p>\n
wo*fk9,yVb!y<\/p>\n
EIJQW-y6:hhe<\/p>\n
set password for root@localhost=password(‘Test@123’);<\/p>\n
Master1 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[16],"tags":[],"_links":{"self":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/6182"}],"collection":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=6182"}],"version-history":[{"count":2,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/6182\/revisions"}],"predecessor-version":[{"id":6191,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/6182\/revisions\/6191"}],"wp:attachment":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=6182"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=6182"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=6182"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}