search

MyCATApache / Mycat-doc

Apache License 2.0
1.08k stars 662 forks source link

分表分库到三个不同的机器报错 #51

Open aiaix opened 7 years ago

aiaix commented 7 years ago

192.168.162.46 manager datanode3 --也是mycat 安装节点 192.168.162.25 test-db1-huaj datanode1 192.168.162.26 test-db2-huaj datanode2

11/15 11:41:40.295 WARN [$_NIOREACTOR-5-RW] (MySQLConnectionAuthenticator.java:100) -can't connect to mysql server ,errmsg:Access denied for user 'root'@'192.168.162.46' (using password: YES) MySQLConnection [id=236, lastTime=1479181300289, user=root, schema=testdb3, old shema=testdb3, borrowed=false, fromSlaveDB=false, threadId=3691, charset=utf8, txIsolation=0, autocommit=true, attachment=null, respHandler=null, host=192.168.162.26, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false] 11/15 11:41:40.295 INFO [$_NIOREACTOR-5-RW] (SQLJob.java:111) -can't get connection for sql :select user() 11/15 11:41:40.296 INFO [$_NIOREACTOR-5-RW] (AbstractConnection.java:458) -close connection,reason:stream closed ,MySQLConnection [id=236, lastTime=1479181300289, user=root, schema=testdb3, old shema=testdb3, borrowed=false, fromSlaveDB=false, threadId=3691, charset=utf8, txIsolation=0, autocommit=true, attachment=null, respHandler=null, host=192.168.162.26, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false] 11/15 11:41:40.296 WARN [$_NIOREACTOR-6-RW] (MySQLConnectionAuthenticator.java:100) -can't connect to mysql server ,errmsg:Access denied for user 'root'@'192.168.162.46' (using password: YES) MySQLConnection [id=237, lastTime=1479181300289, user=root, schema=testdb2, old shema=testdb2, borrowed=false, fromSlaveDB=false, threadId=4238, charset=utf8, txIsolation=0, autocommit=true, attachment=null, respHandler=null, host=192.168.162.25, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false] 11/15 11:41:40.296 INFO [$_NIOREACTOR-6-RW] (SQLJob.java:111) -can't get connection for sql :select user() 11/15 11:41:40.296 INFO [$_NIOREACTOR-6-RW] (PhysicalDatasource.java:373) -not ilde connection in pool,create new connection for test-db1-huaj of schema testdb2 11/15 11:41:40.297 INFO [$_NIOREACTOR-6-RW] (AbstractConnection.java:458) -close connection,reason:stream closed ,MySQLConnection [id=237, lastTime=1479181300289, user=root, schema=testdb2, old shema=testdb2, borrowed=false, fromSlaveDB=false, threadId=4238, charset=utf8, txIsolation=0, autocommit=true, attachment=null, respHandler=null, host=192.168.162.25, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false] 11/15 11:41:40.298 WARN [$_NIOREACTOR-7-RW] (MySQLConnectionAuthenticator.java:100) -can't connect to mysql server ,errmsg:Access denied for user 'root'@'192.168.162.46' (using password: YES) MySQLConnection [id=238, lastTime=1479181300289, user=root, schema=testdb2, old shema=testdb2, borrowed=false, fromSlaveDB=false, threadId=4239, charset=utf8, txIsolation=0, autocommit=true, attachment=null, respHandler=null, host=192.168.162.25, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false] 11/15 11:41:40.299 INFO [$_NIOREACTOR-7-RW] (SQLJob.java:111) -can't get connection for sql :select user() 11/15 11:41:40.299 INFO [$_NIOREACTOR-7-RW] (AbstractConnection.java:458) -close connection,reason:stream closed ,MySQLConnection [id=238, lastTime=1479181300289, user=root, schema=testdb2, old shema=testdb2, borrowed=false, fromSlaveDB=false, threadId=4239, charset=utf8, txIsolation=0, autocommit=true, attachment=null, respHandler=null, host=192.168.162.25, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

aiaix commented 7 years ago

[root@manager ~]# mysql -utest -P8066 -h192.168.162.46 -ptest -DTESTDB Warning: Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.5.8-mycat-1.5.1-RELEASE-20160929233042 MyCat Server (OpenCloundDB)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

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

mysql> exit Bye [root@manager ~]# mysql -utest -P8066 -h192.168.162.25 -ptest -DTESTDB Warning: Using a password on the command line interface can be insecure. ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.162.25' (111) [root@manager ~]#

还有那里需要配置吗

aiaix commented 7 years ago 
<dataNode name="dn1" dataHost="manager" database="testdb1" />
<dataNode name="dn2" dataHost="test-db1-huaj" database="testdb2" />
<dataNode name="dn3" dataHost="test-db2-huaj" database="testdb3" />
<dataHost name="manager" maxCon="1000" minCon="10" balance="0"
    writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
    <heartbeat>select user()</heartbeat>
    <!-- can have multi write hosts -->
    <writeHost host="manager" url="192.168.162.46:3306" user="root"
        password="root">
    </writeHost>
</dataHost>
    <dataHost name="test-db1-huaj" maxCon="1000" minCon="10" balance="0"
            writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
            <heartbeat>select user()</heartbeat>
            <!-- can have multi write hosts -->
            <writeHost host="test-db1-huaj" url="192.168.162.25:3306" user="root"
                    password="root">
            </writeHost>
    </dataHost>
    <dataHost name="test-db2-huaj" maxCon="1000" minCon="10" balance="0"
            writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
            <heartbeat>select user()</heartbeat>
            <!-- can have multi write hosts -->
            <writeHost host="test-db2-huaj" url="192.168.162.26:3306" user="root"
                    password="root">
            </writeHost>
    </dataHost>
SecretSun commented 7 years ago

密码错误吧 mycat需要间断的开始 ping 后台mysql node的

见报错 errmsg:Access denied for user 'root'@'192.168.162.46' (using password: YES) ；can't get connection for sql :select user()

2016-11-15 11:43 GMT+08:00 aiaix notifications@github.com:

192.168.162.46 manager datanode3 --也是mycat 安装节点 192.168.162.25 test-db1-huaj datanode1 192.168.162.26 test-db2-huaj datanode2

11/15 11:41:40.295 WARN $_NIOREACTOR-5-RW -can't connect to mysql server ,errmsg:Access denied for user 'root'@'192.168.162.46' (using password: YES) MySQLConnection [id=236, lastTime=1479181300289, user=root, schema=testdb3, old shema=testdb3, borrowed=false, fromSlaveDB=false, threadId=3691, charset=utf8, txIsolation=0, autocommit=true, attachment=null, respHandler=null, host=192.168.162.26, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false] 11/15 11:41:40.295 INFO $_NIOREACTOR-5-RW -can't get connection for sql :select user() 11/15 11:41:40.296 INFO $_NIOREACTOR-5-RW -close connection,reason:stream closed ,MySQLConnection [id=236, lastTime=1479181300289, user=root, schema=testdb3, old shema=testdb3, borrowed=false, fromSlaveDB=false, threadId=3691, charset=utf8, txIsolation=0, autocommit=true, attachment=null, respHandler=null, host=192.168.162.26, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false] 11/15 11:41:40.296 WARN $_NIOREACTOR-6-RW -can't connect to mysql server ,errmsg:Access denied for user 'root'@'192.168.162.46' (using password: YES) MySQLConnection [id=237, lastTime=1479181300289, user=root, schema=testdb2, old shema=testdb2, borrowed=false, fromSlaveDB=false, threadId=4238, charset=utf8, txIsolation=0, autocommit=true, attachment=null, respHandler=null, host=192.168.162.25, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false] 11/15 11:41:40.296 INFO $_NIOREACTOR-6-RW -can't get connection for sql :select user() 11/15 11:41:40.296 INFO $_NIOREACTOR-6-RW -not ilde connection in pool,create new connection for test-db1-huaj of schema testdb2 11/15 11:41:40.297 INFO $_NIOREACTOR-6-RW -close connection,reason:stream closed ,MySQLConnection [id=237, lastTime=1479181300289, user=root, schema=testdb2, old shema=testdb2, borrowed=false, fromSlaveDB=false, threadId=4238, charset=utf8, txIsolation=0, autocommit=true, attachment=null, respHandler=null, host=192.168.162.25, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false] 11/15 11:41:40.298 WARN $_NIOREACTOR-7-RW -can't connect to mysql server ,errmsg:Access denied for user 'root'@'192.168.162.46' (using password: YES) MySQLConnection [id=238, lastTime=1479181300289, user=root, schema=testdb2, old shema=testdb2, borrowed=false, fromSlaveDB=false, threadId=4239, charset=utf8, txIsolation=0, autocommit=true, attachment=null, respHandler=null, host=192.168.162.25, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false] 11/15 11:41:40.299 INFO $_NIOREACTOR-7-RW -can't get connection for sql :select user() 11/15 11:41:40.299 INFO $_NIOREACTOR-7-RW -close connection,reason:stream closed ,MySQLConnection [id=238, lastTime=1479181300289, user=root, schema=testdb2, old shema=testdb2, borrowed=false, fromSlaveDB=false, threadId=4239, charset=utf8, txIsolation=0, autocommit=true, attachment=null, respHandler=null, host=192.168.162.25, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/MyCATApache/Mycat-doc/issues/51, or mute the thread https://github.com/notifications/unsubscribe-auth/AMdTZMfEXMa5ehCSDpUpibh4iOaBvSyUks5q-SpegaJpZM4KyGH2 .

孙文杰 sunputonsteam@live.cn

aiaix commented 7 years ago

可是我都登录进去了 [root@manager ~]# mysql -utest -P8066 -h192.168.162.46 -ptest -DTESTDB Warning: Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.5.8-mycat-1.5.1-RELEASE-20160929233042 MyCat Server (OpenCloundDB)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

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

mysql> exit Bye

aiaix commented 7 years ago

已经解决是权限问题，是不是所有分片节点的所在数据都要授权给mycat用于访问？

SecretSun commented 7 years ago

需要访问的当然需要开启相关的权限哦

starkZH commented 5 years ago

您好，我已经有授权，也清了hosts，但还是出现了以下错误： close connection,reason:stream closed ,MySQLConnection 我手动可以连上，但mycat却总是连不上

lvswift commented 3 years ago

也是出现同样问题，有解决的吗