您好,登录后才能下订单哦!
环境准备:
三节点PXC,部署过程见: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
工具包版本:
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
本文只介绍PXC+haproxy+keepalived环境搭建过程,各个工具包安装过程略。
一、添加集群检查用户
grant process on *.* to 'clustercheckuser'@'localhost' identified by 'mysql'; flush privileges; select user,host from mysql.user;
二、修改clustercheck脚本
#!/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) # # Grant privileges required: # GRANT PROCESS ON *.* TO 'clustercheckuser'@'localhost' IDENTIFIED BY 'clustercheckpassword!'; 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 MYSQL_USERNAME="${1-clustercheckuser}" MYSQL_PASSWORD="${2-mysql}" AVAILABLE_WHEN_DONOR=${3:-0} ERR_FILE="${4:-/dev/null}" AVAILABLE_WHEN_READONLY=${5:-1} DEFAULTS_EXTRA_FILE=${6:-/mysql/data/3306/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/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 -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: 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 1 fi
三、xinetd 守护进程(PXC所有节点)
mount /dev/cdrom /media yum -y install xinetd yum -y install telnet echo "mysqlchk 9200/tcp #add mysqlchk" >> /etc/services
vi /etc/xinetd.d/mysqlchk
# default: on # description: mysqlchk service mysqlchk { # this is a config for xinetd, place it in /etc/xinetd.d/ disable = no flags = REUSE socket_type = stream port = 9200 wait = no user = nobody server = /mysql/app/mysql/bin/clustercheck log_on_failure += USERID only_from = 0.0.0.0/0 # recommended to put the IPs that need # to connect exclusively (security purposes) per_source = UNLIMITED }
chmod u+x /etc/xinetd.d/mysqlchk
从负载均衡节点测试PXC三个端口状态
[root@node2 bin]# telnet 192.168.8.51 9200 Trying 192.168.8.51... Connected to 192.168.8.51. Escape character is '^]'. HTTP/1.1 200 OK Content-Type: text/plain Connection: close Content-Length: 40 Percona XtraDB Cluster Node is synced. Connection closed by foreign host.
四、配置haproxy
global log 127.0.0.1 local0 notice #user haproxy #group haproxy daemon #quiet nbproc 1 pidfile /usr/local/haproxy/haproxy.pid defaults log global retries 3 option dontlognull option redispatch maxconn 2000 timeout queue 1m timeout http-request 10s timeout connect 10s timeout server 1m timeout client 1m timeout http-keep-alive 10s timeout check 10s balance roundrobin listen mysql_pxc_gwpt1_read bind 192.168.8.98:3307 mode tcp balance leastconn stats hide-version option httpchk server node1 192.168.8.51:3306 check port 9200 inter 12000 rise 3 fall 3 server node2 192.168.8.52:3306 check port 9200 inter 12000 rise 3 fall 3 server node3 192.168.8.53:3306 check port 9200 inter 12000 rise 3 fall 3 listen mysql_pxc_gwpt1_write bind 192.168.8.98:3308 mode tcp balance leastconn stats hide-version option httpchk server node1 192.168.8.51:3306 check port 9200 inter 12000 rise 3 fall 3 server node2 192.168.8.52:3306 check port 9200 inter 12000 rise 3 fall 3 backup server node3 192.168.8.53:3306 check port 9200 inter 12000 rise 3 fall 3 backup listen haproxy_stats mode http bind *:8888 option httplog stats refresh 5s stats uri /haproxy-stat stats realm www.zdd.com moritor stats realm Haproxy Manager stats auth haproxy:haproxy
/etc/rc.d/init.d/haproxy stop /etc/rc.d/init.d/haproxy start systemctl stop keepalived systemctl start keepalived
五、从负载均衡节点访问PXC进行测试
[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 -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 | +------------+
可以看到,端口3307监控的是读操作,三个节点为轮询机制,访问3308一直访问到node1,因为node2和node3为backup,只有node1宕掉时候才会被访问到。
六、查看haproxy控制台状态
http://192.168.8.98:8888/haproxy-stat
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。