Docker Compose - MySQL

bogotobogo.com site search:

docker-compose.yaml / docker-compose up

docker-compose.yml:

version: '2'
services:
  db:
    image: mysql:latest
    volumes:
      - "./.mysql-data/db:/var/lib/mysql"
    restart: always
    ports:
      - 3306:3306
    environment:
      MYSQL_ROOT_PASSWORD: rootpass
      MYSQL_DATABASE: khong
      MYSQL_USER: khong
      MYSQL_PASSWORD: khongpass

To start the container run this command:

$ docker-compose up -d
Creating network "mysql_default" with the default driver
Creating mysql_db_1 ... done   

$ docker ps
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                               NAMES
f349f6de0186        mysql:latest        "docker-entrypoint.s…"   2 minutes ago       Up 2 minutes        0.0.0.0:3306->3306/tcp, 33060/tcp   mysql_db_1

On MySQL shell

Let's enter into the container:

$ docker exec -it mysql_db_1 bash
root@f349f6de0186:/#

Then, log on to MySQL db:

root@f349f6de0186:/# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.21 MySQL Community Server - GPL 
...
mysql>

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| khong              |
| mysql              |
| performance_schema |
| sys                |
| testdb             |
+--------------------+
6 rows in set (0.01 sec)

mysql> create database mydb;
Query OK, 1 row affected (0.02 sec)

mysql> use mydb;
Database changed

mysql> show tables;
Empty set (0.00 sec)

mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20), species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
Query OK, 0 rows affected (0.04 sec)

mysql> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| pet            |
+----------------+
1 row in set (0.00 sec)

mysql> describe pet;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | NULL    |       |
| owner   | varchar(20) | YES  |     | NULL    |       |
| species | varchar(20) | YES  |     | NULL    |       |
| sex     | char(1)     | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
| death   | date        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

mysql> select * from pet;
+--------+--------+---------+------+------------+-------+
| name   | owner  | species | sex  | birth      | death |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | cat     | f    | 1993-02-04 | NULL  |
| Claws  | Gwen   | cat     | m    | 1994-03-17 | NULL  |
| Bluffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+--------+--------+---------+------+------------+-------+
3 rows in set (0.00 sec)

mysql> select count(*) from pet;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

Let's tear it down:

mysql> quit
Bye

root@f349f6de0186:/# exit
exit

$ docker-compose down
Stopping mysql_db_1 ... done
Removing mysql_db_1 ... done
Removing network mysql_default

Note that even though the container is gone the database we built is still there in the "./.mysql" since we mounted the volume:

volumes:
      - "./.mysql-data/db:/var/lib/mysql"

Just for fun 1 - Inner Join

First, create two tables - members and committees:

CREATE TABLE members (
    member_id INT AUTO_INCREMENT,
    name VARCHAR(100),
    PRIMARY KEY (member_id)
);

CREATE TABLE committees (
    committee_id INT AUTO_INCREMENT,
    name VARCHAR(100),
    PRIMARY KEY (committee_id)
);

mysql> show tables;
+-----------------+
| Tables_in_khong |
+-----------------+
| committees      |
| members         |
+-----------------+
2 rows in set (0.01 sec)

Insert some rows into the tables members and committees:

INSERT INTO members(name)
VALUES('John'),('Jane'),('Mary'),('David'),('Amelia');

INSERT INTO committees(name)
VALUES('John'),('Mary'),('Amelia'),('Joe');

mysql> select * from members;
+-----------+--------+
| member_id | name   |
+-----------+--------+
|         1 | John   |
|         2 | Jane   |
|         3 | Mary   |
|         4 | David  |
|         5 | Amelia |
+-----------+--------+
5 rows in set (0.00 sec)

mysql> select * from committees;
+--------------+--------+
| committee_id | name   |
+--------------+--------+
|            1 | John   |
|            2 | Mary   |
|            3 | Amelia |
|            4 | Joe    |
+--------------+--------+
4 rows in set (0.00 sec)

The inner join clause joins two tables based on a condition which is known as a join predicate.

The inner join clause compares each row from the first table with every row from the second table. If values in both rows cause the join condition evaluates to true, the inner join clause creates a new row whose column contains all columns of the two rows from both tables and include this new row in the final result set.

In other words, the inner join clause includes only rows whose values match.

The basic syntax of the inner join clause that joins two tables table_1 and table_2:

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

The following statement finds members who are also the committee members:

mysql> SELECT * FROM members 
    -> INNER JOIN committees 
    -> ON members.name = committees.name;
