haproxy mycat mysql 读写分离MHA高可用

最新推荐文章于 2024-07-31 19:38:21 发布

a82831154

最新推荐文章于 2024-07-31 19:38:21 发布

阅读量1.1k

点赞数

文章标签：数据库 java 网络

原文链接：http://www.cnblogs.com/jenvid/p/8663809.html

版权

主机IP信息

hostname	IP
172.16.3.140	haproxy01
172.16.3.141	haproxy02
172.16.3.142	mycat01
172.16.3.143	mycat02
172.16.3.152	mha
172.16.3.153	master
172.16.3.154	slave01
172.16.3.155	slave02

mysql 安装：http://www.cnblogs.com/jenvid/p/8516781.html

mycat安装

mycat部署到节点

172.16.3.142 mycat01
172.16.3.143 mycat02

1.部署mycat

https://segmentfault.com/a/1190000009520414
https://www.cnblogs.com/raphael5200/p/5884931.html
https://www.cnblogs.com/hk315523748/p/6094656.html
http://www.roncoo.com/course/view/f614343765bc4aac8597c6d8b38f06fd
https://www.cnblogs.com/conanwang/p/5961019.html

1.1解压复制到`/usr/local/mycat`

http://dl.mycat.io/

1.2安装jdk

https://blog.csdn.net/linlinv3/article/details/45060705

1)下载jdkwget http://dl.mycat.io/jdk-8u20-linux-x64.tar.gz
2)解压tar -zxvf jdk-8u20-linux-x64.tar.gz -C /usr/lib
3)解压后拷贝到lib目录cp -rp jdk1.8.0_20 /usr/lib
4)配置环境变量

vi /etc/profile
export JAVA_HOME=/usr/lib/jdk1.8.0_20
export JRE_HOME=$JAVA_HOME/jre
export CLASSPATH=.:$JAVA_HOME/lib:$JER_HOME/lib:$CLASSPATH
export PATH=$JAVA_HOME/bin:$JER_HOME/bin:$PATH:/usr/local/mycat/bin
export MYCAT_HOME=/usr/local/mycat

source /etc/profile
java -version
[root@mycat01 jdk1.8.0_20]# java -version
java version "1.8.0_20"
Java(TM) SE Runtime Environment (build 1.8.0_20-b26)
Java HotSpot(TM) 64-Bit Server VM (build 25.20-b23, mixed mode)

2.配置mycat server.xml

grant select on *.* to 'readonly'@'172.16.3.%' identified by 'readonly';

server.xml

user定义的是mycat服务端口8066和管理端口9066的登录信息，和后端mysql授权没关系
schemas定义的是mycat的逻辑DB名称，必须和schema.xml的schema name=必须一致

        <property name="txlsolation">2</property>
        </system>
        <user name="mycatdb">
                <property name="password">1234567</property>
                <property name="schemas">test</property>
        </user>

        <user name="readonly">
                <property name="password">readonly1</property>
                <property name="schemas">test</property>
                <property name="readOnly">true</property>
        </user>
</mycat:server>

3.配置mycat schema

schema.xml

schema name=必须和server.xml的schema一致
dataNode="dn_tpcc100"指定需要使用的数据节点
dataHost指定数据节点

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://org.opencloudb/" >
        <schema name="test" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn_test"> </schema>

        <dataNode name="dn_test" dataHost="dh_3.151" database="test" />

        <dataHost name="dh_3.151" maxCon="1000" minCon="10" balance="1"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>show slave status</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="Master_vip" url="172.16.3.151:3306" user="root" password="123456">
                        <!-- can have multi read hosts -->
                        <readHost host="S1_3.154" url="172.16.3.154:3306" user="root" password="123456" />
                        <readHost host="S2_3.155" url="172.16.3.155:3306" user="root" password="123456" />
                </writeHost>
                <!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
        </dataHost>
</mycat:schema>

writeHost和readhost如果填写的用户名不一样，所有的读写将发送到writehost，balance不起作用

