Create a MySQL environment with Docker from 0-> 1

What to do in this article

Create a MySQL environment with Docker Create Database Make a table Enter data

environment

Docker mysql 5.7

Create a MySQL environment with Docker

Create a parent folder for this article

$ mkdir mysql-sample

And make docker-compose.yml

docker-compose.yml

version: "3"
services:
  mysql:
    image: mysql:5.7
    ports:
      - "3306:3306"
    environment:
      MYSQL_ROOT_PASSWORD: mysql
    volumes:
      - "./mysql/db-data/:/var/lib/mysql" #Data persistence
      - "./mysql/my.cnf:/etc/mysql/conf.d/my.cnf" #Necessary to use Japanese as data

Create a mysql folder as a place to store MySQL related data.

And write the MySQL configuration file in my.cnf.

msql/my.cnf

[mysqld]
character-set-server=utf8

This allows you to enter Japanese as mysql data

mysql-sample
├── docker-compose.yml
└── mysql
    └── my.cnf

The mysql client is included in the mysql image. We will use it in this article.

docker-compose up -d

Launch the container with. Then mysql is initialized and There will be a folder called db-data under the project's mysql folder

Enter the container with Attach Shell

Start mysql client with

$ mysql -u root -p

The password is mysql set in docker-compose.yml

.sql

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

...

mysql> 

This completes until you start mysql

Create database

Show all db

.sql

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.04 sec)

mysql> 

Add a new db called sample here

.sql

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

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sample             |
| sys                |
+--------------------+
5 rows in set (0.02 sec)

mysql> 

Type the following command to operate this db

mysql> use sample;
Database changed

This completes the db creation

Create table

At first there is no table.

mysql> show tables;
Empty set (0.01 sec)

mysql> 

Therefore, I type the command to create a table, but since the command to type a table is long, write it first with a text editor etc.

.sql

CREATE TABLE users (
id int PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL UNIQUE,
password CHAR(36) NOT NULL
);

Create a table called users.

The explanation about this query is roughly below

Paste the above query as it is and execute it

.sql

mysql> CREATE TABLE users (
    -> id int PRIMARY KEY AUTO_INCREMENT,
    -> username VARCHAR(255) NOT NULL,
    -> email VARCHAR(255) NOT NULL UNIQUE,
    -> password CHAR(36) NOT NULL
    -> );
Query OK, 0 rows affected (0.07 sec)

mysql> show tables;
+------------------+
| Tables_in_sample |
+------------------+
| users            |
+------------------+
1 row in set (0.01 sec)

mysql> show columns from users;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| username | varchar(255) | NO   |     | NULL    |                |
| email    | varchar(255) | NO   | UNI | NULL    |                |
| password | char(36)     | NO   |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
4 rows in set (0.02 sec)

mysql> 

This completes the table creation

Add data

Creating a UUID.

If you use mac, you can make it quickly with uuidgen. For powershell [Guid] :: NewGuid () => 224A68FF-FB77-4F97-94CF-8B7AD846DE05

.sql

insert into users (username, email, password) values ("Teach", "[email protected]", "uooooo");

.sql

mysql> insert into users (username, email, password) values ("Teach", "[email protected]", "uooooo");
Query OK, 1 row affected (0.01 sec)

mysql> 

Data acquisition

.sql

mysql> select * from users;
+----+----------+-------------------+----------+
| id | username | email             | password |
+----+----------+-------------------+----------+
|  1 | Teach    | [email protected] | uooooo   |
+----+----------+-------------------+----------+
1 row in set (0.00 sec)

mysql> 

Supplement

When looking at db from other Docker containers, host will be the service name in docker-compose.yml. Therefore, connect as follows

mysql -u root -h mysql -p