LoginSignup
7
9

More than 5 years have passed since last update.

@ftakao2007(san Fukui)

MySQL5.7覚書

Last updated at Posted at 2016-08-06

概要

MySQL5.7を軸にした覚書です。5.7以外でも参考になることもあると思います。常時更新です。元ネタは徳島県とOracle共催の勉強会です。要点をかいつまんでまとめてます。

MySQLのリリースサイクル

  • DMR (Development Milestone Releases)
    • 開発途上版
    • 年に2-4回リリース予定
    • リリース候補版の品質になった機能のみを含む
  • RC (Rellease Candidate)
    • リリース候補版
    • RCリリース後、バグを修正してからGAがリリースされる
  • GA ( Generally Availability)
    • 製品版
  • Lab Releases
    • テスト目的で先進的、実験的な機能をリリース
    • 本番環境では使わないこと
    • Lab Releaseの機能がDMRやGAに取り込まれる保証はない

インストール

ダウンロード先

  • MySQL Developer Zone
    • Community Editionの製品をダウンロード可能
    • GAだけでなくDMR,RCもダウンロード可能
  • MySQL Labs
    • Lab Releasesをダウンロード可能

関連する単語

  • 設定ファイル
    • my.cnf
    • システム変数 (MySQL Serverのパラメータ)を設定するテキスト形式のファイル
    • 設定しなかったシステム変数にはデフォルト値が設定される
      • MySQLのバージョン(マイナーバージョンも含む)によりデフォルトが違うので要注意
  • システム変数
    • MySQLサーバの設定はシステム変数で設定する
      • my.cnfで設定
        • サーバが起動するたびに設定される恒久的な設定
      • 一時的に設定
        • SET [GLOBAL|SESSION] <variable>=<value>
          • GLOBAL : サーバ全体の設定
          • SESSION : セッション単位の設定
        • SESSIONは設定した時に反映されるが、GLOBALはその時には反映されず、MySQLに繋ぎ直した時に反映される
        • システム変数によっては動的に変更できないものも有る
    • システム変数の確認
      • SHOW [GLOBAL|SESSION] VALIABLES LIKE '%<システム変数名>%';
      • SELECT ＠＠global.<システム変数名>;
      • SELECT ＠＠global.<システム変数名>;
    • innodb関連のシステム変数
      • innodb_buffer_pool_size
        • innoDBバッファプールのサイズ
        • できるだけ大きくとる (普段頻繁にアクセスするデータが乗る程度)
      • innodb_log_buffer_size
        • InnoDBログバッファのサイズ
      • innodb_data_file_path
        • InnoDBのデータファイル名やサイズを指定
      • innodb_file_per_table
        • ONにするとテーブル単位で.ibdファイルが作成される
        • デフォルトはON
      • innodb_log_files_in_group
        • ログファイル数を指定。ほとんどデフォルトの2で問題ない
      • innodb_log_file_size
        • ログファイルのサイズを指定
  • オプションファイル
    • いろんな場所にある設定ファイル
    • 同じシステム変数が設定されていた場合は後勝ちになる
    • ファイルを読み込む順番は mysqld --verbose --help で確認できる
      • 手元の環境は「/etc/my.cnf」、「/etc/mysql/my.cnf」、 「~/.my.cnf」の順で読み込まれる設定
    • mysqlサーバ起動時に --defaults-file オプションを指定することで特定のオプションファイルのみを読み込むことが可能

インストールの流れ

  • CentOS6の想定
    • セミナーではWindowsやtarからインストールする方法の紹介もありました

rootユーザでログインするまで

### 必要なパッケージのインストール
# yum install http://dev.mysql.com/get/mysql57-community-release-el6-8.noarch.rpm
# yum install mysql-community-server

### MySQLの起動
# service mysqld start
================================================
MySQL データベースを初期化中:                              [  OK  ]
Installing validate password plugin:                       [  OK  ]
mysqld を起動中:                                           [  OK  ]
================================================

### 自動起動設定
# chkconfig mysqld on

### 初期パスワードを確認
# cat /var/log/mysqld.log
================================================
2016-08-07T23:45:21.103228Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-08-07T23:45:22.587781Z 0 [Warning] InnoDB: New log files created, LSN=45790
2016-08-07T23:45:22.697361Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2016-08-07T23:45:22.755393Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 01c3183e-5cf9-11e6-ae39-0242ac110006.
2016-08-07T23:45:22.757352Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2016-08-07T23:45:22.757723Z 1 [Note] A temporary password is generated for root@localhost: +8vnw&XRWaBf  ※ これ
2016-08-07T23:45:26.568031Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
...
================================================

### mysqlにログイン 初期パスワードを入力
# mysql -uroot -p
================================================
Enter password:
================================================

### rootのパスワード変更
### ここではこの後の操作を行うために初期パスワードと同じパスワードを設定
### このときに普通にパスワードを設定して利用した方がセキュリティー的には良い
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '+8vnw&XRWaBf';

### パスワード検証プラグインの無効化。
mysql> UNINSTALL PLUGIN validate_password;

### 再度有効にするときは以下
### mysql> INSTALL PLUGIN validate_password SONAME 'validate_password.so';

### ポリシーの確認
### パスワード検証プラグインが有効な時。無効な時は何も表示されない。
mysql> SHOW VARIABLES LIKE 'validate_password%';
================================================
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_dictionary_file    |        |
| validate_password_length             | 8      |  8文字より多い文字数
| validate_password_mixed_case_count   | 1      |  大文字小文字が一つ以上混ざっている
| validate_password_number_count       | 1      |  数字が一つ以上
| validate_password_policy             | MEDIUM |
| validate_password_special_char_count | 1      |  特殊文字が一つ以上
+--------------------------------------+--------+
================================================