4.启动mycat`./mycat start`

#因为磁盘满了，主库创建用户语句还没在从库生效
               Master_SSL_Key: 
        Seconds_Behind_Master: 170301
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0

5.检查端口

[root@mycat01 bin]# netstat -lnpt
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name    
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      880/sshd            
tcp        0      0 127.0.0.1:32000         0.0.0.0:*               LISTEN      1418/java           
tcp6       0      0 :::8066                 :::*                    LISTEN      1418/java           
tcp6       0      0 :::9066                 :::*                    LISTEN      1418/java           
tcp6       0      0 :::55407                :::*                    LISTEN      1418/java           
tcp6       0      0 :::35574                :::*                    LISTEN      1418/java           
tcp6       0      0 :::22                   :::*                    LISTEN      880/sshd            
tcp6       0      0 :::1984                 :::*                    LISTEN      1418/java

https://blog.csdn.net/kefengwang/article/details/54233390

6.登录读写分离服务8066

这里以server.xml的定义的用户登录
显示的是mycat的逻辑库

[root@mycat01 ~]# mysql -uroot -p1234567 -h127.0.0.1 -P 8066
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.8-mycat-1.5.1-RELEASE-20160525110043 MyCat Server (OpenCloundDB)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@127.0.0.1 12:04:  [(none)]> show databases;
+----------+
| DATABASE |
+----------+
| db1      |
| test     |
| tpcc100  |
+----------+
3 rows in set (0.00 sec)

root@127.0.0.1 12:04:  [(none)]>

7.登录mycat管理端9066

