SQL statement learning ~ Environment construction ~ Docker + MySQL

This time, I prepared an environment for learning basic SQL using MySQL using docker, so I would like to share it. It is recommended for those who want to separate the environment from the DB used for daily work and the DB for learning.

Operating environment Mac（Catallina 10.15.7） Docker 19.03.13 docker-compose 1.27.4

Please install docker and docker-compose in advance.

Directory structure

.
├── docker-compose.yml  #docker-compose config file
└── mysql
    ├── my.cnf #mysql configuration file
    └── volume/　#Used to synchronize the data in the container. (For details, search by docker persistence)

I have an article here about the tree command to do this kind of display. Easy display of directory structure! Tree command (Mac) Introducing frequently used options

docker-compose.yml

Please copy and paste the following contents. (For an explanation of the contents, see the docker-compose.yml section of the reference article below.) Environment construction of Laravel + MySQL + phpMyadmin with Docker

Also, this time we will create a sample DB from the MySQL formula, so we will not create it with docker-compose.yml.

docker-compose.yml

version: '3'
services:
    mysql:
        image: mysql:5.7
        container_name: mysql_docker
        environment: 
            MYSQL_ROOT_PASSWORD: root
            MYSQL_USER: dbuser
            MYSQL_PASSWORD: dbpass
            TZ: 'Asia/Tokyo'
        command: mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
        volumes:
            - ./mysql/volume:/var/lib/mysql
            - ./mysql/my.cnf:/etc/mysql/conf.d/my.cnf
        ports:
            - 13306:3306

If you can copy and paste the above Execute the following command in the directory where the docker-compose.yml file is located. If it is finally displayed as below, there is no problem.

[mac]$ docker-compose up -d
Creating network "dockermysql_default" with the default driver
Creating mysql_docker ... done

Let's check that the container has started.

[Mac]$ docker ps
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                                NAMES
aaeda519cf71        mysql:5.7           "docker-entrypoint.s…"   21 seconds ago      Up 5 seconds        33060/tcp, 0.0.0.0:13306->3306/tcp   mysql_docker

If it is displayed as above, there is no problem.

Try connecting with the client tool.

Try connecting with the client tool. The client tool used this time is DBeaver. Besides, TablePlus and sequel pro [MySQL Workbench](https://dev.mysql.com/downloads/ There are various tools such as workbench /), so please use the one you like.

Create a working directory and press the connect button.

This time we will use MySQL, so select MySQL.

Enter the information as follows: Basically, refer to the contents of docker-compose.yml. After inputting, press test connection and

You can connect as follows.

Click the end button to complete.

Pour in official sample data

MySQL officially provides sample data. I would like to pour it in and use it for studying. MySQL proper sample data

I think I downloaded it in gzip format this time.

Now, move the downloaded file to the root directory of this project (the directory where docker-compose.yml is located).

.
├── docker-compose.yml
├── mysql
│   ├── my.cnf
│   └── volume / #By the way, since I launched the container, there are many folders in this directory.
└── world.sql.gz ← Put here.

Since it's a big deal, I'll use the docker cp command to send the folder inside the container.

[Mac]$ docker ps //Now check the name of the container.
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                                NAMES
aaeda519cf71        mysql:5.7           "docker-entrypoint.s…"   21 seconds ago      Up 5 seconds        33060/tcp, 0.0.0.0:13306->3306/tcp   mysql_docker

I found out that the container name is mysql_docker, so I send it to the container.

[Mac]$ docker cp world.sql.gz mysql_docker:./

It means to copy world.sql.gz to the home directory of mysql_docker.

Check if you can copy.

[Mac]$ docker exec -it mysql_docker bash
[container]# ls | grep wold
world.sql.gz

If it appears as above, there is no problem.

Let's pour it in.

Execute the following command in the container.

[container]# zcat world.sql.gz | mysql -uroot -p //Password is docker-compose.The root user password set in yml.
[container]#　//If nothing is displayed, the process is complete.
[container]# mysql -uroot -p 
Enter password:
root@599851f8e10f:/# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.32 MySQL Community Server (GPL)

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

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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

mysql> use world;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city            |
| country         |
| countrylanguage |
+-----------------+
3 rows in set (0.00 sec)

mysql>

zcat world.sql.gz | mysql -uroot -p It is a command to output the contents compressed by zcat world.sql.gz and send it to the mysql server.

mysql -uroot -p Specify the user with -u. This time, enter as the root user. -p is an option to enter the password.

show databases; Outputs all databases. Now make sure you have a DB called world.

use world; The command is to use the laraveldb database.

show tables; It is a command to output all the tables of the database (world) in use.

Check in detail using the client tool.

Open the client tool again and update (F5) to confirm that the DB called world is added.

If the data is entered like this, it is complete.

SQL execution

Finally, I want to execute only a simple SELECT statement to finish.

From DBeaver's SQL editor

SELECT 
*
FROM 
world.city
;

And run

If the contents are displayed, there is no problem.

Finally

Thank you for reading to the end. Now that the environment for executing SQL statements is in place, I would like to continue learning. Thank you very much.