+-----------+--------+--------------+--------+
| member_id | name   | committee_id | name   |
+-----------+--------+--------------+--------+
|         1 | John   |            1 | John   |
|         3 | Mary   |            2 | Mary   |
|         5 | Amelia |            3 | Amelia |
+-----------+--------+--------------+--------+
3 rows in set (0.01 sec)

Q. How can we list all columns for a given table? - SHOW COLUMNS FROM table;:

mysql> SHOW COLUMNS FROM members;
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| member_id | int          | NO   | PRI | NULL    | auto_increment |
| name      | varchar(100) | YES  |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

The short cut is DESCRIBE table:

mysql> DESCRIBE members;
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| member_id | int          | NO   | PRI | NULL    | auto_increment |
| name      | varchar(100) | YES  |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

Just for fun 2 - Foreign key

The foreign key constraint is used to make a relationship between two tables in the MySQL database. To define any foreign key constraint for any table we have to use the primary key of another table. The primary key is a unique key for a table to identify the particular row in the table and when this primary key is used into another table to make one-to-one or one-to-many or many-to-many relationship then it is called a foreign key.

Before creating a foreign key constraint, we have to create a parent table with the primary key. Suppose our database contains two parent tables named 'books' and 'borrower':

CREATE TABLE books (
id INT NOT NULL AUTO_INCREMENT,
title varchar(50) NOT NULL,
author varchar(50) NOT NULL,
publisher varchar(50) NOT NULL,
PRIMARY KEY (id)
) ;

CREATE TABLE borrowers (
id VARCHAR(50) NOT NULL,
name varchar(50) NOT NULL,
address varchar(50) NOT NULL,
email varchar(50) NOT NULL,
PRIMARY KEY (id)
) ;

mysql> DESCRIBE books;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int         | NO   | PRI | NULL    | auto_increment |
| title     | varchar(50) | NO   |     | NULL    |                |
| author    | varchar(50) | NO   |     | NULL    |                |
| publisher | varchar(50) | NO   |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
4 rows in set (0.03 sec)

mysql> DESCRIBE borrowers;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | varchar(50) | NO   | PRI | NULL    |       |
| name    | varchar(50) | NO   |     | NULL    |       |
| address | varchar(50) | NO   |     | NULL    |       |
| email   | varchar(50) | NO   |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

Create a table named 'book_borrow_info' with foreign key constraints by executing the following statement. Here, the book_id field is a foreign key for this table and every value of this field must exist in the id field of books table. books is the parent table and book_borrow_info is the child table. Two restrictions are also set with the foreign key here. These are DELETE CASCADE and UPDATE CASCADE. That means if any primary key will remove or update from the parent table then the corresponding records related to child table related to the foreign key will be removed or the foreign key will be updated.

CREATE TABLE book_borrow_info (
borrow_id VARCHAR(50),
book_id INT,
borrow_date DATE NOT NULL,
return_date DATE NOT NULL,
status VARCHAR(15) NOT NULL,
INDEX par_ind (book_id),
PRIMARY KEY (borrow_id, borrow_date),
FOREIGN KEY (book_id) REFERENCES books(id)
ON DELETE CASCADE
ON UPDATE CASCADE
);

Let's insert some records in both tables:

INSERT INTO books VALUES
(NULL, 'To Kill a Mockingbird', 'Harper Lee', 'Grand Central Publishing'),
(NULL, 'One Hundred Years of Solitude', 'Garcia Marquez', 'Lutfi Ozkok'),
(NULL, 'A Passage to India', 'Forster, E.M.', 'BBC Hulton Picture Library'),
(NULL, 'Invisible Man', 'Ralph Ellison', 'Encyclopædia Britannica, Inc.');

INSERT INTO book_borrow_info VALUES
('123490', 1, '2021-02-15', '2021-02-25', 'Returned'),
('157643', 2, '2021-03-31', '2021-03-10', 'Pending'),
('174562', 4, '2021-04-04', '2021-04-24', 'Borrowed'),
('146788', 3, '2021-04-10', '2021-01-20', 'Borrowed');

mysql> SELECT * from books;
+----+-------------------------------+----------------+------------------------------+
| id | title                         | author         | publisher                    |
+----+-------------------------------+----------------+------------------------------+
|  1 | To Kill a Mockingbird         | Harper Lee     | Grand Central Publishing     |
|  2 | One Hundred Years of Solitude | Garcia Marquez | Lutfi Ozkok                  |
|  3 | A Passage to India            | Forster, E.M.  | BBC Hulton Picture Library   |
|  4 | Invisible Man                 | Ralph Ellison  | Encyclopdia Britannica, Inc. |
+----+-------------------------------+----------------+------------------------------+
4 rows in set (0.00 sec)

