Clustercheck - Revision history

Choeger: /* HAproxy */

2017-11-08T10:04:34Z

HAproxy

← Older revision		Revision as of 10:04, 8 November 2017
Line 131:		Line 131:
	=== HAproxy ===		=== HAproxy ===

	The service can be configured for use with [[HAproxy]]. See the [[HAproxy\|~~Configuration~~]] page for details.		The service can be configured for use with [[HAproxy]]. See the [[HAproxy#Configuration\|configuration]] page for details.

	=== Keepalived ===		=== Keepalived ===

Dominik.epple: /* Installation as web service via xinetd */

2017-09-22T17:10:55Z

Installation as web service via xinetd

← Older revision		Revision as of 17:10, 22 September 2017
Line 103:		Line 103:
	}		}
	</pre>		</pre>

			You need to

			touch /var/log/clustercheck.log
			chown nobody /var/log/clustercheck.log

	So with the usual steps (<code>apt-get install xinetd; service xinetd restart</code>, etc) we have a webservice for our clustercheck script.		So with the usual steps (<code>apt-get install xinetd; service xinetd restart</code>, etc) we have a webservice for our clustercheck script.

Dominik.epple: /* Installation as web service via xinetd */

2017-09-22T17:07:58Z

Installation as web service via xinetd

← Older revision		Revision as of 17:07, 22 September 2017
Line 79:		Line 79:
	== Installation as web service via xinetd ==		== Installation as web service via xinetd ==

	~~The~~ packages install a <code>xinetd</code> service definition file <code>/etc/xinetd.d/mysqlchk</code>. We use / configure it for our custom service like		Some of the different upstream packages install a <code>xinetd</code> service definition file <code>/etc/xinetd.d/mysqlchk</code>. If you don't have one, install the one pasted below. We use / configure it for our custom service like

	<pre># default: on		<pre># default: on
Line 93:		Line 93:
	wait = no		wait = no
	user = nobody		user = nobody
	server = /usr/bin/clustercheck~~.ox~~		server = /usr/local/bin/clustercheck
	server_args = -e /var/log/clustercheck.log -f /usr/local/etc/clustercheck.my.cnf		server_args = -e /var/log/clustercheck.log -f /usr/local/etc/clustercheck.my.cnf
	log_on_failure += USERID		log_on_failure += USERID

Dominik.epple: /* The clustercheck.ox script */

2017-09-22T17:02:49Z

The clustercheck.ox script

