Orchestrator: Master’s Read-Only Flag Checker

Here We have below replication architecture where FlyWithEduDB1 node is the current master and FlyWithEduDB2 nodes is the current slave node.

root@FlyWithEdu-Monitor:~# sudo orchestrator-client -c topology -a FlyWithEduDB1
FlyWithEduDB1:3306   [0s,ok,5.7.29-32-log,rw,ROW,>>,P-GTID]
+ FlyWithEduDB2:3306 [0s,ok,5.7.29-32-log,ro,ROW,>>,P-GTID]

As per orchestrator pre-requisites, it is recommended to place read-only flag inside each node’s my.cnf file.

root@FlyWithEduDB1:~# cat /etc/mysql/percona-server.conf.d/mysqld.cnf |grep -i read
read_only=1

root@FlyWithEduDB2:~# cat /etc/mysql/percona-server.conf.d/mysqld.cnf |grep -i read
read_only=1

Here We know the orchestrator takes care of read-only flag once we perform the failovers. but suppose our master node’s MySQL service is restarted due to accidentally or crash or OOM issue then once the MySQL service will be up on the master node, we will have to manually connect the node and disable the read-only flag, so the application can resume their write hits.

root@FlyWithEduDB1:~# service mysql restart

root@FlyWithEduDB1:~# mysql -e"select @@global.read_only;"
+--------------------+
| @@global.read_only |
+--------------------+
|                  1 |
+--------------------+
root@FlyWithEduDB1:~#
root@FlyWithEdu-Monitor:~# sudo orchestrator-client -c topology -a FlyWithEduDB1
FlyWithEduDB1:3306   [0s,ok,5.7.29-32-log,ro,ROW,>>,P-GTID]
+ FlyWithEduDB2:3306 [0s,ok,5.7.29-32-log,ro,ROW,>>,P-GTID]
root@FlyWithEdu-Monitor:~#

To avoid the manual intervention and reduce the application downtime, here we have prepared a small script which auto takes care of the master node’s read-only flag and it makes sure to keep disable the read-only flag on the master node.


#!/bin/bash

DBUSER=orchestrator
DBPASSWORD=XXXXXXXXX
LOGFILE=/tmp/read-only-Checker.log

for ClusterAlias in `sudo orchestrator-client -c clusters-alias|cut -d ':' -f 1`
do
	MasterNode=`sudo orchestrator-client -c topology -a $ClusterAlias|head -n 1|grep -i ok|grep ro|grep -v downtimed|cut -d ':' -f 1`;
	CountReplicaNode=`sudo orchestrator-client -c topology -a $ClusterAlias|wc -l`
        echo $MasterNode;
	if [ "$MasterNode" != "" ] && [ $CountReplicaNode -ge 2 ]
	then
	       echo "[`date`] : Read Only Checker is activated." >> $LOGFILE
	       echo `sudo orchestrator-client -c topology -a $ClusterAlias` >> $LOGFILE
               echo  `mysql -h $MasterNode -u $DBUSER -p$DBPASSWORD -e"select @@global.read_only,@@global.super_read_only;"` >> $LOGFILE
               mysql -h $MasterNode -u $DBUSER -p$DBPASSWORD -vvv -e"set global read_only=0;set global super_read_only=0;" >> $LOGFILE
               echo  `mysql -h $MasterNode -u $DBUSER -p$DBPASSWORD -e"select @@global.read_only,@@global.super_read_only;"` >> $LOGFILE
	       echo "[`date`] : SLEEP 10 sec is activated." >> $LOGFILE
	       sleep 10
	       echo `sudo orchestrator-client -c topology -a $ClusterAlias` >> $LOGFILE
	       echo "[`date`] : Read Only Checker Operation is completed." >> $LOGFILE
       fi;
done
sleep 10

To automate this script, we have placed script inside the crontab which continuously runs and we have make sure that the script keeps monitoring the read-only flag on the master node of the replication setup every 10 sec.

root@FlyWithEdu-Monitor:~# crontab -l|grep -i read
* * * * * /etc/read-only-checker.sh
root@FlyWithEdu-Monitor:~#

Leave a Reply