MySQL Server Deployment with Docker – Basic Installation Instructions for Both the Community and Enterprise Versions

An easy way to setup one or multiple MySQL server deployments on a single server is to use Docker – a computer program that performs operating-system-level virtualization. Docker is simple-to-use and allows you to run multiple containers at once.

A container is a standard unit of software that packages up code and all its dependencies so the application runs quickly and reliably from one computing environment to another. A Docker container image is a lightweight, standalone, executable package of software that includes everything needed to run an application: code, runtime, system tools, system libraries and settings. (Source: https://www.docker.com/resources/what-container)

In other words, think of a container as a virtual machine without the graphical user interface (GUI). There are third-party GUI’s available, but for this post, I am going to use a terminal window.

I am not a Docker expert, so I did have to spend some time figuring out the basics. But with this tutorial, you should be able to install the Docker software and a MySQL server in less than a fifteen minutes.

---

Let’s get started

First, you will need to download and install Docker. I am not going to cover this part, but installation is fairly straightforward. I downloaded and installed the Docker Desktop for my Mac.

I already have a MySQL instance installed on my server, so I will install this new instance using a different port number than the default port of 3306. On the server-side, I will use port 3307 to connect to the default MySQL port of 3306 inside the Docker container. By using a different external port number, I can install multiple MySQL instances on one server, but still use the default port for the MySQL instance. MySQL has their own set of Docker container images on github, and I can install MySQL directly from the command line. I don’t have to download anything separately. You can create your own local repository, but for this example, Docker will pull the latest version from MySQL’s github page.

To install MySQL, I opened a terminal window and ran the following command – changing the first port number (the external port) to 3307. The second port number is the port for the MySQL instance inside the container. You will notice that Docker first checks the local repository, and then once it can’t locate it, it goes out to github. Installation is done via the Docker run command.

$ docker run -p 3307:3306 -d --name mysql -e MYSQL_ROOT_PASSWORD=password mysql/mysql-server
Unable to find image 'mysql/mysql-server:latest' locally
latest: Pulling from mysql/mysql-server
35defbf6c365: Pull complete 
e13cf68584a3: Pull complete 
259d03b6a792: Pull complete 
892ac46af8c0: Pull complete 
Digest: sha256:8dd16a45d0e3e789f2006b608abb1bb69f1a8632a338eef89aec8d6fccda7793
Status: Downloaded newer image for mysql/mysql-server:latest
d8695b074a014f31c65112fb00ec1e5ad79d4c5ba94eb3be1d0fa424f14f414c

I can then verify to see if the MySQL container is up and running via the Docker container command:

$ docker container ls
CONTAINER ID        IMAGE                COMMAND                  CREATED             STATUS                            PORTS                               NAMES
0b55334fedcb        mysql/mysql-server   "/entrypoint.sh mysq…"   2 minutes ago      Up 3 seconds (health: starting)   33060/tcp, 0.0.0.0:3307->3306/tcp   mysql

---

Note:To start or stop the container, simply type use the Docker start/stop command, where mysql is the name of the container – and not the application being run inside the container:

$ docker stop mysql
mysql
$ docker start mysql
mysql

---

Note: If the container isn’t running and you need to start it, you will see an error like this when you try and connect to the container:

$ docker exec -it mysql bash
Error response from daemon: Container d8695b074a014f31c65112fb00ec1e5ad79d4c5ba94eb3be1d0fa424f14f414c is not running

---

I now have a copy of the MySQL container image stored locally on my server. I can look at all of the Docker images installed so far with the Docker images command:

$ docker images -a
REPOSITORY           TAG                 IMAGE ID            CREATED             SIZE
mysql/mysql-server   latest              39649194a7e7        2 weeks ago         289MB

I can verify if MySQL is running by using the Docker container command:

$ docker container ls -a
CONTAINER ID        IMAGE                COMMAND                  CREATED             STATUS                     PORTS               NAMES
0b55334fedcb        mysql/mysql-server   "/entrypoint.sh --ip…"   23 seconds ago      Exited (1) 22 seconds ago                       mysql

Now that I have MySQL installed and I have verified that the container is running, I can connect to the container using the Docker exec command: (The word mysql is the container name, and not the mysql database instance)

$ docker exec -it mysql bash
bash-4.2# 

After connecting, I am now at a regular Linux prompt. The MySQL data directory is stored in /var/lib/mysql, and the configuration file is in /etc/my.cnf.

bash-4.2# cd /var/lib/mysql
bash-4.2# ls -l
total 174160
drwxr-x--- 2 mysql mysql     4096 May  9 17:10 #innodb_temp
-rw-r----- 1 mysql mysql       56 May  9 17:10 auto.cnf
-rw-r----- 1 mysql mysql      178 May  9 17:10 binlog.000001
-rw-r----- 1 mysql mysql      155 May  9 17:10 binlog.000002
-rw-r----- 1 mysql mysql       32 May  9 17:10 binlog.index
-rw------- 1 mysql mysql     1676 May  9 17:10 ca-key.pem
-rw-r--r-- 1 mysql mysql     1112 May  9 17:10 ca.pem
-rw-r--r-- 1 mysql mysql     1112 May  9 17:10 client-cert.pem
-rw------- 1 mysql mysql     1676 May  9 17:10 client-key.pem
-rw-r----- 1 mysql mysql     5456 May  9 17:10 ib_buffer_pool
-rw-r----- 1 mysql mysql 50331648 May  9 17:10 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 May  9 17:10 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 May  9 17:10 ibdata1
-rw-r----- 1 mysql mysql 12582912 May  9 17:10 ibtmp1
drwxr-x--- 2 mysql mysql     4096 May  9 17:10 mysql
-rw-r----- 1 mysql mysql 29360128 May  9 17:10 mysql.ibd
srwxrwxrwx 1 mysql mysql        0 May  9 17:10 mysql.sock
-rw------- 1 mysql mysql        2 May  9 17:10 mysql.sock.lock
drwxr-x--- 2 mysql mysql     4096 May  9 17:10 performance_schema
-rw------- 1 mysql mysql     1676 May  9 17:10 private_key.pem
-rw-r--r-- 1 mysql mysql      452 May  9 17:10 public_key.pem
-rw-r--r-- 1 mysql mysql     1112 May  9 17:10 server-cert.pem
-rw------- 1 mysql mysql     1676 May  9 17:10 server-key.pem
drwxr-x--- 2 mysql mysql     4096 May  9 17:10 sys
-rw-r----- 1 mysql mysql 12582912 May  9 17:10 undo_001
-rw-r----- 1 mysql mysql 10485760 May  9 17:10 undo_002
bash-4.2# ls -l /etc/my.cnf
-rw-r--r-- 1 root root 1239 May  9 17:10 /etc/my.cnf

I can log into MySQL the same way as if it was a regular MySQL instance. (When I created the container, I used “password” as the password, but you will want a more secure password)

bash-4.2# mysql -uroot -ppassword
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 8.0.16 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

Remember – since I am not connecting to the instance from outside of Docker, I don’t have to use port 3307. But, I will have to do that if I want to connect via MySQL Workbench.

Before I connect via MySQL Workbench, I will want to create a different user for this connection, and use this user for my Workbench connection:

mysql> CREATE USER 'docker'@'%' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.02 sec)