### validate_password_policy
###   LOW    : 文字の長さのみ
###   MEDIUM : validate_～オプションの条件を含む
###   HIGH   : MEDIUM + 辞書に登録されている単語はNG


### SSL接続確認
mysql> \q
$ mysql -u root -p --ssl-mode=REQUIRED

mysql> SHOW VARIABLES LIKE '%ssl%';
================================================
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| have_openssl  | YES             |
| have_ssl      | YES             |
| ssl_ca        | ca.pem          |
| ssl_capath    |                 |
| ssl_cert      | server-cert.pem |
| ssl_cipher    |                 |
| ssl_crl       |                 |
| ssl_crlpath   |                 |
| ssl_key       | server-key.pem  |
+---------------+-----------------+
================================================
mysql> status;
================================================
--------------
mysql  Ver 14.14 Distrib 5.7.14, for Linux (x86_64) using  EditLine wrapper

Connection id:          12
Current database:
Current user:           root@localhost
SSL:                    Cipher in use is DHE-RSA-AES256-SHA
...

※--ssl-modeをつけずにログインした場合
...
SSL:                    Not in use
...
================================================

個別ユーザの作成

### rootユーザでログイン
$ mysql -uroot -p

### 個人ユーザの作成
### ユーザ名「mysql_user」をlocalhostに作成する
### 本番の場合は適切な権限設定とパスワードを付与する
mysql> grant all on *.* to mysql_user@'localhost' IDENTIFIED BY 'pass';

システム変数の確認

default_password_lifetime

  • 設定値の日数が経過するとそのパスワードでログインできなくなる
    • 0になっていない場合、その日数が経過後にパスワードが使えなくなる
    • 最新のものは0となっている
      • 非公式のリポジトリを使った場合は0になってない可能性がある
mysql> SHOW GLOBAL VARIABLES LIKE 'default_password_lifetime';
================================================
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| default_password_lifetime | 0     |
+---------------------------+-------+
================================================

log_timestamps

  • ログに出力されるタイムスタンプの設定
    • デフォルトはUTCになっている
    • SYSTEMを設定するとそのシステム設定のタイムスタンプが使われる
mysql> SHOW VARIABLES LIKE 'log_timestamps';
================================================
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| log_timestamps | UTC   |
+----------------+-------+
================================================

mysql> SET GLOBAL log_timestamps=SYSTEM;
mysql> SHOW GLOBAL VARIABLES LIKE 'log_timestamps';
================================================
+----------------+--------+
| Variable_name  | Value  |
+----------------+--------+
| log_timestamps | SYSTEM |
+----------------+--------+
================================================

binlog_error_action

  • サーバがバイナリログに書き込みができないときの動作
    • ABORT_SERVER
      • ロギングを停止し、サーバをシャットダウンする
    • IGNORE_ERROR
      • 何もしない
      • 可用性を優先する場合はこちらを設定
mysql> SHOW VARIABLES LIKE 'binlog_error_action';
================================================
+---------------------+--------------+
| Variable_name       | Value        |
+---------------------+--------------+
| binlog_error_action | ABORT_SERVER |
+---------------------+--------------+
================================================

mysql> SET GLOBAL binlog_error_action=IGNORE_ERROR;
mysql> SHOW GLOBAL VARIABLES LIKE 'binlog_error_action';
================================================
+---------------------+--------------+
| Variable_name       | Value        |
+---------------------+--------------+
| binlog_error_action | IGNORE_ERROR |
+---------------------+--------------+
================================================

sql_mode

  • SQL標準準拠など、SQLの振る舞いを制御するパラメータ
    • 例えば
      • 0月0日の作成を許可するか
      • 存在しないユーザに関する操作をすると自然とそのユーザが作成されるか
    • 書籍やネットで検索して実行したSQLがエラーになる場合はこの設定を確認する
      • 以下の例のように何も設定しない場合、古いSQLだと動かない可能性がある
mysql> SHOW VARIABLES LIKE 'sql_mode';
================================================
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                                     |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
================================================

mysql> SET GLOBAL sql_mode='';
mysql> SHOW GLOBAL VARIABLES LIKE 'sql_mode';
================================================
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode      |       |
+---------------+-------+
================================================

innodb_strict_mode

  • ONにすると特殊条件に合致したSQLが警告ではなくエラーになる
    • MySQLをよりSQL標準に準拠させられる
mysql> SHOW VARIABLES LIKE 'innodb_strict_mode';
================================================
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| innodb_strict_mode | ON    |
+--------------------+-------+
================================================

mysql> SET GLOBAL innodb_strict_mode='off';
mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_strict_mode';
================================================
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| innodb_strict_mode | OFF   |
+--------------------+-------+
================================================

explicit_defaults_for_timestamp

  • TIMESTAMPに関する非標準な動作の制御
    • ON : 非標準な動作を認めない
    • OFF : 非標準な動作を認める
      • 従来の動きはOFF
      • 将来的に非標準な動作は認められなくなる予定なので今からONにしておくこと推奨
mysql> SHOW VARIABLES LIKE 'explicit_defaults_for_timestamp';
================================================
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| explicit_defaults_for_timestamp | OFF   |
+---------------------------------+-------+
================================================

mysql> SET GLOBAL explicit_defaults_for_timestamp='on';
mysql> SHOW GLOBAL VARIABLES LIKE 'explicit_defaults_for_timestamp';
================================================
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| explicit_defaults_for_timestamp | ON    |
+---------------------------------+-------+
================================================

