http://www.percona.com/doc/percona-xtradb-cluster/5.5/howtos/haproxy.html
apt-add-repository ppa:vbernat/haproxy-1.5
apt-get update
apt-get install haproxy
apt-get install xinetd
cd /etc/xinetd.d
root@server55:/etc/xinetd.d# vim mysqlchk
# default: on
# description: mysqlchk
service mysqlchk
{
disable = no
flags = REUSE
socket_type = stream
port = 9200
wait = no
user = nobody
server = /usr/bin/clustercheck
log_on_failure += USERID
only_from = 0.0.0.0/0
per_source = UNLIMITED
}
########Creating script to check the cluster status for haproxy##########
#create a script in /usr/bin
#vim /usr/bin/clustercheck
#!/bin/bash
#
# Script to make a proxy (ie HAProxy) capable of monitoring Percona XtraDB Cluster nodes properly
#
# Author: Olaf van Zandwijk <olaf.vanzandwijk@nedap.com>
# Author: Raghavendra Prabhu <raghavendra.prabhu@percona.com>
#
# Documentation and download: https://github.com/olafz/percona-clustercheck
#
# Based on the original script from Unai Rodriguez
#
if [[ $1 == '-h' || $1 == '--help' ]];then
echo "Usage: $0 <user> <pass> <available_when_donor=0|1> <log_file> <available_when_readonly=0|1> <defaults_extra_file>"
exit
fi
# if the disabled file is present, return 503. This allows
# admins to manually remove a node from a cluster easily.
if [ -e "/var/tmp/clustercheck.disabled" ]; then
# Shell return-code is 1
echo -en "HTTP/1.1 503 Service Unavailable\r\n"
echo -en "Content-Type: text/plain\r\n"
echo -en "Connection: close\r\n"
echo -en "Content-Length: 51\r\n"
echo -en "\r\n"
echo -en "Percona XtraDB Cluster Node is manually disabled.\r\n"
sleep 0.1
exit 1
fi
MYSQL_USERNAME="${1-clustercheck}"
MYSQL_PASSWORD="${2-clustercHeck223p@ssword}"
AVAILABLE_WHEN_DONOR=${3:-0}
ERR_FILE="${4:-/dev/null}"
AVAILABLE_WHEN_READONLY=${5:-1}
DEFAULTS_EXTRA_FILE=${6:-/etc/my.cnf}
#Timeout exists for instances where mysqld may be hung
TIMEOUT=10
EXTRA_ARGS=""
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 [[ -r $DEFAULTS_EXTRA_FILE ]];then
MYSQL_CMDLINE="mysql --defaults-extra-file=$DEFAULTS_EXTRA_FILE -nNE --connect-timeout=$TIMEOUT \
${EXTRA_ARGS}"
else
MYSQL_CMDLINE="mysql -nNE --connect-timeout=$TIMEOUT ${EXTRA_ARGS}"
fi
#
# Perform the query to check the wsrep_local_state
#
WSREP_STATUS=$($MYSQL_CMDLINE -e "SHOW STATUS LIKE 'wsrep_local_state';" \
2>${ERR_FILE} | tail -1 2>>${ERR_FILE})
if [[ "${WSREP_STATUS}" == "4" ]] || [[ "${WSREP_STATUS}" == "2" && ${AVAILABLE_WHEN_DONOR} == 1 ]]
then
# Check only when set to 0 to avoid latency in response.
if [[ $AVAILABLE_WHEN_READONLY -eq 0 ]];then
READ_ONLY=$($MYSQL_CMDLINE -e "SHOW GLOBAL VARIABLES LIKE 'read_only';" \
2>${ERR_FILE} | tail -1 2>>${ERR_FILE})
if [[ "${READ_ONLY}" == "ON" ]];then
# Percona XtraDB Cluster node local state is 'Synced', but it is in
# read-only mode. The variable AVAILABLE_WHEN_READONLY is set to 0.
# => return HTTP 503
# Shell return-code is 1
echo -en "HTTP/1.1 503 Service Unavailable\r\n"
echo -en "Content-Type: text/plain\r\n"
echo -en "Connection: close\r\n"
echo -en "Content-Length: 43\r\n"
echo -en "\r\n"
echo -en "Percona XtraDB Cluster Node is read-only.\r\n"
sleep 0.1
exit 1
fi
fi
# Percona XtraDB Cluster node local state is 'Synced' => return HTTP 200
# Shell return-code is 0
echo -en "HTTP/1.1 200 OK\r\n"
echo -en "Content-Type: text/plain\r\n"
echo -en "Connection: close\r\n"
echo -en "Content-Length: 40\r\n"
echo -en "\r\n"
echo -en "Percona XtraDB Cluster Node is synced.\r\n"
sleep 0.1
exit 0
else
# Percona XtraDB Cluster node local state is not 'Synced' => return HTTP 503
# Shell return-code is 1
echo -en "HTTP/1.1 503 Service Unavailable\r\n"
echo -en "Content-Type: text/plain\r\n"
echo -en "Connection: close\r\n"
echo -en "Content-Length: 44\r\n"
echo -en "\r\n"
echo -en "Percona XtraDB Cluster Node is not synced.\r\n"
sleep 0.1
exit 1
fi
##############
#chmod 755 /usr/bin/clustercheck
Note:- the user and password should be matched with /usr/bin/clustercheck script user/pass values.
On galera 10 and mariadb 10 , you can create that user on one node only ,rest will be replicated automatically.
### create user for haproxy in each cluster nodes
@db01 #GRANT PROCESS ON *.* TO 'clustercheckuser'@'localhost' IDENTIFIED BY 'clustercHeck223p@ssword' ;
# Modify /etc/services to add 9200 port for mysqlcheck
#check for 9200 port disable it and add like below to end of file
mysqlchk 9200/tcp #galera clustercheck
On EACH MariaDB Cluster node, start xinetd.
@HAPROXY SERVER
global
log /dev/log local0
log /dev/log local1 notice
chroot /var/lib/haproxy
stats socket /run/haproxy/admin.sock mode 660 level admin
stats timeout 30s
user haproxy
group haproxy
daemon
defaults
mode tcp
log global
option dontlognull
option redispatch
retries 3
timeout queue 45s
timeout connect 5s
timeout client 1m
timeout server 1m
timeout check 10s
maxconn 1020
listen haproxy-monitoring *:8090
mode http
stats enable
stats show-legends
stats refresh 5s
stats uri /
stats realm Haproxy\ Statistics
stats auth monitor:AdMiN123
stats admin if TRUE
listen mysql-cluster 0.0.0.0:3306
mode tcp
balance roundrobin
option httpchk
server mysql-1 192.168.163.55:3306 check port 9200 inter 12000 rise 3 fall 3
server mysql-2 192.168.163.56:3306 check port 9200 inter 12000 rise 3 fall 3
server mysql-3 192.168.163.57:3306 check port 9200 inter 12000 rise 3 fall 3
####
to check with script
root@server55:/usr/bin# clustercheck
HTTP/1.1 200 OK
Content-Type: text/plain
Connection: close
Content-Length: 40
Percona XtraDB Cluster Node is synced.
MariaDB [(none)]> show status like '%wsrep_cluster_size%';
MariaDB [(none)]> show status like '%wsrep%';
| wsrep_provider_name | Galera
| wsrep_provider_vendor | Codership Oy <info@codership.com>
| wsrep_provider_version | 25.3.5-wheezy(rXXXX)
| wsrep_ready | ON <= this means good
From a web browser, visit http://192.168.56.224:9000/haproxy_stats and login with user haproxy and password
mysql -e 'show status like "wsrep%"' | grep -E "comment|ready|connected|cluster_size"
output on 3 node cluster
wsrep_local_state_comment Synced
wsrep_cluster_size 3
wsrep_connected ON
wsrep_ready ON
https://dba.stackexchange.com/questions/132769/how-to-verify-galera-is-synced
Reference:-
https://mariadb.com/blog/setup-mariadb-enterprise-cluster-part-3-setup-ha-proxy-load-balancer-read-and-write-pools
apt-add-repository ppa:vbernat/haproxy-1.5
apt-get update
apt-get install haproxy
apt-get install xinetd
cd /etc/xinetd.d
root@server55:/etc/xinetd.d# vim mysqlchk
# default: on
# description: mysqlchk
service mysqlchk
{
disable = no
flags = REUSE
socket_type = stream
port = 9200
wait = no
user = nobody
server = /usr/bin/clustercheck
log_on_failure += USERID
only_from = 0.0.0.0/0
per_source = UNLIMITED
}
########Creating script to check the cluster status for haproxy##########
#create a script in /usr/bin
#vim /usr/bin/clustercheck
#!/bin/bash
#
# Script to make a proxy (ie HAProxy) capable of monitoring Percona XtraDB Cluster nodes properly
#
# Author: Olaf van Zandwijk <olaf.vanzandwijk@nedap.com>
# Author: Raghavendra Prabhu <raghavendra.prabhu@percona.com>
#
# Documentation and download: https://github.com/olafz/percona-clustercheck
#
# Based on the original script from Unai Rodriguez
#
if [[ $1 == '-h' || $1 == '--help' ]];then
echo "Usage: $0 <user> <pass> <available_when_donor=0|1> <log_file> <available_when_readonly=0|1> <defaults_extra_file>"
exit
fi
# if the disabled file is present, return 503. This allows
# admins to manually remove a node from a cluster easily.
if [ -e "/var/tmp/clustercheck.disabled" ]; then
# Shell return-code is 1
echo -en "HTTP/1.1 503 Service Unavailable\r\n"
echo -en "Content-Type: text/plain\r\n"
echo -en "Connection: close\r\n"
echo -en "Content-Length: 51\r\n"
echo -en "\r\n"
echo -en "Percona XtraDB Cluster Node is manually disabled.\r\n"
sleep 0.1
exit 1
fi
MYSQL_USERNAME="${1-clustercheck}"
MYSQL_PASSWORD="${2-clustercHeck223p@ssword}"
AVAILABLE_WHEN_DONOR=${3:-0}
ERR_FILE="${4:-/dev/null}"
AVAILABLE_WHEN_READONLY=${5:-1}
DEFAULTS_EXTRA_FILE=${6:-/etc/my.cnf}
#Timeout exists for instances where mysqld may be hung
TIMEOUT=10
EXTRA_ARGS=""
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 [[ -r $DEFAULTS_EXTRA_FILE ]];then
MYSQL_CMDLINE="mysql --defaults-extra-file=$DEFAULTS_EXTRA_FILE -nNE --connect-timeout=$TIMEOUT \
${EXTRA_ARGS}"
else
MYSQL_CMDLINE="mysql -nNE --connect-timeout=$TIMEOUT ${EXTRA_ARGS}"
fi
#
# Perform the query to check the wsrep_local_state
#
WSREP_STATUS=$($MYSQL_CMDLINE -e "SHOW STATUS LIKE 'wsrep_local_state';" \
2>${ERR_FILE} | tail -1 2>>${ERR_FILE})
if [[ "${WSREP_STATUS}" == "4" ]] || [[ "${WSREP_STATUS}" == "2" && ${AVAILABLE_WHEN_DONOR} == 1 ]]
then
# Check only when set to 0 to avoid latency in response.
if [[ $AVAILABLE_WHEN_READONLY -eq 0 ]];then
READ_ONLY=$($MYSQL_CMDLINE -e "SHOW GLOBAL VARIABLES LIKE 'read_only';" \
2>${ERR_FILE} | tail -1 2>>${ERR_FILE})
if [[ "${READ_ONLY}" == "ON" ]];then
# Percona XtraDB Cluster node local state is 'Synced', but it is in
# read-only mode. The variable AVAILABLE_WHEN_READONLY is set to 0.
# => return HTTP 503
# Shell return-code is 1
echo -en "HTTP/1.1 503 Service Unavailable\r\n"
echo -en "Content-Type: text/plain\r\n"
echo -en "Connection: close\r\n"
echo -en "Content-Length: 43\r\n"
echo -en "\r\n"
echo -en "Percona XtraDB Cluster Node is read-only.\r\n"
sleep 0.1
exit 1
fi
fi
# Percona XtraDB Cluster node local state is 'Synced' => return HTTP 200
# Shell return-code is 0
echo -en "HTTP/1.1 200 OK\r\n"
echo -en "Content-Type: text/plain\r\n"
echo -en "Connection: close\r\n"
echo -en "Content-Length: 40\r\n"
echo -en "\r\n"
echo -en "Percona XtraDB Cluster Node is synced.\r\n"
sleep 0.1
exit 0
else
# Percona XtraDB Cluster node local state is not 'Synced' => return HTTP 503
# Shell return-code is 1
echo -en "HTTP/1.1 503 Service Unavailable\r\n"
echo -en "Content-Type: text/plain\r\n"
echo -en "Connection: close\r\n"
echo -en "Content-Length: 44\r\n"
echo -en "\r\n"
echo -en "Percona XtraDB Cluster Node is not synced.\r\n"
sleep 0.1
exit 1
fi
##############
#chmod 755 /usr/bin/clustercheck
Note:- the user and password should be matched with /usr/bin/clustercheck script user/pass values.
On galera 10 and mariadb 10 , you can create that user on one node only ,rest will be replicated automatically.
### create user for haproxy in each cluster nodes
@db01 #GRANT PROCESS ON *.* TO 'clustercheckuser'@'localhost' IDENTIFIED BY 'clustercHeck223p@ssword' ;
# Modify /etc/services to add 9200 port for mysqlcheck
#check for 9200 port disable it and add like below to end of file
mysqlchk 9200/tcp #galera clustercheck
On EACH MariaDB Cluster node, start xinetd.
service xinetd startNow we create the pools by editing the HAProxy configuration file. It would be a good idea to backup the existing (default) haproxy.cfg file before making edits.
@HAPROXY SERVER
global
log /dev/log local0
log /dev/log local1 notice
chroot /var/lib/haproxy
stats socket /run/haproxy/admin.sock mode 660 level admin
stats timeout 30s
user haproxy
group haproxy
daemon
defaults
mode tcp
log global
option dontlognull
option redispatch
retries 3
timeout queue 45s
timeout connect 5s
timeout client 1m
timeout server 1m
timeout check 10s
maxconn 1020
listen haproxy-monitoring *:8090
mode http
stats enable
stats show-legends
stats refresh 5s
stats uri /
stats realm Haproxy\ Statistics
stats auth monitor:AdMiN123
stats admin if TRUE
listen mysql-cluster 0.0.0.0:3306
mode tcp
balance roundrobin
option httpchk
server mysql-1 192.168.163.55:3306 check port 9200 inter 12000 rise 3 fall 3
server mysql-2 192.168.163.56:3306 check port 9200 inter 12000 rise 3 fall 3
server mysql-3 192.168.163.57:3306 check port 9200 inter 12000 rise 3 fall 3
####
to check with script
root@server55:/usr/bin# clustercheck
HTTP/1.1 200 OK
Content-Type: text/plain
Connection: close
Content-Length: 40
Percona XtraDB Cluster Node is synced.
MariaDB [(none)]> show status like '%wsrep_cluster_size%';
MariaDB [(none)]> show status like '%wsrep%';
| wsrep_provider_name | Galera
| wsrep_provider_vendor | Codership Oy <info@codership.com>
| wsrep_provider_version | 25.3.5-wheezy(rXXXX)
| wsrep_ready | ON <= this means good
From a web browser, visit http://192.168.56.224:9000/haproxy_stats and login with user haproxy and password
mysql -e 'show status like "wsrep%"' | grep -E "comment|ready|connected|cluster_size"
output on 3 node cluster
wsrep_local_state_comment Synced
wsrep_cluster_size 3
wsrep_connected ON
wsrep_ready ON
https://dba.stackexchange.com/questions/132769/how-to-verify-galera-is-synced
Reference:-
https://mariadb.com/blog/setup-mariadb-enterprise-cluster-part-3-setup-ha-proxy-load-balancer-read-and-write-pools
No comments:
Post a Comment