主机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/1190000009520414https://www.cnblogs.com/raphael5200/p/5884931.htmlhttps://www.cnblogs.com/hk315523748/p/6094656.htmlhttp://www.roncoo.com/course/view/f614343765bc4aac8597c6d8b38f06fdhttps://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)下载jdk
wget 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. [email protected] 12:04: [(none)]> show databases; +----------+ | DATABASE | +----------+ | db1 | | test | | tpcc100 | +----------+ 3 rows in set (0.00 sec) [email protected] 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) 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. [email protected] 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) [email protected] 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 <level value="debug" />
- 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) 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. [email protected] 12:12: [(none)]> select user(); +----------------+ | USER() | +----------------+ | [email protected] | +----------------+ 1 row in set (0.00 sec) [email protected] 12:12: [(none)]> show databases; +----------+ | DATABASE | +----------+ | db1 | | test | | tpcc100 | +----------+ 3 rows in set (0.01 sec) [email protected] 12:12: [(none)]> use db1; Database changed [email protected] 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]
- 只读账号进行写数据则报错
[email protected] 13:28: [db1]> create table t5 (id int); ERROR 1495 (HY000): User readonly [email protected] 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个节点上启动keepalivedsystemctl 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 " (if mycat is running smoothly) # # "HTTP/1.x 503 Internal Server Error " (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 " else /bin/echo -e "HTTP/1.1 503 Service Unavailable " 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/statshttp://172.16.3.141:8089/stats
参考：http://www.roncoo.com/course/view/f614343765bc4aac8597c6d8b38f06fdhttps://www.jianshu.com/p/f4b02609a500https://www.cnblogs.com/hk315523748/p/6094656.htmlhttps://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
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 run
# 压测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/725810http://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/50813081http://blog.51cto.com/arthur376/2045596