mysql> SELECT * from book_borrow_info;
+-----------+---------+-------------+-------------+----------+
| borrow_id | book_id | borrow_date | return_date | status   |
+-----------+---------+-------------+-------------+----------+
| 123490    |       1 | 2021-02-15  | 2021-02-25  | Returned |
| 146788    |       3 | 2021-04-10  | 2021-01-20  | Borrowed |
| 157643    |       2 | 2021-03-31  | 2021-03-10  | Pending  |
| 174562    |       4 | 2021-04-04  | 2021-04-24  | Borrowed |
+-----------+---------+-------------+-------------+----------+
4 rows in set (0.00 sec)

If we try to insert a value in the foreign key field of the child table that does not exist in the primary key field of the parent table then MySQL will generate an error. The following SQL statement will generate an error because the parent table, books doesn't contain any id value 10:

INSERT INTO book_borrow_info VALUES
('195684', 10, '2021-04-15', '2020-04-30', 'Returned');

mysql> INSERT INTO book_borrow_info VALUES
    -> ('195684', 10, '2021-04-15', '2020-04-30', 'Returned');  
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails 
(`khong`.`book_borrow_info`, CONSTRAINT `book_borrow_info_ibfk_1` FOREIGN KEY (`book_id`) REFERENCES 
`books` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)

After executing the following DELETE statement, when the fourth record will be removed from the books table then the related records from the book_borrow_info table will be removed automatically for the foreign key constraint.

mysql> SELECT * FROM books;
+----+-------------------------------+----------------+------------------------------+
| id | title                         | author         | publisher                    |
+----+-------------------------------+----------------+------------------------------+
|  1 | To Kill a Mockingbird         | Harper Lee     | Grand Central Publishing     |
|  2 | One Hundred Years of Solitude | Garcia Marquez | Lutfi Ozkok                  |
|  3 | A Passage to India            | Forster, E.M.  | BBC Hulton Picture Library   |
|  4 | Invisible Man                 | Ralph Ellison  | Encyclopdia Britannica, Inc. |
+----+-------------------------------+----------------+------------------------------+
4 rows in set (0.00 sec)

mysql> SELECT * from book_borrow_info;
+-----------+---------+-------------+-------------+----------+
| borrow_id | book_id | borrow_date | return_date | status   |
+-----------+---------+-------------+-------------+----------+
| 123490    |       1 | 2021-02-15  | 2021-02-25  | Returned |
| 146788    |       3 | 2021-04-10  | 2021-01-20  | Borrowed |
| 157643    |       2 | 2021-03-31  | 2021-03-10  | Pending  |
| 174562    |       4 | 2021-04-04  | 2021-04-24  | Borrowed |
+-----------+---------+-------------+-------------+----------+
4 rows in set (0.00 sec)

mysql> DELETE FROM books where id = 4;
Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM books;
+----+-------------------------------+----------------+----------------------------+
| id | title                         | author         | publisher                  |
+----+-------------------------------+----------------+----------------------------+
|  1 | To Kill a Mockingbird         | Harper Lee     | Grand Central Publishing   |
|  2 | One Hundred Years of Solitude | Garcia Marquez | Lutfi Ozkok                |
|  3 | A Passage to India            | Forster, E.M.  | BBC Hulton Picture Library |
+----+-------------------------------+----------------+----------------------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM books;
+----+-------------------------------+----------------+----------------------------+
| id | title                         | author         | publisher                  |
+----+-------------------------------+----------------+----------------------------+
|  1 | To Kill a Mockingbird         | Harper Lee     | Grand Central Publishing   |
|  2 | One Hundred Years of Solitude | Garcia Marquez | Lutfi Ozkok                |
|  3 | A Passage to India            | Forster, E.M.  | BBC Hulton Picture Library |
+----+-------------------------------+----------------+----------------------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM book_borrow_info;
+-----------+---------+-------------+-------------+----------+
| borrow_id | book_id | borrow_date | return_date | status   |
+-----------+---------+-------------+-------------+----------+
| 123490    |       1 | 2021-02-15  | 2021-02-25  | Returned |
| 146788    |       3 | 2021-04-10  | 2021-01-20  | Borrowed |
| 157643    |       2 | 2021-03-31  | 2021-03-10  | Pending  |
+-----------+---------+-------------+-------------+----------+
3 rows in set (0.00 sec)

After executing the DELETE statement, when the fourth record will be removed from the books table then the related records from the book_borrow_info table will be removed automatically for the foreign key constraint.