mycat 配置mysql读写分离+高可用切换不过去 #2618

namehuanxin · 2020-10-22T10:13:46Z

配置文件：

<mycat:schema xmlns:mycat="http://io.mycat/">

select user()

错误日志：
2020-10-22 17:42:05.090 ERROR [Timer0] (io.mycat.backend.jdbc.JDBCHeartbeat.heartbeat(JDBCHeartbeat.java:114)) - JDBCHeartBeat error
com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
at com.mysql.cj.jdbc.exceptions.SQLError.createCommunicationsException(SQLError.java:174) ~[mysql-connector-java-8.0.19.jar:8.0.19]
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:64) ~[mysql-connector-java-8.0.19.jar:8.0.19]
at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:836) ~[mysql-connector-java-8.0.19.jar:8.0.19]
at com.mysql.cj.jdbc.ConnectionImpl.(ConnectionImpl.java:456) ~[mysql-connector-java-8.0.19.jar:8.0.19]
at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:246) ~[mysql-connector-java-8.0.19.jar:8.0.19]
at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:197) ~[mysql-connector-java-8.0.19.jar:8.0.19]
at java.sql.DriverManager.getConnection(DriverManager.java:664) ~[?:1.8.0_251]
at java.sql.DriverManager.getConnection(DriverManager.java:247) ~[?:1.8.0_251]
at io.mycat.backend.jdbc.JDBCDatasource.getConnection(JDBCDatasource.java:109) ~[Mycat-server-1.6.5-release.jar:?]
at io.mycat.backend.jdbc.JDBCHeartbeat.heartbeat(JDBCHeartbeat.java:100) ~[Mycat-server-1.6.5-release.jar:?]
at io.mycat.backend.datasource.PhysicalDatasource.doHeartbeat(PhysicalDatasource.java:440) ~[Mycat-server-1.6.5-release.jar:?]
at io.mycat.backend.datasource.PhysicalDBPool.doHeartbeat(PhysicalDBPool.java:345) ~[Mycat-server-1.6.5-release.jar:?]
at io.mycat.MycatServer$8$1.run(MycatServer.java:897) ~[Mycat-server-1.6.5-release.jar:?]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [?:1.8.0_251]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [?:1.8.0_251]
at java.lang.Thread.run(Thread.java:748) [?:1.8.0_251]
Caused by: com.mysql.cj.exceptions.CJCommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
at sun.reflect.GeneratedConstructorAccessor18.newInstance(Unknown Source) ~[?:?]
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[?:1.8.0_251]
at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[?:1.8.0_251]
at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61) ~[mysql-connector-java-8.0.19.jar:8.0.19]
at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:105) ~[mysql-connector-java-8.0.19.jar:8.0.19]
at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:151) ~[mysql-connector-java-8.0.19.jar:8.0.19]
at com.mysql.cj.exceptions.ExceptionFactory.createCommunicationsException(ExceptionFactory.java:167) ~[mysql-connector-java-8.0.19.jar:8.0.19]
at com.mysql.cj.protocol.a.NativeSocketConnection.connect(NativeSocketConnection.java:91) ~[mysql-connector-java-8.0.19.jar:8.0.19]
at com.mysql.cj.NativeSession.connect(NativeSession.java:144) ~[mysql-connector-java-8.0.19.jar:8.0.19]
at com.mysql.cj.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:956) ~[mysql-connector-java-8.0.19.jar:8.0.19]
at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:826) ~[mysql-connector-java-8.0.19.jar:8.0.19]
... 13 more
Caused by: java.net.ConnectException: 拒绝连接 (Connection refused)
at java.net.PlainSocketImpl.socketConnect(Native Method) ~[?:1.8.0_251]
at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:350) ~[?:1.8.0_251]
at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:206) ~[?:1.8.0_251]
at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:188) ~[?:1.8.0_251]
at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392) ~[?:1.8.0_251]
at java.net.Socket.connect(Socket.java:606) ~[?:1.8.0_251]
at com.mysql.cj.protocol.StandardSocketFactory.connect(StandardSocketFactory.java:155) ~[mysql-connector-java-8.0.19.jar:8.0.19]
at com.mysql.cj.protocol.a.NativeSocketConnection.connect(NativeSocketConnection.java:65) ~[mysql-connector-java-8.0.19.jar:8.0.19]
at com.mysql.cj.NativeSession.connect(NativeSession.java:144) ~[mysql-connector-java-8.0.19.jar:8.0.19]
at com.mysql.cj.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:956) ~[mysql-connector-java-8.0.19.jar:8.0.19]
at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:826) ~[mysql-connector-java-8.0.19.jar:8.0.19]

问题描述：
正常四台server_id=1 ;2; 3; 4
1;2为双主 3；4为从
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 4 |
+-------------+
1 row in set (0.01 sec)

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 3 |
+-------------+
1 row in set (0.00 sec)

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 1 |
+-------------+
1 row in set (0.01 sec)

