Mycat 读写分离

实验环境

主机名	IP地址	系统	软件	备注
mysql-master	200.200.2.10	CentOS7.5	mysql5.6	主库
mysql-slave	200.200.2.11	CentOS7.5	mysql5.6	从库
mycat	200.200.2.12	CentOS7.5	mycat	中间件

数据库主从复制请参考前边的文章进行配置

mycat服务器

安装jdk环境

tar xf jdk-8u60-linux-x64.tar.gz
mv jdk1.8.0_60/ /usr/local/java8

配置java环境

vim /etc/profile.d/java.sh
cat /etc/profile.d/java.sh
export JAVA_HOME=/usr/local/java8
export CLASSPATH=$JAVA_HOME/lib/
export PATH=$PATH:$JAVA_HOME/bin
加载环境变量
source /etc/profile

查看java环境版本

java -version

下载mycat

wget http://dl.mycat.io/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz

解压源码包

tar xf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
mv mycat/ /usr/local/

创建程序用户

useradd -M -s /sbin/nologin mycat
chwon -R mycat:mycat /usr/local/mycat/

添加mycat命令到环境

vim /etc/profile.d/mycat.sh
cat /etc/profile.d/mycat.sh
export MYCAT_HOME=/usr/local/mycat
export PATH=$PATH:$MYCAT_HOME/bin
加载环境变量
source /etc/profile.d/mycat.sh

更改配置文件

vim /usr/local/mycat/conf/schema.xml

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

 <schema name="HEADSHOW" checkSQLschema='flase' dataNode='dn1'></schema>　　定义一个逻辑库HEADSHOW 节点在dn1
    <dataNode name="dn1" dataHost="localhost1" database="abc" />    　　　　 映射成为一个真实的库名
    <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"　　　　与上边的localhost进行管理 最大连接数1000 最小连接数10 balance 0是不开始 1是wirehost内部进行随机负载，2是dataNode内部进行随机负载
writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>　　　　　　　　　　　　　　　　　　　查看哪些用户
        <!-- can have multi write hosts -->
        <writeHost host="200.200.2.10" url="200.200.2.10:3306" user="root"　写入的数据写入到主库200.200.2.10 3306端口 登陆用户为root用户
                   password="123456">　　　　　　　　　　　　　　　　　　　　　　　 登陆密码为123456
            <!-- can have multi read hosts -->
            <readHost host="200.200.2.11" url="200.200.2.11:3306" user="root" password="123456" />　　读的数据将从从库200.200.2.11中查询 登陆用户为root密码是123456
        </writeHost>
        <writeHost host="hostS1" url="localhost:3316" user="root"　　　　　　　备用　
                   password="123456" />
        <!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
    </dataHost>
    <!--
        <dataHost name="sequoiadb1" maxCon="1000" minCon="1" balance="0" dbType="sequoiadb" dbDriver="jdbc">
        <heartbeat>         </heartbeat>
         <writeHost host="hostM1" url="sequoiadb://1426587161.dbaas.sequoialab.net:11920/SAMPLE" user="jifeng"     password="jifeng"></writeHost>
         </dataHost>

      <dataHost name="oracle1" maxCon="1000" minCon="1" balance="0" writeType="0"     dbType="oracle" dbDriver="jdbc"> <heartbeat>select 1 from dual</heartbeat>
        <connectionInitSql>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'</connectionInitSql>
        <writeHost host="hostM1" url="jdbc:oracle:thin:@127.0.0.1:1521:nange" user="base"     password="123456" > </writeHost> </dataHost>

        <dataHost name="jdbchost" maxCon="1000"     minCon="1" balance="0" writeType="0" dbType="mongodb" dbDriver="jdbc">
        <heartbeat>select     user()</heartbeat>
        <writeHost host="hostM" url="mongodb://192.168.0.99/test" user="admin" password="123456" ></writeHost> </dataHost>

        <dataHost name="sparksql" maxCon="1000" minCon="1" balance="0" dbType="spark" dbDriver="jdbc">
        <heartbeat> </heartbeat>
         <writeHost host="hostM1" url="jdbc:hive2://feng01:10000" user="jifeng"     password="jifeng"></writeHost> </dataHost> -->

    <!-- <dataHost name="jdbchost" maxCon="1000" minCon="10" balance="0" dbType="mysql"
        dbDriver="jdbc"> <heartbeat>select user()</heartbeat> <writeHost host="hostM1"
        url="jdbc:mysql://localhost:3306" user="root" password="123456"> </writeHost>
        </dataHost> -->
</mycat:schema>

vim /usr/local/mycat/conf/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://io.mycat/">
    <system>
    <property name="useSqlStat">1</property>
    <property name="useGlobleTableCheck">0</property>  
    <property name="sequnceHandlerType">2</property>
    <property name="processorBufferPoolType">0</property>
    <property name="useHandshakeV10">1</property>
    <property name="useSqlStat">0</property> 
    <property name="useGlobleTableCheck">0</property> 
    <property name="serverPort">3310</property>　　　　　　端口号 
    <property name="managerPort">9066</property>　　　　　 端口号
    <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>
            <!--
            off heap for merge/order/group/limit      1开启   0关闭
        -->
        <property name="useOffHeapForMerge">1</property>

        <!--
            单位为m
        -->
        <property name="memoryPageSize">1m</property>

        <!--
            单位为k
        -->
        <property name="spillsFileBufferSize">1k</property>

        <property name="useStreamOutput">0</property>

        <!--
            单位为m
        -->
        <property name="systemReserveMemorySize">384m</property>


        <!--是否采用zookeeper协调切换  -->
        <property name="useZKSwitch">true</property>


    </system>
    
    <!-- 全局SQL防火墙设置 -->
    <!-- 
    <firewall> 
       <whitehost>
          <host host="127.0.0.1" user="mycat"/>
          <host host="127.0.0.2" user="mycat"/>
       </whitehost>
       <blacklist check="false">
       </blacklist>
    </firewall>
    -->
    
    <user name="root">
        <property name="password">123456</property>
        <property name="schemas">HEADSHOW</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>

    <user name="mycat">
        <property name="password">123456</property>
        <property name="schemas">HEADSHOW</property>
        <property name="readOnly">true</property>
    </user>

</mycat:server>

启动mycat服务

mycat start

下载mariadb，访问测试

yum -y install mariadb
mysql -uroot -p123456 -h 127.0.0.1 -P9066

测试方法

将主从复制关闭，在mycat中插入数据

查看主库中是否存在数据，mycat查询数据是否存在

主库中存在数据则证明通过mycat写入功能正常开启

mycat中查询不到数据则mycat读取是从从服务器上进行读取