一。 高可用方案介绍
高可用通常也叫HA(High Available)。指的是,一台服务器宕机了,照样能对外提供服务。常用的高可用软件方案有:LVS、keepalived、Heartbeat、roseHA(roseHA为收费软件)等。
Mycat本身是无状态的,可以用HAProxy或四层交换机等设备组成Mycat的高可用集群,后端MySQL则配置为主从同步,此时整个系统就是高可用的,下图是一个典型的Mycat系统高可用的方案
haproxy也存在单点问题 可以使用 heartbeat或者keepalived做haproxy高可用
我这里就简单演示 单个haproxy反向代理2个mycat
二。 高可用配置
1》配置环境
数据库 之前的mysql双主双从环境(参考http://blog.csdn.net/liaomin416100569/article/details/78580382)
主:192.168.58.147:3306"
从1:192.168.58.149:3306
主备:192.168.58.151:3306
从2:192.168.58.150:3306
haproxy:
192.168.58.147
mycat:
192.168.58.149:8066
192.168.58.150:8066
2》mycat安装
首页 mycat.io右下角 点击 最新版 1.6下载 (http://dl.mycat.io/1.6-RELEASE/)点击下载linux版本
149和150下载
wget http://dl.mycat.io/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
解压查看目录结构
[root@node3 ~]# tar zxvf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
[root@node3 ~]# cd mycat
[root@node3 mycat]# ll
total 16
drwxr-xr-x 2 root root 4096 Nov 26 19:47 bin
drwxrwxrwx 2 root root 6 Feb 29 2016 catlet
drwxrwxrwx 4 root root 4096 Nov 26 19:47 conf
drwxr-xr-x 2 root root 4096 Nov 26 19:47 lib
drwxrwxrwx 2 root root 6 Oct 28 2016 logs
-rwxrwxrwx 1 root root 217 Oct 28 2016 version.txt
bin目录是可执行文件目录
Linux下运行:./mycat console,首先要chmod +x *
注:mycat支持的命令{ console | start | stop | restart | status | dump }
conf
logs是日志目录
conf是配置目录 几个重要的配置文件 都在里面
conf下配置文件内容和http://blog.csdn.net/liaomin416100569/article/details/78588872一致
schemal.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
<!-- 客户端创建的表 必须在这里定义否则 抛出 op table not in schema MYUSER -->
<table name="myuser" dataNode="dn1,dn2" rule="sharding-by-sex" />
</schema>
<!--分片节点 同一台主机可以有多个数据库充当节点-->
<dataNode name="dn1" dataHost="myhost" database="db1" />
<dataNode name="dn2" dataHost="myhost" database="db2" />
<!--分片主机 -->
<dataHost name="myhost" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- 主从结构 147是主节点 151是备节点 149和150是从节点 -->
<writeHost host="hostM1" url="192.168.58.147:3306" user="root"
password="root">
<!-- 使用客户端连接mycat后 默认使用readHost读操作 使用主节点进行写操作 如果主节点挂了 备节点称为主节点 -->
<readHost host="hostS1" url="192.168.58.149:3306" user="root" password="root" />
</writeHost>
<writeHost host="hostM2" url="192.168.58.151:3306" user="root"
password="root" >
<readHost host="hostS2" url="192.168.58.150:3306" user="root" password="root" />
</writeHost>
</dataHost>
</mycat:schema>
rule.xml配置
<?xml version="1.0" encoding="UTF-8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License");
- you may not use this file except in compliance with the License. - You
may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0
- - Unless required by applicable law or agreed to in writing, software -
distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT
WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the
License for the specific language governing permissions and - limitations
under the License. -->
<!DOCTYPE mycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://io.mycat/">
<tableRule name="sharding-by-sex">
<rule>
<columns>sex</columns>
<algorithm>hash-int</algorithm>
</rule>
</tableRule>
<!--
分片枚举
首先所有的数据节点 配置的索引从 0 开始 如果有三台 分别 是 0-1-2
hash-int表示int类型枚举值 被放到哪个数据节点
比如 数据库字段 sex 0表示男 1表示女 男性放在第二个数据节点 女性放在第一个数据节点
mapfile属性指定文件中 可以这样配置
性别 datanode索引
0 1
1 0
defaultNode表示 如果某些索引值 找不到对应的数据节点 数据存在于默认的该节点
-->
<function name="hash-int"
class="io.mycat.route.function.PartitionByFileMap">
<property name="mapFile">partition-sex.txt</property>
<property name="defaultNode">0</property>
</function>
</mycat:rule>
partition-sex.txt配置
0=1
1=0
server.xml配置允许客户端登录的用户名和密码以及有权限操作的逻辑库(该配置server.xml默认就有)
<user name="root" defaultAccount="true">
<property name="password">123456</property>
<property name="schemas">TESTDB</property>
<!-- 表级 DML 权限设置 -->
<!--
<privileges check="false">
<schema name="TESTDB" dml="0110" >
<table name="tb01" dml="0000"></table>
<table name="tb02" dml="1111"></table>
</schema>
</privileges>
-->
</user>
server修改配置
<system><property name="sequnceHandlerType">0</property></system>
编辑 sequence_conf.properties
MYUSER_SEQ.HISIDS=
MYUSER_SEQ.MINID=1
MYUSER_SEQ.MAXID=1000000000
MYUSER_SEQ.CURID=10
进入cat\bin目录下 运行
mycat start
查看端口是否启动
[root@node2 bin]# netstat -aon | grep 8066
tcp6 0 0 :::8066 :::* LISTEN off (0.00/0/0)
如果不能启动 查看到端口 可以查看 logs\wrapper.log启动日志
使用客户端连接测试(分别连接150和149):
C:\Users\jiaozi>mysql -uroot -p123456 -P8066 -h192.168.58.150
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCl
DB)
Copyright (c) 2000, 2011, 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 statemen
mysql> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB |
+----------+
1 row in set (0.01 sec)
mysql> use TESTDB;
Database changed
mysql> show tables;
+------------------+
| Tables in TESTDB |
+------------------+
| myuser |
+------------------+
1 row in set (0.01 sec)
mysql> select * from myuser;
+----+-------+------+
| id | uname | sex |
+----+-------+------+
| 6 | zs | 0 |
| 7 | ls | 1 |
+----+-------+------+
2 rows in set (0.47 sec)
3》配置haproxy4层反向代理
147安装haproxy 参考(http://blog.csdn.net/liaomin416100569/article/details/78641567)
修改配置文件/etc/haproxy/haproxy.cfg
#---------------------------------------------------------------------
# Example configuration for a possible web application. See the
# full configuration options online.
#
# http://haproxy.1wt.eu/download/1.4/doc/configuration.txt
#
#---------------------------------------------------------------------
#---------------------------------------------------------------------
# Global settings
#---------------------------------------------------------------------
global
# to have these messages end up in /var/log/haproxy.log you will
# need to:
#
# 1) configure syslog to accept network log events. This is done
# by adding the '-r' option to the SYSLOGD_OPTIONS in
# /etc/sysconfig/syslog
#
# 2) configure local2 events to go to the /var/log/haproxy.log
# file. A line like the following can be added to
# /etc/sysconfig/syslog
#
# local2.* /var/log/haproxy.log
#
log 127.0.0.1 local2
chroot /var/lib/haproxy
pidfile /var/run/haproxy.pid
maxconn 4000
user haproxy
group haproxy
daemon
# turn on stats unix socket
stats socket /var/lib/haproxy/stats
#---------------------------------------------------------------------
# common defaults that all the 'listen' and 'backend' sections will
# use if not designated in their block
#---------------------------------------------------------------------
defaults
mode tcp
log global
option dontlognull
option redispatch
retries 3
timeout queue 1m
timeout connect 10s
timeout client 1m
timeout server 1m
timeout check 10s
maxconn 3000
listen 8099
bind *:8886
mode tcp
server node2 192.168.58.149:8066 check
server node3 192.168.58.150:8066 check
启动haproxy 测试查看代理端口 8886
[root@node1 haproxy]# service haproxy start
Redirecting to /bin/systemctl start haproxy.service
[root@node1 haproxy]# netstat -aon | grep 8886
tcp 0 0 0.0.0.0:8886 0.0.0.0:* LISTEN off (0.00/0/0)
unix 3 [ ] STREAM CONNECTED 18886 /var/run/dbus/system_bus_socket
成功
C:\Users\jiaozi>mysql -uroot -p123456 -P8886 -h192.168.58.147
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCloun
DB)
Copyright (c) 2000, 2011, 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.
mysql>