mycat 读写分离配置

2020-03-24


机器 ip 角色
mysql-cluster1 192.168.8.85
mysql-cluster1 192.168.8.87

1.安装mycat

wget https://github.com/MyCATApache/Mycat-download/blob/master/1.5-RELEASE/Mycat-server-1.5.1-RELEASE-20161130213509-linux.tar
.gz

2.调整schema.xml 文件

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://org.opencloudb/" >


    <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
        <table name="item" primaryKey="id" dataNode="dn1" />
    </schema>

    <dataNode name="dn1" dataHost="c1" database="mycat_db01" />

    <!-- 读写分离 -->
    <dataHost name="c1" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native" switchType="-1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="WHost" url="192.168.8.85:3306" user="xiaozhao" password="xiaozhao">
            <readHost host="RHost" url="192.168.8.87:3306" user="xiaozhao" password="xiaozhao" />
        </writeHost>
    </dataHost>

</mycat:schema>

3.调整server.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:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://org.opencloudb/">
        <system>
        <property name="defaultSqlParser">druidparser</property>
      <!--  <property name="useCompression">1</property>--> <!--1为开启mysql压缩协议-->
        <!-- <property name="processorBufferChunk">40960</property> -->
        <!-- 
        <property name="processors">1</property> 
        <property name="processorExecutor">32</property> 
         -->
                <!--默认是65535 64K 用于sql解析时最大文本长度 -->
                <!--<property name="maxStringLiteralLength">65535</property>-->
                <!--<property name="sequnceHandlerType">0</property>-->
                <!--<property name="backSocketNoDelay">1</property>-->
                <!--<property name="frontSocketNoDelay">1</property>-->
                <!--<property name="processorExecutor">16</property>-->
                <!-- 
                        <property name="mutiNodeLimitType">1</property> 0:开启小数量级(默认) ;1:开启亿级数据排序
                <property name="mutiNodePatchSize">100</property> 亿级数量排序批量
                        <property name="processors">32</property> <property name="processorExecutor">32</property> 
                        <property name="serverPort">8066</property> <property name="managerPort">9066</property> 
                        <property name="idleTimeout">300000</property> <property name="bindIp">0.0.0.0</property> 
                        <property name="frontWriteQueueSize">4096</property> <property name="processors">32</property> -->
        </system>
        <user name="xiaozhao">
                <property name="password">xiaozhao</property>
                <property name="schemas">TESTDB</property>
        </user>

        <!-- 
        <quarantine> 
           <whitehost>
              <host host="127.0.0.1" user="mycat"/>
              <host host="127.0.0.2" user="mycat"/>
           </whitehost>
       <blacklist check="false"></blacklist>
        </quarantine>
        -->

</mycat:server>

4.mycat状态查看

Mycat端口8066(数据端口)和9066(管理端口)
启动

# ./mycat start

需要根据dataNode中的database预先在物理数据库中创建mycat_db01
登陆mycat

# cd  cd /usr/local/mysql/bin/
# ./mysql –uxiaozhao –pxiaozhao –h192.168.8.85 –P8066 –DTESTDB

创建表

