MySQLの文字コード事情
の27ページの表が出るようにmy.cfを調整してみる

mysql> SHOW VARIABLES LIKE '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

準備

$ ls
docker-compose.yml  my.cnf

my.cf

$ cat my.cnf
[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_bin
log-bin=/var/log/mysql/bin-log
server-id=1

[client]
default-character-set = utf8mb4

character-set-serverパラメータ

MySQL :: MySQL 5.6 リファレンスマニュアル :: 5.1.4 サーバーシステム変数

他にオプションファイルで指定可能なcharacter_set*といえばcharacter-set-filesystemがあるが、こちらはデフォルトのままが良いようだ。

default-character-setについては
MySQL 5.5ではdefault-character-setが廃止されてた… | イガラシ研
の通り、[mysqld]セクションからは削除されている。ただし、mysqlコマンドは[client]セクションを読んでいるので設定

collation-serverパラメータ

MySQLの文字コードとCollation - Qiita
より、

utf8mb4_bin
A ≠ a 区別する
≠ 区別する
は≠ぱ≠ば 区別する

を選択

docker-compose.yml

$ cat docker-compose.yml
version: '2'
services:
  mysql:
    image: mysql:5.7
    ports:
      - "3306:3306"
    volumes:
      - ./mysql-data:/var/lib/mysql
      - ./my.cnf:/etc/mysql/conf.d/my.cnf
    environment:
      MYSQL_ALLOW_EMPTY_PASSWORD: 1
      TZ: "Asia/Tokyo"

接続

$ docker-compose up -d
$ docker-compose exec mysql bash

# mysql -u root
mysql> SHOW VARIABLES LIKE '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

環境

OS：macOS Mojave 10.14.3
Docker：18.09.2
docker-compose：1.23.2
docker-machine：0.16.1
php：7.1.23
Laravel Framework：5.8.7
mysql：Ver 14.14 Distrib 5.7.25, for Linux (x86_64) using EditLine wrapper

事象

laradockで構築したmysqlを起動すると

$ docker-compose up -d php-fpm nginx mysql
Recreating laradock_mysql_1          ... done
Starting laradock_docker-in-docker_1 ... done
Starting laradock_workspace_1        ... done
Starting laradock_php-fpm_1          ... done
Starting laradock_nginx_1            ... done


$ docker-compose ps
           Name                          Command              State                      Ports                   
-----------------------------------------------------------------------------------------------------------------
laradock_docker-in-docker_1   dockerd-entrypoint.sh           Up       2375/tcp                                  
laradock_mysql_1              docker-entrypoint.sh mysqld     Exit 2                                             
laradock_nginx_1              /bin/bash /opt/startup.sh       Up       0.0.0.0:443->443/tcp, 0.0.0.0:8080->80/tcp
laradock_php-fpm_1            docker-php-entrypoint php-fpm   Up       9000/tcp                                  
laradock_workspace_1          /sbin/my_init                   Up       0.0.0.0:2222->22/tcp

となり起動にコケてしまう事象が発生しました。

解決方法

一旦dockerを止めます。

docker-compose stop

以下のファイルを変更します。

.env

### MYSQL #################################################

MYSQL_VERSION=latest #latestのまま
- MYSQL_DATABASE=default
+ MYSQL_DATABASE=laradock
- MYSQL_USER=root
+ MYSQL_USER=laradock
- MYSQL_PASSWORD=root
+ MYSQL_PASSWORD=secret
MYSQL_PORT=3306
MYSQL_ROOT_PASSWORD=root
MYSQL_ENTRYPOINT_INITDB=./mysql/docker-entrypoint-initdb.d

さらに、以下のディレクトリ配下のファイルをすべて削除します。
/Users/{USER NAME}/.laradock/data/mysql/

終わり

以上、対応後にdockerを起動すると

$ docker-compose up -d php-fpm nginx mysql
Starting laradock_docker-in-docker_1 ... done
Recreating laradock_mysql_1          ... done
Starting laradock_workspace_1        ... done
Starting laradock_php-fpm_1          ... done
Starting laradock_nginx_1            ... done


$ docker-compose ps
           Name                          Command              State                     Ports                   
----------------------------------------------------------------------------------------------------------------
laradock_docker-in-docker_1   dockerd-entrypoint.sh           Up      2375/tcp                                  
laradock_mysql_1              docker-entrypoint.sh mysqld     Up      0.0.0.0:3306->3306/tcp, 33060/tcp         
laradock_nginx_1              /bin/bash /opt/startup.sh       Up      0.0.0.0:443->443/tcp, 0.0.0.0:8080->80/tcp
laradock_php-fpm_1            docker-php-entrypoint php-fpm   Up      9000/tcp                                  
laradock_workspace_1          /sbin/my_init                   Up      0.0.0.0:2222->22/tcp  

mysqlのプロセスが確認できれば完了です。お疲れ様でした＾＾

プリペアードステートメントを使うときにハマったので備忘録。

例えば、

$sql = 'SELECT * FROM orders WHERE buy_user = :user_id OR sale_user = :user_id AND delete_flg = 0';
$data = [':user_id' => $user_id];

だとエラーを吐く。

なので、

$sql = 'SELECT * FROM orders WHERE buy_user = :user_id OR sale_user = :id AND delete_flg = 0';
$data = [':user_id' => $user_id, ':id' => $user_id];

としなくてはならない。理由はわからないがPDOはそういう仕様らしい。バインドするときのキーは複数あったらだめらしい。

docker-composeでMaster/Slave構成のMySQLを手に入れる　の写経

• 永続化のところはmasterもtmpfsへ
• MYSQL_ALLOW_EMPTY_PASSWORD（実験用には）便利すぎる
• 固定IPを振らないとreplユーザーのネットワークアクセス制限ができない
• レプリケーションをrootからreplユーザーで行うよう変更

準備

$ ls
docker-compose.yml  master.cnf  slave.cnf  start-slave.sh

docker-compose.yml

version: '3'
services:
  mysql:
    image: mysql:5.7
    ports:
      - "3306:3306"
    tmpfs: /var/lib/mysql
    volumes:
      - ./master.cnf:/etc/mysql/conf.d/my.cnf
    networks:
      db_net:
        ipv4_address: 192.168.192.2
    environment:
      MYSQL_ALLOW_EMPTY_PASSWORD: 1
      TZ: "Asia/Tokyo"
  mysql-slave:
    image: mysql:5.7
    ports:
      - '13306:3306'
    depends_on:
      - mysql
    tmpfs: /var/lib/mysql
    volumes:
      - ./slave.cnf:/etc/mysql/conf.d/my.cnf
      - ./start-slave.sh:/docker-entrypoint-initdb.d/start-slave.sh
    networks:
      db_net:
        ipv4_address: 192.168.192.3
    environment:
      MYSQL_ALLOW_EMPTY_PASSWORD: 1
      TZ: "Asia/Tokyo"

networks:
  db_net:
    driver: bridge
    ipam:
      config:
      - subnet: 192.168.192.0/24

$ cat master.cnf slave.cnf
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
log-bin=mysql-bin
expire_logs_days=5
server-id=1

[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
log-bin=mysql-bin
expire_logs_days=5
server-id=2
read_only=1

cat start-slave.sh
#!/bin/sh

# depends_onの設定しておけば気にならないけど念の為masterの起動を待つ
while ! mysqladmin ping -h mysql --silent; do
  sleep 1
done

mysql -u root -h mysql -e "CREATE USER 'repl'@'192.168.192.3' IDENTIFIED BY 'slavepass';"
mysql -u root -h mysql -e "GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.192.3';"

# masterをロックする
mysql -u root -h mysql -e "RESET MASTER;"
mysql -u root -h mysql -e "FLUSH TABLES WITH READ LOCK;"

# masterのDB情報をDumpする
# ここでは --all-databases にしてるけど用途に応じて必要なDBだけにしていいと思う
mysqldump -uroot -h mysql --all-databases --master-data --single-transaction --flush-logs --events > /tmp/master_dump.sql
# 特定のDBだけにする場合はこんな感じ(my.cnfのreplica-do-dbも忘れずに設定すること)
# mysqldump -uroot -h mysql データベース名 --master-data --single-transaction --flush-logs --events > /tmp/master_dump.sql

# dumpしたmasterのDBをslaveにimportする
mysql -u root -e "STOP SLAVE;";
mysql -u root < /tmp/master_dump.sql

# masterに繋いで bin-logのファイル名とポジションを取得する
log_file=`mysql -u root -h mysql -e "SHOW MASTER STATUS\G" | grep File: | awk '{print $2}'`
pos=`mysql -u root -h mysql -e "SHOW MASTER STATUS\G" | grep Position: | awk '{print $2}'`

# slaveの開始
mysql -u root -e "RESET SLAVE";
#mysql -u root -e "CHANGE MASTER TO MASTER_HOST='mysql', MASTER_USER='root', MASTER_PASSWORD='', MASTER_LOG_FILE='${log_file}', MASTER_LOG_POS=${pos};"
mysql -u root -e "CHANGE MASTER TO MASTER_HOST='mysql', MASTER_USER='repl', MASTER_PASSWORD='slavepass', MASTER_LOG_FILE='${log_file}', MASTER_LOG_POS=${pos};"
mysql -u root -e "start slave"

# masterをunlockする
mysql -u root -h mysql -e "UNLOCK TABLES;"

実食

$ docker-compose up -d
$ docker-compose exec mysql-slave bash // slaveにログイン
# mysql -u root -e "SHOW SLAVE STATUS\G" // レプリケーションできていること確認
# exit
$ docker-compose exec mysql bash // masterにログイン
# mysql -u root
mysql> CREATE DATABASE users; // usersデータベースを作成
mysql> quit
# exit
$ docker-compose exec mysql-slave bash // 再びslaveにログイン
# mysql -u root -e "show databases"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| users              | // 追加されたものが同期されている
+--------------------+

参考

mysql - Docker Hub
MySQL GTID レプリケーション素振り - ngyukiの日記
docker-composeでMaster/Slave構成のMySQLを手に入れる - Qiita
docker-composeで起動したコンテナにbashで入ったりコマンドを渡す - Qiita
MySQLチートシート - Qiita
MySQL コマンドチートシート　初心者向け - Qiita
PostgreSQLとMySQLの基本的なコマンドを比較 - Qiita
docker-composeでお手軽mysqlのmaster-slave構成 - ウェブエンジニア珍道中
日々の覚書: MySQL 8.0.3とそれ以降では expire_logs_days は非推奨なパラメーターになりました
MySQLでバイナリログを定期的に削除するmy.cnfの設定（expire_logs_days） - ドラあり！*ドラゴンに挑むアリの物語 ** Python使いの日々