IBM Cloud Satellite: Run and manage services anywhere Learn more

Create a MySQL database on OpenShift and link it to your microservices app

Typically, deploying your database to an online server is not as easy as it sounds. It takes a lot of configuration to get it done and to integrate it to your application. Even after successfully deploying the database, maintaining, updating, monitoring, and logging the deployment could be really painful tasks. Moreover, consider the endless security concerns when trying to access the database locally. Fortunately, Red Hat OpenShift can make life easier! With just few clicks, you should be able to have a MySQL database securely deployed and ready for development or even for production behind a firewall. You can safely access and populate the database whether from your local machine or from the deployed pod itself.

This tutorial is a step-by-step guide to teach you how to create a MySQL instance on Red Hat OpenShift on IBM Cloud and integrate with your microservices-based application, which is also deployed on Red Hat OpenShift. The tutorial is intended for developers who spend lots of effort deploying, managing, updating, and monitoring their applications and are interested in microservices in general. After completing the steps, you should have an example microservices-based application for viewing the list of customers and each customer’s details all gathered from the database.

Prerequisites

Before you begin this tutorial, you should have basic SQL knowledge and set up the following environment:

Estimated time

This tutorial takes about 45 minutes to complete.

Step 1: Create a MySQL instance and add data to the database

The first step is to create a project using the following command:

oc new-project mysql-project

Or, you can create a project from the web console:

OpenShift Container Platform web console

  1. Create a MySQL instance from the web console by choosing MySQL(Ephemeral) from the catalog.

    Ephemeral basically means that the database is stateless, meaning all the data stored would be lost if the pod carrying the database is restarted/deleted. This is usually used for testing/development. The other option is to choose MySQL from the catalog which adds persistent volume storage to our project but it is out of scope of this tutorial.

  2. After choosing MySQL(Ephemeral) from the catalog, enter a new MySQL Connection Username, MySQL Connection Password, and MySQL root user Password. These are the credentials that are used to access the database later along with the Database Service Name and MySQL Database Name. The service takes about one minute to provision.

    MySQL(Ephemeral)

  3. Now that you have your MySQL service ready, the next step is to populate the database. There are two ways:

    • One way is to get into the actual pod that is carrying our deployment and populate the database using the mysql client.

      1. Go to your terminal and run the following command:

         oc get pods
        
      2. Copy the name of the pod that contains MySQL as previously shown, and run the following command:

         oc rsh <POD_NAME>
        
      3. Now that you are inside the pod, it is time to add some data to our database. Run the following command:

         mysql -u  <ENTER_MYSQL_USERNAME> -p
        

        Add data to the MySQL database

      4. After you are logged in, execute the following query to create a new table:

         USE sampledb; DROP TABLE IF EXISTS customer; CREATE TABLE IF NOT EXISTS customer ( id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, customerName VARCHAR( 255 ) NOT NULL, effectiveDate VARCHAR( 255 ), description TEXT, status VARCHAR( 255 ) NOT NULL );
        
      5. Then run the following command to add data to the table:

         USE sampledb; INSERT INTO customer VALUES (1,'Testers Inc.','2020-04-01','Testers are who we hire to test our software','active'), (2,'Deployers CO','2019-06-01','Deployers co. deploy our software','active'), (3,'DJ John Doe','2019-12-01','John provides the music for our annual holiday party.',"active"), (4,'Doe Hypermarket','2019-06-05','Doe is where we purchase all food supply.',"active");
        
      6. To make sure everything is fine, run the following query and you should see the records you just entered:

         select * from sampledb.customer;
        
      7. You are done here, so type exit to quit the mysql client and then exit again to exit the pod.

    • Another way to populate the database is by using a Database Management tool like MySQL Workbench on your host machine. You forward the port in the pod that carries the MySQL server to a port on your local machine.

      1. Go to your terminal and run the following command:

         oc get pods
        
      2. Copy the name of the pod that contains MySQL as shown previously and run the following command:

         oc port-forward <POD_NAME> :3306
        

        This command allocates a free port on your local machine to host the MySQL server from the pod.

        Allocate a free port to host the MySQL server

      3. Open the MySQL Workbench and add a new connection using your database credentials and the allocated port to your localhost.

        MySQL Workbench

      4. After you are connected, run the following query to create a new table:

         USE sampledb; DROP TABLE IF EXISTS customer; CREATE TABLE IF NOT EXISTS customer ( id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, customerName VARCHAR( 255 ) NOT NULL, effectiveDate VARCHAR( 255 ), description TEXT, status VARCHAR( 255 ) NOT NULL );
        
      5. Then run the following command to add data to the table:

         USE sampledb; INSERT INTO customer VALUES (1,'Testers Inc.','2020-04-01','Testers are who we hire to test our software','active'), (2,'Deployers CO','2019-06-01','Deployers co. deploy our software','active'), (3,'DJ John Doe','2019-12-01','John provides the music for our annual holiday party.',"active"), (4,'Doe Hypermarket','2019-06-05','Doe is where we purchase all food supply.',"active");
        

        MySQL Workbench

