かもメモ

自分の落ちた落とし穴に何度も落ちる人のメモ帳

Mac OSX High Sierra 開発環境セットアップのメモ (4) MySQL 8.0

f:id:kikiki-kiki:20180714081756p:plain

1. MySQLのインストール

HomebrewでMySQLをインストールする

$ brew install mysql
$ mysql --version
mysql  Ver 8.0.11 for osx10.13 on x86_64 (Homebrew)

v8.011がインストールされました。

2. MySQLの環境設定

MySQLの起動

$ mysql.server start

rootユーザーの パスワードを変更する

$ mysqladmin -u root password

MySQLの初期設定

MySQLが起動している状態で下記のコマンドを実行すると対話式で初期設定が開始されます

$ mysql_secure_installation

対話式で下記の項目を設定していきます

  1. パスワードの強度チェックを有効にするか
  2. rootパスワードの変更
  3. anonymous user(匿名ユーザー)の削除
  4. リモートからrootユーザでのログインの禁止
  5. testデータベースの削除
1. パスワードの強度チェックを有効にするか
Securing the MySQL server deployment.
# rootユーザーのパスワードを入力
Enter password for user root:

VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?
# パスワードの強度をチェックするプラグインを有効にするかどうか
# 有効にする場合は y を入力
Press y|Y for Yes, any other key for No:
1-2. 有効なパスワードの強度レベルの選択(↑のプラグインを有効にした場合)
There are three levels of password validation policy:

LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary file
# LOW: 8文字以上
# MEDIUM: 8文字以上で最低 1 つの数値・小文字および大文字・特殊文字 (英数字以外) を含む
# STRONG: MEDIUMの条件に加えて4文字以上の部分文字列が辞書ファイル内の単語と一致してはならない
Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG:
2. rootパスワードの変更
# rootのパスワードが設定されていると強度が表示される
Using existing password for root.

Estimated strength of the password: <rootパスワードの強度>
# rootのパスワードを変更する場合は y を入力
Change the password for root ? ((Press y|Y for Yes, any other key for No) :
3. anonymous user(匿名ユーザー)の削除
# anonymous user(匿名ユーザー)を削除するかどうか
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.
# anonymous user(匿名ユーザー)を削除する場合は y
Remove anonymous users? (Press y|Y for Yes, any other key for No) :
4. リモートからrootユーザでのログインの禁止
# リモートからrootでのログインを許可するかどうか
Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.
# リモートからrootでのログインを禁止する場合は y
Disallow root login remotely? (Press y|Y for Yes, any other key for No) :
5. testデータベースの削除
# デフォルトで作成されている`test`データベースを削除するかどうか
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.
# 削除する場合は y
Remove test database and access to it? (Press y|Y for Yes, any other key for No) :
6. 設定の反映
# 権限テーブルをリロードして即時変更を反映するかどうか
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.
# 設定を有効にする場合は y
Reload privilege tables now? (Press y|Y for Yes, any other key for No) :

All done!

ローカル環境なので、パスワードの強度の設定とrootのパスワード変更はお好みで、
あとは全てyで良いのではないかと思います。

設定ファイルによる設定

設定ファイルの作成

$ sudo touch /etc/my.cnf

/etc/my.cnfに設定を記入して保存

[mysqld]
# データベースで利用する文字コードをUTF-8に指定
character-set-server = utf8
# パスワードの有効期限日数を無効に
default_password_lifetime = 0
# パスワードの強度をチェックを無効に (ローカル環境なので)
validate-password = OFF

default_password_lifetime

MySQL 5.7 からユーザーのパスワードの有効期限がデフォルトで360日になりました。 360日するとパスワードの変更を促されてログインできなくなります。 パスワードを定期的に変更した方が良いのかという議論は賛否両論あると思いますが、本番環境などでパスワードが有効期限を迎えてしまってサービスがストップすることは、サービス提供を第一に考えた場合によろしくないので、この有効期限の設定を 0 に設定して無効にしておきます。
出典: MySQL 5.7 をインストールしたら最初に行うセットアップ | WEB ARCH LABO

MySQLを再起動

$ mysql.server restart
MySQLコマンドのエイリアスを作成

毎回mysql.serverと書くのも面倒なので.bashrcエイリアスを作成します

# myaql
alias mysql.start='mysql.server start'
alias mysql.stop='mysql.server stop'
alias mysql.restart='mysql.server restart'

3. DBを管理するためのGUI Sequel Proのインストール

Sequel Sequel Pro
サイトからダウンロードしてインストール

Sequel での接続

f:id:kikiki-kiki:20180714064521p:plain

  1. ソケットタブを選択
  2. ユーザー名とパスワードを入力
  3. ソケットのパスを入力
mysql.sockの場所を調べる方法
$ mysql_config --socke
/tmp/mysql.sock

Sequelでlocalhostで接続できない問題にハマる

f:id:kikiki-kiki:20180714065401p:plain ターミナルからは今までどおりmysql -u root -pMySQLに接続できていたのですが、Sequelからlocalhostのrootユーザーでログインしようとすると

Authentication plugin 'caching_sha2_password' cannot be loaded: dlopen(/usr/local/lib/plugin/caching_sha2_password.so, 2): image not found

というエラーが表示されDBに接続することができませんでした。

どうやらMySQL8からユーザー認証の方法がmysql_native_passwordからcaching_sha2_passwordに変更になっているようで、Sequelのようなクライアント側でも同じ接続方式になっていないとダメなようです。
つまり、アプリ側がcaching_sha2_passwordの方式に対応する必要があると...

MySQLの認証をmysql_native_passwordに戻してしまえばcaching_sha2_passwordに対応してないアプリでも接続できるっぽい

良いのか悪いのかはちょっと判断できないのですが、アプリがまだcaching_sha2_passwordの認証に対応していないならMySQLのユーザー認証を以前のmysql_native_passwordにしてしまえば接続できるようです。

1. rootユーザーの認証方式をmysql_native_passwordに変更する

ターミナルでMySQLにログインして下記コマンドでパスワードをを再設定

mysql > ALTER USER root@localhost IDENTIFIED WITH mysql_native_password BY '<PASSWORD>';
2. 設定ファイルでデフォルトの認証プラグインを変更

/etc/my.cnfに下記を追記

[mysqld]
... [中略] ...
# デフォルトの認証プラグインをmysql_native_passwordに
default_authentication_plugin=mysql_native_password
# 3. MySQLを再起動
$ mysql.server restart

MySQLにログインして設定が反映されているか確認

mysql> select user,host,plugin from mysql.user;
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| mysql.infoschema | localhost | mysql_native_password |
| mysql.session    | localhost | mysql_native_password |
| mysql.sys        | localhost | mysql_native_password |
| root             | localhost | mysql_native_password |
+------------------+-----------+-----------------------+

mysql> show variables like 'default_authentication_plugin';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| default_authentication_plugin | mysql_native_password |
+-------------------------------+-----------------------+

再度Sequelで接続すると無事DBに接続することができました!

アプリがcaching_sha2_passwordの認証方式に対応した時はMySQLから下記コマンドでMySQLのユーザーの認証方式を戻してやれば良さそうです。(ちょっと面倒...)

mysql > ALTER USER <user@host> IDENTIFIED WITH caching_sha2_password BY '<PASSWORD>';

 
MySQLの環境設定今まで結構楽だった印象だったのですが、MySQL 8になったタイミングだったせいか結構メンドーでした。。。


[参考]

[GUI Sequel でMySQLに繋げない問題]

SQLアンチパターン

SQLアンチパターン