secure_file_priv

  • FILE権限を持つユーザの捜査範囲を限定できる
    • デフォルトの設定はユーザ環境により異なる
      • 手元の環境では「/var/lib/mysql-files/」
    • NULLを設定すると、以下のようなユーザが実行するSQLでOS上のファイルの読み書きを一切禁止できる
      • LOAD DATA文
      • SELECT ... INTO OUTFILE文
      • LOAD_FILE()関数
mysql> SHOW VARIABLES LIKE 'secure_file_priv';
================================================
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
================================================

ステータス変数

  • MySQLサーバの動作を監視するためにステータス変数を確認する
    • 確認方法
      • SHOW [GLOBAL|SESSION] STATUS LIKE '%<ステータス変数名>%';
    • 特定のクエリを調査する方法
      • FLUSH STATUS;<クエリ実行>; SHOW STATUS
        • ステータス変数は基本的に累計
          • 累計ではないものもある(コネクション数など)
        • FLUSH STATUSでデータをクリアしてからSHOW STATUSで確認している

各種ログファイル

  • エラーログ
    • 起動/停止やサーバサイドでのエラーに関連するログファイル
    • デフォルトで出力されている
      • 手元の環境では/var/log/mysqld.log
  • バイナリログ
    • 発行されたクエリのうち、更新系の処理内容のみ記録しているログファイル
    • バイナリ形式
      • mysqlbinlogコマンドでテキスト化可能
    • デフォルトでは出力されていない
      • システム変数log_binで設定
      • MySQL5.7からserver_idの設定が必須
  • クエリログ
    • クライアントからの接続及び実行された全てのSQLを出力
    • デフォルトは出力されていない
      • システム変数general_logで設定
    • サーバ起動中に動的に設定可能
    • テーブルへの出力も可能
  • スロークエリログ
    • 実行時間が指定した時間以上のクエリを出力
    • デフォルトでは出力されない
      • システム変数log_slow_queriesで設定
    • 関連のシステム変数
      • log_query_time : 秒単位で指定(0.5で500ms)
      • log_queries_not_use_indexes : インデックスを使っていないクエリをすべて出力
        • log_query_timeより短いクエリでも出力

アーキテクチャ基礎

  • クライアント/サーバアーキテクチャ
    • ネットワーク経由でアクセス可能
  • MySQLサーバ(mysqld)
    • データベースサーバのプログラム
    • マルチスレッドで多数のクライアント接続を同時にサポート
  • クライアントプログラム
    • mysql
      • MySQLサーバに接続してSQL等を実行するCUI
    • mysqladmin
      • MySQLサーバーの管理作業を行うCUI
    • mysqldump
      • MySQLサーバからデータを抽出できるCUI
    • mysqlpump
      • MySQLサーバからデータを抽出できるCUI
        • 前処理でデータを抽出できるなどmysqldumpより機能拡張されている
        • 機能制限も有る
        • MySQL5.7から
    • mysqlimport
      • MySQLサーバにデータをロードするCUI
        • LOAD DATA INFILEでもOS上のデータを読み込める
    • MySQL Workbench
      • 公式GUIツール
        • MySQLサーバの管理
        • MySQLデータベースを使った開発作業
        • MySQLデータベースの設計
        • 他DBからMySQLへのデータ移行
        • その他役に立つ機能
  • 通信プロトコル
    • TCP/IP
      • リモートから接続可能
    • ソケットファイル
      • Unix系OSでローカル接続するとき
      • Unix系の場合はローカル接続はTCP/IPよりソケットの方が高速
    • Windows参考
      • Windows系はローカル接続でも、ローカル接続の仕組みを使うよりTCP/IPの方が早い
  • ストレージエンジン
    • 他のRDBMSにはないMySQLだけの機能
    • ストレージエンジンの役割
      • データ保管 : どこに格納するか。データレイアウト
      • インデックス : 実装方法(Btree, B+, T, etc...)
      • メモリ利用 : データキャッシュ、バッファリング
      • トランザクション : ACID, XA, MVCC, 分離レベル
      • 同時実行性 : ロック、排他制御
    • テーブル単位でストレージエンジンを選択可能
      • 指定しない場合はシステムのデフォルトストレージエンジンが使用される
        • MySQL5.7ではInnodb
    • 確認方法
      • テーブル
        • SHOW TABLE STATUS LIKE '(テーブル名)' \G
        • SHOW CREATE TABLE (テーブル名) \G
      • メタデータ管理スキーマ
        • SELECT TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLES
    • 指定方法
      • CREATE TABLEのとき
        • CREATE TABLE t (i INT) ENGINE = InnoDB
        • デフォルトはInnoDB
      • ALTER TABLEでストレージエンジンを変更
        • ALTER TABLE T ENGINE = MEMORY;
    • Innodb
      • MySQL5.5からデフォルトのストレージエンジン
      • ACID特性に沿ったトランザクション処理、クラッシュリカバリ
      • ANSI/ISOで定義されている4つのトランザクション分離レベルを全てサポート
        • READ UNCOMMITTED
          • ロックの範囲が一番狭い
          • 実務ではまず使われない
        • READ COMMITTED
          • 確定したデータを読み込む
            • トランザクション中にデータが変更された場合そのデータを読み込む
        • REPEATABLE READ
          • MySQL5.7デフォルト
            • トランザクション中にデータが変更された場合、そのデータは読み込まない
        • SERIALIZABLE
          • ロックの範囲が一番広い
          • あるユーザが処理しているとき、他のユーザは何もできない
          • 実務ではまず使われない
      • 行レベルロック、読み取り一貫性、デッドロック検知
      • 外部キー参照整合性のサポート
      • 表の構造はClusterd Index(≒索引構成表)
        • 一般的にはBree Index
          • Primary Key Valuesにから実テーブルにアクセスする
        • Clustered Index
          • indexにアクセスすると、そこに必要な実データが入っている
          • 別途テーブルにアクセスする必要がない
          • MySQLは細かなデータに大量にアクセスする、主キーでのアクセスが得意
      • ファイル構成
        • 共有テーブルスペースファイル(ibdata1、ibdata2、...)
          • 各種メタデータ
          • ロールバックセグメントなど
          • innodb_file_per_table=OFFのとき、実データも格納される
        • InnoDBログリカバリ(ib_logfile0、ib_logfile1、...)
          • クラッシュリカバリのためのログファイル
          • 数は2個でよい
          • 更新が多発するものはログファイルを大きくする方がパフォーマンスが良くなる
        • InnoDBデータファイル(テーブル名.ibd)
          • テーブルに保持している実データが格納されているファイル
          • innodb_file_per_table=ON(デフォルト)のときテーブル単位でファイルが作成される
        • テーブル定義ファイル(テーブル名.frm)