Step 2. Deploy a microservices-based application and integrate it with your database

  1. Create the getCustomer service.

    Note: The variables MYSQL_USER and MYSQL_PASSWORD are determined by the values returned after you create the MySQL service in the previous step.

     oc new-app https://github.com/IBM/mysql-openshift --context-dir=src/getCustomer --name getcustomer -e MYSQL_HOST=mysql -e MYSQL_DATABASE=sampledb -e MYSQL_USER=<YOUR_USERNAME> -e MYSQL_PASSWORD=<YOUR_PASSWORD>
    
  2. Expose the getCustomer service.

     oc expose service getcustomer --insecure-skip-tls-verify=false
    
  3. Create the getCustomerList service.

    Note: The variables MYSQL_USER and MYSQL_PASSWORD are determined by the values returned after you create the MySQL service in the previous step.

     oc new-app https://github.com/IBM/mysql-openshift --context-dir=src/getCustomerList --name getcustomerlist -e MYSQL_HOST=mysql -e MYSQL_DATABASE=sampledb -e MYSQL_USER=<YOUR_USERNAME> -e MYSQL_PASSWORD=<YOUR_PASSWORD>
    
  4. Expose the getCustomerList service.

     oc expose service getcustomerlist --insecure-skip-tls-verify=false
    
  5. Get the routes for both services:

     oc get routes
    

    Get the routes for the services

  6. Create the customerui service.

    This step pulls a container image from Docker Hub. This is being done to showcase the versatility of the OpenShift application build feature.

    Note: The variables REACT_APP_CUSTOMER_LIST_URI and REACT_APP_CUSTOMER_URI are determined by the values returned from the previous step.

     oc new-app --name customerui --docker-image=docker.io/mohamed7sherif/mysql-react:latest -e REACT_APP_CUSTOMER_LIST_URI="http://<getcustomerlist_SERVICE_URI>/customers" -e REACT_APP_CUSTOMER_URI="http://<getcustomer_SERVICE_URI>/customer/"
    
  7. Expose the customerui website.

     oc expose service customerui --insecure-skip-tls-verify=false
    
  8. Test it.

    Check that all pods are ready:

     oc get pods
    

    Get the route, and then open it in your browser:

     oc get routes
    

    Copy the route named customerui and paste it in your browser to make sure the application is running.

Summary

As you saw throughout the tutorial, when you use OpenShift, a lot of steps and configurations are cut out of your process, replaced with few clicks.

You witnessed how easy it is provision a MySQL instance on Red Hat OpenShift on IBM Cloud, and you also learned how to safely populate the database using different methods. The tutorial also walked you through deploying microservices to OpenShift and integrating many services together, including your database.

To continue working with OpenShift, try some more OpenShift tutorials.