最近因为某些原因需要 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，这个应该不用多说。

标签云

ajax AOP Bootstrap cdn Chevereto CSS Docker Editormd GC Github Hexo IDEA JavaScript jsDeliver JS樱花特效 JVM Linux Live2D markdown Maven MyBatis MyBatis-plus MySQL Navicat Oracle Pictures QQ Sakura SEO Spring Boot Spring Cloud Spring Cloud Alibaba SpringMVC Thymeleaf Vue Web WebSocket Wechat Social WordPress Yoast SEO 代理 分页 图床 小幸运 通信原理