Shulou(Shulou.com)06/01 Report--

Environmental preparation:

Three-node PXC. For the deployment process, please see: http://blog.itpub.net/30135314/viewspace-2219505/

192.168.8.51

192.168.8.52

192.168.8.53

Haproxy+keepalived

192.168.8.59

192.168.8.61

Toolkit version:

Percona-xtrabackup-2.4.11-Linux-x86_64.libgcrypt145.tar.gz

Percona-XtraDB-Cluster-5.7.21-rel20-29.26.1.Linux.x86_64.ssl101.tar.gz

Keepalived-2.0.5.tar.gz

Haproxy-1.8.9.tar.gz

This article only introduces the process of building PXC+haproxy+keepalived environment, and the installation process of each toolkit is brief.

First, add cluster check users

Grant process on *. * to 'clustercheckuser'@'localhost' identified by' mysql';flush privileges;select user,host from mysql.user

Second, modify the clustercheck script

#! / bin/bash # # Script to make a proxy (ie HAProxy) capable of monitoring Percona XtraDB Cluster nodes properly## Authors:# Raghavendra Prabhu # Olaf van Zandwijk # # Based on the original script from Unai Rodriguez and Olaf (https://github.com/olafz/percona-clustercheck)## Grant privileges required:# GRANT PROCESS ON *. * TO 'clustercheckuser'@'localhost' IDENTIFIED BY' clustercheckpasswordkeeper 'cross if [$1 = ='-h' | $1 = ='--help']] Then echo "Usage: $0" exitfiMYSQL_USERNAME= "${1-clustercheckuser}" MYSQL_PASSWORD= "${2-mysql}" AVAILABLE_WHEN_DONOR=$ {3exitfiMYSQL_USERNAME= 0} ERR_FILE= "${4:-/dev/null}" AVAILABLE_WHEN_READONLY=$ {5exitfiMYSQL_USERNAME= 1} DEFAULTS_EXTRA_FILE=$ {6:-/mysql/data/3306/my.cnf} # Timeout exists for instances where mysqld may be hungTIMEOUT=10EXTRA_ARGS= "" if [[- n "$MYSQL_USERNAME"]] Then EXTRA_ARGS= "$EXTRA_ARGS-- user=$ {MYSQL_USERNAME}" fiif [[- n "$MYSQL_PASSWORD"]; then EXTRA_ARGS= "$EXTRA_ARGS-- password=$ {MYSQL_PASSWORD}" fiif [[- r $DEFAULTS_EXTRA_FILE]] Then MYSQL_CMDLINE= "/ mysql/app/mysql/bin/mysql-- defaults-extra-file=$DEFAULTS_EXTRA_FILE-nNE-- connect-timeout=$TIMEOUT\ ${EXTRA_ARGS}" else MYSQL_CMDLINE= "/ mysql/app/mysql/bin/mysql-nNE-- connect-timeout=$TIMEOUT ${EXTRA_ARGS}" fi## Perform the query to check the wsrep_local_state#WSREP_STATUS= ($($MYSQL_CMDLINE-e "SHOW GLOBAL STATUS LIKE 'wsrep_%') "\ 2 > ${ERR_FILE} | grep-A 1-E 'wsrep_local_state$ | wsrep_cluster_status$'\ | sed-n-e' 2p'-e '5p' | tr'\ n'') if [[${WSREP_STATUS [1]}} = 'Primary' & (${WSREP_STATUS [0]}-eq 4 | |\ (${WSREP_STATUS [0]}-eq 2 & $AVAILABLE_WHEN_DONOR -) Eq 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-12 > > ${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 0else # 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: 57\ r\ n "echo-en"\ r\ n "echo-en" Percona XtraDB Cluster Node is not synced or non-PRIM. \ r\ n "sleep 0.1 exit 1fi

3. Xinetd daemon (PXC all nodes)

Mount / dev/cdrom / mediayum-y install xinetdyum-y install telnetecho "mysqlchk 9200/tcp # add mysqlchk" > > / etc/services

Vi / etc/xinetd.d/mysqlchk

# default: on# description: mysqlchkservice mysqlchk {# this isa config for xinetd, place it in/ etc/xinetd.d/disable = noflags = REUSEsocket_type = streamport = 9200wait = nouser = nobodyserver = / mysql/app/mysql/bin/clusterchecklog_on_failure + = USERIDonly_from = 0.0.0.0Compact recommended to put the IPs that need# to connect exclusively (security purposes) per_source = UNLIMITED} chmod ubix / etc/xinetd.d/mysqlchk

Test the state of the three ports of PXC from the load balancer node

[root@node2 bin] # telnet 192.168.8.51 9200Trying 192.168.8.51...Connected to 192.168.8.51.Escape character is'^] .HTTP/1.1 200 OKContent-Type: text/plainConnection: closeContent-Length: 40Percona XtraDB Cluster Node is synced.Connection closed by foreign host.

