hatakoya memo

MySQL

環境構築

.bashrcに以下を追記する。

alias mysql='docker run -ti mysql:5.7 mysql'

コマンド

ログイン入力なしでクエリを直接実行

-pの後ろにスペースを空けずにパスワードを入れることに注意。

mysql -u [ユーザ] -p[パスワード] -e [クエリ]

# ex:
mysql -u root -ppassword -e "select * from db.table"

CREATE TABLE

create table db.user  (
    id int primary key,
    name varchar(50) not null
) charset=utf8;

mysqldump: ダンプファイル作成

mysqldump -u <user> -p<pass> -h <host> -P <port> --single-transaction <db name> <table name> > <output filename>

起動

docker run -itd --rm --name mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=password mysql

認証が面倒なのでmysql:5.7を使ってもいいかもしれない

MySQLログイン

winpty docker exec -it mysql bash

# ログイン(パスワードは`MYSQL_ROOT_PASSWORD`に設定した値を入力)
mysql -u root -p

# SQL文を適当に実行
mysql > show databases;
# +--------------------+
# | Database           |
# +--------------------+
# | information_schema |
# | mysql              |
# | performance_schema |
# | sys                |
# +--------------------+
# 4 rows in set (0.06 sec)

MySQLのクライアントのみ入れる

MySQLをDockerでのみ動かし、ホストPCからmysql -u root -pしたいときに。

mysqlのzipをダウンロードする。
- https://dev.mysql.com/downloads/mysql/

解凍した中にあるbinフォルダにパスを通す。

初期データつきDockerイメージ

/docker-entrypoint-initdb.dディレクトリに.sqlや.shなどを置いておくと起動時に実行してくれる。

起動時に読んでほしいSQLを書く。 init.sql

create database my_database;

use my_database;

create table my_table  (
    id int primary key,
    name varchar(50) not null
) charset=utf8;

Dockerfileでそのファイルを指定の場所にコピーする。

FROM mysql:5.7

# passwordを設定しておくとdocker runで環境変数設定する手間なくて楽
ENV MYSQL_ROOT_PASSWORD=password

ADD init.sql ./docker-entrypoint-initdb.d/init.sql

# 複数sqlファイルを送る場合はこう
# ADD ./*.sql ./docker-entrypoint-initdb.d/

イメージをビルドして起動する。

docker build -t my_mysql .
docker run -itd --rm --name my_mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=password my_mysql

ちなみに、複数の.sqlファイルを読み込みたい場合はアルファベット順でファイルを置くと読み込む順序を指定できる。

Ref:
https://qiita.com/takyam/items/de87252fca60a9c914c6#initializing-a-fresh-instance

SQL

テーブル作成

create table my_database.user(
    -- オートインクリメントな主キー
    user_id int AUTO_INCREMENT PRIMARY KEY,
    -- 文字列（ユニーク列）
    email varchar(255) UNIQUE,
    -- 時刻
    inserted_time datetime NOT NULL,
    -- テーブル更新時に勝手に更新される時刻列
    updated_time TIMESTAMP NOT NULL DEFAULT current_timestamp ON UPDATE current_timestamp,
    -- UUID
    uuid binary(16) NOT NULL,
    -- 複合主キーの例
    PRIMARY KEY(user_id, email)
) charset=utf8;

パーティション

https://dev.mysql.com/doc/refman/5.6/ja/partitioning-types.html
「～～日ごとにパーティションを分ける」とかができる。
古くなったデータを一括で消すバッチ処理があるときにパフォーマンスがよくなる。

パーティションの確認

パーティションの情報はINFORMATION_SCHEMA.PARTITIONから取得できる。
パーティションの追加時にIF NOT EXISTSは使えないが、以下の例のようにパーティションが存在するかをクエリで確認することはできる。

-- hogeテーブルのパーティション情報を表示
select TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION, TABLE_ROWS from INFORMATION_SCHEMA.PARTITIONS where TABLE_NAME = 'hoge';

-- hogeテーブルにパーティションp0が存在するか確認
select case when count(*) = 0 then 'true' else 'false' end as is_exists from INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'hoge' AND PARTITION_NAME = 'p1';

-- おまけ: 'hoge_'始まりのテーブルのパーティションをすべて表示
select TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION, TABLE_ROWS from INFORMATION_SCHEMA.PARTITIONS where TABLE_NAME LIKE 'hoge_%';

Ref:
https://dev.mysql.com/doc/refman/5.7/en/partitions-table.html

パーティションの作成

alter table my_table partition by range (my_table_time) (PARTITION p1000 VALUES LESS THAN (1000));

-- すでに定義済みのパーティションにパーティションを追加する
alter table <table name> ADD PARTITION (PARTITION <partition name> VALUES LESS THAN (<partition value>))

パーティションの削除

ALTER TABLE hoge_table DROP PARTITION p0;

-- 複数削除はカンマ区切りで
ALTER TABLE hoge_table DROP PARTITION p0, p1, p2

Tips

UUID, GUIDをwhereの条件にする

# 0xをつけてハイフンなし。大文字小文字は区別される
select * from hoge where uuid = 0x10697ebbb3e34ec895119e2c2fb47c1f

コネクションプールについて

Ref:
https://qiita.com/snaka/items/b6e7500c96e04c131d9e
https://qiita.com/methane/items/ccd3fd856b02b06c9452

SELECT結果をcsv出力

# query.sqlにSELECTクエリを書いておく
mysql -uroot -ppassword < query.sql | sed 's/\t/,/g' > out.csv

テーブル名のリストを表形式なしで取得

mysql \
    --silent \
    --disable-column-names \
    -uroot \
    -ppassword \
    -h127.0.0.1 \
    <database name> \
    -e "show tables;"

Ref: https://stackoverflow.com/questions/16711598/get-the-sql-query-result-without-the-table-format

テーブルの情報を表示

# スキーマをコメント含め表示
show full columns from <table name>

クエリでMySQLの疎通確認

SELECT version();をするといい。
MySQLのDockerコンテナが正常に開始したか確認する例：

docker exec -it db mysql -u root -ppassword -e "SELECT version();"

dockerイメージが正常に起動するかテストするシェル。

#!/bin/sh

DOCKER_IMAGE=$1

if [ -z $DOCKER_IMAGE ]; then
    echo 'docker image is not defined'
    exit 1
fi

# debug
docker stop ${DOCKER_IMAGE} 2>/dev/null || true
docker run -itd --rm --name ${DOCKER_IMAGE} ${DOCKER_IMAGE}

for i in {0..10} ; do
    if docker exec -it db mysql -u root -ppassword -e "SELECT version();" > /dev/null 2>&1 ; then
        echo "done."
        docker stop ${DOCKER_IMAGE}
        exit 0
    fi
    echo "waiting..."
    sleep 3
done

echo "failed."
exit 1

binary型のUUIDを追加、表示する

-- UUIDを含む行を追加
insert into my_table (guid, name, age) VALUES
    (UNHEX(REPLACE('00000000-0000-0000-0000-000000000000','-','')), "hoge", 10),
    (UNHEX(REPLACE('00000000-0000-0000-0000-000000000001','-','')), "fuga", 11),
    (UNHEX(REPLACE('00000000-0000-0000-0000-000000000002','-','')), "piyo", 12),

-- ハイフンなしで表示
select HEX(guid) from my_table;

Ref: MySQL < 8.xにUUID処理を追加する

SDK

mysql2を入れるためには事前にmyslqコネクタが必要。

gem install mysql2

require 'mysql2'

client = Mysql2::Client.new(:host => "localhost", :username => "root", :password => "password", :database => "mydatabase")

# データ取得
p client.query("SELECT * FROM mytable").map {|row| 
  {
    "col1" => row['col1'],
    "col2" => row['col2']
  }
}

# データ追加
client.query("INSERT INTO mytable (col1, col2) VALUES 
('aaa1','bbb1'), 
('aaa2','bbb2'), 
('aaa3','bbb3')")

ruby​:2.4.1-alpineで動かす

事前にmariadb-devとbuild-baseが必要。
https://ncona.com/2017/09/getting-rails-to-run-in-an-alpine-container/

apk add build-base mariadb-dev
gem install mysql2

タイムゾーンをJSTに設定する

FROM mysql:5.7

# この2行が必要
ENV TZ='Asia/Tokyo'
RUN echo "USE mysql;" > /docker-entrypoint-initdb.d/timezones.sql &&  mysql_tzinfo_to_sql /usr/share/zoneinfo >> /docker-entrypoint-initdb.d/timezones.sql

Ref:
https://stackoverflow.com/a/50001794

FAQ

localhostに繋げない

mysql --protcol tcp
# or
mysql -h 127.0.0.1

MySQL8.0から変わった認証

Rubyでmysql2を使って接続しようとしたけど認証が通らなかった。

default_authentication.cnf

[mysqld]
default_authentication_plugin= mysql_native_password

作ったファイルを/etc/mysql/conf.dに置けばいい。
Dockerfileで書くならこんな感じ。

Dockerfile

FROM mysql

ADD default_authentication.cnf ./default_authentication.cnf

RUN cp default_authentication.cnf /etc/mysql/conf.d/default_authentication.cnf

There was an error accessing /mltest/config/Gemfile.lock.

The underlying system error is Errno::EROFS: Read-only file system @ rb_sysopen

Gemfile.lockを置く。

便利ツール

EverSQL

SQL文を貼り付けるとフォーマットとバリデーションをしてくれる。

https://www.eversql.com/sql-syntax-check-validator/

← Couchbase →