Setup your database with Docker

Yeah, I wanted to do one of those for a long time ^^

So, we have to stay at home for the moment, I decided to make a “walkthrough” about something I used sometimes and I couldn’t remember all the commands. So, let’s build our MySQL database within a Docker container !

Get the Docker image

I will be using the mysql image from the Docker hub.

1
docker pull mysql

Run the container

For further details, you can read the official documentation for the image on Docker hub. I will just go through the basic steps I use.

1
docker run --name my_container -e MYSQL_ROOT_PASSWORD=toor -d mysql:latest
  • --name : setup a name for our container
  • -e : setup environment variables
    • MYSQL_ROOT_PASSWORD : need explainations ?
  • -d : run in deamon mode
  • mysql:latest : use the mysql image in the latest version

Administrate the database

Once the container is running in background, we have to connect to it :

1
docker exec -it my_container /bin/bash

Here, we connect to the running container and pop a shell in interactive mode. We have to connect to the database :

1
mysql -uroot -ptoor

Now we can start to use SQL commands for our database.

Create the database

1
2
CREATE DATABASE my_db;
USE my_db

Create the table

1
CREATE TABLE compotes (id INT, name TEXT, note INT);

Manipulate the table

INSERT

We want now to add some data in our table :

1
2
INSERT INTO compotes (id, name, note) VALUES (0, "Apple", 3);
INSERT INTO compotes (id, name, note) VALUES (1, "Apple & banana", 5), (2, "Apple & pear", 4), (3, "Apple & apple", 3);

UPDATE

Oh no, I f’cked up my data ! Apple & banana is not that tasty !

1
UPDATE compotes SET (note=4) WHERE id=1;

Better.

DELETE

So drunk I added apple & apple in my table sight

1
DELETE FROM compotes WHERE name="Apple & apple";

OK, we are good.

SELECT

I want to see my beautiful compotes now !

1
2
3
4
5
SELECT * FROM compotes;

SELECT name, note FROM compotes;

SELECT * FROM compotes WHERE id > 1 AND note = 3;

Use SQL script

We don’t want to re-type all the database when building a new container !

SQL scripts are cool for that :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# populate.sql
# SQL script to build the compotes table in our database

create database my_db;
use my_db

create table compotes(id int,
name text,
note int);

insert into compotes(id, text, note) values
(0, "Apple", 3),
(1, "Apple & banana", 5),
(2, "Apple & pear", 4);

And execute the script on the running container :

1
docker exec -i my_container sh -c 'exec mysql -uroot -ptoor' < populate.sql