サンプルデータベース

  • http://dev.mysql.com/doc/index-other.html
    • 実体はSQLスクリプト
    • world database
      • MySQL研修や認定試験で使われるサンプルデータベース
    • sakila database
      • world databaseよりも多くのテーブルがある
      • geometryデータ型もある(addressテーブルのlocation列)
  • インストール
    • mysql> source sakila-schema.sql
    • mysql> source sakira-data.sql

データ型

  • 文字列型
    • CHAR型:固定長文字列
      • 最大文字数255文字
      • 今は使う利点はほとんどない
        • フラグメンテーションがあってもInnoDBは自動で詰める
        • CPUがボトルネックになることはあまりない
    • VARCHAR型:可変長文字列
      • 最大文字数(65532 or max_len)文字
      • キャラクタセットにutf8mb4を利用すると1文字が最大4バイト消費するため16383文字までになる
    • TEXT型(MEDIUM TEXT, LONG TEXT):ラージオブジェクト
      • VARCHAR型より大きな文字数を保持
  • 数値型
    • INTEGER型(INT):整数
      • 格納に使うバイト数によって種類がある
      • TINY -128～127 or 0～255
      • SMALLINT -32768～32767 or 0～65535
      • MEDIUMINT -8388608～8388607 or 0～16777215
      • INT -2147483648～2147483647 or 0～4294967295
      • BIGINT -9223372036854775808～9223372036854775807 or 0～18446744073709551615
        • 19桁
      • or の後ろはUNSIGNEDキーワードをつけたとき
    • NUMERIC型、DECIMAL型:固定小数点
      • 丸め誤差が出ると困る場合
      • BIGINTより大きな値を格納する場合
      • MySQLはNUMERICとDECIMALは同一のデータ型
        • 最大65桁
    • FLOAT型、DOUBLE型:浮動小数点
      • 丸め誤差を許容できる場合
      • FLOATで39桁
      • DOUBLEで309桁
      • 広範囲な値が使える反面格納に必要なバイト数が小さい
        • FLOAT:4バイト,DOUBLE8バイト
  • 日付時刻型
    • YEAR型
      • 年
    • DATE型
      • 日付
    • TIME型
      • 時刻
      • マイクロ病まで格納可能(※ MySQL5.6以上)
    • DATETIME型
      • 日付時刻
    • TIMESTAMP型
      • 日付時刻
      • 値を明示的にしていない場合はその日の日時が格納される
  • バイナリ型
    • バイナリデータを格納
      • BINARY型 : 255Byte (固定長)
      • VARBINARY型 : 64KByte
      • TYNYBLOB型 : 255Byte
      • BLOB型 : 64KByte
      • MEDIUMBLOB型: 64KByte
      • LONGBLOG型: 64KByte
  • その他の型
    • Sptatial型
      • 空間情報(緯度、経度、多角形)を扱うデータ型
      • OGC(Open Geospatial Consortium)標準に準拠
      • GEOMETORY型、POINT型、POLYGON型など
      • いろんな関数もある
    • JSON型
      • JSONを格納できるネイティブJSONデータ型
      • いろんな関数もある

オブジェクト

  • オブジェクトの命名規則
    • データベース名やテーブル名はケースセンシティブ
      • 大文字小文字を区別する
      • ただし、WindowsはOSで大文字小文字を区別しない
        • Windowsでも大文字小文字を意識してDBを作ると他のプラットフォームに移植しやすい
  • インデックス
    • 実装ロジックはストレージエンジンに依存
      • InnoDB
        • クラスタインデックス(主キー)
        • BTREE
        • FULLTEXT (全文検索用)
        • GIS (空間)
  • ビュー
    • ビューと表の名前はユニークである必要がある
    • 更新可能/更新不可能のビューをサポート
    • DEFINER句で参照先テーブルへアクセスするユーザを定義可能
    • 制限
      • ビュー作成時にfrom句にサブクエリを利用できない
      • システム変数やユーザ変数を参照できない
      • プリペアードステートメント変数を参照できない
      • など

セキュリティ

