【mysql】【11】 mycat 在主从的基础上做读写分离

计划

192.168.1.25 主
192.168.1.26 从
192.168.1.27 mycat

1.192.168.1.25、192.168.1.26、192.168.1.27上安装mysql

https://blog.csdn.net/huiyanshizhen21/article/details/88186087

2.192.168.1.25、192.168.1.26mysql配置主从

192.168.1.25 主
192.168.1.26 从
https://blog.csdn.net/huiyanshizhen21/article/details/88186598

3.192.168.127上安装mycat，下载mycat

http://www.mycat.org.cn/

cd /usr/local/src && wget https://github.com/MyCATApache/Mycat-Server/releases/download/Mycat-server-1675-release/Mycat-server-1.6.7.5-release-20200422133810-linux.tar.gz

4.解压mycat，在home目录新建一个logs目录

cd /usr/local/src && tar -zxvf  Mycat-server-1.6.7.5-release-20200422133810-linux.tar.gz -C ../
cd /usr/local/mycat && mkdir logs

5.配置schema.xml，先备份历史的

cd /usr/local/mycat &&  cp schema.xml schema.xml.back

[root@slave2 conf]# cat schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
	<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="test">
	</schema>
	<dataNode name="test" dataHost="localhost1" database="test" />
	<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
			  writeType="0" dbType="mysql" dbDriver="native" switchType="2"  slaveThreshold="100">
		<heartbeat>show slave status</heartbeat>
		<writeHost host="hostM1" url="192.168.1.25:3306" user="root"  password="yzaizlx21">
		</writeHost>

               <writeHost host="hostS1" url="192.168.1.26:3306" user="root"  password="yzaizlx21">
                </writeHost>

	</dataHost>
</mycat:schema>

6.配置server.xml，用户，先备份历史的,注意需要开通端口联通8066、9066

 cd /usr/local/mycat &&  cp server.xml server.xml.back

[root@slave2 conf]# cat 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="defaultSqlParser">druidparser</property>
		<property name="charset">utf8mb4</property>
		<!--  默认配置
			<property name="serverPort">8066</property>
			<property name="managerPort">9066</property> 
		-->
	</system>
	
	<user name="user1" defaultAccount="true">
		<property name="password">123456</property>
		<property name="schemas">TESTDB</property>
		<property name="defaultSchema">TESTDB</property>
	</user>

    <!-- 用户2，只读权限-->
	<user name="user2">
		<property name="password">user</property>
		<property name="schemas">TESTDB</property>
		<property name="readOnly">true</property>
		<property name="defaultSchema">TESTDB</property>
	</user>

</mycat:server>

7.连接mycat

mysql -uuser1 -p123456 -h192.168.1.27 -P8066

8.打开log日志为debug，操作查询、新增观察日志

vim /usr/local/mycat/conf/log4j2.xml

查询操作时会有以下日志会有select read
[root@slave2 logs]# cat /usr/local/mycat/logs/mycat.log |grep "select read"
2020-05-10 00:41:23.373 DEBUG [$_NIOREACTOR-0-RW] (io.mycat.backend.datasource.PhysicalDBPool.getRWBanlanceCon(PhysicalDBPool.java:556)) - select read source hostS1 for dataHost:localhost1
2020-05-10 00:41:27.405 DEBUG [$_NIOREACTOR-0-RW] (io.mycat.backend.datasource.PhysicalDBPool.getRWBanlanceCon(PhysicalDBPool.java:556)) - select read source hostS1 for dataHost:localhost1

tail -10f /usr/local/mycat/logs/mycat.log