Data Volume with MySQL Docker container

Data Volume with MySQL Docker container

MySQL, the most popular Open Source SQL database management system, is developed, distributed, and supported by Oracle Corporation. MySQL is a database management system. MySQL databases are relational. MySQL software is Open Source. The MySQL Database Server is very fast, reliable, scalable, and easy to use. MySQL Server works in client/server or embedded systems. A large amount of contributed MySQL software is available. – by dev.mysql.com

The best way of handling the Data volume in docker container is, keep the data in a separate container and using by other containers which they call “Data Volume Container”.

PULL MYSQL DOCKER IMAGE

First step is, we need to pull the MySQL docker image from the docker hub repository. Execute the below command to pull the latest MySQL docker image from the docker hub repository.

docker pull mysql/mysql-server:latest

VIEW DOCKER IMAGE

Execute the below command, to view the list of docker images and to make sure the MySQL docker image has been downloaded or not.

docker images

create a data volume container

Execute the below command to create a new Data volume container for MySQL. The below command will create a new container to keep the data volume and it will acts a Data Volume Container.

docker create -v /var/lib/mysql --name mysqldata mysql/mysql-server:latest

I named the Data Volume Container as “mysqldata“.

Next, we need to mount the data volume container “mysqldata” when we run the MySQL container.

Execute the below command to run the MySQL as a Docker container, which mounted the Docker Volume Container “mysqldata”.

docker run -p 3306:3306 -d -e MYSQL_ROOT_PASSWORD=root --volumes-from  mysqldata --name=mysqldb mysql/mysql-server:latest

I named the MySQL container “mysqldb” and the exposed the port number is -p 3306:3306. Also, I set the MySQL root user password as “root” by using docker environment variable itself.

After we run the MySQL container, we can access the database by executing the Docker Exec command and create a new database called “MY_FIRST_DATABASE”.

docker exec -it mysqldb bash

Then, execute the below command to login into the MySQL database.

mysql -uroot -p

Console will ask you the password, provide the root user password we setted by using Docker environment variable.

Now, execute the below SQL command to create a new database.

CREATE DATABASE MY_FIRST_DATABASE;

We can delete the “mysqldb” container without affecting our data in “mysqldata”.

To test the data volume, delete the “mysqldb” container. Then, run a new container “mysqldb1”.

docker rm -f mysqldb
docker run --name mysqldb1 --volumes-from mysqldata -e MYSQL_ROOT_PASSWORD=password -p 3306:3306 mysql/mysql-server:latest 

Then, login into the database by executing the Docker Exec command once again.

docker exec -it mysqldb1 bash

After login into the database, execute the below SQL command to view the list of databases. Then the “MY_FIRST_DATABASE” database will be available.

show databases;
show databases

Data Volume path has been successfully mounted to MySQL docker container.

Advertisements

How to run MySQL Docker container?

How to run MySQL Docker container?

MySQL, the most popular Open Source SQL database management system, is developed, distributed, and supported by Oracle Corporation. MySQL is a database management system. MySQL databases are relational. MySQL software is Open Source. The MySQL Database Server is very fast, reliable, scalable, and easy to use. MySQL Server works in client/server or embedded systems. A large amount of contributed MySQL software is available. – by dev.mysql.com

In Today’s world, most of the applications are depends on the databases. If you don’t want to setup a dedicated database server for your applications, then definitely you might go to the containerized platform.

For small and medium scale industries, running MySQL as a docker container is very specialized platform without spending money for any hardware based servers.

Before running MySQL as a docker container, the docker must be installed on your machine. If you want to know How to install Docker? Please refer the below link.

How to install Docker on Linux Debian 9?

pull mysql docker image

First step is, we need to pull the MySQL docker image from the docker hub repository. Execute the below command to pull the latest MySQL docker image from the docker hub repository.

docker pull mysql/mysql-server:latest
root@instance-1:~# docker pull mysql/mysql-server:latest
 latest: Pulling from mysql/mysql-server
 ad18637d63f5: Pull complete 
 3f16d3b57d84: Pull complete 
 e07cad553c93: Pull complete 
 98363e47b5bd: Pull complete 
 Digest: sha256:f9ab5082218fc9128f00f83bc707886b65b8cdfa4b9809315d18c9140655db2f
 Status: Downloaded newer image for mysql/mysql-server:latest
 docker.io/mysql/mysql-server:latest

view docker image

Execute the below command, to view the list of docker images and to make sure the MySQL docker image has been downloaded or not.

docker images
root@instance-1:~# docker images

REPOSITORY           TAG                 IMAGE ID            CREATED             SIZE