mysql> GRANT ALL PRIVILEGES ON * . * TO 'docker'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

I can now create a MySQL Workbench connection. I will open Workbench, and click on the plus symbol to create a new connection.

I need to provide a connection name (Docker Container 3307), the hostname (127.0.0.1) and I need to specify port 3307. If you don’t have another installation of MySQL on your server, you can use the default port of 3306. I will store the password in my keychain by clicking on “Store in Keychain”.

To test and see if you have the correct information, click the “Test Connection” button

I can now use MySQL Workbench to connect to the MySQL Docker container:

That’s it. I now have MySQL installed as a Docker container, and I can access it via a terminal window or via Workbench.

---

MySQL Enterprise Version

The MySQL Community Edition is a great database server, but if you are going to run a database in a production environment, I would recommend you purchasing a MySQL Enterprise Edition license.

The MySQL Enterprise Edition includes the most comprehensive set of advanced features, management tools and technical support to achieve the highest levels of MySQL scalability, security, reliability, and uptime. It reduces the risk, cost, and complexity in developing, deploying, and managing business-critical MySQL applications. (Source)

The steps for installing the Enterprise Edition is almost the same as the Community, but you have to download the Docker image from the Oracle Support portal (My Oracle Support). And, you will need a license to access support. If you are already a customer, login to the support web site, and go to the “Patches and Updates” tab, click on “Patch Search” and then on under “Product or Family (Advanced)”, search for the MySQL version you want and enter the description of “Docker”, then click the “Search” button.

MySQL only has the Linux version of the Enterprise Edition, but I can still install and run it on my Mac. The download file contains a tar file and a README file. For this example, the tar file is named mysql-enterprise-server-8.0.16.tar. I placed this file in my home directory, and from a terminal window, I will need to load the file into the repository using the Docker load command:

$ docker load -i mysql-enterprise-server-8.0.16.tar
d6b2dcf96e3d: Loading layer [==================================================>]  220.6MB/220.6MB
b84b6c2a237e: Loading layer [==================================================>]  8.704kB/8.704kB
141e4cf4cec5: Loading layer [==================================================>]  2.048kB/2.048kB
Loaded image: mysql/enterprise-server:8.0

I can now see the Enterprise Edition image along with the Community Edition image:

$ docker images -a
REPOSITORY                TAG                 IMAGE ID            CREATED             SIZE
mysql/mysql-server        latest              39649194a7e7        2 weeks ago         289MB
mysql/enterprise-server   8.0                 d4410562024a        2 weeks ago         337MB

To install the Enterprise Edition, I only need to change a few of the variables from before. I will also want to use port 3308, since 3306 and 3307 are in use. (Remember – the first port number is the “external” server port number and the second is the port number inside the container)

$ docker run -p 3308:3306 -d --name mysqlEE -e MYSQL_ROOT_PASSWORD=password mysql/enterprise-server:8.0

I can now see the Enterprise Edition container:

$ docker container ls -a
CONTAINER ID        IMAGE                         COMMAND                  CREATED             STATUS                            PORTS                               NAMES
5b4df641d044        mysql/enterprise-server:8.0   "/entrypoint.sh mysq…"   6 seconds ago       Up 4 seconds (health: starting)   33060/tcp, 0.0.0.0:3308->3306/tcp   mysqlEE
0b55334fedcb        mysql/mysql-server            "/entrypoint.sh mysq…"   2 hours ago         Up 2 hours (healthy)              33060/tcp, 0.0.0.0:3307->3306/tcp   mysql

I can connect to docker, and open MySQL – using the container name of mysqlEE:

$ docker exec -it mysqlEE bash

bash-4.2# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.16-commercial MySQL Enterprise Server - Commercial

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

I will want to create a new user like before, and then I can create a Workbench connection as well, using port 3308.

That’s it. I now have two instances of MySQL running in two separate containers on my server.

---

Deleting images and containers

Here are the commands to delete any images or containers. To remove one or more specific images, use the Docker images command to see what images are available:

$ docker images -a
REPOSITORY                TAG                 IMAGE ID            CREATED             SIZE
mysql/mysql-server        latest              39649194a7e7        2 weeks ago         289MB
mysql/enterprise-server   8.0                 d4410562024a        2 weeks ago         337MB

And you can delete the image by deleting the IMAGE ID, by using the Docker rmi command:

$ docker rmi 39649194a7e7
Untagged: mysql/mysql-server:latest
Untagged: mysql/mysql-server@sha256:8dd16a45d0e3e789f2006b608abb1bb69f1a8632a338eef89aec8d6fccda7793
Deleted: sha256:39649194a7e780713ee5681d3bc5ff9e1fddaca744113d4a64ed61f67b7de601
Deleted: sha256:46837581982573a52d3af65de8ac243749c3f8bdf16043541e1a3cfcac721f6b
Deleted: sha256:e311a637abb5186c3bafe967fbb4d10c16258b4b878258ed0ceaff9a07969930
Deleted: sha256:348e9a791d8deb3d6f7ea979c768db0086dbd5172fdbe065649aebfebe509c46
Deleted: sha256:c4a7cf6a6169fb6af5316b4917b6f3417d419b5b5c1e5befd74746996088fc57

To remove a container, use the Docker container command to get a list of containers:

$ docker container ls -a
CONTAINER ID        IMAGE                         COMMAND                  CREATED             STATUS                            PORTS                               NAMES
5b4df641d044        mysql/enterprise-server:8.0   "/entrypoint.sh mysq…"   6 seconds ago       Up 4 seconds (health: starting)   33060/tcp, 0.0.0.0:3308->3306/tcp   mysqlEE
0b55334fedcb        mysql/mysql-server            "/entrypoint.sh mysq…"   2 hours ago         Up 2 hours (healthy)              33060/tcp, 0.0.0.0:3307->3306/tcp   mysql

And you can delete the container by deleting the CONTAINER ID via the the Docker container command:

$ docker container rm 5b4df641d044
5b4df641d044

---

For more information on installing MySQL with Docker, see Deploying MySQL on Linux with Docker.

 

---

 

	Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.
	Tony is the author of Twenty Forty-Four: The League of Patriots  Visit http://2044thebook.com for more information.
	Tony is the editor/illustrator for NASA Graphics Standards Manual Remastered Edition  Visit https://amzn.to/2oPFLI0 for more information.

Advertisement