DBのセキュリティ

  • データベースの脆弱性と対策
    • 設定の不備
      • デフォルト設定からの変更
    • 過剰な権限の付与
      • 権限設定ポリシー
    • 貧弱なアクセス制御
      • 管理者権限付与の限定
    • 貧弱な認証
      • 複雑なパスワードの強制
    • 貧弱な監査
      • コンプライアンスと監査ポリシー
    • 暗号化の不足
      • データ、バックアップ＆ネットワークの暗号化
    • 認証情報の不十分な管理
      • mysql_config_editorの利用、Key Value
    • 無防備なバックアップデータ
      • バックアップデータの暗号化
    • 監視の不備
      • ユーザとオブジェクトの監視
    • アプリケーションの脆弱性
      • データベースファイアウォール (MySQLは有償)
  • データベースへの攻撃パターン
    • SQLインジェクション
      • 対策:データベースファイアウォール、ホワイトリスト、入力バリデーション
    • バッファオーバーフロー
      • 対策:データベースソフトウェアの定期的な更新、データベースファイアウォール、ホワイトリスト、入力バリデーション
    • ブルートフォースアタック(総当たり攻撃)
      • 対策：設定回数を超えた回数ログインを試みたアカウントロック
    • ネットワーク傍受
      • 対策:全ての接続とデータ転送にSSL/TLS必須化
    • マルウェア
      • 対策:強固なアクセスコントロール、接続元IPアドレスの制御、デフォルト設定の変更
  • データベースへの攻撃による問題
    • 情報流出
      • 対策:データやネットワークの暗号化、強固なアクセス制御
    • DoS攻撃：付加の極めて高いクエリの実行
      • 対策:各種のリソース利用制限
        • 最大接続数、セッション数、タイムアウトなど
    • 権限の昇格：管理者の認証情報の不正な取得
      • 対策：認証の強化、アクセス制御、監査
    • なりすまし：他社の認証情報を不正に利用
      • 対策：強力なアカウントおよびパスワードポリシー
    • 不正な改変：データの変更、トランザクション記録の削除
      • 対策：認証の強化、監査、監視、バックアップ
  • DBAによるセキュリティ対策
    • 必要なユーザに最小のアクセス権限を付与
    • ユーザとアプリケーションが
      • 何ができるかを制限
      • どこからデータにアクセスできるかを制限
    • 何がいつ発生したかを適切に監査しておく
    • バックアップが安全でセキュアに取得されていることを確実にする
    • 攻撃経路を最小化する

ファイルシステム

  • 実行バイナリの所有者はroot
  • データディレクトリの所有者は一般ユーザ(mysqlなど)
    • 他のユーザがOS上でこのディレクトリにアクセスできないようにする
  • ログディレクトリの所有者は一般ユーザ(mysqlなど)
  • 一般ユーザ(mysqlなど)はログイン不可にしておく
  • ソケットファイルにアクセス可能にしておく
    • 全てのユーザから参照可能
  • secure_file_privを設定してFILE権限を持つユーザがファイルの入出力に使えるディレクトリを制限しておく
    • ${MYSQL_HOME}/mysql-files など
    • LOAD_FILE()関数およびLOCAD DATA、SELECT ... INTO OUTFILE文がアクセス可能
    • NULLを設定するとファイルの入出力が全て禁止される

データベース

  • MYSQLサーバはrootユーザ以外で起動
    • mysqlユーザとして起動する場合、--user=mysqlオプションをつけて起動
      • 必要以上の権限やファイルシステムアクセス許可を与えない
      • ファイル操作にはFILE権限が必要
      • [自分のコメント]このオプションをつけてサービスに問題ないかは未確認
        • 試した環境ではデフォルトでmysqldはmysqlユーザが起動していた
        • ｍｙｓｑｌｄ_safeはrootが実行している
  • デフォルトは全ネットワークインタフェースを利用
    • 特定のインタフェースを利用 : -bind-address
    • TCP/IPのアクセスを無効にする場合 : --skip-networking
      • 別ホストのAPサーバから繋げなくなるのでローカルでソケットを使うだけの時などに設定

ユーザ認証

  • 権限
    • 管理者権限
      • データベース全体に対する権限
    • データベース権限
      • スキーマやデータに関する権限
    • セッション制限およびオブジェクト権限
    • ユーザ権限に加えさらに劉度の細かい制御
      • DB作成、変更、削除
      • テーブル作成、変更、削除
      • インデックス作成、変更、削除
      • INSERT,SELECT,UPDATE,DELETEの各SQL文の実行
      • ストアドプロシージャの作成、変更、削除及び実行権限の設定
  • MySQLのユーザアカウント
    • ユーザ名とホスト名の組み合わせで構成される
      • 形式 : username@host
    • 接続ホストの指定
      • ホスト名またはIP
      • %をワイルドカードとして利用可能
        • 192.168.%
        • %.example.com
      • サブネットでの指定も可能
    • 接続ホストはDNSルックアップを回避する(推奨)
      • --skip-name-resolveを設定
        • IPアドレスや/etc/hostsで名前解決する
        • 運用中に設定すると、今まで名前解決していたサーバがアクセスできなくなる可能性があるので注意
    • ログインユーザーと現在のユーザーの違い
      • aplというユーザで192.168.33.11からアクセスする場合
        • DBにaplというユーザが作成されている場合
          • user() : apl@192.168.33.11
          • current_user() : apl@192.168.33.11
        • DBにaplというユーザは作成されていないが、"@192.168.33.% でアクセスできる設定の場合
          • user() : apl@192.168.33.11
          • current_user() : @192.168.33.%
        • MySQL5.6までは匿名ユーザがあったが、5.7でなくなった
  • 認証方式オプション
    • ビルトインの認証方式(デフォルト)
      • userテーブルにユーザアカウントとパスワードを認証
    • SHA-256認証
      • SHA-256プラグイン
    • mysql_no_login認証プラグイン
      • MySQL5.7から
      • ログインできないユーザを作成
      • ビューやストアドプロシージャ実行ユーザとして利用
  • 外部認証サポート (Enterprise)
    • LDAP,Unix,Kerberosなど
    • 有償
    • APIの口は空いているらしいので、頑張って自力で実装すれば利用できる？
  • パスワードポリシー
    • 強力になった
    • パスワード変更
      • ALTER USER user IDENTIRIED BY 'pass';
      • SET PASSWORD でも変更できる
        • MySQL5.6までと構文が変わっている
        • 一般的にSQLではALTER USERを利用する

