In this blog post we will start a MySQL database inside a container, and then create and populate a database. In most production environments you may not find database server instances running in containers because of performance issues around Storage IOPs and network bandwidth which is critical for heavy database operations.

In this setup you can choose Podman or Docker as your container runtime engine. For Ubuntu and Debian based systems Docker might be the best option and for RHEL based systems you can go with Podman. Use the commands provided in the next sections to install either Podman and Docker runtimes.

Step 1: Install Docker / Podman Container Engine

For Docker and Podman installation we’ll provide installation commands for Debian/Ubuntu and CentOS Linux system.

Install Docker on Debian:

Run the following commands in your Debian system to install Docker CE.

sudo apt-get update
sudo apt-get install curl software-properties-common apt-transport-https ca-certificates gnupg-agent
curl -fsSL https://download.docker.com/linux/debian/gpg | sudo apt-key add -
sudo add-apt-repository "deb [arch=amd64] https://download.docker.com/linux/debian $(lsb_release -cs) stable"
sudo apt-get update
sudo apt-get install docker-ce docker-ce-cli containerd.io
sudo usermod -aG sudo $USER
newgrp docker

Install Docker on Ubuntu:

Below are the commands you need to install Docker CE on Ubuntu Server and Desktop.

sudo apt-get update
sudo apt-get install curl software-properties-common apt-transport-https ca-certificates gnupg-agent
curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo apt-key add -
sudo add-apt-repository "deb [arch=amd64] https://download.docker.com/linux/ubuntu $(lsb_release -cs) stable"
sudo apt-get update
sudo apt-get install docker-ce docker-ce-cli containerd.io
sudo usermod -aG sudo $USER
newgrp docker

Install Docker on CentOS:

On a CentOS Linux docker community edition can be installed by running the following commands as a user with sudo permissions.

sudo yum install -y yum-utils
sudo yum-config-manager --add-repo https://download.docker.com/linux/centos/docker-ce.repo
sudo yum -y install docker-ce docker-ce-cli containerd.io
sudo systemctl enable --now docker
sudo usermod -aG wheel $USER
newgrp docker

Install Podman on CentOS:

Podman package is available in the default CentOS repositories.

sudo yum -y install podman

Install Podman on Ubuntu:

Use OpenSUSE provided and supported repositories for installation on Ubuntu:

. /etc/os-release
echo "deb https://download.opensuse.org/repositories/devel:/kubic:/libcontainers:/stable/xUbuntu_${VERSION_ID}/ /" | sudo tee /etc/apt/sources.list.d/devel:kubic:libcontainers:stable.list
curl -L https://download.opensuse.org/repositories/devel:/kubic:/libcontainers:/stable/xUbuntu_${VERSION_ID}/Release.key | sudo apt-key add -
sudo apt-get update
sudo apt-get -y upgrade 
sudo apt-get -y install podman

Step 2: Download MySQL Docker Image

Before we can created MySQL instance in a container we need to pull the latest image for the MySQL version we intend on running.

MySQL 8.

## Docker
$ docker pull mysql:8

## Podman
$ podman pull mysql:8

MySQL 5.7:

## Docker
$ docker pull mysql:5.7

## Podman
$ podman pull mysql:5.7

Listing Docker images:

$ docker images
REPOSITORY          TAG                 IMAGE ID            CREATED             SIZE
mysql               5.7                 ae0658fdbad5        10 hours ago        449MB
mysql               8                   dd7265748b5d        10 hours ago        545MB

Listing Podman images:

$ podman images
REPOSITORY               TAG     IMAGE ID      CREATED       SIZE
docker.io/library/mysql  5.7     ae0658fdbad5  10 hours ago  454 MB
docker.io/library/mysql  8       dd7265748b5d  10 hours ago  551 MB

Step 3: Create Persistent Data Directory

If we want to persist database data let’s create a directory where data will be store in our host system.

mkdir ~/mysql_data

Confirm the directory is created and empty.

$ ls -lh ~/mysql_data
total 0

If you have SELinux in enforcing mode apply the container_file_t context to the directory to allow container access to all of its contents.

sudo semanage fcontext -a -t container_file_t ~/mysql_data
sudo restorecon -Rv ~/mysql_data

Step 4: Run MySQL Database Instance in a Container

