MyCat 读写分离,负载均衡

docker mysql 主从复制

配合Spring 事务 注意事项

 

配置好JRE，安装好MYCAT

在mysql主库创建表，会同步到从库

CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `user` varchar(255) NOT NULL COMMENT '名称',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

mycat schema.xml

balance="3"，所有读请求随机的分发到wiriterHost对应的readhost执行，writerHost不负担读压力

switchType='-1'意味着当主挂掉的时候，不进行自动切换，即hostS1和hostS2并不会被提升为主，仍只提供读的功能。

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="slavetest" checkSQLschema="false" sqlMaxLimit="10000">
<!-- 测试表 -->
<table name="user" primaryKey="id" dataNode="dn1" />
</schema>
<!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"/> -->
<dataNode name="dn1" dataHost="localhost1" database="slavetest" />
<dataHost name="localhost1" maxCon="1000" minCon="1000" balance="3" 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.1.9:3306" user="root" password="masterpwd">
<!-- can have multi read hosts -->
<readHost host="hostS2" url="192.168.1.9:3307" user="slave" password="slavepwd" />
</writeHost>
</dataHost>
</mycat:schema>

 

mycat server.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
<system>
<property name="useSqlStat">0</property>  <!-- 1为开启实时统计、0为关闭 -->
<property name="useGlobleTableCheck">0</property>  <!-- 1为开启全加班一致性检测、0为关闭 -->

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

<property name="processorBufferPoolType">0</property>
<property name="handleDistributedTransactions">0</property>

<!--
            off heap for merge/order/group/limit      1开启   0关闭
-->
<property name="useOffHeapForMerge">1</property>

<!--
            单位为m
-->
<property name="memoryPageSize">1m</property>

<!--
            单位为k
-->
<property name="spillsFileBufferSize">1k</property>

<property name="useStreamOutput">0</property>

<!--
            单位为m
-->
<property name="systemReserveMemorySize">384m</property>


<!--是否采用zookeeper协调切换  -->
<property name="useZKSwitch">true</property>


</system>

<user name="root">
<property name="password">123456</property>
<property name="schemas">slavetest</property>
</user>

</mycat:server>

修改mycat log4j.xml 方便查看读写分离是否生效

<?xml version="1.0" encoding="UTF-8"?>
<Configuration status="WARN">
<Appenders>
<Console name="Console" target="SYSTEM_OUT">
<PatternLayout pattern="%d [%-5p][%t] %m %throwable{full} (%C:%F:%L) %n"/>
</Console>

<RollingFile name="RollingFile" fileName="${sys:MYCAT_HOME}/logs/mycat.log"
                     filePattern="${sys:MYCAT_HOME}/logs/$${date:yyyy-MM}/mycat-%d{MM-dd}-%i.log.gz">
<PatternLayout>
<Pattern>%d{yyyy-MM-dd HH:mm:ss.SSS} %5p [%t] (%l) - %m%n</Pattern>
</PatternLayout>
<Policies>
<OnStartupTriggeringPolicy/>
<SizeBasedTriggeringPolicy size="250 MB"/>
<TimeBasedTriggeringPolicy/>
</Policies>
</RollingFile>
</Appenders>
<Loggers>
<asyncRoot level="debug" includeLocation="true">

<AppenderRef ref="FILE" />
<AppenderRef ref="RollingFile"/>

</asyncRoot>
</Loggers>
</Configuration>

重启mycat 登录mycat

root@0b763a8d1ddd:/# mysql -uroot -p123456 -h192.168.1.9 -P8066

插入测试数据

mysql> insert into user values(1,"laizhenwei");
Query OK, 1 row affected (0.01 sec)

mysql> select * from user limit 1;
+----+------------+
| id | user       |
+----+------------+
|  1 | laizhenwei |
+----+------------+
1 row in set (0.00 sec)

查看mycat日志