我在配置完mycat之后,测试连接发现 访问任何一个表都是 ERROR 1184 (HY000): Invalid DataSource:0,在网上也搜了各种教程。在此总结一下。
直接连接远程数据库看是否被限制
ERROR 1129 (HY000): Host '192.168.237.142' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
#连接失败次数太多,限制发起连接的服务器了。
mysqladmin -uroot -pQm123.com flush-hosts
#在目标服务器上,运行解除限制。
还有一些配置的错误,仔细检查一下,比如schema.xml文件:
<schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" randomDataNode="dn1">
<table name="tab" primaryKey="id" dataNode="dn1" />
#此处填表真实名字,主键也要真实存在
</schema>
<dataNode name="dn1" dataHost="localhost1" database="data" />
#'database="data"' 填的是真实数据库名称
write的数据库url有人说需要改成127.0.0.1:3306 这个不知道真实性
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="3"
dbDriver="native" writeType="0" dbType="mysql" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="192.168.237.142" url="127.0.0.1:3306" user="root"
password="Qm123.com">
<readHost host='192.168.237.143' url='192.168.237.143:3306' user='root' password='Qm123.com'/>
</writeHost>
<!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
</dataHost>
#也要检查一下,write和read后端数据库用户的账户和密码有没有填错,有没有远程权限
#还有人说要有‘root’@‘%’ ‘root’@‘localhost’ 两个用户,真个我没尝试
#mysql8版本以后需要把plugin,设置为mysql_native_password,不是mycat,是在后端数据库上
mysql> alter user 'root'@'%' identified with mysql_native_password by 'Zdy123.com';
mysql> select user,host,plugin from mysql.user;
+------------------+-----------+-----------------------+
| user | host | plugin |
+------------------+-----------+-----------------------+
| root | % | mysql_native_password |
| repl | 10.0.0.% | mysql_native_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session | localhost | caching_sha2_password |
| mysql.sys | localhost | caching_sha2_password |
| root | localhost | mysql_native_password |
+------------------+-----------+-----------------------+
schema.xml文件,dataHost标签中的dbDriver="native",看是不是native
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="3"
dbDriver="native" writeType="0" dbType="mysql" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
我之前的dbDriver是jdbc,所以连接不上