mysql> begin ;select @@server_id; commit;
Query OK, 0 rows affected (0.00 sec)

+-------------+
| @@server_id |
+-------------+
| 2 |
+-------------+
1 row in set (0.01 sec)

关掉server_id=2 的那台，会出现读的还是在server_id=1上，他没有切换成写的数据库
+-------------+
| @@server_id |
+-------------+
| 1 |
+-------------+
1 row in set (0.01 sec)

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 3 |
+-------------+
1 row in set (0.00 sec)

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 1 |
+-------------+
1 row in set (0.01 sec)

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 3 |
+-------------+
1 row in set (0.01 sec)

mysql> begin ;select @@server_id; commit;
Query OK, 0 rows affected (0.00 sec)

ERROR:
No operations allowed after connection closed.
ERROR 1003 (HY000): Transaction error, need to rollback.
mysql> begin ;select @@server_id; commit;
Query OK, 0 rows affected (0.00 sec)

ERROR 1003 (HY000): Transaction error, need to rollback. errno:0 No operations allowed after connection closed.
ERROR 1003 (HY000): Transaction error, need to rollback.

funnyAnt · 2020-10-22T11:45:03Z

@namehuanxin 请提供下schema.xml里面有关数据源的配置

namehuanxin · 2020-10-23T01:20:18Z

      <dataHost name="db1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1">
		<heartbeat>select user()</heartbeat>
	<writeHost host="host1" url="jdbc:mysql://192.168.1.57:3306?useSSL=false&amp;useUnicode=true&amp;characterEncoding=UTF8&amp;serverTimezone=UTC" user="mycat1"
			   password="DreamSoft_123">
		<readHost host="host2" url="jdbc:mysql://192.168.1.61:3306?useSSL=false&amp;useUnicode=true&amp;characterEncoding=UTF8&amp;serverTimezone=UTC" user="mycat1" password="DreamSoft_123" />
	</writeHost>
	<writeHost host="host3" url="jdbc:mysql://192.168.1.58:3306?useSSL=false&amp;useUnicode=true&amp;characterEncoding=UTF8&amp;serverTimezone=UTC" user="mycat1"
			   password="DreamSoft_123">
		<readHost host="host4" url="jdbc:mysql://192.168.1.68:3306?useSSL=false&amp;useUnicode=true&amp;characterEncoding=UTF8&amp;serverTimezone=UTC" user="mycat1" password="DreamSoft_123" />

namehuanxin · 2020-10-23T01:20:52Z

这个jdbc对应的url配置有问题吗？

funnyAnt · 2020-10-25T12:14:04Z

@namehuanxin

看了你这个说明，是不是要反馈2个问题：

1 、begin; select @@server_id; commit; 这种语句，select @@server_id为什么没有走slave节点，还是在master上面

答复：因为在事务中，会直接把路由派发到master节点上面
2 、begin; select @@server_id; commit; 这种语句，commit过程中为什么会出现No operations allowed after connection closed.

答复：请把begin; select @@server_id; commit; 分开来单独执行，观察是执行到哪个语句报错的。这个报错是Mysql那边返回的。

namehuanxin · 2020-10-26T11:28:45Z

第一个：
之前那个高可用我们用命令手动去切换是可以切换的，我们用9066管理端口去连接，查发现他还是切不过去。后来换了1.6.7.4-release-20200105164103-linux.tar就好了，在此之前我们用的是，Mycat-server-1.6.5-release-20180122220033-linux.tar。

第二个：
想咨询下：上面的schema.xml
里面的jdbc配置，有问题吗？这样配置是否正确？

第三个：
在部署完成后，去查表发现在mycat里面，执行sql WHERE='xxxxx'查不出数据
但WHERE=”xxxxx“ 可以，是mycat里面修改了什么吗？mysql配置文件里面也加入了忽略大小写。我们去后端数据库mysql里面查询单引号，双引号都可以。

报错：没有BASE TABL表 SHOW FULL TABLES FROM nc_xxqj WHERE table_type = 'BASE TABLE';
正常 SHOW FULL TABLES FROM nc_xxqj WHERE table_type = "BASE TABLE";

funnyAnt · 2020-10-26T12:34:30Z

@namehuanxin
第二个：
想咨询下：上面的schema.xml
里面的jdbc配置，有问题吗？这样配置是否正确？
---你这样的配置，是双主互为同步的mysql架构。如果实际就是这样的架构这个配置没有问题。

第三个：
报错：没有BASE TABL表 SHOW FULL TABLES FROM nc_xxqj WHERE table_type = 'BASE TABLE';
---这个我排查下，确认是问题这周修复。

namehuanxin · 2020-10-28T01:43:44Z