MySQL権限管理

  • ポリシー
    • 所有者の概念はない
      • 権限が与えられているオブジェクトのみにアクセスできる
  • 権限管理テーブル
    • user
      • ユーザアカウント
      • グローバル権限
    • db
      • データベース権限
      • データベース、テーブル、オブジェクト
      • ユーザ、ホスト
    • tables_priv
      • テーブルレベルの権限
      • テーブルおよび列
    • columns_priv
      • 特定の列
    • procs_priv
      • ストアドプロシージャ
      • ファンクション
      • 各ストアドプログラム単位
    • proxies_priv
      • プロキシユーザ
      • プロキシ権限
  • 権限管理方法
    • 付与
      • GRANT文
        • GRANT SELECT,INSERT ON *.* TO 'test'@'localhost';
    • はく奪
      • REVOKE文
        • REVOKE SELECT,INSERT ON *.* TO 'test'@'localhost';
  • グローバル権限
    • SUPER
      • CHANGE MASTER, KILL PURGE MASTER LOGS, SET GLOBAL
    • SHUTDOWN
    • RELOAD
    • PROCESS
      • SHOW ENGINE INNODB STATUSの実行に必要
    • FILE
    • ALL
    • WITH GRANT OPTION
      • 自分に付与された権限を他人に与えられる
  • 権限の確認
    • GRANT USAGE ON *.* TO 'test'@'localhost';
      • SHOW GRANTS文で確認
    • SELECT * FROM INFORMATION_SCHEMA.<table>;
        • USER_PRIVILEGES : root、mysql.sysユーザに権限が付与されている
        • SCHEMA_PRIVILEGES : mysql.sysユーザに権限が付与されている
        • TABLE_PRIVILEGES : mysql.sysユーザに権限が付与されている
        • COLUMN_PRIVILEGES : Empty
      • インストール直後の状態
      • mysql.sysユーザはsysスキーマのユーザ
      • リソースの制限
        • MAX_QUERIES_PER_HOUR
          • アカウントが1時間毎に発行できるクエリーの数
        • MAX_UPDATES_PER_HOUR
          • アカウントが1時間毎に発行できる更新の数
        • MAX_CONNECTIONS_PER_HOUR
          • アカウントが1時間毎にサーバに接続できる回数
        • MAX_USER_CONNECTIONS
          • アカウントによるサーバへの同時接続の数
      • リソース制限方法
        • 制限をする
          • GRANT USAGE ON *.* TO 'test'@'localhost' WITH MAX_QUERIES_PER_HOUR 100;
        • 制限を削除
          • GRANT USAGE ON *.* TO 'test'@'localhost' WITH MAX_QUERIES_PER_HOUR 0;
          • 値を0にすればよい

        • MySQLでの暗号化

        • SSL/TLS暗号化(通信の暗号化)
        • MySQLクライアント/サーバ間
        • レプリケーションマスター/スレーブ間
        • 接続ごとの暗号化が可能
        • 適切なSSL証明書やキーファイルの指定
        • 親愛できるCAを利用可能
          • CRL(Certificate Revocation List/証明書失効リスト)サポート
          • 権限付与時にREQUIRE SSLを利用可能
            • REQUIRE NONE
            • REQUIRE SSL
            • REQUIRE X509
            • REQUIRE ISSUER 'issuer'
            • REQUIRE SUBJECT 'subject'
            • REQUIRE CIPHER 'cipher'
        • AES暗号/複合
          • コミュニティー版の暗号化はそこまで高い強度では無い
        • X.509 (公開鍵証明書の規格の１つ)
          • 要素の追加 : ユーザ/パスワード、そのほか認証

        Firewall

        • 商用版のみ
        • SQLインジェクションを防ぐ
        • ポリシーに違反するトランザクションを検知またはブロックする
        • ユーザー毎にSQL実行パターンを記録してホワイトリストを作成できる
          • 例
            • NG : select * from hoge
            • OK : select * from hoge where id = 3

        データベース監査

        • 商用版
          • adminユーザ権限でも操作できない権限でMySQLの監査ログをとる
            • 内部の人によるデータの持ち出しなどの抑止となる
            • 監査ログをとっているという事を周知することが重要

        MySQlセキュリティー強化策

        • MySQL5.7からはデフォルトで安全な状態
          • testデータベース削除
          • 無名ユーザの作成を割愛
          • rootユーザはローカルホストのみ
          • 暗号化された通信を利用可能
          • SSL証明書/キーを自動検出
          • OSのmysqlユーザとグループを作成し、ログイン抑止など設定
          • データのインポート/エクスポートの無効化が可能に
        • インストール
          • 公式のリポジトリを使う
        • 更新
          • 個別パッチは無い
            • セキュリティーに問題があった場合は新しいバージョンのものを入れることで対応
            • 順番としてはSlaveから更新する
        • サーバ設定パラメータ
          • secure_file_priv
            • データのロード専用ディレクトリを指定する
              • MySQLにOSの様々な場所にアクセスさせない
          • symbolic_links
            • Boolean - NO
              • ファイルシステムのセキュアではないディレクトリへのリダイレクトを防ぐ
          • general-log
            • Boolean - OFF
              • デバッグ時にのみON
          • log-raw
            • デフォルト値 - OFF
              • デバッグ時にのみON

        チューニング

        DBチューニングとSQLチューニング

        • DBチューニング(全体最適、パラメータチューニング)
          • サーバ全体のパフォーマンス(主にスループット)を向上させる
          • 改善しても劇的に、性能が改善することはあまりない
        • SQLチューニング(個別最適、SQLそのもののチューニング)
          • 個別の処理のパフォーマンス(主にレスポンスタイム)を向上させる
          • チューニングの仕方によっては劇的に変わる

        チューニングの流れ

        1. 問題となるSQLの特定

        • スロークエリログ
          • 設定
            • 実行時間が指定した時間以上のクエリを出力する (long-query-timeで秒単位で指定。デフォルトは出力しない)
            • indexを使っていないクエリを出力する (log-queries-not-using-indexesで指定。実行時間が短くてもindexを使ってなければ出力する)
          • 出力のタイミング
            • クエリの実行が完了してから出力。実行中のクエリは出力されていない。
          • mysqldumpslow
            • スロークエリログの集計ツール。優先してチューニングすべきクエリを特定する場合は便利
          • SHOW FULL PROCESSLIST
            • 特徴
              • 現在実行中の時間がかかっているクエリを特定可能
              • クライアントのホスト名も含まれる
              • その瞬間の情報なので、履歴情報が欲しい時は自分でスクリプトを作成して収集する必要がある

        2. 実行計画やSQL実行時の稼働統計などの確認

        • Optimizer

          • コストに基づいてSQLの実行計画を作成する
          • Optimizerの判断が必ずしも最適だとは限らない

        • 実行計画

          • SQL処理するときの内部的処理手順
            • indexスキャン、テーブルスキャン、JOINの順番、サブクエリの処理方法
              • これらはSQL文で指定していない。内部でよしなに判断して処理している。
              • あるときから急に遅くなった場合、実行計画が変更されたという可能性もある
              • 例えば、テーブルAを処理してからテーブルBを処理していたのがコスト計算の結果Optimizerにより実行計画が変更され処理順が逆になったなど
            • EXPLAINで確認できる
              • SELECT,INSERT,UPDATE,DELETE,REPLACEなどの頭にEXPLAINをつければよい -> 例) EXPLAIN SELECT * from users limit 10 \G -> 実行計画を表示するだけで、実際にINSERTなどの処理は行われない

        • コスト

          • 単一クエリに対する計算であることに注意
            • 後の処理で○○があるから実質的にこのクエリのコストはこれくらい、という考慮はしない。あくまでそのクエリ単体でコストを評価する
          • 現時点のデフォルトではディスクからの読み込みもメモリからの読み込みも同一コスト
            • 設定によってディスクとメモリでコストに差をつけることはできるが、実績はあまりない
            • ディスクのコストについてSATAかSASかSSDかも考慮されていない

        3. SQLチューニング実施

        • INDEX作成、削除

          • INDEXを使った方が良い場合
            • 表の中から少量のデータを取り出す
              • 必要な行にのみアクセスして必要なデータだけ取り出す
            • UPDATEはINDEXが使われていないとロック待ちが過剰に発生する
              • UPDATEはアクセスした行に対してロックをとるが、INDEXを使わ無い場合全行にアクセスして全行ロックになる
              • INDEXを使う事で必要な行のみロックするべき
          • INDEXを使わない方が良い場合
            • 表の中から大量のデータを取り出す
              • 例えば、データをすべて取り出す場合にindexを使うと、indexを利用する処理のオーバーヘッドが無駄に発生するだけになる
            • カーディナリティが低い列
              • 取りうる値の種類が少ない場合、カーディナリティが低いという
                • 例えば性別は「男」「女」しかなく、カーディナリティが低い
                  　

        • SHOW STATUSの確認

          • Sort_merge_passes
            • ファイルを利用したマージソートのパス数
            • これが多いとメモリ内でSort処理を仕切れず、中間結果を何回もファイルに書き出して処理しているということになる
            • 対処
              • indexを使ってクエリを処理できないか検討
              • 「sort_buffer_size」を増やす (マージソートに使えるメモリを増やす)

        • Created_tmp_disk_tables

          • 一時テーブルをディスク上に作成した回数
            • 対処
              • メモリ上で処理できるように「tmp_table_size」と「max_heap_table_size」を増やす

        スロークエリログ

        • 概要
          • 実行時間が指定した時間以上のクエリを出力
          • デフォルトでは出力されない
        • 設定
          • long-query-time
            • 実行時間が指定した時間以上のクエリを出力する
            • デフォルトは出力しない設定
            • 秒単位で指定 (0.5とすれば500ms)
            • Query Response Time Index(QRTi)の基準
              • 速い : ～100ms
              • 普通 : 100ms～400ms
              • 遅い : 400ms～
          • log-queries-not-using-indexes
            • indexを使っていないクエリを出力する
            • 実行時間がlong-query-timeより速くてもindexを使ってなければ出力する
          • log-outオプション
            • 出力先をファイルではなくテーブルに変更することが可能
        • 出力のタイミング
          • クエリの実行が完了してから出力
          • 実行中のクエリは出力されていない。

        mysqldumpslow

        スロークエリログの集計ツール。優先してチューニングすべきクエリを特定する場合は便利。ただし特定の時間帯のクエリーのみを取り出すことはできない。

        ### 実行例
