SQL on Docker containers

SQL on Docker Containers

I have tested different databases, namely MySQL, Microsoft SQL, and PostgreSQL, using docker containers. I would like to share my deployment process.

I will follow these steps, below.

Centos 8 installation
Docker deployment
Portainer for container management
MySQL container installation
Microsoft SQL container installation
PostgreSQL container installation
Conclusion

Step 1. Centos 8 installation

I used vmware workstation for Centos 8 server installation. Virtualbox, Proxmox, or ESXI can be used. Giving enough space for database implementations can be good option.

Step 2. Docker deployment

It is straight forward to install it. I will skip it, as it can be found on google easily. I just want to focus on specific installations. There are some outputs from my centos 8.

After some containers installation, port numbers can be seen, PostgreSQL 5432, MicrosoftSQL 1433, MySQL3306, below.

Step 3. Portainer installation

Portainer is an efficient tool to manipulate and manage docker containers.

Deployment can be found easily on the internet. This is a basic command to pull it, below. GUI can be seen, below.

docker pull portainer/portainer

After installing sql containers, it will be like this, below.

Volume list can be seen. I am able to add, remove and change any file name using it. It is so practical to transfer any data.

Step 4. MySQL container installation

It is time to install first database, MySQL. Pull its container from docker hub.

I set a password as Test123 while default user is root. They can be changed later.

docker pull mysql

docker run –name dbmysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=Test123 -d mysql

Check the MySQL container. Also, add any service on firewall of the server. I added mysql service. Optionally, port number can be added such as 3307 that will be used for MariaDB. It can be found other samples.

firewall-cmd –zone=public –add-port=3307/tcp –permanently
firewall-cmd –zone=public –add-service=mysql –permanently

firewall-cmd –reload

Check listening ports, once the containers run, 3306, 1433, 5432, below.

Check it from Portainer and cli. I stick to Portainer to manage them.

A data can be imported by secure copy scp, Filezilla. I also added a shared folder for docker containers in case of any failure on them. I can take a snapshot or backup of this shared folder regularly. Dockerdata and sub drives can be seen on the centos8 server. I will add it.

I transferred a simple SQL data for my implementation using Filezilla, below.

Now, I can it inside of /root/dockerdata/mysqdata drive, below.

MySQL container should be able to access host server’s shared folder, namely mysqldata. Therefore, it should be attached on the container as a volume area. This will be done for for other containers.

Click on command exec to manage mysql container, below.

Now, I can see the files inside of centos8mysqldata folder that is shared by centos8 server from docker container.

mysql -u root -pTest123

CREATE DATABASE mysqlsampledatabase;

mysql -uroot -p mysqlsampledatabase < mysqlsampledatabase.sql

MySQL Workbech is a free tool to access to the database.

Database can be monitored on its main dashboard.

MySQL Workbench allows to import and export data, below.

Sqlectron is another option to connect the database. I used it for database management.

Step 5. Microsoft SQL container installation

Follow the same steps. Go to dockerhub and pull Microsoft SQL server container.

docker pull mcr.microsoft.com/mssql/server

docker run -e ‘ACCEPT_EULA=Y’ -e ‘SA_PASSWORD=Test123/’ -p 1433:1433 -d mcr.microsoft.com/mssql/server:latest

Download Microsoft SQL Server Management Studio and connect the database. Default user is “sa” and password is configured as “Test123/” by me. They can be changed later.

Deploy some data, below. It is easy with Management Studio, below.

Database activities can be seen on its dashboard, below.

I used Sqlectron for the database management, again.

Step 6. PostgreSQL container installation

I used the same steps for PostgreSQL container installation. I can import data using either command line or pgamin web browser below.

docker run –name postgres -e POSTGRES_PASSWORD=Test123 -d postgres -p 5432:5432

psql -U postgres -h localhost -d store < store.sql

psql -U postgres -h localhost -d store < world.sql

I downloaded pgadmin for PostgreSQL database management. Backup and restore can be seen, below.

Database activities can be monitored by using pgadmin dashboard can be seen, below.

Sqlectron is used for PostgreSQL management as well, below.

7. Conclusion

As shown above, docker containers are useful to use different database or application. They are imported and exported to any other servers, if it is required. In addition, Sqlectron was used for management all of them. Its GUI is not friendly but it is practical. I hope this post is informative for you.

yldrmdgn