Mycat的使用 - 02.配置

上次介紹Mycat概念時, 說到了分片表, 下面就從配置一個分片表入手簡述Mycat的配置.

schema.xml是首先遇到的配置文件, 其管理著Mycat的邏輯庫, 表, 分片規則, 分片節點和節點主機, 如此內容分別呈現在相應的標簽中, 配置過程即對這些標簽的設置.

1. 邏輯庫的設置, 即schema標簽, 指定庫名稱testdb.

<schema name="testdb" checkSQLschema="true" sqlMaxLimit="100">

...

</schema>

2. 表的設置, 即table標簽, 指定表名稱tb1, 所在分片節點, 和分片規則. table標簽要包含在schema標簽中.

<table name="tb1" dataNode="dnTest1,dnTest2" rule="mod-long"/>

3. 分片節點的設置, 即dataNode標簽, 指定分片節點名稱, 所在節點主機, 如分片節點dnTest1位于節點主機Rep1_3306上, 和與之對應的物理庫的名稱, 如分片節點dnTest1對應物理庫test1(其真實的存在于后端數據庫實例上).

<dataNode name="dnTest1" dataHost="Rep1_3306" database="test1"/>

<dataNode name="dnTest2" dataHost="Rep2_3306" database="test2"/>

4. 節點主機的設置, 即dataHost標簽，指定節點主機名稱, 與之對應的后端數據庫實例信息, 和讀寫分離配置, 心跳語句等, 之后在Mycat高可用部分會進一步說明該處.

<dataHost name="Rep1_3306" maxCon="20" minCon="5" balance="2" writeType="0" dbType="mysql" dbDriver="native" switchType="1">

<heartbeat>select user()</heartbeat>

<writeHost host="hostM1" url="192.168.4.235:3306" user="zzzz" password="zzzz">

    <readHost host="hostS1" url="192.168.4.234:3306" user="zzzz" password="zzzz"/>

</writeHost>

</dataHost>

<dataHost name="Rep2_3306" maxCon="20" minCon="5" balance="2" writeType="0" dbType="mysql" dbDriver="native" switchType="1">

<heartbeat>select user()</heartbeat>

<writeHost host="hostM2" url="192.168.4.151:3306" user="zzzz" password="zzzz">

    <readHost host="hostS2" url="192.168.4.150:3306" user="zzzz" password="zzzz"/>

</writeHost>

</dataHost>

在對dataHost標簽設置時,后端MySQL實例要創建好zzzz用戶.

mysql> create user 'zzzz'@'192.168.4.%' identified by 'zzzz';

mysql> grant select, insert, update, delete, execute, alter, create on test1.* to 'zzzz'@'192.168.4.%';

由于tb1是分片表, 還需說明分片字段和分片算法, 配置rule.xml文件.

1. 分片字段, 為tb1表的主鍵id字段.

<tableRule name="mod-long">

<rule>

    <columns>id</columns>

    <algorithm>mod-long</algorithm>

</rule>

</tableRule>

2. 分片算法, 簡單取摸.

<function name="mod-long" class="org.opencloudb.route.function.PartitionByMod">

<property name="count">2</property>

</function>

至此tb1表配置完了, 要通過Mycat訪問該表，還要一個簡單權限, 配置server.xml文件, 該文件還包括了Mycat的系統配置, 和調優參數.

<user name="test_user">

<property name="password">test_user</property>

<property name="schemas">testdb</property>

</user>

現在就可以登陸Mycat, 創建tb1的表結構了, 該步驟也可直接到后端實例上做, 要有多個節點的話, 會比較麻煩, 所以才給zzzz用戶賦予了create權限, 直接通過Mycat創建.

1. 登陸mycat

$ mysql -h292.168.4.184 -P8066 -utest_user -ptest_user

mysql> create table tb1(id int auto_increment primary key, user_name varchar(30) not null default '');

此時就可以使用tb1表了, 插入條數據, 通過日志看下路由過程, 為了看到比較詳細的日志, 先將日志級別調整為debug, 配置log4j.xml文件.

<root>

<level value="debug" />

<appender-ref ref="FILE" />

</root>

2. 插入數據

mysql> insert into tb1 select null, 'abc';

ERROR 1064 (HY000): partition table, insert must provide ColumnList

mysql> insert into tb1(id, user_name) values(null, 'abc');

ERROR 1064 (HY000): For input string: "NULL"

mysql> insert into tb1(id, user_name) values(7, 'abc');

Query OK, 1 row affected (0.00 sec)

3. 觀察日志, 可見route到dnTest2分片節點上insert.

03/18 08:33:04.221 DEBUG [$_NIOREACTOR-2-RW] (ServerQueryHandler.java:56) -ServerConnection [id=2, schema=testdb, host=192.168.4.184, user=test_user,txIsolation=3, autocommit=true, schema=testdb]insert into tb1(id, user_name) values(7, 'abc')

03/18 08:33:04.222 DEBUG [$_NIOREACTOR-2-RW] (NonBlockingSession.java:113) -ServerConnection [id=2, schema=testdb, host=192.168.4.184, user=test_user,txIsolation=3, autocommit=true, schema=testdb]insert into tb1(id, user_name) values(7, 'abc'), route={

1 -> dnTest2{insert into tb1(id, user_name) values(7, 'abc')}

} rrs

03/18 08:33:04.222 DEBUG [$_NIOREACTOR-2-RW] (MySQLConnection.java:459) -con need syn ,total syn cmd 2 commands SET names utf8;SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;schema change:false con:MySQLConnection [id=5, lastTime=1521333184222, user=zzzz, schema=test2, old shema=test2, borrowed=true, fromSlaveDB=false, threadId=68, charset=utf8, txIsolation=0, autocommit=true, attachment=dnTest2{insert into tb1(id, user_name) values(7, 'abc')}, respHandler=SingleNodeHandler [node=dnTest2{insert into tb1(id, user_name) values(7, 'abc')}, packetId=0], host=192.168.4.151, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]