Mycat读写分离失败（一主一从）尝试解决

项目场景：Mycat读写分离失败（一主一从）

问题描述

主从数据库关联完之后，对mycat的配置也完成了，可能由于我没有一次性成功修改 /usr/local/mycat/conf/schema.xml文件，最终使用一下命令发现读写分离失败

[root@mycat ~]# mysql -h127.0.0.1 -P9066 -uroot -p123456 -e 'show @@datasource;'
+----------+--------+-------+----------------+------+------+--------+------+------+---------+-----------+------------+
| DATANODE | NAME   | TYPE  | HOST           | PORT | W/R  | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
+----------+--------+-------+----------------+------+------+--------+------+------+---------+-----------+------------+
| dn1      | hostM1 | mysql | 192.168.200.20 | 3306 | W    |      0 |   10 | 1000 |      28 |         3 |          1 |
| dn1      | hostS1 | mysql | 192.168.200.30 | 3316 | R    |      0 |    0 | 1000 |       0 |         0 |          0 |
+----------+--------+-------+----------------+------+------+--------+------+------+---------+-----------+------------+

正常来说表格中的READ_LOAD应该在hostS1有数据，WRITE_LOAD应该在hostM1有数据

解决思路：

1.重启服务
2.查看日志（/usr/local/mycat/logs/wrapper.log)

---

尝试解决：

1.重启

MySQL [(none)]> show databases;
+----------+
| DATABASE |
+----------+
| USERDB   |
+----------+
1 row in set (0.001 sec)

MySQL [(none)]> use USERDB
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MySQL [USERDB]> select * from company ;
+----+----------+------+
| id | name     | addr |
+----+----------+------+
|  1 | facebook | usa  |
|  2 | cbook    | usa  |
+----+----------+------+
2 rows in set (0.005 sec)

MySQL [USERDB]> insert into company  values(3,"cbook","usa");
Query OK, 1 row affected (0.003 sec)

MySQL [USERDB]> Bye
[root@mycat ~]# 
[root@mycat ~]# mysql -h127.0.0.1 -P9066 -uroot -p123456 -e 'show @@datasource;'
+----------+--------+-------+----------------+------+------+--------+------+------+---------+-----------+------------+
| DATANODE | NAME   | TYPE  | HOST           | PORT | W/R  | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
+----------+--------+-------+----------------+------+------+--------+------+------+---------+-----------+------------+
| dn1      | hostM1 | mysql | 192.168.200.20 | 3306 | W    |      0 |   10 | 1000 |      27 |         2 |          1 |
| dn1      | hostS1 | mysql | 192.168.200.30 | 3316 | R    |      0 |    0 | 1000 |       0 |         0 |          0 |
+----------+--------+-------+----------------+------+------+--------+------+------+---------+-----------+------------+

失败

2.查看日志

INFO   | jvm 1    | 2022/03/22 17:58:45 | 2022-03-22 17:58:45,809 [INFO ][$_NIOConnector] no ilde connection in pool,create new connection for hostS1 of schema test  (io.mycat.backend.datasource.PhysicalDatasource:PhysicalDatasource.java:413) 
INFO   | jvm 1    | 2022/03/22 17:58:45 | 2022-03-22 17:58:45,809 [INFO ][$_NIOConnector] close connection,reason:java.net.ConnectException: Connection refused ,MySQLConnection [id=0, lastTime=1647986325802, user=root, schema=test, old shema=test, borrowed=false, fromSlaveDB=true, threadId=0, charset=utf8, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.200.30, port=3316, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]  (io.mycat.net.AbstractConnection:AbstractConnection.java:508) 
INFO   | jvm 1    | 2022/03/22 17:58:45 | 2022-03-22 17:58:45,809 [INFO ][$_NIOConnector] can't get connection for sql :select user()  (io.mycat.sqlengine.SQLJob:SQLJob.java:114) 

挑出日志的重点：

connection for hostS1
close connection,reason: .... port=3316,

有两个重点词—— hostS1和 port=3361，那就简单了，就是 /usr/local/mycat/conf/schema.xml有个地方没修改

查看一下 /usr/local/mycat/conf/schema.xml

[root@mycat ~]# vi /usr/local/mycat/conf/schema.xml 
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

        <schema name="USERDB" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1">
        </schema>
        <dataNode name="dn1" dataHost="localhost1" database="test" />
        <dataHost name="localhost1" maxCon="1000" minCon="10" balance="3"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="hostM1" url="192.168.200.20:3306" user="root"
                                   password="123456">
                <readHost host="hostS1" url="192.168.200.30:3316" user="root"
                                   password="123456" />
                </writeHost>
        </dataHost>
</mycat:schema>

果然看到第13行端口号没改过来，应该改成3306的，这里改完再试一下

[root@mycat ~]# /bin/bash /usr/local/mycat/bin/mycat stop 
Stopping Mycat-server...
^[[AStopped Mycat-server.
[root@mycat ~]# /bin/bash /usr/local/mycat/bin/mycat start
Starting Mycat-server...
[root@mycat ~]# mysql -h127.0.0.1 -P8066 -uroot -p123456;
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCloundDB)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> show databases;
+----------+
| DATABASE |
+----------+
| USERDB   |
+----------+
1 row in set (0.002 sec)

MySQL [(none)]> use USERDB
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MySQL [USERDB]> select * from company ;
+----+----------+------+
| id | name     | addr |
+----+----------+------+
|  1 | facebook | usa  |
|  2 | cbook    | usa  |
|  3 | cbook    | usa  |
+----+----------+------+
3 rows in set (0.026 sec)

MySQL [USERDB]> insert into company  values(4,"cbook","usa");
Query OK, 1 row affected (0.003 sec)

MySQL [USERDB]> select * from company ;
+----+----------+------+
| id | name     | addr |
+----+----------+------+
|  1 | facebook | usa  |
|  2 | cbook    | usa  |
|  3 | cbook    | usa  |
|  4 | cbook    | usa  |
+----+----------+------+
4 rows in set (0.001 sec)

MySQL [USERDB]> Bye
[root@mycat ~]# mysql -h127.0.0.1 -P9066 -uroot -p123456 -e 'show @@datasource;'
+----------+--------+-------+----------------+------+------+--------+------+------+---------+-----------+------------+
| DATANODE | NAME   | TYPE  | HOST           | PORT | W/R  | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
+----------+--------+-------+----------------+------+------+--------+------+------+---------+-----------+------------+
| dn1      | hostM1 | mysql | 192.168.200.20 | 3306 | W    |      0 |   10 | 1000 |      14 |         0 |          1 |
| dn1      | hostS1 | mysql | 192.168.200.30 | 3306 | R    |      0 |    4 | 1000 |       9 |         3 |          0 |
+----------+--------+-------+----------------+------+------+--------+------+------+---------+-----------+------------+

成功解决！！！

总结

查日志是解决问题的好办法（确信）。