主机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 mycat01172.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
1.2安装jdk
- 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
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)
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: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
<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.
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)]> show databases;
+----------+
| DATABASE |
+----------+
| db1 |
| test |
| tpcc100 |
+----------+
3 rows in set (0.01 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 haproxy01172.16.3.141 haproxy02
1.下载解压编译安装
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
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
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 haproxy01172.16.3.141 haproxy02
1.部署haproxy
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)
- 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
参考：
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
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/725810
http://blog.51cto.com/arthur376/2045596
不重启mycat加载配置
- 更改server的配置可以
Mysql>reload @@config - 如果改了schema的配置,需要这个命令
Mysql>reload @@config_all
使用0.5的sysbench
再增加一个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