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.