mysql> create table item(
id int(11),
name varchar(20);

初始化数据

mysql> insert into item (id,name) values (1,'xiaozhao');

开启Mycat 日志DEBUG模式,修改log4j 2.xml

<?xml version="1.0" encoding="UTF-8"?>
<!--
 - Copyright 1999-2012 Alibaba Group.
 -  
 - 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 log4j:configuration SYSTEM "log4j.dtd">
<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">
<appender name="ConsoleAppender" class="org.apache.log4j.ConsoleAppender">
  <layout class="org.apache.log4j.PatternLayout">
   <param name="ConversionPattern" value="%d{MM-dd HH:mm:ss.SSS}  %5p [%t] (%F:%L) -%m%n" />
  </layout>
 </appender>
  <appender name="FILE" class="org.apache.log4j.RollingFileAppender">
    <param name="file" value="${MYCAT_HOME}/logs/mycat.log" />
...skipping...
<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">
<appender name="ConsoleAppender" class="org.apache.log4j.ConsoleAppender">
  <layout class="org.apache.log4j.PatternLayout">
   <param name="ConversionPattern" value="%d{MM-dd HH:mm:ss.SSS}  %5p [%t] (%F:%L) -%m%n" />
  </layout>
 </appender>
  <appender name="FILE" class="org.apache.log4j.RollingFileAppender">
    <param name="file" value="${MYCAT_HOME}/logs/mycat.log" />
    <param name="Append" value="false"/>
    <param name="MaxFileSize" value="1000KB"/>
    <param name="MaxBackupIndex" value="10"/> 
    <param name="encoding" value="UTF-8" />
    <layout class="org.apache.log4j.PatternLayout">
      <param name="ConversionPattern" value="%d{MM/dd HH:mm:ss.SSS}  %5p [%t] (%F:%L) -%m%n" />
    </layout>
  </appender>
 
  
  <root>
    <level value="debug" />
    <appender-ref ref="FILE" />
     <!--<appender-ref ref="FILE" />-->
  </root>

</log4j:configuration>

5. 验证读写分离

# cd /usr/local/mysql/bin
# ./mysql -uxiaozhao -pxiaozhao -P8066 -h192.168.8.85
mysql> show databases;
mysql> USE TESTDB;
mysql> insert into item (id,name) values (1,'xiaozhao');

查看mycat.log日志

 
image.png

从日志可以看出,192.168.8.85 参与了写操作

mysql> select * from item;
 
image.png

从日志可以看出,192.168.8.87 参与了读操作

6. mycat 安装(192.168.8.85)

首先装zookeeper

6.1 zookeeper安装

# wget http://mirror.bit.edu.cn/apache/zookeeper/zookeeper-3.4.10/zookeeper-3.4.10.tar.gz
# tar -zxvf zookeeper-3.4.10.tar.gz
# cd zookeeper-3.4.10/bin
# cp zoo_sample.cfg zoo.cfg
# ./zkServer.sh start
# ps -ef|grep zookeeper

6.2 mycat-web安装

# wget https://raw.githubusercontent.com/MyCATApache/Mycat-download/master/mycat-web-1.0/Mycat-web-1.0-SNAPSHOT-20160617163048-l
inux.tar.gz
# tar -zxvf Mycat-web-1.0-SNAPSHOT-20160617163048-linux.tar.gz
# cd /root/soft/mycat-web/mycat-web/WEB-INF/classes

修改zookeeper地址:
# vim mycat.properties
zookeeper=127.0.0.1:2181

启动:
cd /root/soft/mycat-web
./start.sh &

http://192.168.8.85:8082/mycat/

先启动zookeeper然后再启动mycat-web,检查zookeeper和mycat-web是否启动

[root@mysql-cluster1]# netstat -ntpl |grep 8082                                                                                                                                      

tcp        0      0 :::8082                     :::*                        LISTEN      18288/java         
[root@mysql-cluster1]# netstat -ntpl |grep 2181

tcp        0      0 :::2181                     :::*                        LISTEN      18251/java         

7.打开mycat-web窗口

输入网址:http://192.168.8.85:8082/mycat/

 
image.png

 

8.配置mycat服务

 
image.png

配置成功后见


 
image.png


作者:若有所思11
链接:https://www.jianshu.com/p/66851461cadf
来源:简书
著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。

服务支持

我们珍惜您每一次在线询盘,有问必答,用专业的态度,贴心的服务。

让您真正感受到我们的与众不同!

合作流程

网站制作流程从提出需求到网站制作报价,再到网页制作,每一步都是规范和专业的。

常见问题

提供什么是网站定制?你们的报价如何?等网站建设常见问题。

售后保障

网站制作不难,难的是一如既往的热情服务及技术支持。我们知道:做网站就是做服务,就是做售后。

提交表单信息,免费获取项目方案及报价!

×