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. vertical.png

Premise

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

The development environment is as follows.

Development environment

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.

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

Recommended Posts

I started MySQL 5.7 with docker-compose and tried to connect
I tried to get started with WebAssembly
I tried to link grafana and postgres [docker-compose]
Connect to MySQL 8 with Java
I tried to read and output CSV with Outsystems
I tried to get started with Spring Data JPA
[Java] I tried to connect using a connection pool with Servlet (tomcat) & MySQL & Java
I tried to interact with Java
I tried to make an introduction to PHP + MySQL with Docker
I tried to get started with Swagger using Spring Boot
[Java] I installed JDBC and tried to connect with servlet + MySQL. (There is a version using DAO / Bean)
Try running MySql and Blazor with docker-compose
I tried connecting to MySQL using JDBC Template with Spring MVC
I tried to implement ModanShogi with Kinx
[Amateur remarks] I tried to automate SSL possible (self-signed certificate) with Docker-Compose
01. I tried to build an environment with SpringBoot + IntelliJ + MySQL (MyBatis) (Windows10)
I tried to measure and compare the speed of GraalVM with JMH
I tried to verify AdoptOpenJDK 11 (11.0.2) with Docker image
I tried to make Basic authentication with Java
I tried to manage login information with JMX
I also tried WebAssembly with Nim and C
I tried to link JavaFX and Spring Framework.
Rails beginners tried to get started with RSpec
Error deploying rails5 + Mysql to heroku with Docker-compose
I tried to break a block with java (1)
I tried Getting Started with Gradle on Heroku
[Java] Connect to MySQL
I tried to convert JavaBean and XML with Jackson formatter XML in this era
I tried what I wanted to try with Stream softly.
I tried to implement file upload with Spring MVC
How to connect MySQL / MariaDB + HikariCP with Liferay 7 / DXP
I tried to implement TCP / IP + BIO with JAVA
[Java 11] I tried to execute Java without compiling with javac
I tried to integrate AWS I oT button and Slack
I tried to draw animation with Blazor + canvas API
I tried to implement Stalin sort with Java Collector
I want to transition screens with kotlin and java!
I tried to chew C # (reading and writing files)
roman numerals (I tried to simplify it with hash)
I want to connect to Heroku MySQL from a client
I tried to create a shopping site administrator function / screen with Java and Spring
Bad Gateway came out when I tried to connect Grafana and InfluxDB on Docker
[Azure] I tried to create a Java application for free ~ Connect with FTP ~ [Beginner]
I tried DI with Ruby
Connect to DB with Java
I tried UPSERT with PostgreSQL.
I tried BIND with Docker
I tried to verify yum-cron
Connect to oracle with eclipse!
[Android] Connect to MySQL (unfinished)
I tried to create a java8 development environment with Chocolatey
I tried to modernize a Java EE application with OpenShift.
I tried to increase the processing speed with spiritual engineering
I tried to collect and solve Ruby's "class" related problems.
I tried to summarize the basics of kotlin and java
[Rails] I tried to create a mini app with FullCalendar
I tried to verify this and that of Spring @ Transactional
Connect to multiple MySQL instances with SSL enabled in JDBC
I want to make a list with kotlin and java!
I want to make a function with kotlin and java!
I tried to make Java Optional and guard clause coexist