上次介绍Mycat概念时, 说到了分片表, 下面就从配置一个分片表入手简述Mycat的配置.
schema.xml是首先遇到的配置文件, 其管理着Mycat的逻辑库, 表, 分片规则, 分片节点和节点主机, 如此内容分别呈现在相应的标签中, 配置过程即对这些标签的设置.
<schema name="testdb" checkSQLschema="true" sqlMaxLimit="100">
...
</schema>
<table name="tb1" dataNode="dnTest1,dnTest2" rule="mod-long"/>
<dataNode name="dnTest1" dataHost="Rep1_3306" database="test1"/>
<dataNode name="dnTest2" dataHost="Rep2_3306" database="test2"/>
<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文件.
<tableRule name="mod-long">
<rule>
<columns>id</columns>
<algorithm>mod-long</algorithm>
</rule></tableRule>
<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创建.
$ 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>
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)
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]
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
