Toad World Blog

Using MySQL Database on AWS with Docker Cloud

Feb 10, 2017 11:54:00 AM by Deepak Vohra

Docker is the most commonly used containerization platform. It provides a platform similar to a virtualization platform, with some added benefits:

  • Docker containers are lightweight
  • Multiple Docker containers may be run on a single underlying Linux OS with Docker Engine
  • Docker containers are isolated from other Docker containers and are provided their own file system and networking
  • Some Linux OS such as CoreOS have Docker Engine installed out-of-the-box

 

Problem

To run MySQL database on Docker Engine, the Docker binaries have to be installed, an Amazon EC2 instance has to be started, and Docker commands have to be run on the command line. Docker being a relatively new platform, MySQL developers may not be familiar with Docker commands (such as docker pull, docker run, docker ps). Also, scaling MySQL Database on Docker would require still other commands.

 

Solution

Docker Cloud is a hosted service for Docker containers. Docker Cloud may be used for running Dockerized applications in the cloud. A cloud provider such as AWS or Azure is required to be linked to Docker Cloud. Once linked, Docker applications may be run in the cloud service provider from the user interface provided by Docker Cloud. None or very few commands are required to be run explicitly. Docker Cloud provisions a node cluster, creates services and even could be used to create Docker image repositories.  Docker services and Docker containers on the cluster may be managed individually.  Benefits of using MySQL on the Docker Cloud include the following.

  • Managed service
  • MySQL on Docker
  • Scalable MySQL database
  • Easy to use user interface
  • Linked to Cloud Provider (e.g., AWS)

The procedure to use Docker Cloud for creating a service is as follows.

  1. Link to a Cloud Provider
  2. Deploy a Node Cluster (one or more nodes)
  3. Create a Service (based on a Docker image)

 

What is a Cloud Provider?

A cloud provider is a cloud computing service such as Amazon Web Services (AWS), Azure, and IBM’s SoftLayer. 

 

What is a Node Cluster?

A node cluster is a cluster of Linux hosts used to run Docker containers.

 

What is a Service?

A service is a group of Docker containers for software (in this case, MySQL) deployed on a cluster of nodes.

In this tutorial we shall run MySQL Database in Docker Cloud linked with Amazon Web Services. This tutorial has the following sections:

  • Setting the Environment
  • Linking to a Cloud Provider
  • Deploying a Node Cluster
  • Creating a Service
  • Listing the Containers
  • Starting an Interactive Shell
  • Logging Into MySQL CLI
  • Listing the Databases
  • Creating a MySQL Database Table
  • Listing the Docker Container Logs
  • Stopping a Docker Container
  • Terminating a Service
  • Terminating a Node

Setting the Environment

As Docker Cloud is an online service no local software need be installed.  Only a cloud provider account is required to be linked to Docker Cloud; we have used Amazon Web Services as the cloud provider.  Therefore, to start setting the environment, create an Amazon EC2 account.  A Docker Cloud account is also required. 

 

Linking a Cloud Provider

In this section we shall link a cloud provider with Docker Cloud.  For AWS, Docker Cloud requires an Access Key. Log in to the Amazon EC2 account and expand the Access Keys section. Click on the Create New Access Key button.  A new Access Key gets created. Click on Show Access key link to display the Access Key. The Access Key ID and Secret Access key get displayed. Copy the Access Key ID and Secret Access Key, as we shall need these to link Amazon Web Services with Docker Cloud. In the Account Info page of the Docker Cloud, select the Cloud Providers tab.  For the Amazon Web Services provider, click on the “Add credentials” button.

3326.Deepdoc 1

In the Amazon Web Services credentials dialog, paste the Access Key ID in the field for Access Key ID. Paste the Secret Access Key in the field for Secret Access Key. Click on Save Credentials.

5483.Deepdoc 2

The Amazon AWS account gets linked with the Docker Cloud account.

8206.deepdoc 3

 

Deploying a Node Cluster

 To create a node cluster click on the Nodes tab in the Account Info page. In the Node dashboard click on “Launch your first node”; if it is the first node cluster being created, and click on “Launch new code cluster” to create a subsequent new node cluster.

6558.deepdoc 4

In the “Create a node cluster” page, specify a Node cluster name (OracleDockerCloud for example).  In the Provider field select Amazon Web Services.  Select Region; for example us-east-1.  Select the default settings for "Auto" for VPC, Subnet and Security group. Select Type/size as t2.micro. Select Number of nodes as 1.  Select a Disk size, 60GB for example. Click on “Launch node cluster”.

4087.deepdoc 5

A new node cluster gets created.  Click on Apply to apply the 1 node to the cluster. The Deploying message gets displayed.

2330.deepdoc 6

After the nodes are deployed the messages becomes "Deployed". The single node is displayed in a table of Hostname and State.