← Older revision		Revision as of 17:02, 22 September 2017
Line 132:		Line 132:
	The service can also be configured for use with [[Keepalived]]. See the the [[Keepalived#Keepalived_configuration_.28with_health_checks.29\|Keepalived]] page for more information.		The service can also be configured for use with [[Keepalived]]. See the the [[Keepalived#Keepalived_configuration_.28with_health_checks.29\|Keepalived]] page for more information.

	== The clustercheck~~.ox~~ script ==		== The custom clustercheck script ==

	<pre>#!/bin/bash		<pre>#!/bin/bash

Dominik.epple: /* Installation as web service via xinetd */

2017-09-22T13:18:43Z

Installation as web service via xinetd

← Older revision		Revision as of 13:18, 22 September 2017
Line 79:		Line 79:
	== Installation as web service via xinetd ==		== Installation as web service via xinetd ==

	The packages install a <code>xinetd</code> service definition file <~~ocde~~>/etc/xinetd.d/mysqlchk</code>. We use / configure it for our custom service like		The packages install a <code>xinetd</code> service definition file <code>/etc/xinetd.d/mysqlchk</code>. We use / configure it for our custom service like

	<pre># default: on		<pre># default: on

Dominik.epple: /* Installation as web service via xinetd */

2017-09-22T13:09:02Z

Installation as web service via xinetd

← Older revision		Revision as of 13:09, 22 September 2017
Line 105:		Line 105:

	So with the usual steps (<code>apt-get install xinetd; service xinetd restart</code>, etc) we have a webservice for our clustercheck script.		So with the usual steps (<code>apt-get install xinetd; service xinetd restart</code>, etc) we have a webservice for our clustercheck script.

			'''Note''': Please ensure that you haven't set the <code>max_load</code> parameter in the xinetd configuration. This parameter will lead to <code>xinetd</code> not answering any request if the load increases above this value. So the system will be detected dead even though it actually isn't.

	Final thing to do is to test this from the loadbalancer node (and adjust firewall configuration or whatever, if required).		Final thing to do is to test this from the loadbalancer node (and adjust firewall configuration or whatever, if required).

Dominik.epple at 13:05, 22 September 2017

2017-09-22T13:05:28Z

← Older revision		Revision as of 13:05, 22 September 2017
Line 9:		Line 9:
	Recent versions of the packages (both MariaDB and Percona) ship with a <code>/usr/bin/clustercheck</code> script which has basically been designed for that task. However, the original version has some shortcomings, most noticeably it has been designed for and works only with HAproxy, but not with Keepalived; and it offers no support for the <code>wsrep_local_index=0</code> feature discussed above. So, we decided to improve on that script.		Recent versions of the packages (both MariaDB and Percona) ship with a <code>/usr/bin/clustercheck</code> script which has basically been designed for that task. However, the original version has some shortcomings, most noticeably it has been designed for and works only with HAproxy, but not with Keepalived; and it offers no support for the <code>wsrep_local_index=0</code> feature discussed above. So, we decided to improve on that script.

	== Installation and ~~Configuration~~ ==		== Basic Installation and Testing ==

	Copy-paste the script pasted below in a location on your Galera nodes where it will not be overwritten. We assume <code>/usr/local/bin/clustercheck</code> for that purpose.		Copy-paste the script pasted below in a location on your Galera nodes where it will not be overwritten. We assume <code>/usr/local/bin/clustercheck</code> for that purpose.
Line 120:		Line 120:
	Connection closed by foreign host.		Connection closed by foreign host.

			== Loadbalancer configuration ==

			=== HAproxy ===

			The service can be configured for use with [[HAproxy]]. See the [[HAproxy\|Configuration]] page for details.

			=== Keepalived ===

			The service can also be configured for use with [[Keepalived]]. See the the [[Keepalived#Keepalived_configuration_.28with_health_checks.29\|Keepalived]] page for more information.

	== The clustercheck.ox script ==		== The clustercheck.ox script ==

Dominik.epple at 12:59, 22 September 2017

2017-09-22T12:59:13Z

Show changes

Dominik.epple: Created page with "= Custom clustercheck for Galera cluster = == Introduction == When using Galera as clustered MySQL database for..."

2017-09-22T12:32:07Z

Created page with "= Custom clustercheck for Galera cluster = == Introduction == When using Galera as clustered MySQL database for..."

New page

= Custom clustercheck for Galera cluster =

== Introduction ==

When using [[OXLoadBalancingClustering_Database#Galera_database_setup|Galera]] as clustered MySQL database for OX App Suite, a loadbalancer is required to ''transform'' Galera's notion of ''equivalent'' cluster nodes into OX's understanding of ''master'' and ''slave'' nodes (or <code>writeUrl</code> and <code>readUrl</code>). Typical choice is a round-robin fashion for the <code>readUrl</code> and a persistent active-passive behavior for the <code>writeUrl</code>. See [[OXLoadBalancingClustering_Database#Notes_about_configuring_OX_for_use_with_Galera|the Galera setup page]] for more detailed information.

The loadbalancers need to be able to check the health status of the Galera cluster nodes in order to decide which nodes are available for read requests, and which node should be picked persistently for write requestes. The latter point is most important if we are considering a lot of ''distributed'' loadbalancers not synchronizing their target list with each other, as one of our proposed high level design options works (distributed HAproxy instances on the OX App Suite groupware nodes). It seems natural to leverage the mechanism also used by [[Maxscale|MariaDB Maxscale]] to define a ''master'' node: use the one with <code>wsrep_local_index=0</code>.

Recent versions of the packages (both MariaDB and Percona) ship with a <code>/usr/bin/clustercheck</code> script which has basically been designed for that task. However, the original version has some shortcomings, most noticeably it has been designed for and works only with HAproxy, but not with Keepalived; and it offers no support for the <code>wsrep_local_index=0</code> feature discussed above. So, we decided to improve on that script.

== Installation ==

Copy-paste the script pasted below in a location on your Galera nodes where it will not be overwritten. We assume <code>/usr/local/bin/clustercheck.ox</code> for that purpose.

== The clustercheck.ox script ==

<pre>
#!/bin/bash
#
# Script to make a proxy (ie HAProxy) capable of monitoring Percona XtraDB Cluster nodes properly
#
# Authors:
# Raghavendra Prabhu <raghavendra.prabhu@percona.com>
# Olaf van Zandwijk <olaf.vanzandwijk@nedap.com>
#
# Based on the original script from Unai Rodriguez and Olaf (https://github.com/olafz/percona-clustercheck)
#
# Heavily rewritten and extended by Dominik Epple <dominik.epple@open-xchange.com> 2017-09
#
# Grant privileges required:
# GRANT PROCESS ON *.* TO 'clustercheckuser'@'localhost' IDENTIFIED BY 'clustercheckpassword';

AVAILABLE_WHEN_DONOR=0
ERR_FILE=/dev/null
AVAILABLE_WHEN_READONLY=1
DEFAULTS_EXTRA_FILE=""
DEFAULTS_FILE=""
#Timeout exists for instances where mysqld may be hung
TIMEOUT=10
MASTER_MODE=0

usage() {
cat <<EOF
usage:
$0 [-h]
show this usage text

$0 [-e error_file] [-f defaults_file] [-F defaults_extra_file] [-t timeout_secs] [-d] [-r] [-m] [user [pass]]
Perform clustercheck. Arguments are

-e error_file
File to log errors to. Default: /dev/null

-f defaults_file
Defaults file for MySQL client. Default: none
Preferred way to pass credentials to the MySQL client.

-F defaults_extra_file
Extra defaults file for MySQL client. Default: none
Kept for compatibilty to original clustercheck.

-t timeout
Timeout for the MySQL client in seconds. Default: 10

-d
Consider this node as available while being donor for a SST. Default: Donor node is considered unvailable.

-r
Consider this node as unavailable while being read-only. Default: read-only node is considered available.

-m
Consider this as available only if it has got wsrep_local_index=0. Useful to define a "master" node. Default: It is sufficient to be "Synced" for a node to be considered available.

user, pass
Credentials to connect to MySQL server to

EOF
}

log_debug() {
if [[ "$ERR_FILE" != "/dev/null" ]]; then
# the following woulde give nanoseconds timestamps, but create extra processes, which I want to avoid in normal ops
#echo "$(date --iso-8601=ns) $message" >> ${ERR_FILE}
printf "%(%FT%T%z)T" -1 >> ${ERR_FILE}
echo " $1" >> ${ERR_FILE}
fi
}

output() {
http_status=$1
message="$2"
exit_status=$3

log_debug "sending \"$http_status\" \"$message\" to the client."

length=${#message}
length=$(( length + 2 ))

echo -en "HTTP/1.0 $http_status\r\n"
echo -en "Content-Length: $length\r\n"
echo -en "\r\n"
echo -en "$message\r\n"

1<&-
exit $exit_status
}

while getopts "e:drf:t:mh" o; do
case "${o}" in
e)
ERR_FILE=${OPTARG}
;;
d)
AVAILABLE_WHEN_DONOR=1
;;
r)
AVAILABLE_WHEN_READONLY=0
;;
f)
DEFAULTS_FILE=${OPTARG}
;;
F)
DEFAULTS_EXTRA_FILE=${OPTARG}
;;
t)
TIMEOUT=${OPTARG}
;;
m)
MASTER_MODE=1
;;
h)
usage
exit 0
;;
*)
usage
exit 1
;;
esac
done
shift $((OPTIND-1))