mysql/mysql-server   latest              12a8d88596c0        8 days ago          294MB

run the mysql docker image as a container

Execute the below command to run the MySQL docker image as a container.

docker run -p 3306:3306 -d --name=mysql mysql/mysql-server:latest
root@instance-1:~# docker run -p 3307:3307 -d --name=mysql mysql/mysql-server:latest
de3bad95ad41de45e43f46da8faee8d6aaf6bb64bb099f6f9f8e62aee29bf073root@instance-1:~# 

where,

-p 3306: 3306 – represents exposing the MySQL port number

–name=mysql – represents the name of the container

mysql/mysql-server:latest – represents the name of the MySQL docker image

view mysql docker logs

After run the MySQL image as docker container, execute the below command to view logs and get the default “root” user password to login into the MySQL database.

docker logs mysql

where,

mysql – name of the docker container

You can see the following logs on the console with default “root” user password. Copy the password and keep it safe to login into the database.

root@instance-1:~# docker logs mysql
 [Entrypoint] MySQL Docker Image 8.0.17-1.1.12
 [Entrypoint] No password option specified for new database.
 [Entrypoint]   A random onetime password will be generated.
 [Entrypoint] Initializing database
 2019-07-30T17:58:29.705963Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.17) initializing of server in progress as process 20
 2019-07-30T17:58:33.701238Z 5 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
 2019-07-30T17:58:35.487233Z 0 [System] [MY-013170] [Server] /usr/sbin/mysqld (mysqld 8.0.17) initializing of server has completed
 [Entrypoint] Database initialized
 2019-07-30T17:58:36.967680Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.17) starting as process 67
 2019-07-30T17:58:37.669577Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
 2019-07-30T17:58:37.698884Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.17'  socket: '/var/lib/mysql/mysql.sock'  port: 0  MySQL Community Server - GPL.
 2019-07-30T17:58:37.767916Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock'
 Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.
 Warning: Unable to load '/usr/share/zoneinfo/leapseconds' as time zone. Skipping it.
 Warning: Unable to load '/usr/share/zoneinfo/tzdata.zi' as time zone. Skipping it.
 Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.
 Warning: Unable to load '/usr/share/zoneinfo/zone1970.tab' as time zone. Skipping it.
 [Entrypoint] GENERATED ROOT PASSWORD: om4fK@h0P&3MetAbOdHEfh@xRes
 [Entrypoint] ignoring /docker-entrypoint-initdb.d/*
 2019-07-30T17:58:39.891983Z 10 [System] [MY-013172] [Server] Received SHUTDOWN from user root. Shutting down mysqld (Version: 8.0.17).
 2019-07-30T17:58:41.714397Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.17)  MySQL Community Server - GPL.
 [Entrypoint] Server shut down
 [Entrypoint] Setting root user as expired. Password will need to be changed before database can be used.
 [Entrypoint] MySQL init process done. Ready for start up.
 [Entrypoint] Starting MySQL 8.0.17-1.1.12
 2019-07-30T17:58:42.287092Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.17) starting as process 1
 2019-07-30T17:58:42.889227Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
 2019-07-30T17:58:42.922042Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.17'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server - GPL.
 2019-07-30T17:58:43.064447Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock' bind-address: '::' port: 33060

docker exec

The docker exec command allows us to run the commands inside the docker container.

docker exec -it mysql bash

Then, execute the below command to login into the MySQL database.

mysql -uroot -p

Console will ask you the password, provide the default root user password took it from the container logs.

bash-4.2# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16Server version: 8.0.17Copyright (c) 2000, 2019, Oracle and/or its affiliates. 
All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. 
Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. 
Type '\c' to clear the current input statement.

mysql> 

Now, execute the SQL commands to access the MySQL database as a Docker container.

alter root user password

Execute the below command to alter the root user password.

ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword';

Then, execute the below command to view the list of databases.

show databases;

MySQL successfully running as a docker container.

How to install Docker on Linux Debian 9?

How to install Docker on Linux Debian 9?

Docker is a tool designed to make it easier to create, deploy, and run applications by using containers. Containers allow a developer to package up an application with all of the parts it needs, such as libraries and other dependencies, and ship it all out as one package. By doing so, thanks to the container, the developer can rest assured that the application will run on any other Linux machine regardless of any customized settings that machine might have that could differ from the machine used for writing and testing the code. – by opensource.com

steps

First, update all existing packages list.

sudo apt update

Install prerequisites packages which supports “apt” use packages over HTTPS.

sudo apt install apt-transport-https ca-certificates curl software-properties-common

Add the GPG Key from the official Docker repository site to your system.

curl -fsSL https://download.docker.com/linux/debian/gpg | sudo apt-key add -

Add the Docker repository to APT sources.

sudo add-apt-repository "deb [arch=amd64] https://download.docker.com/linux/debian stretch stable"

Update the packages once again with the Docker packages.

sudo apt update 

Execute the below command to make sure installing the docker from official docker repository instead of default one from your Linux Debian.

apt-cache policy docker-ce

You will get the below message in your console about the Docker.

docker-ce:
   Installed: (none)
   Candidate: 5:19.03.0~3-0~debian-stretch
   Version table:
      5:19.03.0~3-0~debian-stretch 500
         500 https://download.docker.com/linux/debian stretch/stable amd64 Packages
      5:18.09.8~3-0~debian-stretch 500
         500 https://download.docker.com/linux/debian stretch/stable amd64 Packages

Now this is the time to install the Docker. Execute the below command to install Docker in your Linux Debian 9.

sudo apt install docker-ce

After docker installation, check the daemon started and the docker process is enabled to start on boot.

sudo systemctl status docker

You will get the below message on the console and showing that service is started and running.

docker.service - Docker Application Container Engine
    Loaded: loaded (/lib/systemd/system/docker.service; enabled; vendor preset: enabled)
    Active: active (running) since Thu 2019-07-25 15:08:26 UTC; 52s ago
      Docs: https://docs.docker.com
  Main PID: 10170 (dockerd)
    CGroup: /system.slice/docker.service
            └─10170 /usr/bin/dockerd -H fd:// --containerd=/run/containerd/containerd.sock
 Jul 25 15:08:25 my-blog-vm dockerd[10170]: time="2019-07-25T15:08:25.843745232Z" level=warning msg="Your kernel does not support swap memory limit"
 Jul 25 15:08:25 my-blog-vm dockerd[10170]: time="2019-07-25T15:08:25.844505558Z" level=warning msg="Your kernel does not support cgroup rt period"
 Jul 25 15:08:25 my-blog-vm dockerd[10170]: time="2019-07-25T15:08:25.845028493Z" level=warning msg="Your kernel does not support cgroup rt runtime"
 Jul 25 15:08:25 my-blog-vm dockerd[10170]: time="2019-07-25T15:08:25.845611976Z" level=info msg="Loading containers: start."
 Jul 25 15:08:25 my-blog-vm dockerd[10170]: time="2019-07-25T15:08:25.922639398Z" level=info msg="Default bridge (docker0) is assigned with an IP address 172.17.0.0/16. Daemon
 Jul 25 15:08:25 my-blog-vm dockerd[10170]: time="2019-07-25T15:08:25.958337077Z" level=info msg="Loading containers: done."
 Jul 25 15:08:25 my-blog-vm dockerd[10170]: time="2019-07-25T15:08:25.984441997Z" level=info msg="Docker daemon" commit=aeac9490dc graphdriver(s)=overlay2 version=19.03.0
 Jul 25 15:08:25 my-blog-vm dockerd[10170]: time="2019-07-25T15:08:25.985341692Z" level=info msg="Daemon has completed initialization"
 Jul 25 15:08:26 my-blog-vm systemd[1]: Started Docker Application Container Engine.
 Jul 25 15:08:26 my-blog-vm dockerd[10170]: time="2019-07-25T15:08:26.024181113Z" level=info msg="API listen on /var/run/docker.sock"

Now execute the below commands to run Hello World in docker by login as root user. Otherwise, add the “sudo” before the docker commands.

docker run hello-world

You will get the following output on the console.

Unable to find image 'hello-world:latest' locally
 latest: Pulling from library/hello-world
 1b930d010525: Pull complete 
 Digest: sha256:6540fc08ee6e6b7b63468dc3317e3303aae178cb8a45ed3123180328bcc1d20f
 Status: Downloaded newer image for hello-world:latest
 Hello from Docker!
 This message shows that your installation appears to be working correctly.
 To generate this message, Docker took the following steps:
 The Docker client contacted the Docker daemon.
 The Docker daemon pulled the "hello-world" image from the Docker Hub.
 (amd64)
 The Docker daemon created a new container from that image which runs the
 executable that produces the output you are currently reading.
 The Docker daemon streamed that output to the Docker client, which sent it
 to your terminal. 
 To try something more ambitious, you can run an Ubuntu container with:
  $ docker run -it ubuntu bash
 Share images, automate workflows, and more with a free Docker ID:
  https://hub.docker.com/
 For more examples and ideas, visit:
  https://docs.docker.com/get-started/
 …

Execute few more docker commands and play with it.

To view the list of docker running containers.

docker ps

To view the list of docker images.

docker images

Docker has been installed successfully on the Linux Debian 9.