MyCat实现MySQL5.7读写分离与分库分表

[环境]

MySQL主库：192.168.92.201

MySQL从库：192.168.92.202

MyCat：1.6.7.1

JDK：jdk-8u131-linux-x64.rpm

1.建立MySQL主从库

--主库操作
create user 'ms'@'192.168.92.202' identified by '密码';
GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO ms@'192.168.92.202' IDENTIFIED BY '密码'; 
flush privileges;

--从库操作
CHANGE MASTER TO MASTER_HOST='192.168.92.201', MASTER_USER='ms', MASTER_PASSWORD='密码', MASTER_LOG_FILE='mysql-bin.000011', MASTER_LOG_POS=154;
start slave;

2.下载mycat 

安装mycat之前，需要安装jdk。

wget  http://dl.mycat.io/1.6.7.1/Mycat-server-1.6.7.1-release-20190627191042-linux.tar.gz
#解压
tar -xzvf Mycat-server-1.6.7.1-release-20190627191042-linux.tar.gz

3.参数文件配置

3.1 wrapper.conf 内存调整

# Java Additional Parameters
#wrapper.java.additional.1=
wrapper.java.additional.1=-DMYCAT_HOME=.
wrapper.java.additional.2=-server
wrapper.java.additional.3=-XX:MaxPermSize=64M
wrapper.java.additional.4=-XX:+AggressiveOpts
wrapper.java.additional.5=-XX:MaxDirectMemorySize=1G
wrapper.java.additional.6=-Dcom.sun.management.jmxremote
wrapper.java.additional.7=-Dcom.sun.management.jmxremote.port=1984
wrapper.java.additional.8=-Dcom.sun.management.jmxremote.authenticate=false
wrapper.java.additional.9=-Dcom.sun.management.jmxremote.ssl=false
wrapper.java.additional.10=-Xmx1G
wrapper.java.additional.11=-Xms512M

3.2 server.xml 配置白名单和用户信息

        <firewall>
           <whitehost>
              <host host="172.*.*.*" user="root"/>
              <host host="192.168.*.*" user="root"/>
           </whitehost>
       <blacklist check="false">
       </blacklist>
        </firewall>

        <user name="root" defaultAccount="true">
                <property name="password">密码</property>
                <property name="schemas">product</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>

3.3 schema.xml服务器节点

schema 逻辑汇总数据库 ——> 数据库分库dataNode ——> 数据库服务器节点dataHost

mod-long规则对t1表的主键进行求模，将记录分散到3个库的t1表中。

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


<schema name="product" checkSQLschema="false" sqlMaxLimit="100"  >
<table name="t1" primaryKey="id" dataNode="dn1,dn2,dn3" rule="mod-long"/>
</schema>

    <dataNode name="dn1" dataHost="node1" database="product01" />
    <dataNode name="dn2" dataHost="node1" database="product02" />
    <dataNode name="dn3" dataHost="node1" database="product03" />

    <dataHost name="node1" maxCon="3000" minCon="100" balance="3" writeType="0" dbType="mysql" dbDriver="native" switchType="-1">
        <heartbeat>select user()</heartbeat>
        <writeHost host="mysql-master01" url="192.168.92.201:3306" user="root" password="密码">
            <readHost host="mysql-slave01" url="192.168.92.202:3306" user="root" password="密码" />
        </writeHost>
    </dataHost>
</mycat:schema>

3.4 rule.xml 修改表的主键名（如果不是id的话）

4.启动与停止

cd /app/mycat/bin
./mycat start
./mycat stop

5.验证

mycat启动需要等待2分钟左右，3306端口需要启动。