Mycat教程学习笔记

1.读写分离

2.原理

MyCat的原理中最重要的就是“拦截”，它拦截了用户发过来的SQL语句，首先对SQL语句进行一些特定的分析，如分片分析，路由分析，读写分离分析，缓存分析等，再将此SQL发往后端的真实数据库，并将返回的结果做适当的处理，最终再返回给用户，通过此种方式把数据库的分布式从代码中解耦出来。

3.垂直分库

修改schema.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" dataNode="dn1" >
                <table name="rider" dataNode="dn2"></table>
        </schema>
        <dataNode name="dn1" dataHost="localhost1" database="orders"/>
        <dataNode name="dn2" dataHost="localhost2" database="orders"/>
        <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="hostM1" url="192.168.5.129:3306" user="root"
                                   password="123456">
                </writeHost>
        </dataHost>
        <dataHost name="localhost2" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="hostM1" url="192.168.5.130:3306" user="root" password="123456">
                </writeHost>
        </dataHost>

</mycat:schema>  

修改完schema.xml配置文件后，需要重启MyCat

#在/usr/local/MyCat/bin目录下
#两种启动方式(1.控制台启动 2.后台启动)
#1.控制台启动
./mycat console
#2.后台启动
./mycat start

启动后查看MyCat状态

./mycat status

再回到mycat中进行切库操作。

use TESTDB;

4.水平分表

修改schema.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" dataNode="dn1" >
                <table name="rider" dataNode="dn2"></table>
                <table name="fullrider" dataNode="dn1,dn2" rule="mod_rule">
                </table>
        </schema>
        <dataNode name="dn1" dataHost="localhost1" database="orders"/>
        <dataNode name="dn2" dataHost="localhost2" database="orders"/>
        <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="hostM1" url="192.168.5.129:3306" user="root"
                                   password="123456">
                </writeHost>
        </dataHost>
        <dataHost name="localhost2" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="hostM1" url="192.168.5.130:3306" user="root"                                   password="123456">
                </writeHost>
        </dataHost>

</mycat:schema>

修改rule.xml

<tableRule name="mod_rule">
                <rule>
                        <columns>rider_id</columns>
                        <algorithm>mod-long</algorithm>
                </rule>
        </tableRule>

<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
                <!-- how many data nodes -->
                <property name="count">2</property>
        </function>

5.关联查询，分片join

修改schema.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" dataNode="dn1" >
                <table name="rider" dataNode="dn2"></table>
                <table name="fullrider" dataNode="dn1,dn2" rule="mod_rule">
                        <childtable name="oldtonew" primaryKey="id" joinKey="fullrider_id" parentKey="id"/>
                </table>
        </schema>
        <dataNode name="dn1" dataHost="localhost1" database="orders"/>
        <dataNode name="dn2" dataHost="localhost2" database="orders"/>
        <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="hostM1" url="192.168.5.129:3306" user="root"
                                   password="123456">
                </writeHost>
        </dataHost>
        <dataHost name="localhost2" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="hostM1" url="192.168.5.130:3306" user="root"                                   password="123456">
                </writeHost>
        </dataHost>
</mycat:schema>

6.user标签权限控制

修改server.xml

<user name="mycat">
                <property name="password">123456</property>
                <property name="schemas">TESTDB</property>
</user>

<user name="user">
                <property name="password">user</property>
                <property name="schemas">TESTDB</property>
                <property name="readOnly">true</property>
</user>

标签属性	说明
name	应用连接中间件逻辑库的用户名
password	该用户对应的密码
TESTDB	应用当前连接的逻辑库中所对应的逻辑表，schemas中可以配置一个或多个
readOnly	应用连接中间件逻辑库所具有的权限，true为只读，false为可读可写，默认为false

7.privileges标签权限控制

修改server.xml配置文件的privileges部分

在user标签下的privileges标签可以对逻辑库(schema)、表(table)进行精细化的DML权限控制

privileges标签下的check属性，如为true开启权限检查，为flase不开启，默认为false

<privileges check="false">
                        <schema name="TESTDB" dml="0110" >
                                <table name="tb01" dml="0000"></table>
                                <table name="tb02" dml="1111"></table>
                        </schema>
</privileges> 

DML 权限	增加（insert）	更新（update）	查询（select）	删除（select）
0000	禁止	禁止	禁止	禁止
0010	禁止	禁止	允许	禁止
1110	允许	允许	允许	禁止
1111	允许	允许	允许	允许