I started MySQL 5.7 with docker-compose and tried to connect

This article is written for Docker beginners who want to easily start MySQL in a Docker container. Also, please note that since it is based on development with Spring Boot (Frame Work), the target audience may be limited.

Premise

――What is Docker? Get a rough overview --You must have Docker for Mac installed

• If you haven't installed Docker yet, please refer to the link below and start Docker. Qiita：https://qiita.com/scrummasudar/items/750aa52f4e0e747eed68

The development environment is as follows.

Development environment

• Mac OS High Sierra
• IntelliJ IDEA
• SpringBoot 2.0.5
• Java8
• Docker

What is Docker in the first place ??

Simply put, it's open source container management software. By using the Docker container, it is possible to speed up application build and deployment. Please refer to the following as detailed explanations will be skipped. Qiita：https://qiita.com/gold-kou/items/44860fbda1a34a001fc1 Official documentation: https://docs.docker.com/

What is Docker Compose? ??

A tool that can define and start various containers (docker). Basically, you can set the service environment with a YAML file. Also, you can easily get started with just one command.

• When you install Docker For Mac, it comes automatically, so you don't need to install it separately.

What to do this time

--Try starting MySQL5.7 (official image) using the docker-compose command --Try connecting to the started MySQL 5.7 --Check if the DB and table are initialized according to the prepared configuration file --Look at the output query log

procedure

1. Check if Docker is installed

Terminal

$ docker --version
Docker version 18.03, build c97c6d6 #If installed, the version will be displayed

$ docker-compose --version
docker-compose version 1.22.0, build 8dd22a9

$ docker-machine --version
docker-machine version 0.14.0, build 9ba6da9

2. Create a Dockerfile

The directory structure is as follows.

Constitution

sample-project/
             ├ docker/
             |       └ mysql/
             |              ├ conf.d/
             |              |       └ my.cnf
             |              ├ initdb.d/
             |              |         ├ schema.sql
             |              |         └ testdata.sql
             |              └ Dockerfile
             └ docker-compose.yml                      

** Dockerfile ** is like an instruction to order what kind of image to build. To use version 5.7 of MySQL, write as follows to build the official image.

Dockerfile

FROM mysql:5.7
RUN touch /var/log/mysql/mysqld.log #Create a file to log in the specified location

3. Create docker-compose.yml

You can configure multiple services in this YAML file. Specifically, you can specify the port number of each service and set the storage area (volume).

docker-compose.yml

version: '3.3'
services:
  db:
    build: ./docker/mysql
    image: mysql:5.7
    restart: always
    environment:
      MYSQL_DATABASE: sample_db
      MYSQL_USER: user
      MYSQL_PASSWORD: password
      MYSQL_ROOT_PASSWORD: rootpassword
    ports:
      - "3314:3306"
    volumes:
      - ./docker/mysql/initdb.d:/docker-entrypoint-initdb.d
      - ./docker/mysql/conf.d:/etc/mysql/conf.d
      - ./log/mysql:/var/log/mysql

4. Create my.cnf

Here you can configure MySQL.

my.cnf

[mysqld]
character-set-server=utf8mb4       #Character code used by the mysql server side
explicit-defaults-for-timestamp=1　  #Recommended if the table has a TimeStamp type column
general-log=1　                  #All history of executed queries is recorded (it seems to be OFF by default)
general-log-file=/var/log/mysql/mysqld.log #Log output destination

[client]
default-character-set=utf8mb4　　　　　　　　　　　　　　　#Character code used by the client side of mysql

5. Create a table definition file

I want to create the table as an initialization process, so prepare a DDL file.

schema.sql

CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(32) NOT NULL,
    email VARCHAR(32) NOT NULL,
    PRIMARY KEY (id)
);

6. Create test data

You can prepare the data in the table in advance.

testdata.sql

INSERT INTO users (id,name,email) VALUES (1, 'TOM','[email protected]');

7. Execute the docker-compose command

Execute the following command in the root directory of the project.

Terminal

$ docker-compose up -d #Create and start a container
Creating network "sample-project_default" with the default driver
Creating sample-project_db_1 ... done

$ docker-compose ps #View a list of existing containers
        Name                   Command           State           Ports         
--------------------------------------------------------------------------------
sample-project_db_1    docker-entrypoint.sh     Up      0.0.0.0:3314->3306/tcp
                       mysqld                           , 33060/tcp  

↓ Other commands ↓
$ docker-compose stop #Stop the service
$ docker-compose down #Stop and delete containers, and delete all networks and storage areas

8. Try connecting to the started MySQL

Now, let's connect to Mysql launched with docker-compose.

Terminal

$ docker exec -it sample-project_db_1 bash # sample-project_db_Enter the container 1 and execute the command
# mysql -u user -p #log in
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
mysql>　use sample_db;
Database changed

Database changed
mysql> show tables; #Check if the table is made
+---------------------+
| Tables_in_sample_db |
+---------------------+
| users               |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from users; #Check if the prepared data is included
+----+------+-----------------+
| id | name | email           |
+----+------+-----------------+
|  1 | TOM  | [email protected] |
+----+------+-----------------+
1 row in set (0.00 sec)

If this is the case, you are only connecting to Mysql from inside the container, so change the connection method and check. Log in by specifying the host name and port. (If not specified, it seems to connect to the default port 3306)

Terminal

$ mysql --host 127.0.0.1 --port 3314 -u user -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g
・
・
mysql>

9. Check the log

Make sure the file is in the location you set earlier.

Directory structure

sample-project/
              ├ docker
              ├ log/
              |    └ mysql/
              ├ src       └ mysqld.log
              |
              └ docker-compose.yml

The contents of the file look like this.

mysqld.log

mysqld, Version: 5.7.23-log (MySQL Community Server (GPL)). started with:
Tcp port: 0  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
2018-10-13T11:38:04.918533Z	    1 Query	CREATE DATABASE mysql;
2018-10-13T11:38:04.919437Z	    1 Query	USE mysql;
・
・
The following is omitted

This time, all query logs are set to be output, so the file size seems to be quite large (6.71MB).

Impressions

I have a low understanding of the concept of containers, so I feel like I'm doing something, but it's easier than installing MySQL locally and playing around with it, and it doesn't pollute the local environment, so I decided to use it more and more. I haven't introduced it this time, but I hope I can share the procedure for connecting from an application developed with Springboot in the future.

Other reference pages

--External link 1: http://dqn.sakusakutto.jp/2015/10/docker_mysqld_tutorial.html --External link 2: https://blog.local-c.com/archives/1984