MYSQL_USERNAME="${1}"
MYSQL_PASSWORD="${2}"

EXTRA_ARGS="--connect-timeout=$TIMEOUT -B -N"

if [[ -n "$MYSQL_USERNAME" ]]; then
EXTRA_ARGS="$EXTRA_ARGS --user=${MYSQL_USERNAME}"
fi

if [[ -n "$MYSQL_PASSWORD" ]]; then
EXTRA_ARGS="$EXTRA_ARGS --password=${MYSQL_PASSWORD}"
fi

if [[ -n "$DEFAULTS_FILE" ]]; then
if [[ -r "$DEFAULTS_FILE" ]]; then
# seems like it must be the first agrument
EXTRA_ARGS="--defaults-file=$DEFAULTS_FILE $EXTRA_ARGS "
else
echo "$0: error: defaults file $DEFAULTS_FILE not readable." >&2
exit 1
fi
fi

if [[ -n "$DEFAULTS_EXTRA_FILE" ]]; then
if [[ -r "$DEFAULTS_EXTRA_FILE" ]]; then
# seems like it must be the first agrument
EXTRA_ARGS="--defaults-extra-file=$DEFAULTS_EXTRA_FILE $EXTRA_ARGS "
else
echo "$0: error: defaults extra file $DEFAULTS_EXTRA_FILE not readable." >&2
exit 1
fi
fi

