NutzCN Logo
问答 请教一下,mysql 数据库连接失败,Access denied
发布于 2338天前 作者 qq_0852bebe 2997 次浏览 复制 上一个帖子 下一个帖子
标签:

首先说明一下情况,前几天都是正常的,突然就出现了连接失败
1.数据库采用的是ubuntu下的mysql
2.使用SQLyog - 64 bit可以连接上,但是nutz不行,密码肯定是对的
3.数据库信息如下:

ysql> use mysql;
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> select  host,user,password from user;
+-----------+--------+-------------------------------------------+
| host      | user   | password                                  |
+-----------+--------+-------------------------------------------+
| %         | root   | *B2B366CA5C4697F31D4C55D61F0B17E70E5664EC |
| ubuntu    | root   | *B2B366CA5C4697F31D4C55D61F0B17E70E5664EC |
| 127.0.0.1 | root   | *B2B366CA5C4697F31D4C55D61F0B17E70E5664EC |
| ::1       | root   | *B2B366CA5C4697F31D4C55D61F0B17E70E5664EC |
| %         | nutzwk | *B2B366CA5C4697F31D4C55D61F0B17E70E5664EC |
+-----------+--------+-------------------------------------------+
5 rows in set (0.00 sec)

mysql> 

4.nutz报错信息如下:




[DEBUG] 2017-11-30 16:21:10,597 cn.wizzer.app.web.commons.plugin.PluginAopConfigure.getInterceptorPairList(PluginAopConfigure.java:33) - skip -- org.nutz.dao.impl.NutDao
[DEBUG] 2017-11-30 16:21:10,651 org.nutz.ioc.aop.impl.DefaultMirrorFactory.getMirror(DefaultMirrorFactory.java:70) - Load class org.nutz.dao.impl.NutDao without AOP
[DEBUG] 2017-11-30 16:21:10,651 org.nutz.ioc.impl.NutIoc.get(NutIoc.java:157) - Get 'dataSource'<>
[DEBUG] 2017-11-30 16:21:11,470 org.nutz.dao.jdbc.Jdbcs.(Jdbcs.java:90) - Jdbcs init complete
[INFO ] 2017-11-30 16:21:11,471 org.nutz.dao.jdbc.Jdbcs.getExpert(Jdbcs.java:103) - Get Connection from DataSource for JdbcExpert, if you lock at here, check your database server and configure
[ERROR] 2017-11-30 16:21:12,013 com.alibaba.druid.pool.DruidDataSource$CreateConnectionThread.run(DruidDataSource.java:2083) - create connection error, url: jdbc:mysql://192.168.6.169:3306/testwu?useUnicode=true&characterEncoding=utf8&useSSL=false, errorCode 1045, state 28000
java.sql.SQLException: Access denied for user 'root '@'192.168.6.170' (using password: YES)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:873)
at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1710)
at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1226)
at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2205)
at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2236)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2035)
at com.mysql.jdbc.ConnectionImpl.(ConnectionImpl.java:790)
at com.mysql.jdbc.JDBC4Connection.(JDBC4Connection.java:47)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:400)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:330)
at com.alibaba.druid.filter.FilterChainImpl.connection_connect(FilterChainImpl.java:148)
at com.alibaba.druid.filter.stat.StatFilter.connection_connect(StatFilter.java:211)
at com.alibaba.druid.filter.FilterChainImpl.connection_connect(FilterChainImpl.java:142)
at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1454)
at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1516)
at com.alibaba.druid.pool.DruidDataSource$CreateConnectionThread.run(DruidDataSource.java:2080)
[ERROR] 2017-11-30 16:21:12,024 com.alibaba.druid.pool.DruidDataSource$CreateConnectionThread.run(DruidDataSource.java:2083) - create connection error, url: jdbc:mysql://192.168.6.169:3306/testwu?useUnicode=true&characterEncoding=utf8&useSSL=false, errorCode 1045, state 28000
java.sql.SQLException: Access denied for user 'root '@'192.168.6.170' (using password: YES)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:873)
at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1710)
at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1226)
at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2205)
at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2236)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2035)
at com.mysql.jdbc.ConnectionImpl.(ConnectionImpl.java:790)
at com.mysql.jdbc.JDBC4Connection.(JDBC4Connection.java:47)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:400)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:330)
at com.alibaba.druid.filter.FilterChainImpl.connection_connect(FilterChainImpl.java:148)
at com.alibaba.druid.filter.stat.StatFilter.connection_connect(StatFilter.java:211)
at com.alibaba.druid.filter.FilterChainImpl.connection_connect(FilterChainImpl.java:142)
at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1454)
at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1516)
at com.alibaba.druid.pool.DruidDataSource$CreateConnectionThread.run(DruidDataSource.java:2080)

5.db.properties文件内容如下:

db.name=test
db.url=jdbc:mysql://192.168.6.169:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=false

db.username=root
db.password=666666
db.validationQuery=select 1
db.maxActive=100
db.testWhileIdle=true
db.filters=mergeStat
db.connectionProperties=druid.stat.slowSqlMillis=2000
db.defaultAutoCommit=true

