MySQL Master-Master replication:
Master-1 IP: 0.0.0.1
Master-2 IP: 0.0.0.2
Master-1 my.cnf configuration:
log-bin
log_warnings
log_slow_queries = /var/lib/mysql/slow.log
long_query_time = 5
tmpdir = /tmp
server-id = 1
auto_increment_increment = 2
auto_increment_offset = 1
relay-log = mysql-relay-bin
Master-2 my.cnf configuration:
log-bin
log_warnings
log_slow_queries = /var/lib/mysql/slow.log
long_query_time = 5
tmpdir = /tmp
server-id = 2
auto_increment_increment = 2
auto_increment_offset = 2
relay-log = mysql-relay-bin
Replication procedure:
1st setup Master1 as Master and Master2 as slave for Master-1:
Follow below steps:
On Master-1:
Step 1: mysql> grant replication slave on *.* to ‘replicationuser1′@’0.0.0.2′ identified by ‘password’;
Step 2: mysql> show master status;
It shows file name and position, Use these records on Master-2 to run it as slave for Master-1.
Step 3: Now log on to master-2 and run the below query:
CHANGE MASTER TO MASTER_HOST=’0.0.0.1′, MASTER_USER=’replicationuser1′,MASTER_PASSWORD=’password’, MASTER_LOG_FILE=’mysql-bin.000017′,MASTER_LOG_POS=751;
Step 4: start slave
Step 5: show slave status \G
On this status, the following 2 records should be as follows
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
These 2 records indicates Replication status. If these parameters show “Yes” that means replication is running successfully.
Setup Master2 as Master and Master1 as slave for Master-2:
On Master-2 server:
Step 1: mysql> grant replication slave on *.* to ‘replicationuser2′@’0.0.0.1′ identified by ‘password’;
Step 2: mysql> show master status;
Step 3: Now log on to master-1 and run the below query:
CHANGE MASTER TO MASTER_HOST=’0.0.0.2′, MASTER_USER=’replicationuser2′,MASTER_PASSWORD=’password’, MASTER_LOG_FILE=’mysql-bin.000002′,MASTER_LOG_POS=536;
Step 4: start slave
Step 5: show slave status \G
The following parameters should show “Yes”, so that replication is running successfully
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
On both servers “slave_IO_Running” and “slave_SQL_Running” parameters should always be “Yes” for successful Master-Master Replication.
[root@api01 ~]# cat /etc/snmp/mysql.sh
#!/bin/bash
if [ "$1" == "processlist" ]; then
echo -n “0″
echo “select count(id) from information_schema.processlist;” | mysql –defaults-extra-file=/root/my.key | tail -n 1
exit
fi
echo “update SYNC.SYNC set Dt=NOW() where id=1;” | mysql –defaults-extra-file=/root/my.key > /dev/null 2>&1
echo “show slave status\G;” | mysql –defaults-extra-file=/root/my.key | grep Read_Master | awk ‘{print 0$2}’
create database SYNC;
create table SYNC (id int, dt datetime);
exec mysqlslave /etc/snmp/mysql.sh
exec mysqlprocess /etc/snmp/mysql.sh processlist
===== mysql 8.x
root@mysql02:~# while true; do if [[ $(mysql -e "SHOW SLAVE STATUS\G" | grep "Last_SQL_Error:" | grep -c "test.sync") -gt 0 ]]; then mysql -e “STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; START SLAVE;”; else break; fi; sleep 1; done
====