$ mysqldumpslow -s at db_server01-slow.log
================================================
Reading mysql slow query log from db_server01-slow.log
Count: 77  Time=306.23s (23579s)  Lock=0.00s (0s)  Rows=5622.4 (432926), mysql_user[mysql_user]@2hosts
  SELECT /*!N SQL_NO_CACHE */ * FROM `bbs_db_files`

Count: 78  Time=79.83s (6226s)  Lock=0.00s (0s)  Rows=3302.0 (257555), mysql_user[mysql_user]@2hosts
  SELECT /*!N SQL_NO_CACHE */ * FROM `dlibrary_db_files`

Count: 77  Time=11.45s (881s)  Lock=0.00s (0s)  Rows=1253969.7 (96555665), mysql_user[mysql_user]@2hosts
  SELECT /*!N SQL_NO_CACHE */ * FROM `sys_ldap_temps`

Count: 1  Time=5.59s (5s)  Lock=0.05s (0s)  Rows=1.0 (1), mysql_user[mysql_user]@localhost
  select created_at from  mysql_db.sys_ldap_temps order by created_at limit N

Count: 1  Time=12.44s (12s)  Lock=0.00s (0s)  Rows=0.0 (0), mysql_user[mysql_user]@[192.168.33.11]
  FLUSH TABLES