6153.deepdoc 7

 

In the Node dashboard, the single node is displayed with Status “Deployed”.  

0777.deepdoc 8

Click on the node link to display the containers and endpoints in the node.

2844.deepdoc 9

Initially the node does not list any Docker containers as none have been created yet.

3010.deepdoc 10

 

Creating a Service

Having created a node cluster, next we shall run Docker containers on the node. A few different options are available to start Docker containers. First is to click on the link to “start deploying containers”.

7624.deepdoc 11

Alternatively, and the preferred option, is to select the Services tab.

5076.deepdoc 12

If it is the first service, click on “Create your first service” button.

3414.deepdoc 13

Select the Docker image “mysql” in Database servers category.

3823.deepdoc 14

Select the default settings for Service name, Image tag, and Deployment strategy. Select Number of containers as 1. Select the default port setting of 3306 tcp port. Click on Next: environment variables.

2437.deepdoc 15

The only required environment variable is MYSQL_ROOT_PASSWORD. Add the variable value as "mysql" and click on Add.

 4503.deepdoc 16

 

The MYSQL_ROOT_PASSWORD variable gets added.

 3007.deepdoc 17

 

Also add the following optional environment variables.

Environment Variable

Value

MYSQL_DATABASE
mysqldb
MYSQL_USER
mysql
MYSQL_PASSWORD
mysql
MYSQL_ALLOW_EMPTY_PASSWORD
no

 

With the environment variables added, click on Create service to create the service.

8787.deepdoc 18

A service gets created. Initially the service is not running and the Docker containers are not running.

3021.deepdoc 19

Click on Start to start the service.

7711.deepdoc 20

The Starting message should get displayed and a Docker container should also be “Starting”.

3581.deepdoc 21

After the Docker container and the service have started the “Starting” message turns to “Running”.

7723.deepdoc 22

 

Starting an Interactive Shell

To start an interactive bash shell for the Docker container two options are available. For the single Docker container click on the >_ button.

3513.deepdoc 23

 

Alternatively, click on the Terminal tab to start an interactive terminal.

4812.deepdoc 24

 An interactive terminal is available only when a Docker container is running.

0777.deepdoc 25

 

Logging Into MySQL CLI

To start the MySQL command line interface (CLI) as root, run the following command:

mysql -u root -p

Specify the password as configured in the environment variable MYSQL_ROOT_PASSWORD. The MySQL CLI gets started.

7624.deepdoc 26

 

Listing Databases

To list the databases run the following command:

show databases;

The databases listed include the system databases, the mysqldb database created using the MYSQL_DATABASE environment variable, and the user database mysql.

 6646.deepdoc 27

Run the following command to set the database to use as “mysql”.

use mysql;

The database gets set as “mysql”.

2437.deepdoc 28

 

Creating a MySQL Database Table

To create an Oracle Database table wlslog run the following SQL statement.

CREATE  TABLE  wlslog(time_stamp VARCHAR(45) PRIMARY KEY,category VARCHAR(25),type VARCHAR(25),
servername VARCHAR(25),code VARCHAR(25),msg VARCHAR(45));

The wlslog table gets created.

7217.deepdoc 29

 

Listing the Docker Container Logs

To list the Docker container logs, click on the Logs tab for the service. The MySQL database is listed as running on port 3306 “ready for connections”.

 0383.deepdoc 30

 

Stopping a Docker Container

To stop a Docker container click on the Stop button.

5164.deepdoc 31

The “Stopping” message gets displayed.

7230.deepdoc 32

 

Terminating a Service

Stopping a Docker container in a service stops the service. A stopped service may either be started again with the Start button or terminated with the Terminate button. A terminated service cannot be restarted.

8306.deepdoc 33

Click on OK in the confirmation dialog.

4186.deepdoc 34

The service gets terminated as indicated by the Terminated status. 

2425.deepdoc 35

 

Terminating a Node

Terminating a service does not terminate the node cluster. To terminate the node cluster click on Terminate for the node in the Node dashboard.

0876.deepdoc 36

Click on the OK button in the confirmation dialog. At first, the Status of the node turns to “Terminating”.

2043.deepdoc 37

Subsequently the Status becomes “Terminated” A terminated node cluster cannot be deployed again.

8371.deepdoc 38

In this tutorial we discussed deploying a Docker service for MySQL database on Docker Cloud. We discussed setting environment variables and starting and stopping the service. We created a sample database table using an interactive shell for the Docker container in the service.  Docker Cloud is a managed service and especially useful if you are not very familiar with Docker.

Tags: MySQL

Deepak Vohra

Written by Deepak Vohra

Deepak Vohra is an Oracle Certified Associate, Oracle Database 10g, and Sun Certified Java Programmer. Deepak has published on OTN and in Oracle Magazine.