Mycat1.6.6.1兼容 Mysql8

schema.xml

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

	<schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100">
		<table name="tb_user" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
	</schema>
	
	<dataNode name="dn1" dataHost="localhost1" database="db1" />
	<dataNode name="dn2" dataHost="localhost1" database="db2" />
	<dataNode name="dn3" dataHost="localhost1" database="db3" />
	
	<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
			  writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
		<heartbeat>show slave status</heartbeat>
	
		<writeHost host="hostM1" url="jdbc:mysql://192.168.1.28:3306?useSSL=false&amp;serverTimezone=UTC" 
		user="mysql账户" password="mysq密码" />
	</dataHost>

</mycat:schema>

server.xml

<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
	<system>
		<property name="defaultSqlParser">druidparser</property>
		<property name="useHandshakeV10">1</property>
		<property name="mutiNodeLimitType">1</property>
		<property name="charset">utf8</property>
		<property name="serverPort">8066</property>
		<property name="managerPort">9066</property>
	</system>
	
	<user name="mycat" defaultAccount="true">
		<property name="password">mycat</property>
		<property name="schemas">TESTDB</property>
	</user>
</mycat:server>

schema.xml截图配置

server.xml 截图配置

1. mysql unblock with ‘mysqladmin flush-hosts’ 解决方法登陆mysql -u root -p 后执行命令 flush hosts;
2. 错误号码 2058：Plugin caching_sha2_passwordcould not be loaded
   
   mysql8.0 之前的版本中加密规则是mysql_native_password,
   而在mysql8之后,加密规则是caching_sha2_password,
   解决此问题方法有两种,一种是升级客户端驱动,
   一种是把mysql用户登录密码加密规则还原成mysql_native_password。

修改密码并指定加密规则为mysql_native_password

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

3. mycat安装目录下/lib 放入高版本 mysql-connector-java-8.0.16.jar
   
   4.测试
   

在mycat 服务器上执行脚本语句