• 微信公众号:美女很有趣。 工作之余,放松一下,关注即送10G+美女照片!

keeplived+mycat+mysql高可用读写分离水平分表(谁看谁都会)

开发技术 开发技术 3小时前 1次浏览

一:环境准备:

应用 主机
mysqlmaster 192.168.205.184
mysql-slave 192.168.205.185
mycat-01,keeplived,jdk 192.168.205.182
mycat-02,keeplived,jdk 192.168.205.183

mysql主从环境(略)

二: 主机(192.168.205.183,192.168.205.182)上安装jdk,mycat,keeplived

以192.168.205.183主机为例,另外一台主机配置与183主机一致:

1.安装jdk

上传jdk安装包解压安装到/usr/local/jdk

vim /etc/profile

export JAVA_HOME=/usr/local/jdk

export CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar

export PATH=$JAVA_HOME/bin:$PATH

. /etc/profile

2.官网下载安装mycat

wget http://dl.mycat.org.cn/1.6.7.4/Mycat-server-1.6.7.4-release/Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz

tar -zxvf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz

mv mycat/ /opt/

3.修改配置文件

cd /opt/mycat/conf

vim server.xml

keeplived+mycat+mysql高可用读写分离水平分表(谁看谁都会)

 

 

 

vim schema.xml

keeplived+mycat+mysql高可用读写分离水平分表(谁看谁都会)

 

 

 

4.启动mycat服务

cd /opt/mycat/bin

./mycat start

keeplived+mycat+mysql高可用读写分离水平分表(谁看谁都会)

 

 

 

mycat启动后会有2个端口8066(数据连接端口),9066(管理端口)

ss -ntupl

keeplived+mycat+mysql高可用读写分离水平分表(谁看谁都会)

 

 

 

5.通过mysql客户端连接8066(我们在mysqlmaster节点登录mycat试下)

mysql -ulilong -p111111 -P8066 -h 192.168.205.183

keeplived+mycat+mysql高可用读写分离水平分表(谁看谁都会)

 

 

 

6.查看mycat里数据库信息

keeplived+mycat+mysql高可用读写分离水平分表(谁看谁都会)

 

 

 

7.进入TSDB创建表city

keeplived+mycat+mysql高可用读写分离水平分表(谁看谁都会)

 

 

 

crate table city(id int,name varchar(8),area float(5,2),people_num int);

查看数据库中是否创建成功

show tables;

keeplived+mycat+mysql高可用读写分离水平分表(谁看谁都会)

 

 

 

在2台数据库节点看是否已存在此表(由于做了读写分离在创建表时走了写节点也就是主库,从库也同步过来了)

keeplived+mycat+mysql高可用读写分离水平分表(谁看谁都会)

 

 

 

8.通过mysql客户端连接9066可以查看到相关dataNode信息

mysql -ulilong -p111111 -P9066 -h 192.168.205.183

show @@dataNode;

keeplived+mycat+mysql高可用读写分离水平分表(谁看谁都会)

 

 

 

在2台mycat节点上安装keepalived并单独配置日志文件(默认在/var/log/messages日志查看不方便)

192.168.205.182:

yum -y install keepalived

vim /etc/sysconfig/keepalived

KEEPALIVED_OPTIONS=”-D -d -S 0″

keeplived+mycat+mysql高可用读写分离水平分表(谁看谁都会)

 

 

 

vim /etc/rsyslog.conf

local0.* /var/log/keepalived.log

keeplived+mycat+mysql高可用读写分离水平分表(谁看谁都会)

 

 

 

systemctl restart rsyslog

vim /etc/keepalived/keepalived.conf

keeplived+mycat+mysql高可用读写分离水平分表(谁看谁都会)

 

 

 

192.168.205.183:

yum -y install keepalived

vim /etc/sysconfig/keepalived

KEEPALIVED_OPTIONS=”-D -d -S 0″

keeplived+mycat+mysql高可用读写分离水平分表(谁看谁都会)

 

 

 

vim /etc/rsyslog.conf

local0.* /var/log/keepalived.log

keeplived+mycat+mysql高可用读写分离水平分表(谁看谁都会)

 

 

 

systemctl restart rsyslog

vim /etc/keepalived/keepalived.conf

keeplived+mycat+mysql高可用读写分离水平分表(谁看谁都会)

 

 

 

分别启动2节点keepalived

192.168.205.182:

systemctl start keepalived

可以看到vip接口在182节点上

keeplived+mycat+mysql高可用读写分离水平分表(谁看谁都会)

 

 

 

192.168.205.183:

systemctl start keepalived

keeplived+mycat+mysql高可用读写分离水平分表(谁看谁都会)

 

 

 

在mysql节点上我们通过vip接口登录试下

mysql -ulilong -p111111 -P8066 -h 192.168.205.250

下图我们看到已经登录成功

keeplived+mycat+mysql高可用读写分离水平分表(谁看谁都会)

 

 

 

我们停用192.168.205.182的keepalived试下看是否转移到192.168.205.183上

可以看到182节点vip接口不在了

keeplived+mycat+mysql高可用读写分离水平分表(谁看谁都会)

 

 

 

在看下183节点vip接口已飘逸到此节点上

keeplived+mycat+mysql高可用读写分离水平分表(谁看谁都会)

 

 

 

我们再次启动主节点上keepalived发现vip接口已经飘逸回来,默认情况下keepalived是抢占模式所以主节点恢复后会直接抢占回来

keeplived+mycat+mysql高可用读写分离水平分表(谁看谁都会)

 

 

 