show @@heartbeat;里面显示的name是在schema.xml里面定义
- RS_CODE=1表示心跳正常
```
[root@mycat01 bin]# mysql -uroot -p1234567 -h127.0.0.1 -P 9066
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.8-mycat-1.5.1-RELEASE-20160525110043 MyCat Server (monitor)

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@127.0.0.1 12:06: [(none)]> show @@heartbeat;
+------------+-------+--------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| NAME | TYPE | HOST | PORT | RS_CODE | RETRY | STATUS | TIMEOUT | EXECUTE_TIME | LAST_ACTIVE_TIME | STOP |
+------------+-------+--------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| Master_vip | mysql | 172.16.3.151 | 3306 | 1 | 0 | idle | 0 | 0,0,0 | 2018-03-26 12:06:53 | false |
| S1_3.154 | mysql | 172.16.3.154 | 3306 | 1 | 0 | idle | 0 | 0,0,0 | 2018-03-26 12:06:53 | false |
| S2_3.155 | mysql | 172.16.3.155 | 3306 | 1 | 0 | idle | 0 | 0,0,0 | 2018-03-26 12:06:53 | false |
+------------+-------+--------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
3 rows in set (0.01 sec)

root@127.0.0.1 12:07: [(none)]> show @@datasource;
+------------+------------+-------+--------------+------+------+--------+------+------+---------+
| DATANODE | NAME | TYPE | HOST | PORT | W/R | ACTIVE | IDLE | SIZE | EXECUTE |
+------------+------------+-------+--------------+------+------+--------+------+------+---------+
| dn_db1 | Master_vip | mysql | 172.16.3.151 | 3306 | W | 0 | 15 | 1000 | 244 |
| dn_db1 | S1_3.154 | mysql | 172.16.3.154 | 3306 | R | 0 | 11 | 1000 | 234 |
| dn_db1 | S2_3.155 | mysql | 172.16.3.155 | 3306 | R | 0 | 11 | 1000 | 235 |
| dn_test | Master_vip | mysql | 172.16.3.151 | 3306 | W | 0 | 15 | 1000 | 244 |
| dn_test | S1_3.154 | mysql | 172.16.3.154 | 3306 | R | 0 | 11 | 1000 | 234 |
| dn_test | S2_3.155 | mysql | 172.16.3.155 | 3306 | R | 0 | 11 | 1000 | 235 |
| dn_tpcc100 | Master_vip | mysql | 172.16.3.151 | 3306 | W | 0 | 15 | 1000 | 244 |
| dn_tpcc100 | S1_3.154 | mysql | 172.16.3.154 | 3306 | R | 0 | 11 | 1000 | 234 |
| dn_tpcc100 | S2_3.155 | mysql | 172.16.3.155 | 3306 | R | 0 | 11 | 1000 | 235 |
+------------+------------+-------+--------------+------+------+--------+------+------+---------+
9 rows in set (0.00 sec)

##### 8.mycat读写分离验证
- 1)修改mycat日志等级为`debug`，重启mycat生效

vi /usr/local/mycat/conf/log4j.xml

- 2)以mycat的root用户创建表插入数据

[root@mycat01 ~]# mysql -uroot -p1234567 -h127.0.0.1 -P 8066
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.5.8-mycat-1.5.1-RELEASE-20160525110043 MyCat Server (OpenCloundDB)

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@127.0.0.1 12:12: [(none)]> select user();
+----------------+
| USER() |
+----------------+
| root@127.0.0.1 |
+----------------+
1 row in set (0.00 sec)

root@127.0.0.1 12:12: [(none)]> use db1;
Database changed
root@127.0.0.1 12:12: [db1]> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| t1 |
| t2 |
| t3 |
+---------------+
3 rows in set (0.01 sec)

create table t4 (id int not null primary key,user_id varchar(20),user_name varchar(20),date DATE);
insert into t4 values(1,'001','yzw1',20180326);
insert into t4 values(2,'002','yzw2',20180326);
insert into t4 values(3,'003','yzw3',20180326);

- 在mycat.log中可以看到路由信息

03/26 12:17:23.925 DEBUG [$_NIOREACTOR-1-RW] (NonBlockingSession.java:229) -release connection MySQLConnection [id=7, lastTime=1522037843904, user=root, schema=db1
, old shema=db1, borrowed=true, fromSlaveDB=false, threadId=183, charset=utf8, txIsolation=3, autocommit=true, attachment=dn_db1{insert into t4 values(3,'003','yzw3
',20180326)}, respHandler=SingleNodeHandler [node=dn_db1{insert into t4 values(3,'003','yzw3',20180326)}, packetId=1], host=172.16.3.151, port=3306, statusSync=null
, writeQueue=0, modifiedSQLExecuted=true]

- 读的时候则选择从库路由

03/26 13:27:24.531 DEBUG [$_NIOREACTOR-0-RW] (NonBlockingSession.java:229) -release connection MySQLConnection [id=35, lastTime=1522042044524, user=readonly, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=true, threadId=83, charset=utf8, txIsolation=3, autocommit=true, attachment=dn_db1{select * from db1}, respHandler=SingleNodeHandler [node=dn_db1{select * from db1}, packetId=1], host=172.16.3.155, port=3306, statusSync=org.opencloudb.mysql.nio.MySQLConnection$StatusSync@c7c82a3, writeQueue=0, modifiedSQLExecuted=false]
03/26 13:27:24.531 DEBUG [$_NIOREACTOR-0-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=35, lastTime=1522042044524, user=readonly, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=true, threadId=83, charset=utf8, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=172.16.3.155, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

- 只读账号进行写数据则报错

readonly@127.0.0.1 13:28: [db1]> create table t5 (id int);
ERROR 1495 (HY000): User readonly
readonly@127.0.0.1 13:28: [db1]>


---

#### keepalived安装  
keepalvied部署到节点  
- `172.16.3.140 haproxy01`
- `172.16.3.141 haproxy02`

##### 1.下载解压编译安装
> http://www.keepalived.org/download.html

wget http://www.keepalived.org/software/keepalived-1.4.2.tar.gz
yum install curl gcc openssl-devel libnl3-devel net-snmp-devel libnfnetlink-devel
tar -zxvf keepalived-1.4.2.tar.gz
cd keepalived-1.4.2
./configure --prefix=/usr/local/keepalived-1.4.2
make && make install

> http://www.keepalived.org/doc/installing_keepalived.html

##### 2.拷贝文件

cp -rp /root/keepalived-1.4.2/keepalived/etc/init.d/keepalived /etc/init.d
cp -rp /root/keepalived-1.4.2/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
ln -s /usr/local/keepalived-1.4.2/sbin/keepalived /usr/sbin
cp -rp /usr/local/keepalived-1.4.2/etc/keepalived/keepalived.conf /etc/keepalived
systemctl enable keepalived
ln -s /usr/local/keepalived-1.4.2 /usr/local/keepalived/
mkdir /usr/local/keepalived/log/

##### 3.增加配置`/etc/keepalived/keepalived.conf`
- master

! Configuration File for keepalived
global_defs {

keepalived 自带的邮件提醒需要开启 sendmail 服务。建议用独立的监控或第三方 SMTP

router_id haproxy02 ## 标识本节点的字条串，通常为 hostname

}

keepalived 会定时执行脚本并对脚本执行的结果进行分析，动态调整 vrrp_instance 的优先级。

如果脚本执行结果为 0，并且 weight 配置的值大于 0，则优先级相应的增加。

如果脚本执行结果非 0，并且 weight 配置的值小于 0，则优先级相应的减少。

其他情况，维持原本配置的优先级，即配置文件中 priority 对应的值。

vrrp_script chk_haproxy {
script "/etc/keepalived/haproxy_check.sh" ## 检测 haproxy 状态的脚本路径
interval 2 ## 检测时间间隔
weight 2 ## 如果条件成立，权重+2
}

定义虚拟路由， VI_1 为虚拟路由的标示符，自己定义名称

vrrp_instance VI_1 {
state BACKUP ## 默认主设备（priority 值大的）和备用设备（priority 值小的）都设置为 BACKUP，
## 由 priority 来控制同时启动情况下的默认主备，否则先启动的为主设备
interface eth0 ## 绑定虚拟 IP 的网络接口，与本机 IP 地址所在的网络接口相同，我的是 eth0
virtual_router_id 35 ## 虚拟路由的 ID 号，两个节点设置必须一样，可选 IP 最后一段使用,
## 相同的 VRID 为一个组，他将决定多播的 MAC 地址
priority 180 ## 节点优先级，值范围 0-254， MASTER 要比 BACKUP 高
nopreempt ## 主设备（priority 值大的）配置一定要加上 nopreempt，否则非抢占也不起作用
advert_int 1 ## 组播信息发送间隔，两个节点设置必须一样，默认 1s
## 设置验证信息，两个节点必须一致
authentication {
auth_type PASS
auth_pass 180326 ## 真实生产，按需求对应该过来
}
## 将 track_script 块加入 instance 配置块
track_script {
chk_haproxy ## 检查 HAProxy 服务是否存活
}
## 虚拟 IP 池, 两个节点设置必须一样
virtual_ipaddress {
172.16.3.144 ## 虚拟 ip，可以定义多个，每行一个
}
}

- backup

! Configuration File for keepalived
global_defs {

keepalived 自带的邮件提醒需要开启 sendmail 服务。建议用独立的监控或第三方 SMTP

router_id haproxy01 ## 标识本节点的字条串，通常为 hostname

}

keepalived 会定时执行脚本并对脚本执行的结果进行分析，动态调整 vrrp_instance 的优先级。

如果脚本执行结果为 0，并且 weight 配置的值大于 0，则优先级相应的增加。

如果脚本执行结果非 0，并且 weight 配置的值小于 0，则优先级相应的减少。

其他情况，维持原本配置的优先级，即配置文件中 priority 对应的值。

vrrp_script chk_haproxy {
script "/etc/keepalived/haproxy_check.sh" ## 检测 haproxy 状态的脚本路径
interval 2 ## 检测时间间隔
weight 2 ## 如果条件成立，权重+2
}

定义虚拟路由， VI_1 为虚拟路由的标示符，自己定义名称

vrrp_instance VI_1 {
state BACKUP ## 默认主设备（priority 值大的）和备用设备（priority 值小的）都设置为 BACKUP，
## 由 priority 来控制同时启动情况下的默认主备，否则先启动的为主设备
interface eth0 ## 绑定虚拟 IP 的网络接口，与本机 IP 地址所在的网络接口相同，我的是 eth0
virtual_router_id 35 ## 虚拟路由的 ID 号，两个节点设置必须一样，可选 IP 最后一段使用,
## 相同的 VRID 为一个组，他将决定多播的 MAC 地址
priority 120 ## 节点优先级，值范围 0-254， MASTER 要比 BACKUP 高
nopreempt ## 主设备（priority 值大的）配置一定要加上 nopreempt，否则非抢占也不起作用
advert_int 1 ## 组播信息发送间隔，两个节点设置必须一样，默认 1s
## 设置验证信息，两个节点必须一致
authentication {
auth_type PASS
auth_pass 180326 ## 真实生产，按需求对应该过来
}
## 将 track_script 块加入 instance 配置块
track_script {
chk_haproxy ## 检查 HAProxy 服务是否存活
}
## 虚拟 IP 池, 两个节点设置必须一样
virtual_ipaddress {
172.16.3.144 ## 虚拟 ip，可以定义多个，每行一个
}
}

##### 4.检查脚本

vi /etc/keepalived/haproxy_check.sh

!/bin/bash

START_HAPROXY="/etc/rc.d/init.d/haproxy start"
STOP_HAPROXY="/etc/rc.d/init.d/haproxy stop"
LOG_FILE="/usr/local/keepalived/log/haproxy-check.log"
HAPS=ps -C haproxy --no-header |wc -l
date "+%Y-%m-%d %H:%M:%S" >> $LOG_FILE
echo "check haproxy status" >> $LOG_FILE
if [ $HAPS -eq 0 ];then
echo $START_HAPROXY >> $LOG_FILE
$START_HAPROXY >> $LOG_FILE 2>&1
sleep 3
if [ ps -C haproxy --no-header |wc -l -eq 0 ];then
echo "start haproxy failed, killall keepalived" >> $LOG_FILE
killall keepalived
fi
fi

##### 5.授权检查脚本`chmod +x /etc/keepalived/haproxy_check.sh`

> https://blog.csdn.net/l1028386804/article/details/76397064

##### 6.在2个节点上启动keepalived`systemctl start keepalived`

2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP qlen 1000
link/ether 00:50:56:a3:d0:2e brd ff:ff:ff:ff:ff:ff
inet 172.16.3.140/24 brd 172.16.3.255 scope global eth0
valid_lft forever preferred_lft forever
inet 172.16.3.144/32 scope global eth0
valid_lft forever preferred_lft forever
inet6 fe80::250:56ff:fea3:d02e/64 scope link
valid_lft forever preferred_lft forever

##### 7.切换VIP测试

[root@haproxy01 keepalived]# ip a
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP qlen 1000
link/ether 00:50:56:a3:d0:2e brd ff:ff:ff:ff:ff:ff
inet 172.16.3.140/24 brd 172.16.3.255 scope global eth0
valid_lft forever preferred_lft forever
inet 172.16.3.144/32 scope global eth0
valid_lft forever preferred_lft forever
inet6 fe80::250:56ff:fea3:d02e/64 scope link
valid_lft forever preferred_lft forever

[root@haproxy01 keepalived]# systemctl stop keepalived
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP qlen 1000
link/ether 00:50:56:a3:d0:2e brd ff:ff:ff:ff:ff:ff
inet 172.16.3.140/24 brd 172.16.3.255 scope global eth0
valid_lft forever preferred_lft forever
inet6 fe80::250:56ff:fea3:d02e/64 scope link
valid_lft forever preferred_lft forever

[root@haproxy02 keepalived]# ip a
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP qlen 1000
link/ether 00:50:56:a3:fe:0a brd ff:ff:ff:ff:ff:ff
inet 172.16.3.141/24 brd 172.16.3.255 scope global eth0
valid_lft forever preferred_lft forever
inet 172.16.3.144/32 scope global eth0
valid_lft forever preferred_lft forever
inet6 fe80::250:56ff:fea3:fe0a/64 scope link
valid_lft forever preferred_lft forever


---

#### haproxy安装
mycat部署到节点
- `172.16.3.140 haproxy01`
- `172.16.3.141 haproxy02`

##### 1.部署haproxy
> https://www.cnblogs.com/zhangs1986/p/6517788.html

wget http://www.haproxy.org/download/1.7/src/haproxy-1.7.3.tar.gz
[root@haproxy01 haproxy-1.7.3]# uname -a
Linux haproxy01 3.10.0-229.el7.x86_64 #1 SMP Fri Mar 6 11:36:42 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux
make TARGET=linux3100 CPU=x86_64 PREFIX=/usr/local/haproxy-1.7.3
make install PREFIX=/usr/local/haproxy-1.7.3

##### 2.配置haproxy
- 2.1创建配置文件目录

mkdir -p /usr/local/haproxy-1.7.3/conf

- 2.2创建配置文件目录

mkdir -p /etc/haproxy

- 2.3创建配置文件

touch /usr/local/haproxy-1.7.3/conf/haproxy.cfg

- 2.4添加配置文件软连接

ln -s /usr/local/haproxy-1.7.3/conf/haproxy.cfg /etc/haproxy/haproxy.cfg

- 2.5拷贝错误页面

cp -r /root/haproxy-1.7.3/examples/errorfiles /usr/local/haproxy-1.7.3/errorfiles

- 2.6添加软连接

ln -s /usr/local/haproxy-1.7.3/errorfiles /etc/haproxy/errorfiles

- 2.7创建日志文件目录

mkdir -p /usr/local/haproxy-1.7.3/log

- 2.8创建日志文件

touch /usr/local/haproxy-1.7.3/log/haproxy.log

- 2.9添加软连接

ln -s /usr/local/haproxy-1.7.3/log/haproxy.log /var/log/haproxy.log

- 2.10拷贝启动文件

cp /root/haproxy-1.7.3/examples/haproxy.init /etc/rc.d/init.d/haproxy

- 2.11添加脚本执行权限

chmod +x /etc/rc.d/init.d/haproxy

- 2.12设置开机启动(6.x)

chkconfig haproxy on

- 2.13添加软连接

ln -s /usr/local/haproxy-1.7.3/sbin/haproxy /usr/sbin

##### 3.配置参数
###### 3.1haproxy.cfg

vi /usr/local/haproxy-1.7.3/conf/haproxy.cfg
global
chroot /usr/local/haproxy-1.7.3
user haproxy
group haproxy
node haproxy01
description haproxy01

defaults
listen admin_stats
bind 0.0.0.0:8089
stats enable
mode http
log global
stats uri /stats
stats realm Haproxy Statistics
stats auth admin:admin
stats admin if TRUE
stats refresh 30s
option httplog
listen mycat_service
bind :3306
mode tcp
option tcplog
option tcpka
balance roundrobin
server mycat01 172.16.3.142:8066 check port 48700 inter 2000ms weight 10 rise 2 fall 3
server mycat02 172.16.3.143:8066 check port 48700 inter 2000ms weight 10 rise 2 fall 3
listen mycat_admin
bind 0.0.0.0:9066
mode tcp
option tcplog
option tcpka
balance roundrobin
server mycat01 172.16.3.142:9066 check port 48700 inter 2000ms weight 10 rise 2 fall 3
server mycat02 172.16.3.143:9066 check port 48700 inter 2000ms weight 10 rise 2 fall 3

errorfile 403 /etc/haproxy/errorfiles/403.http
errorfile 500 /etc/haproxy/errorfiles/500.http
errorfile 502 /etc/haproxy/errorfiles/502.http
errorfile 503 /etc/haproxy/errorfiles/503.http
errorfile 504 /etc/haproxy/errorfiles/504.http

###### 3.1创建haproxy运行用户

groupadd haproxy
useradd -g haproxy haproxy
chown -R haproxy:haproxy /usr/local/haproxy-1.7.3/

###### 3.2配置rsyslog

/etc/rsyslog.conf

在 #### RULES #### 上面增加配置

$IncludeConfig /etc/rsyslog.d/*.conf

###### 3.3配置haproxy日志

vi /etc/rsyslog.d/haproxy.conf

增加下内容：

$ModLoad imudp
$UDPServerRun 514
local2.* /var/log/haproxy.log
& stop

###### 3.4重启rsyslog

systemctl restart rsyslog

###### 3.5配置网络IP转发

vi /etc/sysctl.conf
net.ipv4.ip_forward = 1
sysctl -p

echo "1" > /proc/sys/net/ipv4/ip_forward

###### 3.6在mycat服务器上增加mycat检查脚本
- 1)安装xinetd`yum install xinetd -y`
- 2)`/etc/xinetd.conf `最后增加`includedir /etc/xinetd.d`
- 3)增加端口服务

vi /etc/xinetd.d/mycat_status
service mycat_status
{
flags = REUSE
socket_type = stream
port = 48700
wait = no
user = root
server = /usr/local/mycat/bin/mycat_status
log_on_failure += USERID
disable = no
}

- 4)增加检查脚本

vi /usr/local/mycat/bin/mycat_status

!/bin/bash

/usr/local/mycat/bin/mycat_status

This script checks if a mycat server is healthy running on localhost. It will

return:

"HTTP/1.x 200 OK\r" (if mycat is running smoothly)

"HTTP/1.x 503 Internal Server Error\r" (else)

mycat=/usr/local/mycat/bin/mycat status | grep 'not running' | wc -l
if [ "$mycat" = "0" ];
then
/bin/echo -e "HTTP/1.1 200 OK\r\n"
else
/bin/echo -e "HTTP/1.1 503 Service Unavailable\r\n"
fi

- 5)增加可执行权限`chmod a+x /usr/local/mycat/bin/mycat_status`
- 6)增加服务

vi /etc/services
mycat_status 48700/tcp # mycat_status

- 7)启动网络服务`systemctl start xinetd`
- 8)检查端口状态

[root@mycat01 bin]# netstat -ano|grep 487
tcp6 0 0 :::48700 :::* LISTEN off (0.00/0/0)

> https://blog.csdn.net/u012758088/article/details/78654628

- centos7写入开机启动

> https://blog.csdn.net/chenxiabinffff/article/details/51374635

###### 3.7启动haproxy

/etc/init.d/haproxy start

###### 3.8启动报错

Mar 26 13:53:15 localhost haproxy[10067]: Server mycat_servers/mycat01 is DOWN, reason: Layer4 connection problem, info: "Connection refused", check duration: 0ms. 1 active and 0 backup servers left. 0 sessions active, 0 requeued, 0 remaining in queue.
Mar 26 13:53:15 haproxy01 haproxy: [ OK ]
Mar 26 13:53:15 haproxy01 systemd: Started SYSV: HA-Proxy is a TCP/HTTP reverse proxy which is particularly suited for high availability environments..
Mar 26 13:53:16 localhost haproxy[10067]: Server mycat_servers/mycat02 is DOWN, reason: Layer4 connection problem, info: "Connection refused", check duration: 0ms. 0 active and 0 backup servers left. 0 sessions active, 0 requeued, 0 remaining in queue.
Mar 26 13:53:16 localhost haproxy[10067]: proxy mycat_servers has no server available!

> 因为haproxy.conf里面的48700并没有在mycat服务器启动  
> https://blog.csdn.net/u012758088/article/details/78654628

###### 3.9登录状态页查看状态
http://172.16.3.140:8089/stats  
http://172.16.3.141:8089/stats
![image](https://note.youdao.com/yws/public/resource/174b4d43ac3b33531309a11b4cf768d1/xmlnote/748B3931578448F990B66FD92D3433DB/11327)

> 参考：  
> http://www.roncoo.com/course/view/f614343765bc4aac8597c6d8b38f06fd   
> https://www.jianshu.com/p/f4b02609a500    
> https://www.cnblogs.com/hk315523748/p/6094656.html  
> https://www.cnblogs.com/fxwl/p/7990906.html  

---

#### 压测
##### 1.sysbench

sysbench --test=/usr/local/share/sysbench/tests/include/oltp_legacy/select.lua --oltp-table-size=20000 --mysql-table-engine=innodb --mysql-user=mycatdb --mysql-password=1234567 --mysql-port=3306 --mysql-host=172.16.3.144 --mysql-db=test --max-requests=0 --max-time=120 --oltp-tables-count=20 --report-interval=10 --num_threads=10 prepare

##### 2.直接压测mysql

sysbench --test=/usr/local/share/sysbench/tests/include/oltp_legacy/select.lua --oltp-table-size=20000 --mysql-table-engine=innodb --mysql-user=root --mysql-password=123456 --mysql-port=3306 --mysql-host=172.16.3.153 --mysql-db=test --max-requests=0 --max-time=120 --oltp-tables-count=20 --report-interval=10 --num_threads=10 run

SQL statistics:
queries performed:
read: 2171226
write: 0
other: 0
total: 2171226
transactions: 2171226 (18092.46 per sec.)
queries: 2171226 (18092.46 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)

##### 3.压测haproxy VIP

压测haproxy的QPS/TPS比直接压测mysql更低？？！！差了将近4倍

SQL statistics:
queries performed:
read: 498794
write: 0
other: 0
total: 498794
transactions: 498794 (4156.37 per sec.)
queries: 498794 (4156.37 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)

##### 4压测mycat IP

sysbench --test=/usr/local/share/sysbench/tests/include/oltp_legacy/select.lua --oltp-table-size=20000 --mysql-table-engine=innodb --mysql-user=mycatdb --mysql-password=1234567 --mysql-port=8066 --mysql-host=172.16.3.143 --mysql-db=test --max-requests=0 --max-time=120 --oltp-tables-count=20 --report-interval=10 --num_threads=10 run

基本只有haproxy的一半

SQL statistics:
queries performed:
read: 233707
write: 0
other: 0
total: 233707
transactions: 233707 (1947.43 per sec.)
queries: 233707 (1947.43 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)

> https://my.oschina.net/shyloveliyi/blog/725810  
> http://blog.51cto.com/arthur376/2045596

#### 不重启mycat加载配置
- 更改server的配置可以`Mysql>reload @@config`
- 如果改了schema的配置,需要这个命令`Mysql>reload @@config_all`

---

---
#### 使用0.5的sysbench
> https://github.com/akopytov/sysbench/tree/0.5

#### 再增加一个mycat，压测haproxy VIP

OLTP test statistics:
queries performed:
read: 658118
write: 0
other: 0
total: 658118
transactions: 0 (0.00 per sec.)
read/write requests: 658118 (5484.25 per sec.)
other operations: 0 (0.00 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
```

仅仅增加15W多QPS

https://blog.csdn.net/zhxdick/article/details/50813081
http://blog.51cto.com/arthur376/2045596

转载于:https://www.cnblogs.com/jenvid/p/8663809.html

a82831154

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
haproxy mycat mysql 读写分离MHA高可用

主机IP信息hostnameIP172.16.3.140haproxy01172.16.3.141haproxy02172.16.3.142mycat01172.16.3.143mycat02172.16.3.152mha172.16.3.153master172.16.3.154slave01172.16.3....
复制链接

扫一扫