```

11 回复

my.cnf文件内容:

[mysqld]
#
# * Basic Settings
#
user		= mysql
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
port		= 3306
basedir		= /usr
datadir		= /var/lib/mysql
tmpdir		= /tmp
lc-messages-dir	= /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address		= 127.0.0.1
bind-address		= 0.0.0.0
skip-external-locking
skip-name-resolve
#lower_case_table_names=1
#skip-grant-tables
#
# * Fine Tuning
mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION
mysql>FLUSH PRIVILEGES

还是一样的报错,连接补上

root@ubuntu:~# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.5.54-0ubuntu0.12.04.1 (Ubuntu)

Copyright (c) 2000, 2016, 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> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

sDatabase changed
mysql> select host,user,password from user;
+-----------+--------+-------------------------------------------+
| host      | user   | password                                  |
+-----------+--------+-------------------------------------------+
| %         | root   | *B2B366CA5C4697F31D4C55D61F0B17E70E5664EC |
| ubuntu    | root   | *B2B366CA5C4697F31D4C55D61F0B17E70E5664EC |
| 127.0.0.1 | root   | *B2B366CA5C4697F31D4C55D61F0B17E70E5664EC |
| ::1       | root   | *B2B366CA5C4697F31D4C55D61F0B17E70E5664EC |
| %         | nutzwk | *B2B366CA5C4697F31D4C55D61F0B17E70E5664EC |
+-----------+--------+-------------------------------------------+
5 rows in set (0.00 sec)

mysql> select * from user;
+-----------+--------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
| Host      | User   | Password                                  | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string |
+-----------+--------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
| %         | root   | *B2B366CA5C4697F31D4C55D61F0B17E70E5664EC | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          |            |             |              |             0 |           0 |               0 |                    0 |        |                       |
| ubuntu    | root   | *B2B366CA5C4697F31D4C55D61F0B17E70E5664EC | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          |            |             |              |             0 |           0 |               0 |                    0 |        |                       |
| 127.0.0.1 | root   | *B2B366CA5C4697F31D4C55D61F0B17E70E5664EC | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          |            |             |              |             0 |           0 |               0 |                    0 |        |                       |
| ::1       | root   | *B2B366CA5C4697F31D4C55D61F0B17E70E5664EC | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          |            |             |              |             0 |           0 |               0 |                    0 |        |                       |
| %         | nutzwk | *B2B366CA5C4697F31D4C55D61F0B17E70E5664EC | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | N          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          |            |             |              |             0 |           0 |               0 |                    0 |        | NULL                  |
+-----------+--------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
5 rows in set (0.00 sec)

mysql> quit;
Bye
root@ubuntu:~# service mysql restart
mysql stop/waiting
mysql start/running, process 1685
root@ubuntu:~# 
[INFO ] 2017-11-30 16:40:40,911 org.nutz.dao.jdbc.Jdbcs.getExpert(Jdbcs.java:103) - Get Connection from DataSource for JdbcExpert, if you lock at here, check your database server and configure
[ERROR] 2017-11-30 16:40:41,350 com.alibaba.druid.pool.DruidDataSource$CreateConnectionThread.run(DruidDataSource.java:2083) - create connection error, url: jdbc:mysql://192.168.6.169:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=false, errorCode 1045, state 28000
java.sql.SQLException: Access denied for user 'root '@'192.168.6.170' (using password: YES)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:873)
	at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1710)
	at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1226)
	at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2205)
	at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2236)
	at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2035)
	at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:790)
	at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
	at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:400)
	at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:330)
	at com.alibaba.druid.filter.FilterChainImpl.connection_connect(FilterChainImpl.java:148)
	at com.alibaba.druid.filter.stat.StatFilter.connection_connect(StatFilter.java:211)
	at com.alibaba.druid.filter.FilterChainImpl.connection_connect(FilterChainImpl.java:142)
	at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1454)
	at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1516)
	at com.alibaba.druid.pool.DruidDataSource$CreateConnectionThread.run(DruidDataSource.java:2080)
[ERROR] 2017-11-30 16:40:41,364 com.alibaba.druid.pool.DruidDataSource$CreateConnectionThread.run(DruidDataSource.java:2083) - create connection error, url: jdbc:mysql://192.168.6.169:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=false, errorCode 1045, state 28000
java.sql.SQLException: Access denied for user 'root '@'192.168.6.170' (using password: YES)

在192.168.6.170这个服务器上去连192.168.6.169

mysql -u root -p -h 192.168.6.169

192.168.6.170上没有安装mysql客户端,但是我在192.168.6.170上用SQLyog是可以连接上的。正常访问
稍等一下,我安装一下客户端试试

在170上使用本地的jar连接是可以,成功连接到数据库

        try{
            Class.forName("com.mysql.jdbc.Driver");
            System.out.println("成功加载MySQL驱动!");
        }catch(ClassNotFoundException e1){
            System.out.println("找不到MySQL驱动!");
            e1.printStackTrace();
        }
        
        String url="jdbc:mysql://192.168.6.169:3306/test?characterEncoding=UTF-8";    //JDBC的URL    
        //调用DriverManager对象的getConnection()方法,获得一个Connection对象
        Connection conn = null;
        Statement stmt = null;
        try {
            conn = DriverManager.getConnection(url, "root", "666666");
            //创建一个Statement对象
            stmt = conn.createStatement(); //创建Statement对象
            System.out.print("成功连接到数据库!");

        } catch (SQLException e){
            e.printStackTrace();
        }

debug一下 这个方法 com.alibaba.druid.pool.DruidAbstractDataSource.setPassword(String)

基本找到原因了
刚看你说setPassword,后面我仔细检查了一下db.properties文件,发现密码数字后面多了一个空格,导致密码不对。
所以我建议db.properties等配置文件应该把后面的空格或者前面的空格删除掉,虽然也有空格是密码的。
但是这样非常不容易检查出问题来。
开始做了一个测试在db.username=root 后面添加一个空格,密码后面的空格去掉也是出现连接数据库失败。

非常感谢

打赏一下,了表心意!非常感谢

去空格? 嗯,我考虑一下, 要不报个issue吧?

在那个地址报issue呢

添加回复
请先登陆
回到顶部