MYSQL_CMDLINE="mysql ${EXTRA_ARGS}"

# irrelevant for haproxy, required for keepalived: try to read input
log_debug "Reading HTTP request ..."
while read line
do
# https://stackoverflow.com/questions/369758/how-to-trim-whitespace-from-a-bash-variable
# remove trailing control characters
# inner expression: truncate left everything until to the right only spaces are left -> is only right spaces
# outer expression: truncate to the right the "right spaces"
line="${line%"${line##*[![:cntrl:]]}"}"

log_debug "Client sent: \"===$line===\""
if [[ -z "$line" ]]; then
log_debug "Client sent empty line, breaking"
break
fi

set -- $line
# haproxy sends by default OPTIONS, keepalived sends GET
if [[ ${1,,} = "get" || ${1,,} = "options" ]]; then
if [[ ${2:0:7} = "/master" ]]; then
log_debug "Upgrading to master mode as requrested by /master URL."
MASTER_MODE=1
fi
fi
done
log_debug "Done reading HTTP request."
set --

log_debug "Calling MySQL..."
mysql_output=$($MYSQL_CMDLINE -e 'SHOW GLOBAL STATUS WHERE Variable_name REGEXP "^(wsrep_local_state|wsrep_cluster_status|wsrep_local_index)$"; show global variables like "read_only";' 2>>${ERR_FILE} )
log_debug "MySQL output: ===$mysql_output==="

set -- $mysql_output
while [[ $# -gt 1 ]]
do
case "$1" in
wsrep_local_state|wsrep_cluster_status|wsrep_local_index|read_only)
declare $1="$2"
shift
shift
;;
*)
log_debug "unexpected output from MySQL: $1 $2"
shift
shift
;;
esac
done
log_debug "After parsing: wsrep_local_state=$wsrep_local_state wsrep_cluster_status=$wsrep_cluster_status wsrep_local_index=$wsrep_local_index read_only=$read_only"

if [[ "$wsrep_cluster_status" == 'Primary' && ( $wsrep_local_state -eq 4 || ( $wsrep_local_state -eq 2 && $AVAILABLE_WHEN_DONOR -eq 1 ) ) ]]
then
if [[ "${MASTER_MODE}" == 1 ]];then
if [[ ${wsrep_local_index} -eq 0 ]];then
output "200 OK" "Percona XtraDB Cluster Node is synced and wsrep_local_index==0." 0
else
output "503 Service Unavailable" "Percona XtraDB Cluster Node is not wsrep_local_index==0 and you requested master mode." 1
fi
fi

if [[ "${read_only}" == "ON" && $AVAILABLE_WHEN_READONLY -eq 0 ]];then
output "503 Service Unavailable" "Percona XtraDB Cluster Node is read_only and you requested AVAILABLE_WHEN_READONLY=0." 1
fi

output "200 OK" "Percona XtraDB Cluster Node is synced." 0
else
output "503 Service Unavailable" "Percona XtraDB Cluster Node is not synced or non-PRIM." 1
fi
</pre>