Percona XtraDB Cluster(PXC) + HAProxy

• 安装haproxy，sysbench

yum -y install haproxy sysbench

•  修改 /etc/my.cnf

# Template my.cnf for PXC
# Edit to your requirements.

[mysqld]

server_id                      = 1
log_bin
binlog_format                  = ROW
innodb_buffer_pool_size        = 100M
innodb_flush_log_at_trx_commit = 0
innodb_flush_method            = O_DIRECT
innodb_log_files_in_group      = 2
innodb_log_file_size           = 20M
innodb_file_per_table          = 1
datadir                        = /var/lib/mysql

#wsrep_cluster_address          = gcomm://192.168.1.11,192.168.1.12,192.168.1.13
wsrep_cluster_address          = gcomm://
wsrep_provider                 = /usr/lib64/galera3/libgalera_smm.so

wsrep_slave_threads            = 8
wsrep_cluster_name             = Cluster
wsrep_node_name                = Node1
wsrep_sst_auth                 = sstuser:sstuser

wsrep_sst_method               = xtrabackup

innodb_locks_unsafe_for_binlog = 1
innodb_autoinc_lock_mode       = 2

default_storage_engine=InnoDB



[mysqld_safe]
pid-file = /run/mysqld/mysql.pid
syslog


!includedir /etc/my.cnf.d

其他节点修改 server_id, wsrep_node_name,wsrep_cluster_address

wsrep_cluster_address = gcomm://192.168.1.11

 

主节点启动
systemctl start mysql@bootstrap.service

其它节点启动
service mysql start

• 配置 /etc/haproxy/haproxy.cfg

#---------------------------------------------------------------------
# Example configuration for a possible web application.  See the
# full configuration options online.
#
#   http://haproxy.1wt.eu/download/1.4/doc/configuration.txt
#
#---------------------------------------------------------------------

#---------------------------------------------------------------------
# Global settings
#---------------------------------------------------------------------
global
    # to have these messages end up in /var/log/haproxy.log you will
    # need to:
    #
    # 1) configure syslog to accept network log events.  This is done
    #    by adding the '-r' option to the SYSLOGD_OPTIONS in
    #    /etc/sysconfig/syslog
    #
    # 2) configure local2 events to go to the /var/log/haproxy.log
    #   file. A line like the following can be added to
    #   /etc/sysconfig/syslog
    #
    #    local2.*                       /var/log/haproxy.log
    #
    log         127.0.0.1 local2

    chroot      /var/lib/haproxy
    pidfile     /var/run/haproxy.pid
    maxconn     4000
    user        haproxy
    group       haproxy
    daemon

    # turn on stats unix socket
    stats socket /var/lib/haproxy/stats

#---------------------------------------------------------------------
# common defaults that all the 'listen' and 'backend' sections will
# use if not designated in their block
#---------------------------------------------------------------------
defaults
    mode                    tcp
    log                     global
    option                  tcplog
    option                  dontlognull
    option http-server-close
   # option forwardfor       except 127.0.0.0/8
    option                  redispatch
    retries                 3
    timeout http-request    10s
    timeout queue           1m
    timeout connect         10s
    timeout client          1m
    timeout server          1m
    timeout http-keep-alive 10s
    timeout check           10s
    maxconn                 3000

#---------------------------------------------------------------------
# main frontend which proxys to the backends
#---------------------------------------------------------------------

frontend pxc-front  #描述允许客户端连接的监听套接字
        bind    *:3306
        mode    tcp
        default_backend pxc-back #当没有匹配use_backend时，默认的backend
frontend stats-front
        bind    *:80
        mode    http
        default_backend stats-back

#---------------------------------------------------------------------
# static backend for serving up images, stylesheets and such
#---------------------------------------------------------------------

backend pxc-back #描述进来的连接将转发到哪些后端服务器
        mode    tcp
        balance leastconn    #负载均衡算法，使用最少连接算法，适合长连接应用
        option httpchk #启用HTTP协议检查服务器监控状态，通过调用脚本检查节点的状态
        server db01 192.168.1.11:3306 check port 9200 inter 12000 rise 3 fall 3 #fall连续3次检查错误后，将表明服务器死亡，默认为3；inter连续两次检查的间隔时间值，单位为毫秒，默认为2s；rise连续3次检查成功，表明服务可用
        server db02 192.168.1.12:3306 check port 9200 inter 12000 rise 3 fall 3
        server db03 192.168.1.13:3306 check port 9200 inter 12000 rise 3 fall 3
        #option  mysql-check user haproxy_check #使用Mysql健康检查，不检查数据库和数据一致性，需要在mysql上创建相应的检查帐户
        #server  db01 192.168.1.11:3306 check
        #server  db02 192.168.1.12:3306 check
        #server  db03 192.168.1.13:3306 check
backend stats-back  #开启haproxy的状态页面
        mode http
        balance roundrobin
        stats   uri /haproxy/stats #定义访问统计信息的URI
        stats   auth    admin:admin #设置查看统计信息的用户名和密码---------------------------------------------------------------------

•  启动haproxy

/usr/sbin/haproxy -f /etc/haproxy/haproxy.cfg

• 配置mysql健康检查

在每个PXC 每个mysql节点安装mysql健康状态检查脚本：
1）脚本拷贝
# cp /usr/local/mysql/bin/clustercheck /usr/bin/
# cp /usr/local/mysql/xinetd.d/mysqlchk /etc/xinetd.d/

2）创建mysql用户，用于mysql健康检查（在任一节点即可）：
> grant process on *.* to 'clustercheckuser'@'localhost' identified by 'clustercheckpassword!';
> flush privileges;
ps：如不使用clustercheck中默认用户名和密码，将需要修改clustercheck脚本，MYSQL_USERNAME和MYSQL_PASSWORD值

3）更改/etc/services添加mysqlchk的服务端口号：
# echo 'mysqlchk 9200/tcp # mysqlchk' >> /etc/services

4）安装xinetd服务
# yum -y install xinetd
# service xinetd restart

• 测试

sysbench --test=/usr/share/doc/sysbench/tests/db/oltp.lua --db-driver=mysql --mysql-engine-trx=yes --mysql-table-engine=innodb --mysql-host=192.168.1.14 --mysql-port=3306 --mysql-user=root --mysql-password=passw0rd --mysql-db=db_cluster_test --oltp-table-size=10000 prepare

sysbench --test=/usr/share/doc/sysbench/tests/db/oltp.lua --db-driver=mysql --mysql-engine-trx=yes --mysql-table-engine=innodb --mysql-host=192.168.1.14 --mysql-port=3306 --mysql-user=root --mysql-password=passw0rd --mysql-db=db_cluster_test --oltp-table-size=10000 --num-threads=8 run

参考

https://www.percona.com/doc/percona-xtradb-cluster/5.6/howtos/virt_sandbox.html

http://my.oschina.net/anthonyyau/blog/277450

http://www.ha97.com/5646.html

http://blog.csdn.net/renfengjun/article/details/41379901

 

转载于:https://my.oschina.net/u/938837/blog/707429