LoginSignup
6
13

More than 1 year has passed since last update.

@yuuki

MySQLチートシート

Last updated at Posted at 2015-06-24

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

ユーザ管理

該当するドキュメント : 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していると、レプリケーション遅延を引き起こすかもしれません。
6
13
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
6
13