Spring Boot集成MySQL遇Access denied数据库连接权限错误求助

问题现象 #

启动Spring Boot应用时遇到以下错误：

java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES)

相关配置文件 #

application.properties #

spring.datasource.url=jdbc:mysql://localhost:3306/AssignmentSubApp_DB
spring.datasource.username=root
spring.datasource.password= password123

spring.jpa.database-platform=org.hibernate.dialect.MySQLDialect
spring.jpa.show-sql=true
#spring.jpa.hibernate.ddl-auto=update

pom.xml #

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>3.1.2</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.AssignmentApp</groupId>
    <artifactId>AssignmentSubmission</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>war</packaging>
    <name>AssignmentSubmission</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>11</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-security</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-tomcat</artifactId>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.security</groupId>
            <artifactId>spring-security-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

排查与解决步骤 #

1. 修正密码配置的空格问题 #

从application.properties中可以看到spring.datasource.password= password123的密码前有多余空格，这会导致实际传递的密码带空格，与MySQL中存储的密码不匹配。直接去掉空格：

spring.datasource.password=password123

2. 验证MySQL用户权限 #

登录MySQL控制台，执行以下命令确认root@localhost的权限情况：

SELECT user, host FROM mysql.user WHERE user='root';
SHOW GRANTS FOR 'root'@'localhost';

若该用户没有AssignmentSubApp_DB数据库的操作权限，执行授权命令：

GRANT ALL PRIVILEGES ON AssignmentSubApp_DB.* TO 'root'@'localhost';
FLUSH PRIVILEGES;

3. 确认数据库与服务状态 #

• 检查数据库是否存在：

SHOW DATABASES LIKE 'AssignmentSubApp_DB';

不存在则创建：

CREATE DATABASE AssignmentSubApp_DB;

• 确保本地MySQL服务正在运行，端口3306未被占用。

4. 命令行测试连接 #

用命令行直接测试数据库连接，确认参数正确性：

mysql -u root -ppassword123 -h localhost -P 3306 AssignmentSubApp_DB

如果能成功连接，说明数据库端配置无问题，重启Spring Boot应用即可。

内容的提问来源于stack exchange，提问作者Anonymous