[MySQL][RDS] 各種設定

各種設定確認

# 時刻/Timezone確認
mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2017-04-19 17:39:19 |
+---------------------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE '%time_zone%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| system_time_zone | UTC   |
| time_zone        | UTC   |
+------------------+-------+
2 rows in set (0.01 sec)

# パラメータ確認
mysql> show variables;

# 文字コード確認
mysql> show variables like "chara%";
mysql> create database {DBNAME} default character set utf8;

# ユーザー一覧の取得
mysql> select Host, User, Password from mysql.user;

# Log設定反映の確認
mysql> show global variables like 'general_log';
mysql> show global variables like 'slow_query_log';
mysql> show global variables like 'long_query_time';

# 全クエリ確認
mysql> select * from mysql.general_log \G;

# スロークエリ確認
mysql> select * from mysql.slow_log \G;

ユーザーの追加

mysql> CREATE USER '{username}'@"{from_domain(ipaddress)}" IDENTIFIED BY "{password}";
mysql> FLUSH PRIVILEGES;

mysql> grant all on xxx.* to xxx_user@'%' identified by 'xxx_user';
Query OK, 0 rows affected (0.01 sec)

mysql> SET PASSWORD FOR 'xxx_user'@'%' = PASSWORD('pass');
Query OK, 0 rows affected (0.01 sec)

dump

# データベースを指定してdump
$ mysqldump -u {USERNAME} -p {DBNAME} > ./xxx.sql

# 全てのデータベースをdump
$ mysqldump -u {USERNAME} -p --all-databases > ./xxx.sql

# 流し込み時に既存のデータベースを削除するstateを記述しつつ外部キーを解除しつつデータベースを指定してgzip圧縮してdump
$ mysqldump -h {HOSTNAME} -u {USERNAME} -p --routines --add-drop-database --disable-keys --databases {DBNAME} | gzip > ./xxx.sql.gz

# dumpしたファイルをmysql側へ流し込む
$ mysql -u {USERNAME} -p -h {HOSTNAME} {DBNAME}< ./xxx.sql

# dump作成
$ mysqldump --databases {DBNAME} --default-character-set=utf8 --add-drop-database --user={USERNAME} --password --host={HOSTNAME} > ./xxx.sql

# アーカイブ
# 一階層上位のディレクトリで圧縮を行う。パス指定で操作すると事故を起こす。
# tarの必須オプションは事故を起こすものが多い為、注意
$ tar -cf xxx.sql.tar xxx.sql
$ gzip xxx.sql.tar

# リストア
$ mysql --default-character-set=utf8 -u {USERNAME} -p {DBNAME} < ./xxx.sql

設定ファイル(例)

/etc/my.cnf

[mysqld]
max_connections = 200
wait_timeout = 100
character-set-server=utf8

AWS RDSパラメータグループ

設定例

character-set-client-handshake: 1
skip-character-set-client-handshake: 1
character_set_client: utf8
character_set_connection: utf8
character_set_database: utf8
character_set_filesystem: utf8
character_set_results: utf8
character_set_server: utf8
time_zone: Asia/Tokyo
collation_connection: utf8_general_ci
collation_server: utf8_general_ci
max_connect_errors: 999999999
max_connections: {DBInstanceClassMemory/12582880} -> 2048 へ変更
wait_timeout: 100
general_log: 1 # 全クエリをログに残す
slow_query_log: 1 # スロークエリの保存
long_query_time: 2 # スロークエリの時間(秒)
log_output: TABLE || FILE # ログの出力先

ログ

RDSのFILEログとTABLEログのrotateは以下の通り。

FILE ログ記録が有効になっている場合、ログファイルの検査が 1 時間ごとに実行され、作成後 24 時間を超えた古いログファイルは削除されます。削除後、残りのログファイルの合計サイズが、DB インスタンスに割り当てられた領域の 2 パーセントというしきい値を超えている場合、ログファイルのサイズがしきい値以下になるまで、最も大きいログファイルから順に削除されます。

TABLE ログ記録が有効になっている場合は、テーブルログに使用されている領域が、割り当てられたストレージ領域の 20% を超えるか、すべてのログの合計サイズが 10 GB を超えると、24 時間ごとにログテーブルのローテーションが実行されます。DB インスタンスに使用されている領域が、DB インスタンスに割り当てられたストレージ領域の 90% を超えている場合は、ログのローテーションを実行するためのしきい値が小さくなります。テーブルログに使用されている領域が、割り当てられたストレージ領域の 10% を超えるか、すべてのログの合計サイズが 5 GB を超えると、ログテーブルのローテーションが実行されます。

MySQL データベースログファイル

general/slow_queryログをTABLEで有効にしている場合、以下のようにストレージを圧迫するとデフォルト設定で自動rotateされる。

General and slow query logs have been automatically rotated as they were consuming a very large percentage of the provisioned storage for the DB Instance dev-rds

The MySQL general and/or slow logs of the DB Instance: dev-rds are consuming a large amount of provisioned storage. Please refer to http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_LogAccess.Concepts.MySQL.html#USER_LogAccess.MySQL.Generallog for more details. The storage consumed by general and/or slow logs is at 20% of the provisioned storage [Total: 965.80 MB] [Components: generalLogSize:965.80 MB,binlogSize:755.75 KB,innoDbLogSize:256.00 MB]

データベースのパフォーマンスの最適化を図るのであれば、log_outputは”FILE”を選択すべきである。

slow_logの調査は、ログの出力に時間が掛かったり、HDDの容量を圧迫する為、本番ではなく開発環境にてパラメータを設定、調査する。

文字コード変更

mysql> show variables like "chara%";
+--------------------------+-------------------------------------------+
| Variable_name            | Value                                     |
+--------------------------+-------------------------------------------+
| character_set_client     | utf8                                      |
| character_set_connection | utf8                                      |
| character_set_database   | utf8                                      |
| character_set_filesystem | utf8                                      |
| character_set_results    | utf8                                      |
| character_set_server     | utf8                                      |
| character_set_system     | utf8                                      |
| character_sets_dir       | /rdsdbbin/mysql-5.5.46.R1/share/charsets/ |
+--------------------------+-------------------------------------------+

mysql> alter database {DBNAME} character set utf8;
mysql> alter table {TABLENAME} to character set utf8;

LEFT JOIN 外部結合

外部結合は、結合対象のカラム値が一致しているのに加えて、カラムの値がどちらかのテーブルにしかなかった場合でも、データとして取得する。

UPDATE xxx.yyy xy
LEFT JOIN xxx.zzz xz
ON xz.a_id = xy.a_id
SET xz.a_flg = 0,
    xz.b_flg = 1,
    xy.b_id = '111',
    xy.b_user = 'aaa'
WHERE xz.a_flg = 1
  OR xz.b_flg = 0

ベンチマーク

MySQL5.5と5.7の比較。
大幅にパフォーマンスが異なる。
https://www.mysql.com/jp/why-mysql/benchmarks/

関連記事

障害発生時確認事項/対応方法