- 安装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