非常感谢您！
我们会及时关注官方信息，第三个问题单双引号查询的问题，如果确定单双引号问题，在修复问题后，我们也会及时去测试！
在此，再次感谢您的帮助解答。

funnyAnt · 2020-10-31T05:17:21Z

@namehuanxin 已经修复，
875afc4

#2618 支持语法 SHOW full TABLES FROM TESTDB WHERE table_type = 'BASE TABLE';

namehuanxin · 2020-11-02T07:00:56Z

我们可以下载新版本用了是吧？

namehuanxin · 2020-11-02T07:02:20Z

那是用Mycat-server-1.6.7.4-release版本，还是Mycat-server-1.6.7.5-test版本发布

namehuanxin · 2020-11-02T07:17:24Z

还有一个问题想咨询下您，1.服务器硬件资源良好 2.sql数据小大概只有200M左右，直接在mysql里面插入查询优化较流畅，但是通过mycat做了插入操作的时候，立马去查询操作时，会存在延时大概一两毫秒。还是说要从程序，sql上去优化吗？

funnyAnt · 2020-11-02T09:27:09Z

@namehuanxin

那是用Mycat-server-1.6.7.4-release版本，还是Mycat-server-1.6.7.5-test版本发布

答复：最近会发布Mycat-server-1.6.7.6版本，如果你们有运维能力，自己看看我修改的代码，自己重新打个包。

1.服务器硬件资源良好 2.sql数据小大概只有200M左右，直接在mysql里面插入查询优化较流畅，但是通过mycat做了插入操作的时候，立马去查询操作时，会存在延时大概一两毫秒

答复：是在mycat上面插入成功后，然后再去查询吗？如果mycat开启读写分离，会存在一定主从延时的。如果想获取到最新的数据，建议开启事务，在事务中执行select ....，这样保证路由到刚才的写节点上面执行。

namehuanxin · 2020-11-03T02:15:59Z

非常感谢您！
新的包我们已经测试过了，SHOW FULL TABLES FROM nc_xxqj WHERE table_type = 'BASE TABLE';
单双引号问题已经解决，感谢！！！！
关于延时问题，我们开启事物开关，会进行测试。
在此还是非常感谢您！

xukaiping717 · 2022-11-15T02:53:51Z

请问一下如何支持 show index 语法，如何修改源码

xukaiping717 · 2022-11-15T03:29:15Z

@funnyAnt

junwen12221 added a commit that referenced this issue Nov 1, 2020

Merge pull request #2626 from funnyAnt/#2618

cb9c32b

#2618 支持语法 SHOW full TABLES FROM TESTDB WHERE table_type = 'BASE TABLE';

junwen12221 mentioned this issue Dec 31, 2020

[更新日志] #2707

Open

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

mycat 配置mysql读写分离+高可用切换不过去 #2618

mycat 配置mysql读写分离+高可用切换不过去 #2618

namehuanxin commented Oct 22, 2020

funnyAnt commented Oct 22, 2020

namehuanxin commented Oct 23, 2020

namehuanxin commented Oct 23, 2020

funnyAnt commented Oct 25, 2020 •

edited

Loading

namehuanxin commented Oct 26, 2020

funnyAnt commented Oct 26, 2020 •

edited

Loading

namehuanxin commented Oct 28, 2020

funnyAnt commented Oct 31, 2020

namehuanxin commented Nov 2, 2020

namehuanxin commented Nov 2, 2020

namehuanxin commented Nov 2, 2020

funnyAnt commented Nov 2, 2020 •

edited

Loading

namehuanxin commented Nov 3, 2020

xukaiping717 commented Nov 15, 2022

xukaiping717 commented Nov 15, 2022

mycat 配置mysql读写分离+高可用切换不过去 #2618

mycat 配置mysql读写分离+高可用切换不过去 #2618

Comments

namehuanxin commented Oct 22, 2020

funnyAnt commented Oct 22, 2020

namehuanxin commented Oct 23, 2020

namehuanxin commented Oct 23, 2020

funnyAnt commented Oct 25, 2020 • edited Loading

看了你这个说明，是不是要反馈2个问题：

namehuanxin commented Oct 26, 2020

funnyAnt commented Oct 26, 2020 • edited Loading

namehuanxin commented Oct 28, 2020

funnyAnt commented Oct 31, 2020

namehuanxin commented Nov 2, 2020

namehuanxin commented Nov 2, 2020

namehuanxin commented Nov 2, 2020

funnyAnt commented Nov 2, 2020 • edited Loading

namehuanxin commented Nov 3, 2020

xukaiping717 commented Nov 15, 2022

xukaiping717 commented Nov 15, 2022

funnyAnt commented Oct 25, 2020 •

edited

Loading

funnyAnt commented Oct 26, 2020 •

edited

Loading

funnyAnt commented Nov 2, 2020 •

edited

Loading