Centos7 mariadb-galera-cluster-5.5+HAProxy+keepalived
1 服务器
mysql00 192.168.20.104
mysql01 192.168.20.102
mysql02 192.168.20.103
haproxy 192.168.20.105
haproxy1 192.168.20.106
2 yum源
vi /etc/yum.repos.d/galera.repo
[galera]
name=Galera
baseurl=http://releases.galeracluster.com/centos/7/x86_64/
gpgkey=http://releases.galeracluster.com/GPG-KEY-galeracluster.com
gpgcheck=1
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/5.5/centos7-amd64/
enabled = 1
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
3 修改hosts hostname
vi /etc/hosts
mysql00 192.168.20.104
mysql01 192.168.20.102
mysql02 192.168.20.103
vi /etc/hostname
mysql00(01,02)
4 yum install MariaDB-client MariaDB-Galera-server galera -y
5 修改数据库配置文件
vi /etc/my.cnf
[mysqld]
datadir=/home/mysql #修改mysql数据目录
socket=/var/lib/mysql/mysql.sock
user=mysql
binlog_format=ROW
bind-address=0.0.0.0
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
innodb_flush_log_at_trx_commit=0
innodb_buffer_pool_size=122M
innodb_log_file_size =48M
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
#wsrep_provider_options="gcache.size=300M; gcache.page_size=300M"
wsrep_cluster_name="cluster"
wsrep_cluster_address="gcomm://192.168.20.104,192.168.20.102,192.168.20.103" #配置集群中所有节点IP
wsrep_node_name=mysql00 #本节点NAME
wsrep_node_address="192.168.20.104" #本节点IP地址
wsrep_sst_method=rsync #全量同步方式
[mysql_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
6 启动第一个节点mysql00
必须按顺序启动，先启动第一个节点（/etc/init.d/mysql start --wsrep-new-cluster），然后启动其他节点（/etc/init.d/mysql start ）
/etc/init.d/mysql start --wsrep-new-cluster
成功显示success
在数据库中查看：
MariaDB [(none)]> show global status like 'wsrep_cluster%';
+--------------------------+--------------------------------------+
| Variable_name | Value |
+--------------------------+--------------------------------------+
| wsrep_cluster_conf_id | 20 |
| wsrep_cluster_size | 1 |
| wsrep_cluster_state_uuid | f5845fb3-abd6-11e6-82e5-627a888c772f |
| wsrep_cluster_status | Primary |
+--------------------------+--------------------------------------+
wsrep_cluster_size 1 表示已有一个节点
7 设置数据库root密码
/usr/bin/mysql_secure_installation
8 配置cluster_check
cd /tmp
wget https://raw.githubusercontent.com/olafz/percona-clustercheck/master/clustercheck
chmod +x clustercheck
mv clustercheck /usr/bin/
vi /etc/xinetd.d/mysqlchk
# default: on
# description: mysqlchk
service mysqlchk
{
disable = no
flags = REUSE
socket_type = stream
port = 9200 # This port used by xinetd for clustercheck
wait = no
user = nobody
server = /usr/bin/clustercheck
log_on_failure += USERID
only_from = 0.0.0.0/0
per_source = UNLIMITED
}
systemctl start xinetd
安装HAproxy
登录192.168.20.105 192.168.20.106
vi /etc/hosts
mysql00 192.168.20.104
mysql01 192.168.20.102
mysql02 192.168.20.103
haproxy 192.168.20.105
haproxy1 192.168.20.106
yum -y install haproxy
vi /etc/rsyslog.conf(为haproxy配置log)
# Provides UDP syslog reception $ModLoad imudp $UDPServerRun 51
vi /etc/rsyslog.d/haproxy.conf
local2.=info /var/log/haproxy-access.log local2.notice /var/log/haproxy-info.log
systemctl restart rsyslog
Create a new HAProxy configuration file
cd /etc/haproxy/
mv haproxy.cfg haproxy.cfg.orig （将之前的配置文件备份）
vi /etc/haproxy/haproxy.cfg （创建新的配置文件）
global
log 127.0.0.1 local2
maxconn 1024
user haproxy
group haproxy
daemon
stats socket /var/run/haproxy.sock mode 600 level admin # Make sock file for haproxy
defaults
log global
mode http
option tcplog
option dontlognull
retries 3
option redispatch
maxconn 1024
timeout connect 5000ms
timeout client 50000ms
timeout server 50000ms
listen mariadb_cluster 0.0.0.0:3030
## MariaDB balance leastconn - the cluster listening on port 3030.
mode tcp
# balance leastconn
balance roundrobin #轮询
option tcpka
option mysql-check user haproxy #健康检查
server mysql00 192.168.20.104:3306 check weight 1 #主用
server mysql01 192.168.20.102:3306 check weight 1 #主用
server mysql02 192.168.20.103:3306 check weight 1 backup #备用
listen stats 0.0.0.0:9000
## HAProxy stats web gui running on port 9000 - username and password: howtoforge. WEB界面管理
mode http
stats enable
stats uri /stats
stats realm HAProxy\ Statistics
stats auth howtoforge:howtoforge
stats admin if TRUE
KEEPALIVED 配置 （虚拟地址为192.168.20.107）
HAproxy
yum install -y keepalived
global_defs {
notification_email {
# cloud_data@163.com # e-mail地址
}
# notification_email_from cloud_data@163.com
# smtp_server smtp.163.com # 邮件服务器地址
# smtp_connect_timeout 30 # 连接超时时间
router_id LVS_Master
}
vrrp_script chk_http_port {
script "/etc/keepalived/check_haproxy.sh" # haproxy运行检测脚本[haproxy宕掉重启haproxy服务]
interval 5 # 脚本执行间隔
weight -5 # 执行脚本后优先级变更：5表示优先级+5;-5则表示优先级-5
}
vrrp_instance VI_A {
state MASTER # 主上此值为MASTER,从上为BACKUP
interface eth0
virtual_router_id 50 # 此值主从必须一致
priority 100
advert_int 1
authentication { # authentication两个参数值,主从也必须一致
auth_type PASS
auth_pass kongzhong
}
track_script {
chk_http_port
}
virtual_ipaddress {
192.168.20.107 # haproxy提供的虚拟IP地址
}
}
HAproxy1
global_defs {
notification_email {
# cloud_data@163.com
}
# notification_email_from cloud_data@163.com
#smtp_server smtp.163.com
# smtp_connect_timeout 30
router_id LVS_Master
}
vrrp_script chk_http_port {
script "/etc/keepalived/check_haproxy.sh"
interval 5
weight -5
}
vrrp_instance VI_A {
state BACKUP
interface eth0
virtual_router_id 50
priority 80
advert_int 1
authentication {
auth_type PASS
auth_pass kongzhong
}
track_script {
chk_http_port
}
virtual_ipaddress {
192.168.20.107
}
}
验证：
在浏览器输入 http://192.168.20.107:9000/stats
用户名/密码：howtoforge/howtoforge
状态显示正常
断开192.168.20.104 192.168.20.102其中一台 ，登录数据库虚拟地址 mysql -uroot -p -P 3030 -h 192.168.20.107 ，是否可以正常登录