Spring Boot连接远程服务器MySQL数据库

最近因为某些原因需要 Spring Boot连接远程MySQL数据库 写项目,其实之前在刚学这个Spring Boot的时候也尝试直接连接到远程数据库,但是均以失败告终,最近终于解决了这个问题,特此记录也同时分享一下自己的经验吧,希望能帮助到有需要的人。

前提条件

  1. 服务器已经安装并启动数据库服务,如MySQL
  2. 建立相应的数据库,下面以我要连接的数据库example 为例

整合Mybatis

在pom.xml 引入相关依赖

 <!--引入mybatis-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.2.0</version>
        </dependency>
  <!--mysql-connector-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

编辑配置文件

编辑application.properties,准备连接数据库

#connect to mysql
#yremp.live 可以修改为你的服务器公网ip地址 | example 是你要连接的数据库名称
spring.datasource.url=jdbc:mysql://yremp.live:3306/example?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC&allowMultiQueries=true
#数据库用户名
spring.datasource.username=example
#对应用户的密码
spring.datasource.password=123456
#JDBC驱动固定格式
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

放行数据库运行端口

这是最重要的一个环节,我前几次失败的原因就在于此,只顾着考虑MySQL已经运行在3306端口,而我忘记了去防火墙放行3306端口,导致无法连接到数据库。

pic018499e.png

如果不修改,则会报以下错误信息

Whitelabel Error Page
This application has no explicit mapping for /error, so you are seeing this as a fallback.

Mon Sep 09 10:19:40 CST 2019
There was an unexpected error (type=Internal Server Error, status=500).
nested exception is org.apache.ibatis.exceptions.PersistenceException: ### Error querying database. Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server. ### The error may exist in com/example/demo/mapper/UserMapper.java (best guess) ### The error may involve com.example.demo.mapper.UserMapper.SelectAll ### The error occurred while executing a query ### Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.

修改数据库访问权限

最后就是数据库权限,需要修改为指定ip或者所有人

pic0205558.png

否则报以下错误信息

Whitelabel Error Page
This application has no explicit mapping for /error, so you are seeing this as a fallback.

Mon Sep 09 10:16:02 CST 2019
There was an unexpected error (type=Internal Server Error, status=500).
nested exception is org.apache.ibatis.exceptions.PersistenceException: ### Error querying database. Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLException: null, message from server: "Host '1.194.129.186' is not allowed to connect to this MySQL server" ### The error may exist in com/example/demo/mapper/UserMapper.java (best guess) ### The error may involve com.example.demo.mapper.UserMapper.SelectAll ### The error occurred while executing a query ### Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLException: null, message from server: "Host '1.194.129.186' is not allowed to connect to this MySQL server"

查询数据

编写代码查询数据,这里就不展示代码,就是简单的数据查询

左边是查询结果,右边是数据库信息

注意事项

为方便快速搭建环境,我使用了以下工具

  1. 宝塔面板,快速安装配置管理MySQL等服务。
  2. Navicat Premium 12 ,数据库管理工具。
  3. IntelliJ IDEA,这个应该不用多说。
Author: yremp
Link: /2019/09/09/spring-boot-mysql/
Copyright Notice: All articles in this blog are licensed under CC BY-NC-SA 4.0 unless stating additionally.