4. Configure haproxy

Globallog 127.0.0.1 local0 notice#user haproxy#group haproxydaemon#quietnbproc 1pidfile / usr/local/haproxy/haproxy.piddefaultslog globalretries 3option dontlognulloption redispatchmaxconn 2000timeout queue 1mtimeout http-request 10stimeout connect 10stimeout server 1mtimeout client 1mtimeout http-keep-alive 10stimeout check 10sbalance roundrobinlisten mysql_pxc_gwpt1_readbind 192.168.8.98:3307mode tcpbalance leastconnstats hide-versionoption httpchkserver node1 192.168.8.51:3306 check port 9200 inter 12000 rise 3 fall 3server node2 192.168.8.52:3306 check port 9200 inter 12000 rise 3 fall 3server node3 192.168. 8.53 check port 3306 check port 9200 inter 12000 rise 3 fall 3listen mysql_pxc_gwpt1_writebind 192.168.8.98:3308mode tcpbalance leastconnstats hide-versionoption httpchkserver node1 192.168.8.51 fall 3listen mysql_pxc_gwpt1_writebind 192.168.8.98:3308mode tcpbalance leastconnstats hide-versionoption httpchkserver node1 3306 check port 9200 inter 12000 rise 3 fall 3server node2 192.168.8.52 fall 3306 check port 9200 inter 12000 fall 3 backupserver node3 192.168.53 check port 9200 inter 12000 rise 3 fall 3 backuplisten haproxy_statsmode httpbind *: 8888option httplogstats refresh 5sstats uri / haproxy-statstats realm www.zdd.com moritorstats realm Haproxy Managerstats Auth haproxy:haproxy/etc/rc.d/init.d/haproxy stop/etc/rc.d/init.d/haproxy startsystemctl stop keepalivedsystemctl start keepalived

5. Access PXC from the load balancer node for testing

[root@slave2 haproxy] # mysql-uroot-pmysql-h292.168.98-P3308-e "select @ @ hostname;" mysql: [Warning] Using a password on the command line interface can be insecure.+-+ | @ @ hostname | +-+ | node1 | +-+ [root@slave2 haproxy] # mysql-uroot-pmysql-h292.168.98-P3308-e "select @ @ hostname" "mysql: [Warning] Using a password on the command line interface can be insecure.+-+ | @ @ hostname | +-+ | node1 | +-+ [root@slave2 haproxy] # mysql-uroot-pmysql-h292.168.8.98-P3308-e" select @ @ hostname " "mysql: [Warning] Using a password on the command line interface can be insecure.+-+ | @ @ hostname | +-+ | node1 | +-+ [root@slave2 haproxy] # mysql-uroot-pmysql-h292.168.8.98-P3308-e" select @ @ hostname " "mysql: [Warning] Using a password on the command line interface can be insecure.+-+ | @ @ hostname | +-+ | node1 | +-+ [root@slave2 haproxy] # mysql-uroot-pmysql-h292.168.8.98-P3307-e" select @ @ hostname " "mysql: [Warning] Using a password on the command line interface can be insecure.+-+ | @ @ hostname | +-+ | node1 | +-+ [root@slave2 haproxy] # mysql-uroot-pmysql-h292.168.8.98-P3307-e" select @ @ hostname " "mysql: [Warning] Using a password on the command line interface can be insecure.+-+ | @ @ hostname | +-+ | node2 | +-+ [root@slave2 haproxy] # mysql-uroot-pmysql-h292.168.8.98-P3307-e" select @ @ hostname " "mysql: [Warning] Using a password on the command line interface can be insecure.+-+ | @ @ hostname | +-+ | node3 | +-+ [root@slave2 haproxy] # mysql-uroot-pmysql-h292.168.8.98-P3307-e" select @ @ hostname " "mysql: [Warning] Using a password on the command line interface can be insecure.+-+ | @ @ hostname | +-+ | node1 | +-+ [root@slave2 haproxy] # mysql-uroot-pmysql-h292.168.8.98-P3307-e" select @ @ hostname " "mysql: [Warning] Using a password on the command line interface can be insecure.+-+ | @ @ hostname | +-+ | node2 | +-+ [root@slave2 haproxy] # mysql-uroot-pmysql-h292.168.8.98-P3307-e" select @ @ hostname " "mysql: [Warning] Using a password on the command line interface can be insecure.+-+ | @ @ hostname | +-+ | node3 | +-+

As you can see, port 3307 monitors read operations, and the three nodes are polling mechanisms. Access 3308 is accessed all the way to node1, because node2 and node3 are backup and will be accessed only when node1 is down.

Check the status of the haproxy console

Http://192.168.8.98:8888/haproxy-stat

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.