Search
j0ke.net Open Build Service
>
Projects
>
internetx
:
mysql5
>
proxysql
> proxysql_galera_checker
Sign Up
|
Log In
Username
Password
Cancel
Overview
Repositories
Revisions
Requests
Users
Advanced
Attributes
Meta
File proxysql_galera_checker of Package proxysql
#!/bin/bash ## inspired by Percona clustercheck.sh ERR_FILE="${5:-/dev/null}" if [ -f /etc/proxysql-admin.cnf ]; then source /etc/proxysql-admin.cnf else echo "Assert! proxysql-admin configuration file :/etc/proxysql-admin.cnf does not exists, Terminating!" >> $ERR_FILE exit 1 fi # if [[ -z "$PROXYSQL_DATADIR" ]]; then PROXYSQL_DATADIR='/var/lib/proxysql' fi function usage() { cat << EOF Usage: $0 <hostgroup_id write> [hostgroup_id read] [number writers] [writers are readers 0|1] [log_file] - HOSTGROUP WRITERS (required) (0..) The hostgroup_id that contains nodes that will server 'writes' - HOSTGROUP READERS (optional) (0..) The hostgroup_id that contains nodes that will server 'reads' - NUMBER WRITERS (optional) (0..) Maximum number of write hostgroup_id node that can be marked ONLINE When 0 (default), all nodes can be marked ONLINE - WRITERS ARE READERS (optional) (0|1) When 1 (default), ONLINE nodes in write hostgroup_id will prefer not to be ONLINE in read hostgroup_id - LOG_FILE (optional) file logfile where node state checks & changes are written to (verbose) Notes about the mysql_servers in ProxySQL: - WEIGHT Hosts with a higher weight will be prefered to be put ONLINE - NODE STATUS * Nodes that are in status OFFLINE_HARD will not be checked nor will their status be changed * SHUNNED nodes are not to be used with Galera based systems, they will be checked and status will be changed to either ONLINE or OFFLINE_SOFT. When no nodes were found to be in wsrep_local_state=4 (SYNCED) for either read or write nodes, then the script will try 5 times for each node to try to find nodes wsrep_local_state=4 (SYNCED) or wsrep_local_state=2 (DONOR/DESYNC) This is to avoid $0 to mark all nodes as OFFLINE_SOFT EOF } # DEFAULTS HOSTGROUP_WRITER_ID="${1}" HOSTGROUP_READER_ID="${2:--1}" NUMBER_WRITERS="${3:-0}" WRITER_IS_READER="${4:-1}" proxysql_exec() { local query="$1" printf "%s\n" \ "[client]" \ "user=${PROXYSQL_USERNAME}" \ "password=${PROXYSQL_PASSWORD}" \ "host=${PROXYSQL_HOSTNAME}" \ "port=${PROXYSQL_PORT}" \ | mysql --defaults-file=/dev/stdin --protocol=tcp -Nse "${query}" } CLUSTER_NAME=$(proxysql_exec "select comment from scheduler where arg1=$HOSTGROUP_WRITER_ID") RELOAD_CHECK_FILE="${PROXYSQL_DATADIR}/${CLUSTER_NAME}_reload" echo "0" > ${RELOAD_CHECK_FILE} #Timeout exists for instances where mysqld may be hung TIMEOUT=10 mysql_exec() { local query="$1" printf "%s\n" \ "[client]" \ "user=${MYSQL_USERNAME}" \ "password=${MYSQL_PASSWORD}" \ "host=${server}" \ "port=${port}" \ | timeout $TIMEOUT mysql --defaults-file=/dev/stdin --protocol=tcp -nNE -e "${query}" } #Running proxysql_node_monitor script. if [ ! -f /usr/bin/proxysql_node_monitor ] ;then echo "`date` ERROR! Could not run /usr/bin/proxysql_node_monitor. Monitoring script does not exists in default location. Terminating" >> ${ERR_FILE} exit 1 else /usr/bin/proxysql_node_monitor $HOSTGROUP_WRITER_ID $HOSTGROUP_READER_ID ${PROXYSQL_DATADIR}/${CLUSTER_NAME}_proxysql_node_monitor.log fi if [ "$1" = '-h' -o "$1" = '--help' -o -z "$1" ] then usage exit 0 fi test $HOSTGROUP_WRITER_ID -ge 0 &> /dev/null if [ $? -ne 0 ]; then echo "ERROR: writer hostgroup_id is not an integer" usage exit 1 fi test $HOSTGROUP_READER_ID -ge -1 &> /dev/null if [ $? -ne 0 ]; then echo "ERROR: reader hostgroup_id is not an integer" usage exit 1 fi if [ $# -lt 1 -o $# -gt 5 ]; then echo "ERROR: Invalid number of arguments" usage exit 1 fi if [ $NUMBER_WRITERS -lt 0 ]; then echo "ERROR: The number of writers should either be 0 to enable all possible nodes ONLINE" echo " or be larger than 0 to limit the number of writers" usage exit 1 fi if [ $WRITER_IS_READER -ne 0 -a $WRITER_IS_READER -ne 1 ]; then echo "ERROR: Writers are readers requires a boolean argument (0|1)" usage exit 1 fi # print information prior to a run if ${ERR_FILE} is defined echo "`date` ###### proxysql_galera_checker.sh SUMMARY ######" >> ${ERR_FILE} echo "`date` Hostgroup writers $HOSTGROUP_WRITER_ID" >> ${ERR_FILE} echo "`date` Hostgroup readers $HOSTGROUP_READER_ID" >> ${ERR_FILE} echo "`date` Number of writers $NUMBER_WRITERS" >> ${ERR_FILE} echo "`date` Writers are readers $WRITER_IS_READER" >> ${ERR_FILE} echo "`date` log file $ERR_FILE" >> ${ERR_FILE} #Timeout exists for instances where mysqld may be hung TIMEOUT=10 #proxysql_exec="env MYSQL_PWD=$PROXYSQL_PASSWORD mysql -u$PROXYSQL_USERNAME -h $PROXYSQL_HOSTNAME -P $PROXYSQL_PORT --protocol=tcp -Nse" MYSQL_CREDENTIALS=$(proxysql_exec "SELECT variable_value FROM global_variables WHERE variable_name IN ('mysql-monitor_username','mysql-monitor_password') ORDER BY variable_name DESC") MYSQL_USERNAME=$(echo $MYSQL_CREDENTIALS | awk '{print $1}') MYSQL_PASSWORD=$(echo $MYSQL_CREDENTIALS | awk '{print $2}') #mysql_exec="env MYSQL_PWD=$MYSQL_PASSWORD timeout $TIMEOUT mysql -nNE -u$MYSQL_USERNAME" function change_server_status() { echo "`date` Changing server $1:$2:$3 to status $4. Reason: $5" >> ${ERR_FILE} proxysql_exec "UPDATE mysql_servers set status = '$4' WHERE hostgroup_id = $1 AND hostname = '$2' AND port = $3;" 2>> ${ERR_FILE} } echo "`date` ###### HANDLE WRITER NODES ######" >> ${ERR_FILE} NUMBER_WRITERS_ONLINE=0 proxysql_exec "SELECT hostgroup_id, hostname, port, status FROM mysql_servers WHERE hostgroup_id IN ($HOSTGROUP_WRITER_ID) AND status <> 'OFFLINE_HARD' AND comment <> 'SLAVEREAD' ORDER BY hostgroup_id, weight DESC, hostname, port" | while read hostgroup server port stat do WSREP_STATUS=$(mysql_exec "SHOW STATUS LIKE 'wsrep_local_state'" 2>>${ERR_FILE}| tail -1 2>>${ERR_FILE}) PXC_MAIN_MODE=$(mysql_exec "SHOW VARIABLES LIKE 'pxc_maint_mode'" 2>>${ERR_FILE} | tail -1 2>>${ERR_FILE}) echo "`date` --> Checking WRITE server $hostgroup:$server:$port, current status $stat, wsrep_local_state $WSREP_STATUS" >> ${ERR_FILE} # we have to limit amount of writers, WSREP status OK, AND node is not marked ONLINE if [ -z "$PXC_MAIN_MODE" ]; then if [ $NUMBER_WRITERS -gt 0 -a "${WSREP_STATUS}" = "4" -a "$stat" == "ONLINE" ] ; then if [ $NUMBER_WRITERS_ONLINE -lt $NUMBER_WRITERS ]; then NUMBER_WRITERS_ONLINE=$(( $NUMBER_WRITERS_ONLINE + 1 )) echo "`date` server $hostgroup:$server:$port is already ONLINE: ${NUMBER_WRITERS_ONLINE} of ${NUMBER_WRITERS} write nodes" >> ${ERR_FILE} else NUMBER_WRITERS_ONLINE=$(( $NUMBER_WRITERS_ONLINE + 1 )) change_server_status $HOSTGROUP_WRITER_ID "$server" $port "OFFLINE_SOFT" "max write nodes reached (${NUMBER_WRITERS})" echo "1" > ${RELOAD_CHECK_FILE} fi fi else if [ $NUMBER_WRITERS -gt 0 -a "${WSREP_STATUS}" = "4" -a "$stat" == "ONLINE" ] ; then if [ $NUMBER_WRITERS_ONLINE -lt $NUMBER_WRITERS ]; then NUMBER_WRITERS_ONLINE=$(( $NUMBER_WRITERS_ONLINE + 1 )) echo "`date` server $hostgroup:$server:$port is already ONLINE: ${NUMBER_WRITERS_ONLINE} of ${NUMBER_WRITERS} write nodes" >> ${ERR_FILE} else NUMBER_WRITERS_ONLINE=$(( $NUMBER_WRITERS_ONLINE + 1 )) change_server_status $HOSTGROUP_WRITER_ID "$server" $port "OFFLINE_SOFT" "max write nodes reached (${NUMBER_WRITERS})" echo "1" > ${RELOAD_CHECK_FILE} fi elif [ $NUMBER_WRITERS -gt 0 -a "${WSREP_STATUS}" = "4" -a "$stat" != "ONLINE" -a "${PXC_MAIN_MODE}" != "DISABLED" ] ; then NUMBER_WRITERS_ONLINE=$(( $NUMBER_WRITERS_ONLINE + 1 )) echo "`date` server $hostgroup:$server:$port is $stat : ${NUMBER_WRITERS_ONLINE} of ${NUMBER_WRITERS} write nodes" >> ${ERR_FILE} fi fi # WSREP status OK, but node is not marked ONLINE if [ -z "$PXC_MAIN_MODE" ]; then if [ "${WSREP_STATUS}" = "4" -a "$stat" != "ONLINE" ] ; then # we have to limit amount of writers if [ $NUMBER_WRITERS -gt 0 ] ; then if [ $NUMBER_WRITERS_ONLINE -lt $NUMBER_WRITERS ]; then NUMBER_WRITERS_ONLINE=$(( $NUMBER_WRITERS_ONLINE + 1 )) change_server_status $HOSTGROUP_WRITER_ID "$server" $port "ONLINE" "{NUMBER_WRITERS_ONLINE} of ${NUMBER_WRITERS} write nodes" echo "1" > ${RELOAD_CHECK_FILE} else NUMBER_WRITERS_ONLINE=$(( $NUMBER_WRITERS_ONLINE + 1 )) if [ "$stat" != "OFFLINE_SOFT" ]; then change_server_status $HOSTGROUP_WRITER_ID "$server" $port "OFFLINE_SOFT" "max write nodes reached (${NUMBER_WRITERS})" echo "1" > ${RELOAD_CHECK_FILE} else echo "`date` server $hostgroup:$server:$port is already OFFLINE_SOFT, max write nodes reached (${NUMBER_WRITERS})" >> ${ERR_FILE} fi fi # we do not have to limit elif [ $NUMBER_WRITERS -eq 0 ] ; then change_server_status $HOSTGROUP_WRITER_ID "$server" $port "ONLINE" "Changed state, marking write node ONLINE" echo "1" > ${RELOAD_CHECK_FILE} fi fi else if [ "${WSREP_STATUS}" = "4" -a "$stat" != "ONLINE" -a "${PXC_MAIN_MODE}" == "DISABLED" ] ; then # we have to limit amount of writers if [ $NUMBER_WRITERS -gt 0 ] ; then if [ $NUMBER_WRITERS_ONLINE -lt $NUMBER_WRITERS ]; then NUMBER_WRITERS_ONLINE=$(( $NUMBER_WRITERS_ONLINE + 1 )) change_server_status $HOSTGROUP_WRITER_ID "$server" $port "ONLINE" "{NUMBER_WRITERS_ONLINE} of ${NUMBER_WRITERS} write nodes" echo "1" > ${RELOAD_CHECK_FILE} else NUMBER_WRITERS_ONLINE=$(( $NUMBER_WRITERS_ONLINE + 1 )) if [ "$stat" != "OFFLINE_SOFT" ]; then change_server_status $HOSTGROUP_WRITER_ID "$server" $port "OFFLINE_SOFT" "max write nodes reached (${NUMBER_WRITERS})" echo "1" > ${RELOAD_CHECK_FILE} else echo "`date` server $hostgroup:$server:$port is already OFFLINE_SOFT, max write nodes reached (${NUMBER_WRITERS})" >> ${ERR_FILE} fi fi # we do not have to limit elif [ $NUMBER_WRITERS -eq 0 ] ; then change_server_status $HOSTGROUP_WRITER_ID "$server" $port "ONLINE" "Changed state, marking write node ONLINE" echo "1" > ${RELOAD_CHECK_FILE} fi fi fi # WSREP status is not ok, but the node is marked online, we should put it offline if [ -z "$PXC_MAIN_MODE" ]; then if [ "${WSREP_STATUS}" != "4" -a "$stat" = "ONLINE" ]; then change_server_status $HOSTGROUP_WRITER_ID "$server" $port "OFFLINE_SOFT" "WSREP status is ${WSREP_STATUS} which is not ok" echo "1" > ${RELOAD_CHECK_FILE} elif [ "${WSREP_STATUS}" != "4" -a "$stat" = "OFFLINE_SOFT" ]; then echo "`date` server $hostgroup:$server:$port is already OFFLINE_SOFT, WSREP status is ${WSREP_STATUS} which is not ok" >> ${ERR_FILE} fi else if [ "${WSREP_STATUS}" != "4" -a "$stat" = "ONLINE" ]; then change_server_status $HOSTGROUP_WRITER_ID "$server" $port "OFFLINE_SOFT" "WSREP status is ${WSREP_STATUS} which is not ok" echo "1" > ${RELOAD_CHECK_FILE} elif [ "${PXC_MAIN_MODE}" != "DISABLED" -a "$stat" = "ONLINE" ];then echo "`date` Changing server $hostgroup:$server:$port to status OFFLINE_SOFT due to $PXC_MAIN_MODE" >> ${ERR_FILE} change_server_status $HOSTGROUP_WRITER_ID "$server" $port "OFFLINE_SOFT" "Changed server $hostgroup:$server:$port to status OFFLINE_SOFT due to $PXC_MAIN_MODE" 2>> ${ERR_FILE} echo "1" > ${RELOAD_CHECK_FILE} elif [ "${WSREP_STATUS}" != "4" -a "$stat" = "OFFLINE_SOFT" ]; then echo "`date` server $hostgroup:$server:$port is already OFFLINE_SOFT, WSREP status is ${WSREP_STATUS} which is not ok" >> ${ERR_FILE} fi fi done # NUMBER_WRITERS_ONLINE is lost after loop NUMBER_WRITERS_ONLINE=$(proxysql_exec "SELECT count(*) FROM mysql_servers WHERE hostgroup_id IN ($HOSTGROUP_WRITER_ID) AND status = 'ONLINE' AND comment <> 'SLAVEREAD'" 2>>${ERR_FILE}| tail -1 2>>${ERR_FILE}) NUMBER_READERS_ONLINE=0 if [ ${HOSTGROUP_READER_ID} -ne -1 ]; then echo "`date` ###### HANDLE READER NODES ######" >> ${ERR_FILE} if [ $WRITER_IS_READER -eq 1 ]; then READER_PROXYSQL_QUERY="SELECT hostgroup_id, hostname, port, status, 'NULL' FROM mysql_servers WHERE hostgroup_id IN ($HOSTGROUP_READER_ID) AND status <> 'OFFLINE_HARD' AND comment <> 'SLAVEREAD' ORDER BY weight DESC, hostname, port" elif [ $WRITER_IS_READER -eq 0 ]; then # We will not try to change reader state of nodes that are writer ONLINE, so what we do is we ORDER BY writer.status ASC because by accident ONLINE is last in the line READER_PROXYSQL_QUERY="SELECT reader.hostgroup_id, reader.hostname, reader.port, reader.status, writer.status FROM mysql_servers as reader LEFT JOIN mysql_servers as writer ON writer.hostgroup_id = $HOSTGROUP_WRITER_ID AND writer.hostname = reader.hostname AND writer.port = reader.port WHERE reader.hostgroup_id = $HOSTGROUP_READER_ID AND reader.status <> 'OFFLINE_HARD' AND reader.comment <> 'SLAVEREAD' ORDER BY writer.status ASC, reader.weight DESC, reader.hostname, reader.port" fi OFFLINE_READERS_FOUND=0 proxysql_exec "$READER_PROXYSQL_QUERY" | while read hostgroup server port stat writer_stat do PXC_MAIN_MODE=$(mysql_exec "SHOW VARIABLES LIKE 'pxc_maint_mode'" 2>>${ERR_FILE} | tail -1 2>>${ERR_FILE}) WSREP_STATUS=$(mysql_exec "SHOW STATUS LIKE 'wsrep_local_state'" 2>>${ERR_FILE}| tail -1 2>>${ERR_FILE}) echo "`date` --> Checking READ server $hostgroup:$server:$port, current status $stat, wsrep_local_state $WSREP_STATUS" >> ${ERR_FILE} if [ $WRITER_IS_READER -eq 0 -a "$writer_stat" == "ONLINE" ] ; then if [ $OFFLINE_READERS_FOUND -eq 0 ] ; then if [ -z "$PXC_MAIN_MODE" ]; then if [ "${WSREP_STATUS}" = "4" -a "$stat" == "ONLINE" ] ; then echo "`date` server $hostgroup:$server:$port is already ONLINE, is also write node in ONLINE state, not enough non-ONLINE readers found" >> ${ERR_FILE} fi if [ "${WSREP_STATUS}" = "4" -a "$stat" != "ONLINE" ] ; then change_server_status $HOSTGROUP_READER_ID "$server" $port "ONLINE" "marking ONLINE write node as read ONLINE state, not enough non-ONLINE readers found" echo "1" > ${RELOAD_CHECK_FILE} fi else if [ "${WSREP_STATUS}" = "4" -a "$stat" == "ONLINE" -a "${PXC_MAIN_MODE}" == "DISABLED" ] ; then echo "`date` server $hostgroup:$server:$port is already ONLINE, is also write node in ONLINE state, not enough non-ONLINE readers found" >> ${ERR_FILE} fi if [ "${WSREP_STATUS}" = "4" -a "$stat" != "ONLINE" -a "${PXC_MAIN_MODE}" == "DISABLED" ] ; then change_server_status $HOSTGROUP_READER_ID "$server" $port "ONLINE" "marking ONLINE write node as read ONLINE state, not enough non-ONLINE readers found" echo "1" > ${RELOAD_CHECK_FILE} elif [ "${PXC_MAIN_MODE}" != "DISABLED" -a "$stat" = "ONLINE" ];then echo "`date` Changing server $hostgroup:$server:$port to status OFFLINE_SOFT due to $PXC_MAIN_MODE" >> ${ERR_FILE} change_server_status $hostgroup "$server" $port "OFFLINE_SOFT" "Changed server $hostgroup:$server:$port to status OFFLINE_SOFT due to $PXC_MAIN_MODE" 2>> ${ERR_FILE} echo "1" > ${RELOAD_CHECK_FILE} fi fi else if [ -z "$PXC_MAIN_MODE" ]; then if [ "${WSREP_STATUS}" = "4" -a "$stat" == "ONLINE" ] ; then change_server_status $HOSTGROUP_READER_ID "$server" $port "OFFLINE_SOFT" "making ONLINE writer node as read OFFLINE_SOFT as well because writers should not be readers" echo "1" > ${RELOAD_CHECK_FILE} fi if [ "${WSREP_STATUS}" = "4" -a "$stat" != "ONLINE" ] ; then echo "`date` server $hostgroup:$server:$port is $stat, keeping node in $stat is a writer ONLINE and it's preferred not to have writers as readers" >> ${ERR_FILE} fi else if [ "${WSREP_STATUS}" = "4" -a "$stat" == "ONLINE" -a "${PXC_MAIN_MODE}" == "DISABLED" ] ; then change_server_status $HOSTGROUP_READER_ID "$server" $port "OFFLINE_SOFT" "making ONLINE writer node as read OFFLINE_SOFT as well because writers should not be readers" echo "1" > ${RELOAD_CHECK_FILE} fi if [ "${WSREP_STATUS}" = "4" -a "$stat" != "ONLINE" -a "${PXC_MAIN_MODE}" == "DISABLED" ] ; then echo "`date` server $hostgroup:$server:$port is $stat, keeping node in $stat is a writer ONLINE and it's preferred not to have writers as readers" >> ${ERR_FILE} elif [ "${PXC_MAIN_MODE}" != "DISABLED" -a "$stat" = "ONLINE" ];then echo "`date` Changing server $hostgroup:$server:$port to status OFFLINE_SOFT due to $PXC_MAIN_MODE" >> ${ERR_FILE} change_server_status $hostgroup "$server" $port "OFFLINE_SOFT" "Changed server $hostgroup:$server:$port to status OFFLINE_SOFT due to $PXC_MAIN_MODE" 2>> ${ERR_FILE} echo "1" > ${RELOAD_CHECK_FILE} fi fi fi else if [ -z "$PXC_MAIN_MODE" ]; then if [ "${WSREP_STATUS}" = "4" -a "$stat" == "ONLINE" ] ; then echo "`date` server $hostgroup:$server:$port is already ONLINE" >> ${ERR_FILE} OFFLINE_READERS_FOUND=$(( $OFFLINE_READERS_FOUND + 1 )) fi # WSREP status OK, but node is not marked ONLINE if [ "${WSREP_STATUS}" = "4" -a "$stat" != "ONLINE" ] ; then change_server_status $HOSTGROUP_READER_ID "$server" $port "ONLINE" "changed state, making read node ONLINE" echo "1" > ${RELOAD_CHECK_FILE} OFFLINE_READERS_FOUND=$(( $OFFLINE_READERS_FOUND + 1 )) fi else if [ "${WSREP_STATUS}" = "4" -a "$stat" == "ONLINE" -a "${PXC_MAIN_MODE}" == "DISABLED" ] ; then echo "`date` server $hostgroup:$server:$port is already ONLINE" >> ${ERR_FILE} OFFLINE_READERS_FOUND=$(( $OFFLINE_READERS_FOUND + 1 )) elif [ "${WSREP_STATUS}" = "4" -a "$stat" != "ONLINE" -a "${PXC_MAIN_MODE}" != "DISABLED" ] ; then echo "`date` server $hostgroup:$server:$port is $stat" >> ${ERR_FILE} OFFLINE_READERS_FOUND=$(( $OFFLINE_READERS_FOUND + 1 )) fi # WSREP status OK, but node is not marked ONLINE if [ "${WSREP_STATUS}" = "4" -a "$stat" != "ONLINE" -a "${PXC_MAIN_MODE}" == "DISABLED" ] ; then change_server_status $HOSTGROUP_READER_ID "$server" $port "ONLINE" "changed state, making read node ONLINE" echo "1" > ${RELOAD_CHECK_FILE} OFFLINE_READERS_FOUND=$(( $OFFLINE_READERS_FOUND + 1 )) elif [ "${PXC_MAIN_MODE}" != "DISABLED" -a "$stat" = "ONLINE" ];then echo "`date` Changing server $hostgroup:$server:$port to status OFFLINE_SOFT due to $PXC_MAIN_MODE" >> ${ERR_FILE} change_server_status $hostgroup "$server" $port "OFFLINE_SOFT" "Changed server $hostgroup:$server:$port to status OFFLINE_SOFT due to $PXC_MAIN_MODE" 2>> ${ERR_FILE} echo "1" > ${RELOAD_CHECK_FILE} fi fi fi # WSREP status is not ok, but the node is marked online, we should put it offline if [ -z "$PXC_MAIN_MODE" ]; then if [ "${WSREP_STATUS}" != "4" -a "$stat" = "ONLINE" ]; then change_server_status $HOSTGROUP_READER_ID "$server" $port "OFFLINE_SOFT" "WSREP status is ${WSREP_STATUS} which is not ok" echo "1" > ${RELOAD_CHECK_FILE} elif [ "${WSREP_STATUS}" != "4" -a "$stat" = "OFFLINE_SOFT" ]; then echo "`date` server $hostgroup:$server:$port is already OFFLINE_SOFT, WSREP status is ${WSREP_STATUS} which is not ok" >> ${ERR_FILE} fi else if [ "${WSREP_STATUS}" != "4" -a "$stat" = "ONLINE" ]; then change_server_status $HOSTGROUP_READER_ID "$server" $port "OFFLINE_SOFT" "WSREP status is ${WSREP_STATUS} which is not ok" echo "1" > ${RELOAD_CHECK_FILE} elif [ "${PXC_MAIN_MODE}" != "DISABLED" -a "$stat" = "ONLINE" ];then echo "`date` Changing server $hostgroup:$server:$port to status OFFLINE_SOFT due to $PXC_MAIN_MODE" >> ${ERR_FILE} change_server_status $HOSTGROUP_WRITER_ID "$server" $port "OFFLINE_SOFT" "Changed server $hostgroup:$server:$port to status OFFLINE_SOFT due to $PXC_MAIN_MODE" 2>> ${ERR_FILE} echo "1" > ${RELOAD_CHECK_FILE} elif [ "${WSREP_STATUS}" != "4" -a "$stat" = "OFFLINE_SOFT" ]; then echo "`date` server $hostgroup:$server:$port is already OFFLINE_SOFT, WSREP status is ${WSREP_STATUS} which is not ok" >> ${ERR_FILE} fi fi done NUMBER_READERS_ONLINE=$(proxysql_exec "SELECT count(*) FROM mysql_servers WHERE hostgroup_id IN ($HOSTGROUP_READER_ID) AND status = 'ONLINE' AND comment <> 'SLAVEREAD'" 2>>${ERR_FILE}| tail -1 2>>${ERR_FILE}) fi echo "`date` ###### SUMMARY ######" >> ${ERR_FILE} echo "`date` --> Number of writers that are 'ONLINE': ${NUMBER_WRITERS_ONLINE} : hostgroup: ${HOSTGROUP_WRITER_ID}" >> ${ERR_FILE} [ ${HOSTGROUP_READER_ID} -ne -1 ] && echo "`date` --> Number of readers that are 'ONLINE': ${NUMBER_READERS_ONLINE} : hostgroup: ${HOSTGROUP_READER_ID}" >> ${ERR_FILE} cnt=0 # We don't have any writers... alert, try to bring some online! # This includes bringing a DONOR online if [ ${NUMBER_WRITERS_ONLINE} -eq 0 ]; then echo "`date` ###### TRYING TO FIX MISSING WRITERS ######" echo "`date` No writers found, Trying to enable last available node of the cluster (in Donor/Desync state)" >> ${ERR_FILE} proxysql_exec "SELECT hostgroup_id, hostname, port, status FROM mysql_servers WHERE hostgroup_id IN ($HOSTGROUP_WRITER_ID) AND status <> 'OFFLINE_HARD' AND comment <> 'SLAVEREAD'" | while read hostgroup server port stat do safety_cnt=0 while [ ${cnt} -le $NUMBER_WRITERS -a ${safety_cnt} -lt 5 ] do WSREP_STATUS=$(mysql_exec "SHOW STATUS LIKE 'wsrep_local_state'" 2>>${ERR_FILE} | tail -1 2>>${ERR_FILE}) echo "`date` Check server $hostgroup:$server:$port for only available node in DONOR state, status $stat , wsrep_local_state $WSREP_STATUS" >> ${ERR_FILE} if [ "${WSREP_STATUS}" = "2" -a "$stat" != "ONLINE" ] # if we are on Donor/Desync an not online in mysql_servers -> proceed then PROXY_RUNTIME_STATUS=$(proxysql_exec "SELECT status FROM runtime_mysql_servers WHERE hostname='${server}' AND port='${port}' AND hostgroup_id='${hostgroup}'") if [ "${PROXY_RUNTIME_STATUS}" != "ONLINE" ] # if we are not online in runtime_mysql_servers, proceed to change the server status and reload mysql_servers then change_server_status $HOSTGROUP_WRITER_ID "$server" $port "ONLINE" "WSREP status is DESYNC/DONOR, as this is the only node we will put this one online" echo "1" > ${RELOAD_CHECK_FILE} cnt=$(( $cnt + 1 )) else # otherwise (we are already ONLINE in runtime_mysql_servers) no need to reload so let's just remove RELOAD_CHECK_FILE and update it to ONLINE in mysql_servers (in case something would reload it) rm ${RELOAD_CHECK_FILE} cnt=$(( $cnt + 1 )) change_server_status $HOSTGROUP_WRITER_ID "$server" $port "ONLINE" "WSREP status is DESYNC/DONOR, as this is the only node we will put this one online" fi fi safety_cnt=$(( $safety_cnt + 1 )) done done fi cnt=0 # We don't have any readers... alert, try to bring some online! if [ ${HOSTGROUP_READER_ID} -ne -1 -a ${NUMBER_READERS_ONLINE} -eq 0 ]; then echo "`date` ###### TRYING TO FIX MISSING READERS ######" echo "`date` --> No readers found, Trying to enable last available node of the cluster (in Donor/Desync state) or pick the master" >> ${ERR_FILE} proxysql_exec "SELECT hostgroup_id, hostname, port, status FROM mysql_servers WHERE hostgroup_id IN ($HOSTGROUP_READER_ID) AND status <> 'OFFLINE_HARD' AND comment <> 'SLAVEREAD'" | while read hostgroup server port stat do safety_cnt=0 while [ ${cnt} -eq 0 -a ${safety_cnt} -lt 5 ] do WSREP_STATUS=$(mysql_exec "SHOW STATUS LIKE 'wsrep_local_state'" 2>>${ERR_FILE} | tail -1 2>>${ERR_FILE}) echo "`date` Check server $hostgroup:$server:$port for only available node in DONOR state, status $stat , wsrep_local_state $WSREP_STATUS" >> ${ERR_FILE} if [ "${WSREP_STATUS}" = "2" -a "$stat" != "ONLINE" ];then # if we are on Donor/Desync an not online in mysql_servers -> proceed PROXY_RUNTIME_STATUS=$(proxysql_exec "SELECT status FROM runtime_mysql_servers WHERE hostname='${server}' AND port='${port}' AND hostgroup_id='${hostgroup}'") if [ "${PROXY_RUNTIME_STATUS}" != "ONLINE" ] # if we are not online in runtime_mysql_servers, proceed to change the server status and reload mysql_servers then change_server_status $HOSTGROUP_READER_ID "$server" $port "ONLINE" "WSREP status is DESYNC/DONOR, as this is the only node we will put this one online" echo "1" > ${RELOAD_CHECK_FILE} cnt=$(( $cnt + 1 )) else # otherwise (we are already ONLINE in runtime_mysql_servers) no need to reload so let's just remove RELOAD_CHECK_FILE and update it to ONLINE in mysql_servers (in case something would reload it) rm ${RELOAD_CHECK_FILE} cnt=$(( $cnt + 1 )) change_server_status $HOSTGROUP_READER_ID "$server" $port "ONLINE" "WSREP status is DESYNC/DONOR, as this is the only node we will put this one online" fi fi safety_cnt=$(( $safety_cnt + 1 )) done done CHECK_SLAVE=$(proxysql_exec "SELECT hostname FROM mysql_servers WHERE comment='SLAVEREAD' AND status='ONLINE'" 2>>${ERR_FILE}| tail -1 2>>${ERR_FILE}) IS_QUERY_RULE_ACTIVE=$(proxysql_exec "SELECT active FROM mysql_query_rules WHERE destination_hostgroup=$HOSTGROUP_READER_ID limit 1;" 2>> ${ERR_FILE} | tail -1 2>>${ERR_FILE}) if [ "$MODE" == "singlewrite" ]; then if [[ -z ${CHECK_SLAVE} ]]; then if [[ ${IS_QUERY_RULE_ACTIVE} -eq 1 ]]; then proxysql_exec "UPDATE mysql_query_rules SET active=0 WHERE destination_hostgroup=$HOSTGROUP_READER_ID;" 2>> ${ERR_FILE} proxysql_exec "LOAD MYSQL QUERY RULES TO RUNTIME;" 2>> ${ERR_FILE} echo "`date` No readers found, marking single writer node as read/write mode" >> ${ERR_FILE} fi else if [[ ${IS_QUERY_RULE_ACTIVE} -eq 0 ]]; then proxysql_exec "UPDATE mysql_query_rules SET active=1 WHERE destination_hostgroup=$HOSTGROUP_READER_ID;" 2>> ${ERR_FILE} proxysql_exec "LOAD MYSQL QUERY RULES TO RUNTIME;" 2>> ${ERR_FILE} echo "`date` Slave host is online, disabling read transaction from writer node" >> ${ERR_FILE} fi fi fi fi if [ "$MODE" == "singlewrite" ]; then if [ ${HOSTGROUP_READER_ID} -ne -1 -a ${NUMBER_READERS_ONLINE} -gt 0 ]; then IS_QUERY_RULE_ACTIVE=$(proxysql_exec "SELECT active FROM mysql_query_rules WHERE destination_hostgroup=$HOSTGROUP_READER_ID limit 1;" 2>> ${ERR_FILE} | tail -1 2>>${ERR_FILE}) if [[ ${IS_QUERY_RULE_ACTIVE} -eq 0 ]]; then proxysql_exec "UPDATE mysql_query_rules SET active=1 WHERE destination_hostgroup=$HOSTGROUP_READER_ID;" 2>> ${ERR_FILE} proxysql_exec "LOAD MYSQL QUERY RULES TO RUNTIME;" 2>> ${ERR_FILE} echo "`date` Found reader, disabling read transaction from writer node" >> ${ERR_FILE} fi fi fi if [ $(cat ${RELOAD_CHECK_FILE}) -ne 0 ] ; then echo "`date` ###### Loading mysql_servers config into runtime ######" >> ${ERR_FILE} proxysql_exec "LOAD MYSQL SERVERS TO RUNTIME;" 2>> ${ERR_FILE} else echo "`date` ###### Not loading mysql_servers, no change needed ######" >> ${ERR_FILE} fi exit 0
