Mycat读写分离

一、mycat读写分离

1.1 安装环境
tar xf jdk-8u241-linux-x64.tar.gz -C /data/software/
echo "export JAVA_HOME=/data/software/jdk1.8.0_241" >> /etc/profile
echo "export PATH=\$JAVA_HOME/bin:\$PATH" >> /etc/profile
source /etc/profile
java -version
1.2 解压安装
#http://www.mycat.org.cn/
wget http://dl.mycat.org.cn/1.6.7.6/20201126013625/Mycat-server-1.6.7.6-release-20201126013625-linux.tar.gz
tar xf Mycat-server-1.6.7.6-release-20201126013625-linux.tar.gz -C /usr/local/
1.3 创建用户
useradd mycat
chown mycat.mycat -R /usr/local/mycat
cd /usr/local/mycat/
chmod 750 -R catlet conf logs
1.4 环境变量
echo "export PATH=/usr/local/mycat/bin:\$PATH" >> /home/mycat/.bashrc
source ~/.bashrc
1.5 授权连接
# 后端数据库授权mycat连接用户(有两个mycat中间件)
grant all on db1.* to 'mycat'@'10.0.0.10' identified by '123123';
grant all on db1.* to 'mycat'@'10.0.0.22' identified by '123123';
flush privileges;
1.6 配置服务
cd /usr/local/mycat/conf/;cp server.xml server.xml_bak #备份连接配置
cp schema.xml schema.xml_bak #备份数据库配置
vim server.xml
#
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
    <system>
        <property name="nonePasswordLogin">0</property> 
        <property name="useHandshakeV10">1</property>
        <property name="useSqlStat">0</property>  
        <property name="useGlobleTableCheck">0</property> 
        <property name="sqlExecuteTimeout">300</property> 
        <property name="sequnceHandlerType">2</property>
        <property name="sequnceHandlerPattern">(?:(\s*next\s+value\s+for\s*MYCATSEQ_(\w+))(,|\)|\s)*)+</property>
        <property name="subqueryRelationshipCheck">false</property> 
        <property name="processorBufferPoolType">0</property>
        <property name="handleDistributedTransactions">0</property>
        <property name="useOffHeapForMerge">0</property>
        <property name="memoryPageSize">1m</property>
        <property name="spillsFileBufferSize">1k</property>
        <property name="useStreamOutput">0</property>
        <property name="systemReserveMemorySize">384m</property>
        <property name="useZKSwitch">false</property>
        <property name="strictTxIsolation">false</property>
        <property name="useZKSwitch">true</property>
    </system>

    <!-- 定义客户端连接虚拟用户demo,密码123123,虚拟数据库db1,并设为默认账号 -->
    <user name="demo" defaultAccount="true">
        <property name="password">123123</property>
        <property name="schemas">db1</property>
    </user>

</mycat:server>
vim schema.xml
#
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

    <!-- 1、匹配虚拟数据库db1(建议与真实库名一致,不然可能会出现问题),定义数据节点dn1  -->
    <schema name="db1" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
    </schema>

    <!-- 2、匹配数据节点dn1,定义数据主机池dbhostA,后端真实数据库db1  -->
    <dataNode name="dn1" dataHost="dbhostA" database="db1" />

    <!-- 3、匹配数据库主机池dbhostA,定义heartbeat心跳检测,定义读与写的连接信息  -->
    <dataHost name="dbhostA" maxCon="1000" minCon="10" balance="3"
              writeType="0" dbType="mysql" dbDriver="native" switchType="-1"  slaveThreshold="100">

        <heartbeat>select user()</heartbeat>

        <writeHost host="hostM" url="10.0.0.10:3306" user="mycat" password="123123">
            <readHost host="hostS" url="10.0.0.22:3306" user="mycat" password="123123"></readHost>
        </writeHost>
    </dataHost>

</mycat:schema>
1.7 启动服务
mycat console #测试启动
mycat start   #后台启动
1.8 连接测试
mysql -udemo -h10.0.0.10 -P8066 db1 -p #在应用节点进行连接测试,使用定义的虚拟用户
mysql -udemo -h10.0.0.22 -P8066 db1 -p
1.9 错误问题
wrapper  | Startup failed: Timed out waiting for a signal from the JVM. #启动console测试时报错
#
echo "wrapper.startup.timeout=300" >> /usr/local/mycat/conf/wrapper.conf #在最后添加配置,调整启动时间

二、mycat高负载

2.1 创建容器
mkdir /data/software/haproxy
docker pull haproxy:alpine
docker create --name haproxy --net host -v /data/software/haproxy:/usr/local/etc/haproxy haproxy:alpine
2.2 定义配置
vim /data/software/haproxy/haproxy.cfg
###########全局配置#########
global
    daemon                       #后台运行
    nbproc 3                     #进程数

########默认配置############
defaults
    mode tcp                 #默认的模式mode[tcp|http|health],tcp是4层,http是7层,health只会返回OK
    retries 3                #三次连接失败就认为是服务器不可用,也可以通过后面设置
    option redispatch        #当serverId对应的服务器挂掉后,强制定向到其他健康的服务器
    option abortonclose      #当服务器负载很高的时候,自动结束掉当前队列处理比较久的链接
    maxconn 65535            #默认的最大连接数
    timeout connect 5000ms   #连接超时
    timeout client  30000ms  #客户端超时
    timeout server  30000ms  #服务器超时
    log 127.0.0.1 local3 info#日志级别[err warning info debug]


########mycat代理配置#################
listen nginx
    bind 10.0.0.26:10000
    mode tcp
    balance roundrobin
    server as1 10.0.0.10:8066 weight 1 maxconn 60000 check inter 3s
    server as2 10.0.0.22:8066 weight 1 maxconn 60000 check inter 3s


########统计页面配置(可选,看需求吧)########
listen admin
    bind 0.0.0.0:8888
    mode http
    maxconn 10
    option httplog   #采用http日志格式
    stats refresh 3s #统计页面自动刷新时间
    stats uri /stats #统计页面url
    stats realm RootopHaproxy #统计页面密码框上提示文本
    stats auth admin:admin    #统计页面用户名和密码设置
    stats hide-version        #隐藏统计页面上HAProxy的版本信息
docker start haproxy  #运行服务
2.3 访问测试
mysql -udemo -h10.0.0.26 -P10000 db1 -p #访问http://10.0.0.26:8888/stats查看状态