資料庫中間件mycat安裝與使用

1.下載

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

2.安裝

# tar -zxvf ~/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz -C /usr/local

3.配置schema.xml

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

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

    <schema name="testMyCat" checkSQLschema="false" sqlMaxLimit="100">
<!--table 表名-->

        <table name="order"  type="global" dataNode="order" />
        <table name="orderDetail"  type="global" dataNode="orderDetail" />
        <table name="commodity"  type="global" dataNode="order" />
    </schema>
<!---datanode數據節點，database對應各個datahost中的資料庫名，datahost用於配置資料庫伺服器地址->

    <dataNode name="order" dataHost="host2" database="jxtest" />
    <dataNode name="orderDetail" dataHost="host1" database="usercenter" />
    <dataHost name="host1" maxCon="1000" minCon="10" balance="0"
              writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="hostM1" url="192.168.3.30:3306" user="root" password="Gaia@Works12345^&amp;*()" />
    </dataHost>
    <dataHost name="host2" maxCon="1000" minCon="10" balance="0"
              writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="hostM2" url="192.168.3.62:4000" user="root" password="" />
    </dataHost>
</mycat:schema>

mycat就是把跨庫的資料庫表，彙集到schema（新庫）中，然後就可以join查詢了

4.配置server.xml

# vim server.xml       #然後找到user節點配置

<user name="root">
        <property name="password">123456</property>
        <property name="schemas">testMyCat</property>
    </user>
<!--user.name登陸用戶名，password登錄密碼，schemas該用戶可使用的資料庫名，testMayCat對應schema.xml文件中配置的schema-->

5.啟動mycat

# cd /usr/local/mycat/bin
# ./mycat start

6.查看mycat狀態

# ./mycat status
Mycat-server is running (29287).

7.使用mysql客戶端連接mycat

然後就可以使用mysql語法查詢了

8. 兩個表的join

/*!mycat:catlet=io.mycat.catlets.ShareJoin */
SELECT
*
FROM t_order t
INNER JOIN order_detail td ON t.orderId = td.orderId

9.三個表的join

/*!mycat:catlet=io.mycat.catlets.ShareJoin */
SELECT
*
FROM t_order t
INNER JOIN order_detail td ON t.orderId = td.orderId
INNER JOIN commodity c ON td.commodityId = c.id

可見，三個表的join，mycat是不支持的