Deploy Spring Boot App with MySQL Database on Heroku

Details
Written by  
Last Updated on 21 November 2023   |   Print  Email


In this Heroku tutorial, I’d love to share with you, fellow developers, about deployment of a Spring Boot application to Heroku cloud platform with MySQL database, using Heroku Command Line Interface (Heroku CLI) and Git.

To follow this guide, you must have:

  • an existing Spring Boot project with MySQL database
  • a verified Heroku account
  • Heroku CLI installed
  • Git installed
  • MySQL Community server with MySQL Command Line Client

 

1. Deploy a Spring Boot Project to Heroku

The process for deploying a Spring Boot project to Heroku is similar to this guide. Here I just summary the steps and commands:

  • Open a new command prompt on Windows, or terminal on Mac or Linux.
  • Change the current directory to your project’s root directory
  • Create a new Git local repo: git init
  • Add the project’s files to the local repo’s index: git add .
  • Commit the files to Git local repo: git commit –m “first commit”
  • Login to Heroku: heroku login
  • Create a new Heroku app: heroku create your-app-name
  • Deploy the project via Git: git push heroku master

Now you should have your Spring Boot app deployed on Heroku, but it is not working because no MySQL database installed.


2. Install ClearDB MySQL Add-on

You know, Heroku supports PostgreSQL by default so if you need to use MySQL, you need to install an add-on for your app. There are several add-ons for MySQL on Heroku’s marketplace such as Trevor, JawsDB MySQL, Draxlr, ClearDB MySQL… and in this tutorial, I will show you how to install ClearDB MySQL add-on for a Heroku app.

Type the following command to install ClearDB MySQL add-on for your app:

heroku addons:create cleardb:ignite –a your-app-name

This will create a new add-on ClearDB MySQL with free plan called ignite. With free plan, you can have a MySQL database of maximum 5MB and maximum 10 concurrent connections, and maximum 10,000 queries per hour. If you want to raise this limit, you need to purchase a paid plan.

Once installed, this add-on creates a new config var for your app, named CLEARDB_DATABASE_URL, which stores database connection information. It is something like this:

CLEARDB_DATABASE_URL: mysql://username:password@hostname/schema_name

ClearDB creates a new MySQL database schema with a random name, and also gives you a credential to access the database – as you can see in the config var.


3. Export local MySQL database to SQL file

Next, you need to export (dump) your local MySQL database to a .sql file. Open a new command prompt (terminal). Change the current directory to the bin folder of MySQL server installation directory. Type the following command:

mysqldump –u username –p schema_name > mysql_dump_file.sql

This command exports the given database schema to a .sql file. Replace username, schema_name and mysql_dump_file by actual values. Note that you need to enter MySQL password to execute this command.


4. Import SQL File to Remote MySQL Server

Next, I will show you how to connect to the remote MySQL server to import the .sql file. Type this command to see config vars of your app:

heroku config –a your_app_name

Extract username, password, hostname and schema name from the CLEARDB_DATABASE_URL config var. And type the following command to connect to the remote MySQL server with MySQL Command Line client program:

mysql –u username –p –h hostname

You need to enter password to login. Then in MySQL Command Line Client prompt, type this command to connect to the database schema created by ClearDB add-on:

connect schema_name;

Next, type the following command to get the character set and collation of the current database:

select @@character_set_database, @@collation_database

You would see the character set is utf8 and collation is utf8_general_ci. Open the .sql file using a text editor, and you need to update a couple of things:

Insert this line before the SQL statements to use the database schema:

use schema_name;

Then replace all values of character set by utf8; collate/collation by utf8_general_ci.

Save the .sql file. Then type exit to quit the MySQL Command Line client program.

 

Spring Boot E-Commerce Ultimate Course

Learn to Build a complete shopping website using Java, Spring Boot, Thymeleaf, Bootstrap, jQuery and MySQL database

 

Now type this command to import the .sql file to remote MySQL server:

mysql –u username –p –h hostname < mysql_dump_file.sql

You need to enter password when asked. And wait for a while until the command finishes silently. Then you can connect to the remote MySQL server again, and type the following commands to verify the data imported properly:

      connect schema_name;

      show tables;

      desc table table_name;

      select * from table_name;

Then type exit to quit MySQL Command Line client program.


5. Access MySQL Database in Spring Boot App

Note that if your deployed app is a Spring Boot application, ClearDB MySQL add-on will also add the following environment variables:

SPRING_DATASOURCE_URL

SPRING_DATASOURCE_USERNAME

SPRING_DATASOURCE_PASSWORD

Spring framework prioritizes environment variables over ones declared in the application configuration file (application.properties) so you don’t need to change database connection information for your project. The deployed app will be able to connect to the installed MySQL database automatically.

That’s how to deploy a Spring Boot project with MySQL database to Heroku, with Heroku CLI, Git and ClearDB MySQL add-on. To see the steps in action, I recommend you watch this video:

 

Other Heroku tutorials:


About the Author:

is certified Java programmer (SCJP and SCWCD). He began programming with Java back in the days of Java 1.4 and has been passionate about it ever since. You can connect with him on Facebook and watch his Java videos on YouTube.