centos7的mysql Galera Cluster3集群+hoproxy+keepalived

2020年6月7日

官方文档地址：

https://galeracluster.com/documentation-webpages/training/tutorials/index.html

整体框架图如下：

其中mysql Galera Cluster需要至少三台机器，haproxy两台以进行负载均衡，keepalived是同时建立在48和49上的，来使用虚拟IP（0.50）来各外部开放接口进行mysql统一访问地址。当某个ha宕机后可进行IP漂移，保证集群的统一访问入口。

1.搭建mysql Galera Cluster（0.45，0.46，0.47）

cd /etc/yum.repos.d/

vim galera.repo

其内容如下：

[galera]
name = Galera
baseurl = https://releases.galeracluster.com/galera-3.29/centos/7/x86_64/
gpgkey = https://releases.galeracluster.com/GPG-KEY-galeracluster.com
gpgcheck = 1

[mysql-wsrep]
name = MySQL-wsrep
baseurl = https://releases.galeracluster.com/mysql-wsrep-5.7.29-25.21/centos/7/x86_64/
gpgkey = https://releases.galeracluster.com/GPG-KEY-galeracluster.com
gpgcheck = 1

如果有版本变更，则可以访问https://galeracluster.com/documentation-webpages/documentation/install-mysql.html 进行相应设置

安装

yum install galera-3 mysql-wsrep-5.7

配置

vim /etc/my.cnf

注意全部内容并添加以下信息：

!includedir /etc/my.cnf.d/

cd /etc/my.cnf.d/

vim wsrep.cnf

内容如下：

log_timestamps=SYSTEM
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
server_id=11
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2

wsrep_cluster_name='cs_cluster'
wsrep-provider=/usr/lib64/galera-3/libgalera_smm.so
binlog_format=ROW
bind-address=0.0.0.0
innodb_flush_log_at_trx_commit=0
innodb_buffer_pool_size=122M
wsrep_provider_options="gcache.size=300M; gcache.page_size=300M"
wsrep_cluster_address="gcomm://192.168.0.45,192.168.0.46,192.168.0.47"
wsrep_sst_method=rsync
wsrep_node_name=node1
wsrep_node_address="192.168.0.45"

以上内容三台机器中加粗处设置不同，wsrep_cluster_address把集群的地址输出全即可,server_id在每台进行区别

启动：

/usr/bin/mysqld_bootstrap --wsrep-new-cluster（第一台启动时需要添加--wsrep-new-cluster参数）

后续其它机器启动只需要systemctl start mysql

mysql正常启动后即可，

登录查看

root默认密码可以通过以下命令从mysqld.log中查看

cat mysqld.log | grep 'temporary password'

mysqld.log地址可以查看mysql配置文件具体配置

mysql -uroot -p

SHOW STATUS LIKE 'wsrep_cluster_size';

+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+
表示当前有3台机器

2.搭建haproxy负载均衡，keepalived监控虚拟ip

安装haproxy,keepalived

yum -y install haproxy keepalived

修改配置haproxy（0.48，0.49)

cd /etc/haproxy/
cp haproxy.cfg{,.bak}
vim /etc/haproxy/haproxy.cfg
启用web管理：
listen admin_stats
stats enable
bind *:8080
mode http
option httplog
log global
maxconn 10
stats refresh 30s
stats uri /admin
stats realm haproxy
stats auth admin:admin
stats hide-version
stats admin if TRUE

监听mysql：
listen mysql_proxy 0.0.0.0:3306
mode tcp
balance leastconn
server mysql1 192.168.0.45:3306 weight 1 maxconn 151 check
server mysql2 192.168.0.46:3306 weight 1 maxconn 151 check
server mysql3 192.168.0.47:3306 weight 1 maxconn 151 check
有check参数，web管理页才可进行状态检测
启动haproxy
systemctl start haproxy
systemctl enable haproxy.service #设置开机自动启动

配置keepalived(0.48 主)
cd /etc/keepalived/
vim keepalived.conf
内容如下：

global_defs {
notification_email {
root@localhost
}
notification_email_from root@localhost
smtp_server localhost
smtp_connect_timeout 30
router_id youxi1
}
vrrp_script chk_haproxy {//要检测的脚本信息
script "/etc/keepalived/check_haproxy.sh"
interval 5
weight -5
}
vrrp_instance VI_1 {
state MASTER
interface ens33
virtual_router_id 51//主从里此值要设置相同
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.0.50 //这里为要虚拟出来的ip
}
track_script {
chk_haproxy
}
}

/etc/keepalived/check_haproxy.sh
内容如下：
A=`ps -C haproxy --no-header | wc -l`
if [ $A -eq 0 ]; then
systemctl restart haproxy
echo "Start haproxy" &> /dev/null
sleep 3
if [ `ps -C haproxy --no-header | wc -l` -eq 0 ];then
systemctl kill keepalived
echo "Stop keepalived" &> /dev/null
fi
fi

chmod +x check_haproxy.sh
启动服务：(0.48)
systemctl start keepalived

配置keepalived(0.49 从)

cd /etc/keepalived/
vim keepalived.conf
内容如下：

global_defs {
notification_email {
root@localhost
}
notification_email_from root@localhost
smtp_server localhost
smtp_connect_timeout 30
router_id youxi2
}
vrrp_script chk_haproxy {
script "/etc/keepalived/check_haproxy.sh"
interval 5
weight -5
}
vrrp_instance VI_1 {
state BACKUP
interface ens33
virtual_router_id 51
priority 98
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.0.50
}
track_script {
chk_haproxy
}
}

chmod +x check_haproxy.sh
启动服务：(0.49)
systemctl start keepalived

以上集群组件完成。

使用：

应用可以连接 192.168.0.50:3306端口进行数据库的访问。

命令ps aux | grep keepalived | grep -v grep | awk '{print $2}'| xargs kill可以杀掉keepalived进程

http://192.168.0.49:8080/admin

输入账号和密码admin/admin可以进入 haroxy的web管理界面

使用问题：
1.重启网络后vip消失，一般不建议使用service network restart重启网络，如果需要重启网络，可以在其后，使用以下命令进行手动绑定。
ip addr add 192.168.0.50 dev ens33

参考文件：

https://www.cnblogs.com/robbinluobo/p/8294782.html

https://blog.csdn.net/wzy0623/article/details/102522268

https://www.cnblogs.com/fawaikuangtu123/p/10915229.html

linux, mysql

leo的个人博客