皆さんはMySQLのパラメータや変数をどのように設定しているでしょうか? MySQLやデータベースを運用する専用のチームの人たちがいて、
検証環境
今回は、apt install wget
を行うかして進めてください。
MySQLTunerとは?
MySQLTunerはperlで作られた、
MySQLTunerは、
インストール方法
MySQLTunerは、
$ docker-compose up -d $ docker-compose exec db bash # cd /tmp # wget http://mysqltuner.pl/ -O mysqltuner.pl # wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/basic_passwords.txt -O basic_passwords.txt # passwordチェックをする場合に必要なファイルよくあるパスワード一覧が載ってます。 # wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/vulnerabilities.csv -O vulnerabilities.csv # MySQLの脆弱性情報がまとまっています。
これで準備完了です。非常に簡単でいいですね。
動かしてみる
それではMySQLTunerを動かしてみましょう。perl mysqltuner.
とコマンドを実行すると、
今回はローカルホストに対して実行していますが、
# perl mysqltuner.pl >> MySQLTuner 1.7.20 - Major Hayden <major@mhtx.net> >> Bug reports, feature requests, and downloads at http://mysqltuner.pl/ >> Run with '--help' for additional options and output filtering [--] Skipped version check for MySQLTuner script Please enter your MySQL administrative login: root Please enter your MySQL administrative password:
以下のようにチェックが進んでいることがわかります。
[OK] Currently running supported MySQL version 8.0.22 [OK] Operating on 64-bit architecture -------- Log file Recommendations ------------------------------------------------------------------ log_error is set to stderr MT can't read stderr -------- Storage Engine Statistics ----------------------------------------------------------------- [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA [--] Data in InnoDB tables: 48.0K (Tables: 3) [OK] Total fragmented tables: 0 ~省略~
完了すると、
-------- Recommendations --------------------------------------------------------------------------- General recommendations: MySQL was started within the last 24 hours - recommendations may be inaccurate Reduce your overall MySQL memory footprint for system stability Dedicate this server to your database for highest performance. Reduce or eliminate unclosed connections and network issues Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU Variables to adjust: *** MySQL's maximum memory usage is dangerously high *** *** Add RAM before increasing MySQL buffer variables *** innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
General recommendations: には一般的な推奨事項が書かれています。簡単に意訳していくと、
- MySQLが24時間以内に立ち上がったもので、
正確性に欠ける可能性があります。 - システムの安定性のためにMySQLの全体的なメモリー使用量を減らすべきです。
- パフォーマンスを最大化したい場合はサーバーをデータベースに専念させたほうが良いです。
- MySQLへの接続の失敗や、
ネットワーク上の問題を減らしましょう - MySQL 8.
0のinnnodb-redo-logのドキュメント を読んでからinnodb_log_ file_ sizeとinnodb_ log_ files_ in_ groupを変更しましょう
となっています。納得が行くものがあるのではないかなと思います。
続いて、
- MySQLの最大メモリー使用量が危険なほど高い
- MySQLのバッファー変数を増やす前にまずはメモリーを増やしてください
- InnnoDBの全体のログファイルの大きさをbuffer poolサイズの25%にしたほうが良いので、
もしできるならinnodb_ log_ file_ sizeのサイズを16Mにしてください。
ここでは簡単に変更できるinnodb_
docker-compose.
変更前は以下のようになっていました。
command: mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci --secure-file-priv="/docker-entrypoint-initdb.d"
これを以下のように変更して再起動します。
command: mysqld --innodb-log-file-size=16M --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci --secure-file-priv="/docker-entrypoint-initdb.d"
もう一度wgetでMySQLTunerを取得して、
-------- Recommendations --------------------------------------------------------------------------- General recommendations: MySQL was started within the last 24 hours - recommendations may be inaccurate Reduce your overall MySQL memory footprint for system stability Dedicate this server to your database for highest performance. Reduce or eliminate unclosed connections and network issues
このように、
今回説明した項目以外にも、
脆弱性情報を確認する
インストール時に一緒にダウンロードしたファイルを使用して、
# perl mysqltuner.pl --cvefile=vulnerabilities.csv --user=root --password=password ~省略~ -------- CVE Security Recommendations -------------------------------------------------------------- [OK] NO SECURITY CVE FOUND FOR YOUR VERSION ~省略~
5.
~省略~ -------- CVE Security Recommendations -------------------------------------------------------------- CVE-2016-3492(<= 5.7.14) : "Unspecified vulnerability in Oracle MySQL 5.5.51 and earlier ~省略~ CVE-2018-3077(<= 5.7.22) : "Vulnerability in the MySQL Server component of Oracle MySQL (subcomponent: Server: DDL). Supported versions that are affected are 5.7.22 and prior and 8.0.11 and prior. Easily exploitable vulnerability allows high privileged attacker with network access via multiple protocols to compromise MySQL Server. Successful attacks of this vulnerability can result in unauthorized ability to cause a hang or frequently repeatable crash (complete DOS) of MySQL Server. CVSS 3.0 Base Score 4.9 (Availability impacts). CVSS Vector: (CVSS:3.0/AV:N/AC:L/PR:H/UI:N/S:U/C:N/I:N/A:H)." 165 CVE(s) found for your MySQL release. ~省略~
続いてせっかくなので、
General recommendations: Restrict Host for 'root'@% to root@SpecificDNSorIp UPDATE mysql.user SET host ='SpecificDNSorIp' WHERE user='root' AND host ='%'; FLUSH PRIVILEGES; Set up a Secure Password for root@% user: SET PASSWORD FOR 'root'@'%' = PASSWORD('secure_password'); 1 user(s) used basic or weak password from basic dictionary. 165 CVE(s) found for your MySQL release. Consider upgrading your version ! MySQL was started within the last 24 hours - recommendations may be inaccurate Reduce or eliminate unclosed connections and network issues Variables to adjust: query_cache_size (=0) query_cache_type (=0) query_cache_limit (> 1M, or use smaller result sets)
MySQL 8.
- rootユーザで接続できるホスト名を制限しなさい。
- UPDATE mysql.
user SET host ='SpecificDNSorIp' WHERE user='root' AND host ='%'; FLUSH PRIVILEGES; (制限する方法のSQL) - root@% ユーザに、
SET PASSWORD FOR 'root'@'%' = PASSWORD('secure_ password')でセキュアなパスワードを設定しなさい - 一人のユーザが、
基本的な辞書に載っているような脆弱なパスワードを使用しています。 - 165個のCVEが、
あなたの使用しているMySQLのバージョンで見つかりました。アップグレードの検討をしてください!
以上のようになります。ということで、
まとめ
今回は、
ただし注意をすることとしては、