到此为止基本完成配置,可仔细想想如果是所在keepalived的master节点mycat服务有问题那它还会切换到备用节点吗。答案肯定是否定的。这就需要在keepalived 配置脚本来检测mycat服务状态如果所在节点master的mycat服务挂掉了, 那就主动结束所在master节点的keepalived进程切换至备用节点继续提供服务

2节点分别创建存放脚本的文件

mkdir -p /etc/keepalived/scripts

vim /etc/keepalived/scripts/chk_mycat.sh

#!/bin/bash

MYCAT_PORT=ss -ntupl | egrep '8066|9066' | wc -l

if [ $MYCAT_PORT -ne 2 ];then

pkill keepalived

fi

keeplived+mycat+mysql高可用读写分离水平分表(谁看谁都会)

 

 

 

在 /etc/keepalived/keepalived.conf配置文件中将以下内容加入到相应位置

vrrp_script chk_mycat {

script “/etc/keepalived/scripts/chk_mycat.sh”

interval 2

weight -50

fall 3

rise 3

timeout 3

}

track_script {

chk_mycat

}

keeplived+mycat+mysql高可用读写分离水平分表(谁看谁都会)

 

 

 

重新启动keepalived服务

systemctl restart keepalived

验证检测脚本是否生效:

master节点上mycat服务手动停掉

cd /opt/mycat/bin && ./mycat stop

keeplived+mycat+mysql高可用读写分离水平分表(谁看谁都会)

 

 

 

此时master节点keepalived服务已经停止了

keeplived+mycat+mysql高可用读写分离水平分表(谁看谁都会)

 

 

 

在看下backup节点,发现vip已经飘移到此节点上

keeplived+mycat+mysql高可用读写分离水平分表(谁看谁都会)

 

 

 

从mysql客户端访问正常

keeplived+mycat+mysql高可用读写分离水平分表(谁看谁都会)

 

 

 

配置MASTER<–>slave 相互切换时邮件通知

2个keepalived节点安装postfix邮件服务(默认已安装)和 发送邮件插件mailx

yum -y install mailx

vim /etc/mail.rc

set from=test@hzs.com.cn

set smtp=smtp.exmail.qq.com

set smtp-auth-user=test@hzs.com.cn

set smtp-auth-password=***

smtp-auth=login

keeplived+mycat+mysql高可用读写分离水平分表(谁看谁都会)

 

 

 

将以下配置加入下面相应位置(2台做同样操作)

MASTER,BACKUP,FAULT(大小写均可)

vim /etc/keepalived/keepalived.conf

notify_master “/etc/keepalived/scripts/notify.sh MASTER”

notify_backup “/etc/keepalived/scripts/notify.sh BACKUP”

notify_fault “/etc/keepalived/scripts/notify.sh FAULT”

keeplived+mycat+mysql高可用读写分离水平分表(谁看谁都会)

 

 

 

创建相应脚本文件(2台做同样操作)

cd /etc/keepalived/scripts

vim notify.sh

#!/bin/bash

SEND_to_MAIL=’1550789579@qq.com

notify() {

MAIL_SUBJECT=”$(hostname) to be $1, vip 转移”

MAIL_TEXT=”$(date +’%F %T’): vrrp transition, $(hostname) changed to be $1″

echo “$MAIL_TEXT” | mail -s “$MAIL_SUBJECT” $SEND_to_MAIL

}

case $1 in

MASTER)

notify MASTER

;;

BACKUP)

notify BACKUP

;;

FAULT)

notify FAULT

;;

*)

echo “Usage: $(basename $0) {MASTER|BACKUP|FAULT}”

exit 1

;;

esac

keeplived+mycat+mysql高可用读写分离水平分表(谁看谁都会)

 

 

 

更改脚本执行权限

chmod +x notify

重新启动keepalived服务

systemctl restart keepalived

已可以看到vip已转移到备用节点上

keeplived+mycat+mysql高可用读写分离水平分表(谁看谁都会)

 

 

 

————————————————————————————————————————————

mycat分表

1.我们以2个dataNode节点为例子在mysql-master库上新建cs1_db,mysql-slave会同步cs1_db库,之前已建过cs_db库,在2个库里分别新建表’wuhan’

keeplived+mycat+mysql高可用读写分离水平分表(谁看谁都会)

 

 

2.mycat节点(2台分表做以下配置)

vim schema.xml

keeplived+mycat+mysql高可用读写分离水平分表(谁看谁都会)

 

 

vim rule.xml

keeplived+mycat+mysql高可用读写分离水平分表(谁看谁都会)

 

 

keeplived+mycat+mysql高可用读写分离水平分表(谁看谁都会)

 

 

3.重启mycat服务

cd /opt/mycat/bin && ./mycat restart

4.通过VIP接口连接mycat服务,插入数据

mysql -ulilong -p111111 -P8066 -h 192.168.205.250

insert into wuhan(id,address) values(7,’xx’),(8,’xg’),(9,’lx’),(10,’ob’),(11,’kx’),(12,’hx’);

keeplived+mycat+mysql高可用读写分离水平分表(谁看谁都会)

 

 

5.查看2个数据库wuhan表中数据,发现数据已经分表插入到了2个数据库中同一表里

keeplived+mycat+mysql高可用读写分离水平分表(谁看谁都会)

 

 

6.可以看下mysql-slave库也同步了

keeplived+mycat+mysql高可用读写分离水平分表(谁看谁都会)

 

 

到此所有配置已结束


喜欢 (0)