Customize your MySQL Database in Docker

Customize your MySQL Database in DockerCreating Docker images and using bind mounts to customize your MySQL databaseLorenz VanthilloBlockedUnblockFollowFollowingMar 30, 2018Many developers use Docker to spin up a local database.

This makes it easy to test their code and write data without installing and configuring a lot of tools.

In this piece, I’ll explain how you can customize your Docker database.

While I’ll be explaining the steps for MySQL, it’s the same for PostgreSQL! Follow along and let’s create a custom MySQL which contains your desired tables and data.

Creating SQL scriptsWe will create SQL scripts which contain the SQL statements we want to execute on our database.

Create a directory in which we will work.

Also, create a subdirectory where we will store our .

sql scripts.

$ mkdir -p ~/my-mysql/sql-scripts$ cd ~/my-mysql/sql-scriptsI want to customize my database with a table called employees.

The table needs to contain one row with an employee (first name, last name, department, and email).

Write a CreateTable.

sql.

This file contains the SQL statement to create a table called employees.

We will add four columns to our tableWrite a InsertData.

sql.

This file contains our statement to insert data in the table ‘employees’.

Execute the tree command to verify that your two scripts exist and are saved in the right directory.

$ cd ~/my-mysql$ tree└── sql-scripts ├── CreateTable.

sql └── InsertData.

sqlCreating a Docker Image for Your Customized MySQL DatabaseNow that the scripts are ready, we can write a Dockerfile to create our own Docker image (based on the official image of MySQL).

$ cd ~/my-mysql/$ vi DockerfileContent of Dockerfile:# Derived from official mysql image (our base image)FROM mysql# Add a databaseENV MYSQL_DATABASE company# Add the content of the sql-scripts/ directory to your image# All scripts in docker-entrypoint-initdb.

d/ are automatically# executed during container startupCOPY .

/sql-scripts/ /docker-entrypoint-initdb.

d/Create your Docker image:$ cd ~/my-mysql/$ docker build -t my-mysql .

Sending build context to Docker daemon 4.

608kBStep 1/2 : FROM mysqllatest: Pulling from library/mysqlDigest: sha256:691c55aabb3c4e3b89b953dd2f022f7ea845e5443954767d321d5f5fa394e28cStatus: Downloaded newer image for mysql:latest —> 5195076672a7Step 2/2 : ADD sql-scripts/ /docker-entrypoint-initdb.

d/ —> 25065c3d93c0Successfully built 25065c3d93c0Successfully tagged my-mysql:latestAnd start your MySQL container from the image:$ docker run -d -p 3306:3306 –name my-mysql -e MYSQL_ROOT_PASSWORD=supersecret my-mysqlNow we can verify.

We will exec inside the container:$ docker exec -it my-mysql bashroot@c86ff80d7524:/# mysql -uroot -pEnter password: (supersecret)mysql> show databases;+——————–+| Database |+——————–+| information_schema || company || mysql || performance_schema || sys |+——————–+5 rows in set (0.

00 sec)mysql> use company;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> show tables;+——————-+| Tables_in_company |+——————-+| employees |+——————-+1 row in set (0.

00 sec)mysql> show columns from employees;+————+————-+——+—–+———+——-+| Field | Type | Null | Key | Default | Extra |+————+————-+——+—–+———+——-+| first_name | varchar(25) | YES | | NULL | || last_name | varchar(25) | YES | | NULL | || department | varchar(15) | YES | | NULL | || email | varchar(50) | YES | | NULL | |+————+————-+——+—–+———+——-+4 rows in set (0.

00 sec)mysql> select * from employees;+————+———–+————+——————-+| first_name | last_name | department | email |+————+———–+————+——————-+| Lorenz | Vanthillo | IT | lvthillo@mail.

com |+————+———–+————+——————-+1 row in set (0.

01 sec)It works!.We have our customized MySQL database Docker image!.This is a great solution for local development between multiple developers.

By sharing the Docker image, every developer can use the database by just starting a container from the image.

It’s important to note, however, that this is not always the best solution:If you insert a lot of data your image size will grow significantlyNeed to build a new image when you want to update the dataThat’s why there is another way to customize your Docker MySQL.

Use Bind Mounts to Customize Your MySQL Database in DockerIn this last section, we will simply mount the scripts inside the official MySQL Docker container.

$ docker run -d -p 3306:3306 –name my-mysql -v ~/my-mysql/sql-scripts:/docker-entrypoint-initdb.

d/ -e MYSQL_ROOT_PASSWORD=supersecret -e MYSQL_DATABASE=company mysqlAnd we can verify again!.Use the same steps as we did before: exec inside the container and check if the table and data exist!This method is more flexible but it will be a little bit harder to distribute among the developers.

They all need to store the scripts in a certain directory on their local machine and they need to point to that directory when they execute the docker run command.

ConclusionIn this piece, I’ve described how you can create your custom MySQL database in Docker.

We’ve explored the two methods:Custom MySQL Docker imageBind mounts to mount scripts inside a containerWe also discussed some of the advantages and disadvantages of each method.

We hope you enjoyed it!.

. More details

Leave a Reply