mycat 测试主从读写分离
下载解压及创建用户组和目录属性
下载地址：1、https://github.com/MyCATApache/Mycat-download。2、http://dl.mycat.io/ wget http://dl.mycat.io/1.6.6.1/Mycat-server-1.6.6.1-release-20180908155252-linux.tar.gz tar -zxf Mycat-server-1.6.6.1-release-20180908155252-linux.tar.gz -C /usr/local/ groupadd mycat useradd mycat -g mycat chown -R mycat:mycat mycat
mycat主要配置3个参数文件需要配置
--server.xml：是Mycat服务器参数调整和用户授权的配置文件。 --schema.xml：是逻辑库定义和表以及分片定义的配置文件。 --rule.xml： 是分片规则的配置文件，分片规则的具体一些参数信息单独存放为文件，也在这个目录下，配置文件修改需要重启MyCAT。
配置server.xml
server.xml,用户权限及表的精细权限
<user name="ht">
<property name="password">ocm123</property>
<property name="schemas">sakila</property>
</user>
配置schema.xml
[mycat@redis04 conf]$ cat schema.xml <?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"> <schema name="sakila" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"> </schema> <dataNode name="dn1" dataHost="localhost1" database="sakila" /> <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" 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.20.201:3306" user="ht" password="ocm123"> <!-- can have multi read hosts --> <readHost host="hostS2" url="192.168.20.203:3306" user="ht" password="ocm123" /> </writeHost> <!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> --> </dataHost> </mycat:schema> schema.xml schema的值与server.xml中schema的值是多对1的关系，server.xml中schema的值可以是多个、schema.xml schema的值是唯一，可以有多个schema。schema 相对于mysql中的database dataNode 数据分片。一个dataNode标签就是一个独立的数据分片。 dataHost 物理主机 balance属性 负载均衡类型，目前的取值有3种： 1. balance="0", 不开启读写分离机制，所有读操作都发送到当前可用的writeHost上。 2. balance="1"，全部的readHost与stand by writeHost参与select语句的负载均衡，简单的说，当双主双从模式(M1->S1，M2->S2，并且M1与 M2互为主备)，正常情况下，M2,S1,S2都参与select语句的负载均衡。 3. balance="2"，所有读操作都随机的在writeHost、readhost上分发。 4. balance="3"，所有读请求随机的分发到wiriterHost对应的readhost执行，writerHost不负担读压力，注意balance=3只在1.4及其以后版本有，1.3没有。 writeType属性 负载均衡类型，目前的取值有3种： 1. writeType="0", 所有写操作发送到配置的第一个writeHost，第一个挂了切到还生存的第二个writeHost，重新启动后已切换后的为准，切换记录在配置文件中:dnindex.properties . 2. writeType="1"，所有写操作都随机的发送到配置的writeHost，1.5以后废弃不推荐。 switchType属性 - -1 表示不自动切换 - 1 默认值，自动切换 - 2 基于MySQL主从同步的状态决定是否切换 - 3 基于MySQL galary cluster的切换机制（适合集群）（1.4.1）心跳语句为 show status like ‘wsrep%’.
启动mycat
mycat start 启动 mycat stop 停止 mycat console 前台运行 mycat restart 重启服务 mycat pause 暂停 mycat status 查看启动状态
连接和管理mycat
mysql -h 192.168.20.204 -P 8066 -u ht -p --应该用连接 mysql -h 192.168.20.204 -P 9066 -u ht -p --管理 ,查看mycat的详细信息
打开mycat日志debug，为下面查看具体路由和调试做准备
vi log4j2.xml
<!--<AsyncLogger name="io.mycat" level="debug" includeLocation="true" additivity="false">-->
<!--<AppenderRef ref="Console"/>-->
<!--<AppenderRef ref="RollingFile"/>-->
<!--</AsyncLogger>-->
执行select语句看后台日志
SELECT * from sakila.actor a where a.actor_id=1;
2018-09-12 14:13:42.433 DEBUG [$_NIOREACTOR-2-RW] (io.mycat.server.ServerQueryHandler.query(ServerQueryHandler.java:57)) - ServerConnection [id=1, schema=null, host=192.168.20.204, user=ht,txIsolation=3, autocommit=true, schema=null]SELECT * from sakila.actor a where a.actor_id=1
2018-09-12 14:13:42.434 DEBUG [$_NIOREACTOR-2-RW] (io.mycat.cache.impl.EnchachePool.get(EnchachePool.java:77)) - SQLRouteCache miss cache ,key:sakilaSELECT * from sakila.actor a where a.actor_id=1
2018-09-12 14:13:42.434 DEBUG [$_NIOREACTOR-2-RW] (io.mycat.server.NonBlockingSession.execute(NonBlockingSession.java:126)) - ServerConnection [id=1, schema=null, host=192.168.20.204, user=ht,txIsolation=3, autocommit=true, schema=null]SELECT * from sakila.actor a where a.actor_id=1, route={
1 -> dn1{SELECT * from sakila.actor a where a.actor_id=1}
} rrs
2018-09-12 14:13:42.434 DEBUG [$_NIOREACTOR-2-RW] (io.mycat.backend.mysql.nio.handler.SingleNodeHandler.execute(SingleNodeHandler.java:167)) - rrs.getRunOnSlave() default
2018-09-12 14:13:42.434 DEBUG [$_NIOREACTOR-2-RW] (io.mycat.backend.mysql.nio.handler.SingleNodeHandler.execute(SingleNodeHandler.java:169)) - node.getRunOnSlave() default
2018-09-12 14:13:42.434 DEBUG [$_NIOREACTOR-2-RW] (io.mycat.backend.mysql.nio.handler.SingleNodeHandler.execute(SingleNodeHandler.java:178)) - node.getRunOnSlave() default
2018-09-12 14:13:42.434 DEBUG [$_NIOREACTOR-2-RW] (io.mycat.backend.mysql.nio.handler.SingleNodeHandler.execute(SingleNodeHandler.java:180)) - node.getRunOnSlave() default
2018-09-12 14:13:42.434 DEBUG [$_NIOREACTOR-2-RW] (io.mycat.backend.datasource.PhysicalDBNode.getConnection(PhysicalDBNode.java:96)) - rrs.getRunOnSlave() default
2018-09-12 14:13:42.434 DEBUG [$_NIOREACTOR-2-RW] (io.mycat.backend.datasource.PhysicalDBNode.getConnection(PhysicalDBNode.java:127)) - rrs.getRunOnSlave() default
2018-09-12 14:13:42.434 DEBUG [$_NIOREACTOR-2-RW] (io.mycat.backend.datasource.PhysicalDBPool.getRWBanlanceCon(PhysicalDBPool.java:530)) - select read source hostS2 for dataHost:localhost1
2018-09-12 14:13:42.434 DEBUG [$_NIOREACTOR-2-RW] (io.mycat.backend.mysql.nio.MySQLConnection.synAndDoExecute(MySQLConnection.java:462)) - con need syn ,total syn cmd 1 commands SET names utf8;schema change:false con:MySQLConnection [id=13, lastTime=1536732822434, user=ht, schema=sakila, old shema=sakila, borrowed=true, fromSlaveDB=true, threadId=357, charset=utf8, txIsolation=3, autocommit=true, attachment=dn1{SELECT * from sakila.actor a where a.actor_id=1}, respHandler=SingleNodeHandler [node=dn1{SELECT * from sakila.actor a where a.actor_id=1}, packetId=0], host=192.168.20.203, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
执行update语句查看后台日志
update sakila.actor a set a.last_name='sahnghai' where a.actor_id=1;
2018-09-12 14:13:41.830 DEBUG [$_NIOREACTOR-2-RW] (io.mycat.server.NonBlockingSession.execute(NonBlockingSession.java:126)) - ServerConnection [id=1, schema=null, host=192.168.20.204, user=ht,txIsolation=3, autocommit=true, schema=null]update sakila.actor a set a.last_name='sahnghai' where a.actor_id=1, route={
1 -> dn1{update sakila.actor a set a.last_name='sahnghai' where a.actor_id=1}
} rrs
2018-09-12 14:13:41.830 DEBUG [$_NIOREACTOR-2-RW] (io.mycat.backend.mysql.nio.handler.SingleNodeHandler.execute(SingleNodeHandler.java:167)) - rrs.getRunOnSlave() default
2018-09-12 14:13:41.830 DEBUG [$_NIOREACTOR-2-RW] (io.mycat.backend.mysql.nio.handler.SingleNodeHandler.execute(SingleNodeHandler.java:169)) - node.getRunOnSlave() default
2018-09-12 14:13:41.830 DEBUG [$_NIOREACTOR-2-RW] (io.mycat.backend.mysql.nio.handler.SingleNodeHandler.execute(SingleNodeHandler.java:178)) - node.getRunOnSlave() default
2018-09-12 14:13:41.830 DEBUG [$_NIOREACTOR-2-RW] (io.mycat.backend.mysql.nio.handler.SingleNodeHandler.execute(SingleNodeHandler.java:180)) - node.getRunOnSlave() default
2018-09-12 14:13:41.830 DEBUG [$_NIOREACTOR-2-RW] (io.mycat.backend.datasource.PhysicalDBNode.getConnection(PhysicalDBNode.java:96)) - rrs.getRunOnSlave() default
2018-09-12 14:13:41.830 DEBUG [$_NIOREACTOR-2-RW] (io.mycat.backend.datasource.PhysicalDBNode.getConnection(PhysicalDBNode.java:127)) - rrs.getRunOnSlave() default
2018-09-12 14:13:41.830 DEBUG [$_NIOREACTOR-2-RW] (io.mycat.backend.mysql.nio.MySQLConnection.synAndDoExecute(MySQLConnection.java:462)) - con need syn ,total syn cmd 1 commands SET names utf8;schema change:false con:MySQLConnection [id=10, lastTime=1536732821830, user=ht, schema=sakila, old shema=sakila, borrowed=true, fromSlaveDB=false, threadId=410, charset=utf8, txIsolation=3, autocommit=true, attachment=dn1{update sakila.actor a set a.last_name='sahnghai' where a.actor_id=1}, respHandler=SingleNodeHandler [node=dn1{update sakila.actor a set a.last_name='sahnghai' where a.actor_id=1}, packetId=0], host=192.168.20.201, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
2018-09-12 14:13:41.940 DEBUG [$_NIOREACTOR-1-RW] (io.mycat.server.NonBlockingSession.releaseConnection(NonBlockingSession.java:386)) - release connection MySQLConnection [id=10, lastTime=1536732821828, user=ht, schema=sakila, old shema=sakila, borrowed=true, fromSlaveDB=false, threadId=410, charset=utf8, txIsolation=3, autocommit=true, attachment=dn1{update sakila.actor a set a.last_name='sahnghai' where a.actor_id=1}, respHandler=SingleNodeHandler [node=dn1{update sakila.actor a set a.last_name='sahnghai' where a.actor_id=1}, packetId=1], host=192.168.20.201, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
开启mycat日志看到读写分离测试成功
