springboot2.1.1连接mysql8.0.13遇到的问题解决

问题:
连接时报错,加上各种url参数依然报错,
主要有两个错误

1. Access denied for user ‘root’@‘IP地址’ (using password: YES)
2. Public Key Retrieval is not allowed

即使加上useSSL=false&allowPublicKeyRetrieval=true

jdbc:mysql://IP地址:3306/demo?useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true&rewriteBatchedStatements=true

也无济于事.
mysql命令行执行

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root123';
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root123';
flush privileges;

同样无效

最终解决方案:
在appliaction.properties中配置spring.datasource且给hikari单独配置数据库(也就是说同样的配置要写2遍),并明确指定使用HikariDataSource(你要使用其他的连接池同理)
与时区之类的乱七八糟的参数统统无关

spring.datasource.url=jdbc:mysql://IP地址:3306/demo?useSSL=false
spring.datasource.username=root
spring.datasource.password=root123
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
#明确指定type
spring.datasource.type=com.zaxxer.hikari.HikariDataSource
#同样的配置单独给连接池再来一份
spring.datasource.hikari.jdbc-url=jdbc:mysql://IP地址:3306/demo
spring.datasource.hikari.username=root
spring.datasource.hikari.password=root123
spring.datasource.hikari.driver-class-name=com.mysql.cj.jdbc.Driver

或者直接禁用数据源自动配置(DataSourceAutoConfiguration)

//启动类的SpringBootApplication排除DataSourceAutoConfiguration
@SpringBootApplication(exclude= {DataSourceAutoConfiguration.class})

以下是解决思路:

环境:

pom.xml:

<parent>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-parent</artifactId>
	<version>2.1.1.RELEASE</version>
	<relativePath/>
</parent>
<dependencies>
<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.13</version>
</dependency>
</dependencies>

application.properties

#database
#useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true&rewriteBatchedStatements=true
spring.datasource.url=jdbc:mysql://IP地址:3306/demo
spring.datasource.username=root
spring.datasource.password==root123
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

解决:

1.由于mysql是在linux上的docker容器运行的,首先连接服务器测试db是否正常,经测试服务正常开启

docker exec -it mysql1 /bin/bash
mysql -u root -p

2.再查看端口是否正常,经测试端口开放正常

[root@localhost ~] netstat -tnl
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State      
tcp        0      0 0.0.0.0:80              0.0.0.0:*               LISTEN     
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN     
tcp6       0      0 :::5671                 :::*                    LISTEN     
tcp6       0      0 :::5672                 :::*                    LISTEN     
tcp6       0      0 :::25672                :::*                    LISTEN     
tcp6       0      0 :::3306                 :::*                    LISTEN     
tcp6       0      0 :::6379                 :::*                    LISTEN     
tcp6       0      0 :::15671                :::*                    LISTEN     
tcp6       0      0 :::15672                :::*                    LISTEN

3.都没问题,那么问题一定出在java中,首先测试是否是JDBC问题,经测试JDBC连接正常

package com.example.demo;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

import org.junit.Test;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

public class TestJDBC {
	private static String jdbcUrl = "jdbc:mysql://IP地址:3306/demo";
	private static String driverClassName = "com.mysql.cj.jdbc.Driver";
	private static String username = "root";
	private static String password = "root123";
	
	@Test
	public void exec() throws Exception {
		exe(jdbc());//正常获取连接
		exe(hikari1());//正常获取连接
		exe(hikari2());//正常获取连接
	}
	
	static Connection jdbc() throws Exception {
		Class.forName(driverClassName).newInstance();
		return DriverManager.getConnection(jdbcUrl, username, password);
	}
	
	static Connection hikari1() throws Exception {
		HikariConfig config = new HikariConfig();
		config.setJdbcUrl(jdbcUrl);
		config.setUsername(username);
		config.setPassword(password);
		config.addDataSourceProperty("cachePrepStmts", "true");
		config.addDataSourceProperty("prepStmtCacheSize", "250");
		config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
		config.setDriverClassName(driverClassName);
		HikariDataSource ds = new HikariDataSource(config);
		return ds.getConnection();
	}
	
	static Connection hikari2() throws Exception {
		HikariDataSource ds = new HikariDataSource();
		ds.setJdbcUrl(jdbcUrl);
		ds.setUsername(username);
		ds.setPassword(password);
		ds.addDataSourceProperty("cachePrepStmts", "true");
		ds.addDataSourceProperty("prepStmtCacheSize", "250");
		ds.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
		ds.setDriverClassName(driverClassName);
		Connection conn = ds.getConnection();
		return conn;
	}
	
	static void exe(Connection conn)  throws Exception {
		Statement s = conn.createStatement();
		ResultSet rs = s.executeQuery("select name from user;");
		while(rs.next()) {
			System.out.println(rs.getString("name"));
		}
		conn.close();
	}
}

4.既然直接使用JDBC和连接池都没问题,那么有可能是SpringBoot给HikariDataSource.getConnection()注入的环境有问题呢?首先Debug

package com.zaxxer.hikari;
//省略代码......
public class HikariDataSource extends HikariConfig implements DataSource, Closeable
{
	//省略代码......
	public Connection getConnection() throws SQLException{
	//在第95行打上断点
	}
}

package com.example.demo;

import java.util.List;
import java.util.Map;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.junit4.SpringRunner;

@RunWith(SpringRunner.class)
@SpringBootTest
public class JdbctTests {
	@Autowired
    private JdbcTemplate jdbcTemplate;
	
	@Test
	public void testUser() {
		List<Map<String,Object>> list = jdbcTemplate.queryForList("select * from user");
		list.forEach(System.out::println);
	}
}
/*
经过SpringBootTest的debug,
获取连接时应有的参数(url,username,password)都没有任何问题
但就是要报错,就是要死给你看!!!
是否是连接池需要单独配置呢?
*/

5.依照官方文档给hikari配一份一模一样的配置
application.properties添加

spring.datasource.hikari.jdbc-url=jdbc:mysql://IP地址:3306/demo
spring.datasource.hikari.username=root
spring.datasource.hikari.password=root123
spring.datasource.hikari.driver-class-name=com.mysql.cj.jdbc.Driver

重新使用Junit测试JdbctTests.testUser(),测试依然报错
6.再明确指定datasource type呢?
application.properties添加

spring.datasource.type=com.zaxxer.hikari.HikariDataSource

重新使用Junit测试JdbctTests.testUser().测试成功!!!

再次DEBUG查看HikariDataSource.getConnection()环境与之前并没有任何差别,但不在application给hikari单独添加数据库配置就是不能正常连接数据库,
因为是首次接触springboot,暂时没时间深入查看源码,所以异常原因未知,
在ssm框架中没有过需要重复配置的情况,即使是配置多数据源也不是这么搞的,因而解决这个问题看起来很快,但实际上花了3个小时左右,比较久,不太明白springboot是出于什么样的考虑要这样搞
但好在问题解决了