Table of Contents
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.
1docker 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.
1docker 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
mysqlimage in the latest version
Administrate the database #
Once the container is running in background, we have to connect to it :
1docker 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 :
1mysql -uroot -ptoor
Now we can start to use SQL commands for our database.
Create the database #
1CREATE DATABASE my_db; 2USE my_db
Create the table #
1CREATE TABLE compotes (id INT, name TEXT, note INT);
Manipulate the table #
We want now to add some data in our table :
1INSERT INTO compotes (id, name, note) VALUES (0, "Apple", 3); 2INSERT INTO compotes (id, name, note) VALUES (1, "Apple & banana", 5), (2, "Apple & pear", 4), (3, "Apple & apple", 3);
Oh no, I f’cked up my data ! Apple & banana is not that tasty !
1UPDATE compotes SET (note=4) WHERE id=1;
So drunk I added apple & apple in my table sight
1DELETE FROM compotes WHERE name="Apple & apple";
OK, we are good.
I want to see my beautiful compotes now !
1SELECT * FROM compotes; 2 3SELECT name, note FROM compotes; 4 5SELECT * 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# populate.sql 2# SQL script to build the compotes table in our database 3 4create database my_db; 5use my_db 6 7create table compotes(id int, 8 name text, 9 note int); 10 11insert into compotes(id, text, note) values 12 (0, "Apple", 3), 13 (1, "Apple & banana", 5), 14 (2, "Apple & pear", 4);
And execute the script on the running container :
1docker exec -i my_container sh -c 'exec mysql -uroot -ptoor' < populate.sql