记一次sringboot集成druid密码加密失效的问题排查

问题说明

配置druid密码公钥和加密密码后，无法正常访问Mysql，报连接超时异常，无法正常连接mysql

2020-11-04 19:31:14.076 [Druid-ConnectionPool-Create-614054257] ERROR c.alibaba.druid.pool.DruidDataSource - create connection SQLException, url: jdbc:mysql://localhost:3306/test?ucharacterEncoding=UTF-8&useSSL=false, errorCode 0, state 08001
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.
	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:422)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
	at com.mysql.jdbc.Util.getInstance(Util.java:408)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:918)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:897)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:886)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860)
	at com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2097)
	at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2022)
	at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:779)
	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:422)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
	at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:389)
	at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:330)
	at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1644)
	at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1710)
	at com.alibaba.druid.pool.DruidDataSource$CreateConnectionThread.run(DruidDataSource.java:2779)
Caused by: java.sql.SQLException: Access denied for user 'dev'@'2.0.1.2' (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:2191)
	at com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2038)
	... 13 common frames omitted

环境&配置

• jdk8
• druid:1.1.22
• mysql-connector-java:5.1.44

 

用druid生成密码和公钥

java -cp druid-1.1.22.jar com.alibaba.druid.filter.config.ConfigTools root

privateKey:MIIBVQIBADANBgkqhkiG9w0BAQEFAASCAT8wggE7AgEAAkEA9DN268JqM1oxTvWovksYEuMri/9cWHn8ExNVwy7B03vw6V1UKJusnuPy1HapaqTOGSiZVOn14RnK5Q0XgcKuLQIDAQABAkAPctoJHCl/yiroZd4g+7swJrLwjl0XM9BgnI6GZITajhMQ7Eg6X2ajbH/5Wad9liUD16Wsx5aWGfiUPMizXAA5AiEA/6AWmR7gl1tGn2RerOOiMnSvZWAAV/ul+LhxfLuwPqcCIQD0jxb9zpnNF/OPloXXxgOPDAr0NUwz9XjXmFUn6rS7CwIhAIEbDc7QNYa8ZvLh8p9HaQgqvttonUPSqYTxQwBImz9JAiEArsvfSQV0FI07/rM9ct4LCSE4axf1IWYgOyicJO2yw8UCICq1mFfOs28sQ3nwcAfG2sKYxXq1dFUGxjnE3babmMLe

publicKey:MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBAPQzduvCajNaMU71qL5LGBLjK4v/XFh5/BMTVcMuwdN78OldVCibrJ7j8tR2qWqkzhkomVTp9eEZyuUNF4HCri0CAwEAAQ==

password:hcmUY6Ml/mAM9I0iR3Y/De96Km+OOGGVZZk7scvvzmD8wDrd1KvS1ytIf3CsABouTV4jrFMm/IRIOxsV8BZq6Q==

 

application.yml

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    driver-class-name: com.mysql.jdbc.Driver
    url: jdbc:mysql://localhost:3306/test?characterEncoding=UTF-8&useSSL=false
    username: root
    password: hcmUY6Ml/mAM9I0iR3Y/De96Km+OOGGVZZk7scvvzmD8wDrd1KvS1ytIf3CsABouTV4jrFMm/IRIOxsV8BZq6Q==
    connectionProperties: config.decrypt=true;config.decrypt.key=MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBAPQzduvCajNaMU71qL5LGBLjK4v/XFh5/BMTVcMuwdN78OldVCibrJ7j8tR2qWqkzhkomVTp9eEZyuUNF4HCri0CAwEAAQ==
    filters: stat,wall,config
    initialSize: 5
    minIdle: 10
    maxActive: 100
    maxWait: 60000
    timeBetweenEvictionRunsMillis: 60000
    minEvictableIdleTimeMillis: 300000
    maxEvictableIdleTimeMillis: 900000
    validationQuery: SELECT 1
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false

排查过程

1. 刚开始怀疑mysql对连接ip做了限制，但去掉加密用明文的可以正常访问的，同时检查mysql配置并无限制，排除限制的问题
2. 然后怀疑配置项有问题，根据官网说明检查配置项命名，命名都是符合规范的，有的小伙伴可能存在配置的变量名不符合规范，需要特别注意（比如，用的user而不是username）
3. 参考官网说明如何在-spring-boot-中配置数据库密码加密，发现配置的参数路径有问题（其实idea已经帮忙检查了），正确的配置参考见下文

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    driver-class-name: com.mysql.jdbc.Driver
    url: jdbc:mysql://localhost:3306/test?characterEncoding=UTF-8&useSSL=false
    username: root
    password: hcmUY6Ml/mAM9I0iR3Y/De96Km+OOGGVZZk7scvvzmD8wDrd1KvS1ytIf3CsABouTV4jrFMm/IRIOxsV8BZq6Q==
    druid:
      connectionProperties: config.decrypt=true;config.decrypt.key=MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBAPQzduvCajNaMU71qL5LGBLjK4v/XFh5/BMTVcMuwdN78OldVCibrJ7j8tR2qWqkzhkomVTp9eEZyuUNF4HCri0CAwEAAQ==
      filters: stat,wall,config
      initialSize: 5
      minIdle: 10
      maxActive: 100
      maxWait: 60000
      timeBetweenEvictionRunsMillis: 60000
      minEvictableIdleTimeMillis: 300000
      maxEvictableIdleTimeMillis: 900000
      validationQuery: SELECT 1
      testWhileIdle: true
      testOnBorrow: false
      testOnReturn: false