使用Mycat实现读写分离

Mycat是常用的数据库代理层中间件，本文通过Mycat实现读写分离，应用程序先访问Mycat服务，Mycat根据SQL语句进行分发，读操作并且无事务走从库，写操作走主库。

Mycat服务配置

$ cat mycat/conf/server.xml

<?xml version="1.0" encoding="UTF-8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License"); 
	- you may not use this file except in compliance with the License. - You 
	may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0 
	- - Unless required by applicable law or agreed to in writing, software - 
	distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT 
	WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the 
	License for the specific language governing permissions and - limitations 
	under the License. -->
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
	<system>
	<!-- 这里配置的都是一些系统属性，可以自己查看mycat文档 -->
	<property name="defaultSqlParser">druidparser</property>
	<property name="charset">utf8mb4</property>
	</system>

	<user name="root" defaultAccount="true">
		<property name="password">123456</property>
		<property name="schemas">prod</property>
		
	</user>


</mycat:server>

schemas表示schema.xml中的schema节点，多个库用逗号分隔

表配置

$ cat mycat/conf/schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

	<schema name="prod" checkSQLschema="false" sqlMaxLimit="100" dataNode="pcxNode"></schema>

	<dataNode name="pcxNode" dataHost="dtHost" database="test" />



	<dataHost name="dtHost" maxCon="1000" minCon="10" 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="localhost:3308" user="root"
				   password="123456">
			<!-- can have multi read hosts -->
			<readHost host="hostS1" url="localhost:3307" user="root" password="123456" />
		</writeHost>
	</dataHost>
</mycat:schema>

增加数据库时要添加dataNode及schema

Mycat 启动

./mycat start