[JAVA] 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
Recommended Posts