With all requirements met we can create MySQL database instance. The key Environment Variables available when running a MySQL Container are:

  • MYSQL_ROOT_PASSWORD: This variable is mandatory and specifies the password that will be set for the MySQL root superuser account.
  • MYSQL_DATABASE: This variable is optional and allows you to specify the name of a database to be created on image startup.
  • MYSQL_USER, MYSQL_PASSWORD: Optional and used in conjunction to create a new user and to set that user’s password. This user will be granted superuser permissions for the database specified by the MYSQL_DATABASE variable.

As an alternative to passing sensitive information via environment variables, _FILE may be appended to the previously listed environment variables, causing the initialization script to load the values for those variables from files present in the container.

Run container with Docker

This is an example container:

docker run -d \
--name mysql-8 \
-v mysql_data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD='[email protected]' \
mysql:8

Verify that the container was started withoutt errors:

$ docker ps --format "{{.ID}} {{.Image}} {{.Names}}"
5cc254aa2981 mysql:8 mysql-8

You can run the docker ps command to list all running containers:

$ docker ps
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                 NAMES
b28ffd77d489        mysql:8             "docker-entrypoint.s…"   43 seconds ago      Up 41 seconds       3306/tcp, 33060/tcp   mysql-8

Run container and create new database and user on startup.

docker run -d \
--name mysql-8 \
-v ~/mysql_data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD='[email protected]' \
-e MYSQL_USER=dbuser1 \
-e MYSQL_PASSWORD='dbuser1password' \
-e MYSQL_DATABASE=testdb \
mysql:8

Check data directory to confirm volume mounting works:

$ ls ~/mysql_data
 auto.cnf        binlog.index   client-cert.pem     '#ib_16384_1.dblwr'   ib_logfile0  '#innodb_temp'   performance_schema   server-cert.pem   testdb
 binlog.000001   ca-key.pem     client-key.pem       ib_buffer_pool       ib_logfile1   mysql           private_key.pem      server-key.pem    undo_001
 binlog.000002   ca.pem        '#ib_16384_0.dblwr'   ibdata1              ibtmp1        mysql.ibd       public_key.pem       sys               undo_002

Remove and clean data of running container.

$ docker ps
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                 NAMES
b28ffd77d489        mysql:8             "docker-entrypoint.s…"   5 minutes ago       Up 5 minutes        3306/tcp, 33060/tcp   mysql-8

$ docker rm -f b28ffd77d489
b28ffd77d489

$ rm -rf ~/mysql_data/*

Run container with Podman

The commands are similar to ones used with Docker.

podman run -d \
--name mysql-8 \
-v ~/mysql_data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD='[email protected]' \
-e MYSQL_USER=dbuser1 \
-e MYSQL_PASSWORD='dbuser1password' \
-e MYSQL_DATABASE=testdb \
mysql:8

Confirm it is running:

$ podman ps
CONTAINER ID  IMAGE                      COMMAND  CREATED        STATUS            PORTS   NAMES
83524fde1c05  docker.io/library/mysql:8  mysqld   5 seconds ago  Up 4 seconds ago          mysql-8

Step 4: Running mysql commands inside the MySQL container

You can access the container sandbox by running the following command:

## Docker
$ docker exec -it mysql-8 /bin/bash
[email protected]:/#

## Podman
$ podman exec -it mysql-8 /bin/bash
[email protected]:/#

Connect to MySQL as the database administrator user (root).

[email protected]:/# mysql -uroot -p'[email protected]'
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 9
Server version: 8.0.22 MySQL Community Server - GPL

Copyright (c) 2000, 2020, 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>

Use MySQL interactive prompt to list created databases.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb             |
+--------------------+
5 rows in set (0.02 sec)

You can create new table in the database:

mysql> USE testdb;
Database changed

Create a table called Projects in the testdb database.

CREATE TABLE Projects (id int(11) NOT NULL,
  name varchar(255) DEFAULT NULL,
  code varchar(255) DEFAULT NULL,
  PRIMARY KEY (id));

Use the show tables command to verify that the table was created.

mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| Projects         |
+------------------+
1 row in set (0.01 sec)

Use the insert command to insert a row into the table.

mysql> INSERT INTO Projects (id, name, code) values (1,'IT','Servers');
Query OK, 1 row affected (0.03 sec)

Verify data added to the table.

mysql> SELECT * FROM Projects;
+----+------+---------+
| id | name | code    |
+----+------+---------+
|  1 | IT   | Servers |
+----+------+---------+
1 row in set (0.01 sec)

When done exit from the MySQL prompt and the MySQL container:

mysql> exit
Bye
[email protected]:/# exit
exit

Reference:

LEAVE A REPLY

Please enter your comment!
Please enter your name here

eighteen + 9 =