4

mysql高可用集群搭建（上篇）

 1 year ago
source link: https://zhanggq.github.io/post/zgq-paas-mysql/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

众所周知，Mysql Galera Cluster为数据库提供了高一致性高可用的方案。但从实际商用的角度来看，仅仅依靠Galera插件并不能算是一个完整的Mysql高可用方案。一个完整的高可用方案还要考虑VIP、备份、灾难恢复等。这里将介绍我自己搭建的Mysql+Haproxy+Keepalived+Zookeeper+contrail cmon的方案。

一、前置准备

首先准备一个mysql的docker容器（或者裸金属服务器上直接安装），并且在容器中安装了Haproxy、Keepalived、Zookeeper、contrail cmon等服务。另外还需要单独挂载硬盘给mysql的/var/lib/mysql目录使用。

其它前置条件 1. 安装docker 2. 下载好容器镜像 3. 关闭防火墙等

systemctl stop firewalld && systemctl disable firewalld
setenforce 0
sed -i "s/^SELINUX=enforcing/SELINUX=disabled/g" /etc/sysconfig/selinux
sed -i "s/^SELINUX=enforcing/SELINUX=disabled/g" /etc/selinux/config
sed -i "s/^SELINUX=permissive/SELINUX=disabled/g" /etc/sysconfig/selinux
sed -i "s/^SELINUX=permissive/SELINUX=disabled/g" /etc/selinux/config

  1. 相关内核 自己的内核加载脚本

    ./ebtables.modules
    ./iptables.modules
    ./ip6tables.modules

    KUKztx4.png

  2. 三容器节点互信

容器启动命令

docker run --privileged -tid -e "container=docker" -h mysql1 --name=mysql --restart=always --net=host -p 10022:22 --stop-signal=RTMIN+3 docker-registry:5000/library/mysql:v1.3 /usr/sbin/init
docker run --privileged -tid -e "container=docker" -h mysql2 --name=mysql --restart=always --net=host -p 10022:22 --stop-signal=RTMIN+3 docker-registry:5000/library/mysql:v1.3 /usr/sbin/init
docker run --privileged -tid -e "container=docker" -h mysql3 --name=mysql --restart=always --net=host -p 10022:22 --stop-signal=RTMIN+3 docker-registry:5000/library/mysql:v1.3 /usr/sbin/init

二、配置mysql

三台节点配置mysql.cnf

3afy8Y6.png

Y19vjpp.png

三、配置zookeeper

三台Server节点配置zookeeper

STqgWv4.png

四、配置keepalived

三台Server节点配置keepalived

XkRdhe9.png

F8j7kTq.png

五、配置Haproxy

三台Server节点配置Haproxy

vHMK73D.png

yokHP8d.png

六、服务启动

第一个节点启动

service mysql start --wsrep-new-cluster

其它两个节点启动及集群检查

service mysql start
mysql -uroot -p123456 -e 'show status like "wsrep%"'

启动zookeeper

service zookeeper start
service zookeeper status
/sbin/chkconfig zookeeper on
chkconfig --list

启动haproxy

systemctl start haproxy
systemctl status haproxy
systemctl enable haproxy

启动keepalived

systemctl start keepalived
systemctl status keepalived
systemctl enable keepalived

检查vip是否有效

mysql -uroot -p123456 -h 172.16.165.180 -P3306

七、配置cmon服务

mysql集群启动正常之后，开始配置cmon。contrail-cmon作用是在重启后，通过contrail-cmon-monitor.sh和contrail-bootstrap-galera.sh脚本将mysql集群恢复。其过程首先是用“service mysql start –wsrep_recover”启动mysql，找出gitd最大的，也就是数据最新的mysql节点，启动。再依次拉起其它两个节点。

首先创建cmon用户

mysql -uroot -p123456 -e "create database cmon character set utf8 collate utf8_bin;"
mysql -uroot -p123456 -e "CREATE SCHEMA IF NOT EXISTS cmon;"
mysql -uroot -p123456 -e "grant all privileges on *.* to cmon@localhost identified by 'cmon' with grant option;"
mysql -uroot -p123456 -e "grant all privileges on *.* to 'cmon'@'%' identified by 'cmon' with grant option;"
mysql -uroot -p123456 -e "flush privileges;"
mysql -uroot -p123456 < /usr/share/cmon/cmon_db.sql
mysql -uroot -p123456 < /usr/share/cmon/cmon_data.sql
mysql -uroot -p123456 -e "use cmon; insert into cluster(type) VALUES ('galera')"
mysql -ucmon -pcmon -h 172.16.165.180 cmon
mysql -ucmon -pcmon -h 172.16.165.162 -p 3306 cmon

配置cmon

IryLvyJ.png

jAWkgnv.png

配置完成后手工修改一下contrail-cmon-monitor.sh和contrail-bootstrap-galera.sh脚本，由于篇幅问题，整理不再详述。之后启动cmon，观察日志。

systemctl status contrail-hamon.service
systemctl restart contrail-hamon.service
systemctl enable contrail-hamon.service
systemctl stop contrail-hamon.service
/opt/contrail/bin (cmon 启动脚本)
tail -f /var/log/contrail/ha/cmon-monitor.log (cmon 日志)
tail -f /var/log/galera-bootstrap.log (cmon 启动mysql 日志)

八、数据库备份

可以采用cron定时任务方式，定时执行dump脚本，并且通过目录映射方式，将备份的sql压缩文件存放在物理服务器上。

rqm7Txb.png

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK