MySQLチートシート

忘れやすいことを中心に書いてます。

ユーザ管理

該当するドキュメント : https://dev.mysql.com/doc/refman/8.0/en/access-control.html

ユーザを追加・削除

-- 現在のユーザ
SELECT USER(), CURRENT_USER();
-- 一覧
SELECT User, Host, plugin, ssl_type, account_locked
FROM mysql.user
ORDER BY User, Host;
-- 追加
CREATE USER
    'user_name'@'host' IDENTIFIED BY 'password',
    'user_name'@'host' IDENTIFIED BY 'password', ...;
-- 削除
DROP USER
    'user1'@'host2',
    'user2'@'host2', ...;
-- パスワード設定
ALTER USER 'user_name'@'host' IDENTIFIED BY 'password';
ALTER USER 'user_name'@'host' IDENTIFIED BY RANDOM PASSWORD;  -- MySQL 8.0以降
-- オプション
ALTER USER 'user_name'@'host' REQUIRE SSL;
ALTER USER 'user_name'@'host' WITH MAX_USER_CONNECTIONS 100;
ALTER USER 'user_name'@'host' ACCOUNT LOCK;

ロール

CREATE ROLE 'admin', 'developer';
GRANT ALL ON *.* TO 'admin';
GRANT SELECT ON app_db.* TO 'developer';
SET DEFAULT ROLE 'admin', 'developer' TO 'joe'@'10.0.0.1';

-- 現在のセッションでのアクティブにするロールを変更
SET ROLE DEFAULT;
SET ROLE NONE;
SET ROLE ALL;

SET DEFAULT ROLE and SET ROLE DEFAULT are different statements:

• SET DEFAULT ROLE defines which account roles to activate by default within account sessions.
• SET ROLE DEFAULT sets the active roles within the current session to the current account default roles.

https://dev.mysql.com/doc/refman/8.0/en/set-default-role.html

権限を付与・剥奪

-- 付与
GRANT '権限' ON 'レベル' TO 'ユーザ';
-- 確認
SHOW GRANTS FOR 'ユーザ';
SHOW GRANTS;  -- 現在のユーザが持っている権限を表示
-- 剥奪
REVOKE '権限' ON 'レベル' FROM 'ユーザ';

権限はSELECT, INSERT, UPDATE, DELETE, CREATE, DROPなど
権限の一覧: https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html

プロセス操作

-- 一覧
SHOW PROCESSLIST;
-- こっちの方がおすすめ。WHERE句で絞り込みができる。
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;
-- 問題を起こしているクエリを探す
SELECT COMMAND,
       STATE,
       COUNT(1) THREADS,
       MAX(TIME) MAX_TIME
FROM INFORMATION_SCHEMA.PROCESSLIST
GROUP BY COMMAND, STATE
ORDER BY THREADS DESC, COMMAND, STATE;

-- 強制停止
KILL QUERY ID;
KILL [CONNECTION] ID

参考: https://dev.mysql.com/doc/refman/8.0/en/kill.html

パーティション

既存のテーブルにパーティションを作成・追加・削除

ALTER TABLE 'table_name'
PARTITION BY RANGE COLUMNS(timestamp) (
    PARTITION p_201501 VALUES LESS THAN ('2015-01-01 00:00:00'),
    PARTITION p_201502 VALUES LESS THAN ('2015-02-01 00:00:00')
);

ALTER TABLE 'table_name' ADD PARTITION (
    PARTITION p_future VALUES LESS THAN (MAXVALUE)
);

ALTER TABLE 'table_name' DROP PARTITION p_201501, p_201502;

https://dev.mysql.com/doc/refman/5.5/en/alter-table.html
https://dev.mysql.com/doc/refman/5.5/en/alter-table-partition-operations.html

参考、及び注意点

REORGANIZE PARTITION するにも ADD PARTITION するにもテーブルロックが避けられないので、事前にまとめてガガッとパーティション作っちゃう場合が多いみたいですね。
http://blog.livedoor.jp/sasata299/archives/51882315.html

削除対象のパーティションの前後のパーティションが存在すると、遅くなるケースがある。
http://lab.adn-mobasia.net/?p=182

パーティションの使用状況を確認

SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME='table_name';

テーブルの列

DESC 'table_name';

テーブルの詳細情報を得る

-- 現在のデータベース内の全てのテーブルについて表示
SHOW TABLE STATUS \G
-- 指定したテーブルのみ表示
SHOW TABLE STATUS LIKE 'table_name' \G

デーブルのサイズや、auto_inclimentの現在の値などが調べられる。

注意点

InnoDBでは行数に関するデータはかなりアバウトな値なのであまり信用しないほうがいい。

SSL

鍵を作る

注意:

• 鍵のパーミッションには注意してください。mysqlと管理者以外のユーザから鍵にアクセスできる状態は好ましくありません。
• 証明書を作成するときCNに、一意なホスト名を指定してください。MySQLはCAと同じCNの証明書では、SSL接続が出来ません。

コンピュータ名:

• ca: 認証局の役割をするコンピュータ。手元のPCのことだと思ってください
• db: MySQLが稼働するサーバ
• client: MySQLへアクセスするサーバ

変数:

• CA_HOST: CA(認証局)のCommon Name
• DB_HOST: DBサーバのCommon Name
• CLIENT_HOST: MySQLクライアントのCommon Name

# ca -- CA(認証局)の鍵ペアを作る
$ openssl genrsa 2048 > ca-key.pem
$ openssl req -new -x509 -nodes -days 3600 \
    -key ca-key.pem -out ca-cert.pem \
    -subj "/CN=$CA_HOST"
# ca -- ca-cert.pemをdbとclientにコピー

# db -- DBサーバの鍵ペアを作る
$ openssl req -newkey rsa:2048 -days 3600  \
    -nodes -keyout server-key.pem -out server-req.pem \
    -subj "/CN=$DB_HOST"
$ openssl rsa -in server-key.pem -out server-key.pem

# クライアント証明書を利用しないなら、この作業は必要ない。
# client -- クライアントの鍵ペアを作る
$ openssl req -newkey rsa:2048 -days 3600 \
    -nodes -keyout client-key.pem -out client-req.pem \
    -subj "/CN=$CLIENT_HOST"
$ openssl rsa -in client-key.pem -out client-key.pem

# ca -- server-req.pemをdbからコピー
# ca -- CAの鍵ペアを使ってDBサーバの鍵に署名する
$ openssl x509 -req -in server-req.pem -days 3600 \
    -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 \
    -out server-cert.pem
# ca -- 署名済みのDBサーバの鍵(server-cert.pem)をdbにコピー

# クライアント証明書を利用しないなら、この作業は必要ない。
# ca -- client-req.pemをclientからコピー
# ca -- CAの鍵ペアを使ってクライアントの鍵に署名する
$ openssl x509 -req -in client-req.pem -days 3600 \
    -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 \
    -out client-cert.pem
# ca -- 署名済みのクライアントの鍵(client-cert.pem)をclientにコピー

MySQLサーバの設定

$ mkdir /etc/mysql/ssl
$ chown root:mysql /etc/mysql/ssl
$ chmod 750 /etc/mysql/ssl

# MySQL 5.6.3以降を使用するなら、証明書失効リストも作っておく
touch /etc/mysql/ssl/crl.pem

# /etc/mysql/conf.d/ssl.cnf
[mysqld]
# SSL接続の設定
ssl-key  = /etc/mysql/ssl/server-key.pem
ssl-cert = /etc/mysql/ssl/server-cert.pem

# (MySQL 5.6.3以降) 証明書失効リスト
# (MySQL 8.0.16以降) 動的に別のファイルに変更できる。定期的に更新が出来る。
ssl-crl  = /etc/mysql/ssl/crl-YYYYMMDD.pem

# クライアント証明書を利用するならこれも指定する
ssl-ca   = /etc/mysql/ssl/ca-cert.pem

# (8.0.27以前) デフォルトではTLSv1やTLSv1.1が有効になっている。古いTLSプロトコルを使用する必要がない場合は、無効化しておく。
# tls_version = TLSv1.2,TLSv1.3
# admin_tls_version = TLSv1.2,TLSv1.3

mysqlコマンドでSSL接続をする

引数:

• --ssl-ca: SSL接続を使用します。ca-cert.pemを指定
• --ssl-verify-server-cert: CNと接続先ホスト名を比較し、一致しない場合は接続しない
• --ssl-cert: クライアント証明書を使用する。client-cert.pemを指定
• --ssl-key: クライアント証明書を使用する。client-key.pemを指定
• --ssl-crl: 証明書失効リストを指定 (MySQL 5.6.3以降)

SSLを使用して接続しているか確認

$ mysql -h example.com -utest -p --ssl-ca /path/to/ca-cert.pem
Enter password:
mysql> \s
(前略)
SSL: Cipher in use in DHE-RSA-AES256-SHA
(後略)

SSLが有効にならないときに確認すること:

• localhostへ接続する場合、通常はSSLが有効になりません。
  引数に-h 127.0.0.1を追加するとSSLが有効になります。
• mysqldのerror.logにSSL error: Unable to get private key from '/etc/mysql/ssl/server-key.pem'がでていたら、秘密鍵のフォーマットが古いのが原因かも。
  秘密鍵の最初の行が-----BEGIN PRIVATE KEY-----から始まっていたら古いフォーマット(PKCS#1)です。openssl rsa -in old-server-key.pem -out new-server-key.pemでフォーマットを変換してみる。-----BEGIN RSA PRIVATE KEY-----から始まるPKCS#8の秘密鍵が出力されればOK。
• mysqldのerror.logに[Warning] SSL error: SSL_CTX_set_default_verify_paths failedがでていたら、MySQLが鍵に証明書や秘密鍵にアクセスできるか確認する。ファイルパーミッションやSELinuxの設定など。

SSL接続を強制する

-- test@127.0.0.1はSSL接続を必須にする
GRANT USAGE ON test.* TO test@127.0.0.1 IDENTIFIED BY ${password} REQUIRE SSL;

-- test@127.0.0.1はクライアント証明書を必須にする
GRANT USAGE ON test.* to test@127.0.0.1 IDENTIFIED BY ${password} REQUIRE X509;

SlaveをMasterに昇格する

データロストをすること無く、安全にslaveをmasterに昇格する方法を解説する。

この手順は、既にGTIDベースのレプリケーションがされているシステムで、masterサーバのメンテをする場面を想定している。masterで障害が起きたときのフェイルオーバーについては考慮していない。

-- 実行中のクエリを確認。
-- 重たいクエリが流れてないかなどを確認。
srv1> SHOW PROCESSLIST;

-- 念のため、slave遅延が起きてないか確認。
-- Seconds_Behind_Master が大きくなっているなら、色々まずい。
srv2> SHOW SLAVE STATUS \G

--------------------------------------------
-- ↓　これ以降はDBがダウンするので、テキパキ作業する。

-- masterへの書き込みをブロック
srv1> FLUSH TABLES WITH READ LOCK;
srv1> SET GLOBAL read_only = ON;

-- masterに追いついたか確認
-- Executed_Gtid_Set がmasterと一致するか確認
srv2> SHOW SLAVE STATUS \G

-- slaveをmasterに昇格する
-- これ以降は
--   srv1: 引退
--   srv2: master
srv2> STOP SLAVE;
srv2> RESET SLAVE ALL;
srv2> RESET MASTER;

-- * HAProxyの設定を変更し、masterサーバの向き先をsrv1からsrv2に変更。
-- * srv1のmysqlプロセスを停止し、強制的に既存のセッションを切断。

-- masterへ書き込み可能にする。
-- この作業は、全てのセッションが新しいサーバに向いた後にすること！
-- srv1へのセッションが残っている状況で書き込み可能にすると、commitしたあとにselectしても更新した内容が見えないなど、思わぬトラブルが発生する恐れがある。
srv2> SET GLOBAL read_only = OFF;

-- ↑　DBが復帰しているはず
-- アプリケーションの動作状況を確認する。

-- mysqld.cnfを修正
-- srv1とsrv2のmysqld.cnfを修正し、read_onlyの値を正しい値に書き換えておく。
-- これ忘れると、サーバが再起動したときに事故ります...

バックアップとリストア

Percona XtraBackupを使ったオンラインバックアップ取得方法とリストア方法の解説。公式の解説も読んでおくとよい。

オンラインバックアップ

--stream tarオプションを指定すると、標準出力にtarballが出力される。サイズが大きいのでgzipやlzoで圧縮すると良いだろう。バックアップの負荷が気になるなら、pvコマンドなどで転送レート制限をする。

xtrabackup --backup --stream tar -uroot |pigz --fast >mysql-backup.tar.gz

リストア

注意: もしslaveを構築するなら、xtrabackup_binlog_pos_innodbファイルを残しておく。

# MySQLの停止 & データディレクトリ削除
systemctl stop mysql
mv /var/lib/mysql{,.old}

# tarballを展開
mkdir /var/lib/mysql
tar xvf mysql-backup.tar.gz -C /var/lib/mysql

# データ修正 & MySQL起動
xtrabackup --prepare --target-dir /var/lib/mysql
chown -R mysql:mysql /var/lib/mysql
systemctl start mysql

Slave構築

バックアップをリストアして、change master to文をすれば動きます。

注意: XtraBackupで取得したバックアップデータを利用してslaveを構築する場合、決してautopositionを使ってはいけない。xtrabackup_binlog_pos_innodbに書いてあるpositionにすること。xtrabackup --prepareしてもGTIDが正しい位置に更新されない(?)ようで、autopositionを使うとレプリケーションに失敗します。

$ cat /var/lib/mysql/xtrabackup_binlog_pos_innodb
mysql-bin.025018        43974570

mysql> change master to master_host='xxx',
    master_port=3306,
    master_user='foo',
    master_password='bar',
    master_log_file='mysql-bin.025018',
    master_log_pos=43974570,
    master_ssl=1;
mysql> start slave;
mysql> show slave status;
-- masterに追いつくまで待ちましょう

Binary Log Transaction Compression

8.0.20以降で使用可能。binlogをzstdで可逆圧縮できる。一つのトランザクションで発生した複数のbinary log eventsを一つのイベント ( Transaction_payload_event) として扱い、スレーブに送信する。

注意点: masterで巨大な変更が発生するトランザクションを実行すると、スレーブが壊れるようです...

大量のデータを本番影響を出すことなく削除する

適切な粒度でパーティションを分けて、適切な頻度でパーティションをdropしていくのが理想。とはいえ、色々な制約があって、大量のデータを一気に削除していかないといけないケースはある。そのような状況下での対処法を記しておく。

巨大なデータベース/テーブル/パーティションをDROPする

下記のようなケースでは、unlink(2)の遅さに注意！

• HDDで数百GBやTB級のファイルを削除する
• HDDで数百ファイルを削除する

巨大ファイルに対するunlink(2)や、多数のファイルをunlinkするときは処理時間が長くなる。unlinkは同期的に行うため、削除処理中は他のトランザクションに影響を及ぼす可能性がある。
別プロセスでopen(2)するかhard linkを作っておき、mysqldがunlink(2)したタイミングでファイルの実態の削除が始まるのを防ぐと、意図せぬブロッキングを防げる。dropが終わったら、バックグラウンドで様子をうかがいながら少しずつ削除できる。

# お手軽なやり方。
# I/O負荷が低いDBサーバでおすすめ。
sleep 10m <large_table.idb
# 10分以内に DROP TABLE large_table;
# 10分経過したら、バックグラウンドで巨大ファイルの削除が始まる。
# sleepコマンドがしばらくDステートで固まる。削除処理の中断はできません...

# 少しずつ消していくパターン。この例では50MB/sのペースで領域を開放していく。
# ちょっと手間はかかるけど、作業の中断やI/O負荷の調整は出来る。
# I/O負荷の高いDBサーバでおすすめ。今どきのSSDが搭載されたサーバでは、通常はここまで手間をかける必要ない。
tmp_file=large_table.idb.tmp
ln large_table.idb $tmp_file
# DROP TABLE large_table;
while [ -s $tmp_file ] && truncate -s -50M $tmp_file; do sleep 1; done
rm $tmp_file

大量の行をDELETEする

本番トラフィックを捌きながら、何十億行をDELETEしたいですか？　...数週間の作業時間を確保しておくべきです。
DELETEは遅い。マジで遅い。DELETEクエリをしくじるとDBが落ちる。調子こいて勢い良くDELETEしていくと、レプリカが遅延する。

1つのトランザクションで消すのは無理です。多数のトランザクションに細切れに分割して、時間をかけて少しずつ消していくのがいいでしょう。

実際に実行するクエリのサンプルは下記の通り。下記のクエリを@offsetの値をずらしながら実行していく。

SET @offset = 1000000; -- この部分の値を増やしながら、この一連のクエリを繰り返し実行する。
SET @batch_size = 10000;

BEGIN;
DELETE /*+ INDEX(large_table PRIMARY) */  -- ポイント1: Optimizer Hintsを指定
  FROM large_table
  WHERE id BETWEEN @offset AND @offset + @batch_size AND  -- ポイント2: idで範囲指定
        created_at < '2020-01-01';  -- ポイント3: 削除条件の指定
SET @deleted_rows = ROW_COUNT();
COMMIT;

-- ポイント4: sleepする
-- DBのレプリケーション遅延に寛容なシステムであれば、レプリカの様子を見ながら動的にsleep時間を調整するアプローチが良い。
-- システムの負荷に応じて自動的に調整されるので、人間が張り付いてsleep時間を調整する必要が無くなる。
-- 
-- mysqlの拡張コマンド systemコマンドを使う。ローカルで任意のスクリプトを実行できる機能。
-- 行の分布が偏っている場合、このスクリプトは高頻度で実行される可能性がある。TTL数秒のキャッシュがあるといいだろう。
system wait_until_replication_lag_is_less_than_10_sec.sh

-- レプリケーション遅延に非寛容なシステムであれば、長めのsleepを付けていくしか無い。
-- SELECT SLEEP(1);
--
-- 行の分布が偏っている場合は、削除した行数に応じて動的にsleep時間を調整するといい。
-- SET @sleep_time = CASE
--   WHEN @deleted_rows < 100 THEN 0
--   WHEN @deleted_rows < 5000 THEN 0.5
--   ELSE 1
-- END;
-- SELECT SLEEP(@sleep_time);

• ポイント1: Optimizer Hintsを指定する。Optimizerが誤った判断をしてしまうと途端にパフォーマンス劣化し、障害を引き起こす可能性がある。
• ポイント2: PRIMARY KEYで削除範囲を限定する。DELETE対象行が非常に多い場合は、lockの獲得に時間がかかる。他のクエリが詰まってDB障害になる...。
• ポイント3: 削除対象外の行を誤って削除しないよう、削除条件を入れておく。
• ポイント4: sleepする。全速力でDELETEしていると、レプリケーション遅延を引き起こすかもしれません。