Mycat的使用 - 02.配置

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

schema.xml是首先遇到的配置文件, 其管理著Mycat的邏輯庫, 表, 分片規(guī)則, 分片節(jié)點(diǎn)和節(jié)點(diǎn)主機(jī), 如此內(nèi)容分別呈現(xiàn)在相應(yīng)的標(biāo)簽中, 配置過程即對(duì)這些標(biāo)簽的設(shè)置.

1. 邏輯庫的設(shè)置, 即schema標(biāo)簽, 指定庫名稱testdb.

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

...

</schema>

2. 表的設(shè)置, 即table標(biāo)簽, 指定表名稱tb1, 所在分片節(jié)點(diǎn), 和分片規(guī)則. table標(biāo)簽要包含在schema標(biāo)簽中.

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

3. 分片節(jié)點(diǎn)的設(shè)置, 即dataNode標(biāo)簽, 指定分片節(jié)點(diǎn)名稱, 所在節(jié)點(diǎn)主機(jī), 如分片節(jié)點(diǎn)dnTest1位于節(jié)點(diǎn)主機(jī)Rep1_3306上, 和與之對(duì)應(yīng)的物理庫的名稱, 如分片節(jié)點(diǎn)dnTest1對(duì)應(yīng)物理庫test1(其真實(shí)的存在于后端數(shù)據(jù)庫實(shí)例上).

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

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

4. 節(jié)點(diǎn)主機(jī)的設(shè)置, 即dataHost標(biāo)簽，指定節(jié)點(diǎn)主機(jī)名稱, 與之對(duì)應(yīng)的后端數(shù)據(jù)庫實(shí)例信息, 和讀寫分離配置, 心跳語句等, 之后在Mycat高可用部分會(huì)進(jìn)一步說明該處.

<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>

在對(duì)dataHost標(biāo)簽設(shè)置時(shí),后端MySQL實(shí)例要?jiǎng)?chuàng)建好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訪問該表，還要一個(gè)簡單權(quán)限, 配置server.xml文件, 該文件還包括了Mycat的系統(tǒng)配置, 和調(diào)優(yōu)參數(shù).

<user name="test_user">

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

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

</user>

現(xiàn)在就可以登陸Mycat, 創(chuàng)建tb1的表結(jié)構(gòu)了, 該步驟也可直接到后端實(shí)例上做, 要有多個(gè)節(jié)點(diǎn)的話, 會(huì)比較麻煩, 所以才給zzzz用戶賦予了create權(quán)限, 直接通過Mycat創(chuàng)建.

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 '');

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

<root>

<level value="debug" />

<appender-ref ref="FILE" />

</root>

2. 插入數(shù)據(jù)

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分片節(jié)點(diǎn)上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]