Dockerizing MySQL step by step.


As a Database Engineer in Mydbops. We tend to solve multiple complex problems for our esteemed customers. To control the System resources and scale up /down based on needed we are evaluating Dockers and Kubernetes.

Docker is a set of platform as a service products that uses OS-level virtualization to deliver software in packages called Containers. Containers are isolated from one another and bundle their own software, libraries and configuration files; they can communicate with each other through well-defined channels.It’s more lightweight than standard Containers and boots up in seconds.

Docker also is easy to use when you need a simple, single instance. What is great about Docker though is that it allows configuring multiple versions of MySQL.

Docker Installation:

Docker can be installed with yum repository or apt-get repository based on your linux operating system distributions. (I’m using CentOS 7 operating system in the following examples).

[root@mydbopslabs202 vagrant]# yum install docker -y

To start the docker service, Run the following command:

[root@mydbopslabs202 vagrant]# systemctl start docker

How to pull the Docker MySQL Images?


There are  two official MySQL Docker Repositories. 

docker run mysql:latest
docker run mysql/mysql-server:latest  

I have used docker’s team images(latest), from Oracle MySQL’s team , but there are many custom designed docker images available in the Dockerhub too.

To download the MySQL Server image, run this command:

 shell> docker pull mysql/mysql-server:tag

If :tag is omitted, the latest tag is used by default and the image for the latest GA version of MySQL Server is downloaded. For older versions use the tags available with above command.

Step 1 : Pull the Docker image for MySQL

[root@mydbopslabs202 vagrant]# docker pull mysql/mysql-server:latest
Trying to pull repository docker.io/mysql/mysql-server ...
latest: Pulling from docker.io/mysql/mysql-server
c7127dfa6d78: Pull complete
530b30ab10d9: Pull complete
59c6388c2493: Pull complete
cca3f8362bb0: Pull complete
Digest: sha256:7cd104d6ff11f7e6a16087f88b1ce538bcb0126c048a60cd28632e7cf3dbe1b7
Status: Downloaded newer image for docker.io/mysql/mysql-server:latest

To list all the docker images downloaded, run this command:

[root@mydbopslabs202 vagrant]# docker images
REPOSITORY                     TAG                 IMAGE ID            CREATED             SIZE
docker.io/mysql/mysql-server   latest              a7a39f15d42d        3 months ago        381 MB

Start MySQL Server Instance

Start a new docker container for MySQL Server, run below command

[root@mydbopslabs202 vagrant]# docker run --name=test -d mysql/mysql-server:latest -m 500M -c 1 --port=3306
585f3cec96f1636838b7327e80b10a0354f13fc0e9d4f06f07b3b99c59d2c319

The –name option, for supplying a custom name for your server container and it is optional. If no container name is supplied, a random one is generated.

  • -m ( Memory )
  • -c ( CPU’s )
  • –port=3306.

Initialization for the container begins, and the container appears in the list of running containers when the docker ps command is executed.

[root@mydbopslabs202 vagrant]# docker ps
CONTAINER ID        IMAGE                       COMMAND                  CREATED             STATUS                             PORTS                 NAMES
4a6ad40ba073        mysql/mysql-server:latest   "/entrypoint.sh my..."   30 seconds ago      Up 29 seconds (health: starting)   3306/tcp, 33060/tcp   test

Run this command to monitor the output from the container:

[root@mydbopslabs202 vagrant]# docker logs test

Once the initialisation is completed, the random password generated for the root user is filtered from the log output. And reset the password on the initial run.

[root@mydbopslabs202 vagrant]# docker logs test 2>&1 | grep GENERATED
[Entrypoint] GENERATED ROOT PASSWORD: q0tyDrAbPyzYK+Unl4xopiDUB4k

Step 3: Connect the MySQL Server within the Container
Run the following command to start a mysql client inside the docker container,

[root@mydbopslabs202 vagrant]# docker exec -it test mysql -uroot -p
Enter password:

When asked, enter the generated root password.You must reset the server root password because the MYSQL_ONETIME_PASSWORD option is true by default.

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'Data3ng!neer@123';
Query OK, 0 rows affected (0.01 sec)

Container Shell Access:
To have shell access to your MySQL Server container, run the docker exec -it command to start a bash shell inside the container:

[root@mydbopslabs202 vagrant]# docker exec -it test bash
bash-4.2#

You can then run Linux commands inside the container. For example, to view contents in the MySQL server’s data directory inside the container, run this command:

bash-4.2# ls /var/lib/mysql
#innodb_temp   binlog.000002  ca.pem           ib_buffer_pool  ibdata1  mysql.ibd        performance_schema  server-cert.pem  undo_001
auto.cnf       binlog.index   client-cert.pem  ib_logfile0     ibtmp1   mysql.sock       private_key.pem     server-key.pem   undo_002
binlog.000001  ca-key.pem     client-key.pem   ib_logfile1     mysql    mysql.sock.lock  public_key.pem      sys
bash-4.2#
bash-4.2# exit
exit
[root@mydbopslabs202 vagrant]#

Stopping and Deleting a MySQL Container

To stop the MySQL Server container you have created, run this command:

[root@mydbopslabs202 vagrant]# docker stop test
test
[root@mydbopslabs202 vagrant]#

docker stop sends a SIGTERM signal to themysqld process, so that the server is shut down gracefully.

To start the MySQL Server container again, execute this command:

[root@mydbopslabs202 vagrant]# docker start test
test
[root@mydbopslabs202 vagrant]#

To restart the MySQL Server, run this command:

[root@mydbopslabs202 vagrant]# docker restart test
test
[root@mydbopslabs202 vagrant]#

To delete the MySQL container, stop it first, and then run the docker rm command:

[root@mydbopslabs202 vagrant]# docker rm test
test
[root@mydbopslabs202 vagrant]#


Storage management in docker ?

By default, Docker stores data in its internal volume. To validate the location of the volumes, use the command:

[root@mydbopslabs202 vagrant]# docker inspect test

In that output,it shows

"Image": "mysql/mysql-server:latest",
            "Volumes": {
                "/var/lib/mysql": {}
            },

You can also change the location of the data directory and create one on the host for persistence. Having a volume outside the container allows other applications and tools to access the volumes when needed. It’s also a best practice followed for databases.

For example if you remove the docker instance unfortunately the data directory gets also removed, it is always better to have the data directory outside the docker with the DB log files.

Containers help us in automation and easy customisations. We have all needed DB tool and monitoring packages with a custom MySQL images which can scale the database operations.We will evaluate more about containers in upcoming days.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s