MySQLの環境を作る
docker-composeでMySQLを利用できる環境を作る
※mysqlのイメージをそのまま利用するとWindowsでは下記の警告が発生し、my.cnfが読み込めなかった。この為、Dockerfile を介してパーミッションを変更する処理を追加した。
mysqld: [Warning] World-writable config file '/etc/mysql/conf.d/my.cnf' is ignored.
Windows + docker-compose + mysql で文字化けさせない方法 - Qiita
ディレクトリ構成
docker
├─docker-compose.yml
└─mysql
├─Dockerfile
├─conf.d
│ └─my.cnf
├─data
├─logs
└─initdb.d
└─init.sql
docker-compose.yml
version: '3.8'
services:
db:
container_name: oursql5.7
build: ./mysql/
hostname: mysql
environment:
MYSQL_DATABASE: sample
MYSQL_USER: mysql-app
MYSQL_PASSWORD: mysql-app
MYSQL_ROOT_PASSWORD: admin
BIND-ADDRESS: 0.0.0.0
ports:
- "33060:3306"
volumes:
- ./mysql/initdb.d:/docker-entrypoint-initdb.d
- ./mysql/data:/var/lib/mysql
- ./mysql/logs:/var/log/mysql
tty: true
Dockerfile
FROM mysql:5.7 EXPOSE 3306 ADD ./conf.d/my.cnf /etc/mysql/conf.d/my.cnf RUN chmod 644 /etc/mysql/conf.d/my.cnf CMD ["mysqld"]
my.cnf
[mysqld] character-set-server=utf8mb4 #エラーログを出力 log-error=/var/log/mysql/error.log #詳細ログを出力 general_log=ON general_log_file=/var/log/mysql/query.log #Slow Queryログを出力 slow_query_log=ON # slow queryログの有効化 slow_query_log_file=/var/log/mysql/slow.log # ファイルパス long_query_time=5 # 5秒以上処理に時間がかかったクエリを記録 log-queries-not-using-indexes # インデックスが使用されていないクエリをログに出力 [mysql] default-character-set=utf8mb4 [mysqldump] default-character-set=utf8mb4
init.sql
CREATE TABLE IF NOT EXISTS t_users ( ID INT(11) NOT NULL auto_increment PRIMARY KEY COMMENT 'ID', MAIL_ADDRESS VARCHAR(256) NOT NULL COMMENT 'メールアドレス', USER_NAME VARCHAR(15) NOT NULL COMMENT 'ユーザ名', PASSWORD VARCHAR(256) NOT NULL COMMENT 'パスワード', LAST_LOGIN_TIME DATETIME COMMENT '最終ログイン日時', REGIST_TIME DATETIME NOT NULL COMMENT '登録日時', UPDATE_TIME DATETIME NOT NULL COMMENT '更新日時', DELETED TINYINT(1) NOT NULL DEFAULT 0 COMMENT '削除フラグ' ) DEFAULT CHARSET=utf8mb4 ; INSERT INTO t_users (MAIL_ADDRESS, USER_NAME, PASSWORD, LAST_LOGIN_TIME, REGIST_TIME, UPDATE_TIME) VALUES ('jack@hoge.example.jp', 'Jack', SHA2('1234567', 256), NULL, NOW(), NOW()), ('lary@hoge.example.jp', 'Lary', SHA2('1234567', 256), NULL, NOW(), NOW()), ('pitt@hoge.example.jp', 'Pitt', SHA2('1234567', 256), NULL, NOW(), NOW()) ;
起動
docker-compose up -d
疎通確認
mysql -u mysql-app -p -P 33060 sample Enter password: ********* Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.34 MySQL Community Server (GPL) Copyright (c) 2000, 2017, 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> select * from t_users; +----+----------------------+-----------+------------------------------------------------------------------+-----------------+---------------------+---------------------+--------------+ | ID | MAIL_ADDRESS | USER_NAME | PASSWORD | LAST_LOGIN_TIME | REGIST_TIME | UPDATE_TIME | DELETED| +----+----------------------+-----------+------------------------------------------------------------------+-----------------+---------------------+---------------------+--------------+ | 1 | jack@hoge.example.jp | Jack | 8bb0cf6eb9b17d0f7d22b456f121257dc1254e1f01665370476383ea776df414 | NULL | 2021-06-10 07:18:41 | 2021-06-10 07:18:41 | 0 | | 2 | lary@hoge.example.jp | Lary | 8bb0cf6eb9b17d0f7d22b456f121257dc1254e1f01665370476383ea776df414 | NULL | 2021-06-10 07:18:41 | 2021-06-10 07:18:41 | 0 | | 3 | pitt@hoge.example.jp | Pitt | 8bb0cf6eb9b17d0f7d22b456f121257dc1254e1f01665370476383ea776df414 | NULL | 2021-06-10 07:18:41 | 2021-06-10 07:18:41 | 0 | +----+----------------------+-----------+------------------------------------------------------------------+-----------------+---------------------+---------------------+--------------+ 3 rows in set (0.00 sec)