Count: 1  Time=5.08s (5s)  Lock=0.00s (0s)  Rows=1.0 (1), mysql_user[mysql_user]@localhost
  select created_at from  mysql_db.hoge_docs order by created_at limit N
================================================
        • Count: 出現回数
        • Time: 平均実行時間。カッコの中は、合計実行時間
        • Lock: テーブルをロックした平均時間。カッコの中は合計ロック時間
        • Rows: 読取り/更新したレコード数の平均。カッコの中は合計
        • mysql_user[mysql_user]@[192.168.33.11] アクセス元
          • Linuxのユーザではなく、mysqlログインユーザ

        バックアップ

        • 手法
          • ホット(オンライン)バックアップ
            • CUIツール
              • mysqldump
              • mysqlpump
                • 並列処理
                • インデックスをデータをロード時に作成 (一般的にデータ量が多くなるほどmysqldumpと比較して速い)
                • 処理状況のレポート
                • ユーザのバックアップ
                • innodbが前提
            • ロックを利用して
              • 更新処理をブロックする(DBは起動している)
              • mysqldumpで--lock-all-tablesオプションをつける
            • トランザクションを利用して
              • 更新処理をブロックしない
              • mysqldumpで--single-transaction
              • DBの一貫性を担保できる
              • オプションをつけないとテーブルごとは問題ないが、全体的に一貫性のないものになる
          • コールド(オフライン)バックアップ
            • DBを停止して/var/lib/mysqlを丸コピー
            • 場所は SHOW GLOBAL VARIABLES like 'datadir';
          • 増分/差分バックアップ
            • バイナリログを利用する
          • バイナリログ
            • デフォルトでは出力されない
              • log_binとserver_idを設定する必要がある
            • トランザクションのコミット時に同期的に記録
              • MySQL5.7からsync_binlog=1がデフォルト
            • システム変数 log_bin[=file_name] を指定して出力する
              • データディレクトリと別のディスクに出力することを推奨
              • MySQL5.7からlog_bin設定時にはserver_idの設定も必須
            • MySQL5.7はbinlog_format=ROWがデフォルトになった
              • 前はbinlog_format=statementだった
                • 更新処理実行したSQL文そのものをバイナリログに格納する
                • バックアップ・リカバリを考える上では問題にならない
                • レプリケーションを使う場合は同じSQL文を実行しても結果が異なる場合はちょっとまずかった
                • 例えばnow()だと実行した時間で結果が違う
                • ROWだとその時のデータがそのまま入る
                • →バイナリログが想定外に大きくなる可能性がある(100万行を2倍するなどのSQL)
                • MIXEDはいいとこ取り
              • binlog_row_image=minimalを設定
                • 必要最小限のデータを保存するのでバイナリログが小さくなる
                • ただし、マスターとスレーブのテーブルが同一定義が同一でないといけない
                • →普通は同一なのでよっぽどでないと気にする必要はない
            • 管理
              • SHOW MASTER STATUS
                • 現在使用中のバイナリログファイル名とポジションを確認
              • SHOW MASTER LOGS
                • コマンドで全てのバイナリログファイル名を列挙
              • FLUSH [BINARY] LOGS
                • サーバの再起動でログファイルのローテーション
              • PURGE MASTER LOGS TO '(バイナリログ)'
                • 特定の時点までのバイナリログを削除
              • RESET MASTER
                • 全てのバイナリログを削除
              • SHOW BINLOG EVENTS
                • BINLOG関連のイベントを表示
            • mysqlbinlog
              • MySQL5.6からリモートバックアップ可能
          • リストアとリカバリ
            • リカバリ
              • 特定の日時の状態にデータを復旧すること
              • mysqldumpで取得したバックアップをリストアした後、バイナリログの内容をDBに適用してリカバリする

        メモ

        Relationship

        • Identifying Relationship
          • ビルの中の部屋
            • ビルは単独で存在できるが部屋はビルなしには存在できない。ビルに依存している
        • Non-Identifying Relationship
          • DVDを借りる人
            • ある一つのDVDを借りる人は複数人いるが、その人は別にDVDが無くても存在している
        • 参考

        カラムに設定できるオプション

        雑多メモ

        • mysqld_safeの設定はなくなった？
          • systemdを使っている場合は設定不要？逆に使っていなければ必要？
        • パスワードが複雑なものを設定しないといけない
          • 単純なパスワードが通らなくなった
          • 特に最初rootパスワードを登録するときはvalidate-password = offにするとDB初期化できずに落ちる
        • key_buffer、innodb_file_io_threads、innodb_additional_mem_pool_sizeが廃止？
          • ただしmyisamchk内ではkey_bufferは値があってもエラーにならない
        • DBの初期化はしなくても初回のmysql起動時に初期化される
        • innodb_file_per_table
          • 「ON」のときにテーブル単位でファイルが作成される
            • .frm : テーブル定義
            • .ibd : innodbの実データ
          • 5.6以前はテーブル単位で作成されなかった
            • 「ibdata1」というようなファイルに入っていた
              • 一回大きくなるとファイルサイズを小さくできない
              • ⇒ 一時的に大きなデータを入れて肥大化した時、消しても小さくすることができない

        参考

7
9
1

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
7
9