localhostで動作するMySQLにrootでtcp接続する
開発環境やCIではrootでは繋ぎに行く、セキュリティ的にはアレでも簡易的に済ませることもありますが、ここでHOST指定に 127.0.0.1 を指定するとUNIXドメインソケットではなくTCPでの接続]となります。 shimizukawa.iconはmysqlの以前の挙動として、 -h 127.0.0.1 を指定するとmysql内のユーザー認証が root@localhost から root@127.0.0.1 に変わり、そのようなユーザーを登録していないために認証エラーになる、と思っていましたが、間違いでした。
前提
結論
rootをパスワード認証に変えるには sudo mysql で接続して ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY 'password'; を実行します。
mysqlクライアントの表示からはUNIXドメインソケットかTCPか見分けられない
エラーを見るためにパスワードをわざと間違えて接続します。
-hオプションなしだとsocket通信です。
code:terminal
$ mysql -u root --password=x
mysql: Warning Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
--protocol=TCP で強制的にTCP通信にします
code:terminal
$ mysql -u root --password=x --protocol=TCP
mysql: Warning Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
-h 127.0.0.1 を指定するとTCP通信です。mysqlクライアントの接続はlocalhostと127.0.0.1で変わります。
code:terminal
$ mysql -u root --password=x -h 127.0.0.1
mysql: Warning Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
見た目全部いっしょじゃん！
これだと、 root@localhost ユーザーで認証しているようにしか見えないですが、実際には root@127.0.0.1 ユーザーが存在しないためエラーになっているのかもしれず、判断が付かなかった。
OSユーザー認証をネイティブ認証に変える
Ubuntu-18.04 で確認したところ、OSユーザーでの認証になっていた（以下の auth_socket というやつ） code:terminal
$ sudo mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.39-0ubuntu0.18.04.2 (Ubuntu)
...
mysql> select Host,User,plugin from mysql.user;
+-----------+------------------+-----------------------+
| Host | User | plugin |
+-----------+------------------+-----------------------+
| localhost | root | auth_socket |
| localhost | mysql.session | mysql_native_password |
| localhost | mysql.sys | mysql_native_password |
| localhost | debian-sys-maint | mysql_native_password |
+-----------+------------------+-----------------------+
4 rows in set (0.00 sec)
auth_socketの詳細
The server-side auth_socket authentication plugin authenticates clients that connect from the local host through the Unix socket file.
Unix ソケットファイル経由でローカルホストから接続するクライアントを認証
認証プラグインの変更
ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY 'password';
mysql_native_password に認証方式を変えれば、OSユーザーと合わせなくてもパスワード認証できるようになる
code:termina
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY 'root';
Query OK, 0 rows affected (0.00 sec)
mysql> select Host,User,plugin from mysql.user;
+-----------+------------------+-----------------------+
| Host | User | plugin |
+-----------+------------------+-----------------------+
| localhost | root | mysql_native_password |
| localhost | mysql.session | mysql_native_password |
| localhost | mysql.sys | mysql_native_password |
| localhost | debian-sys-maint | mysql_native_password |
+-----------+------------------+-----------------------+
4 rows in set (0.00 sec)
mysql_native_password の詳細
これで mysql -u root --password=root で接続できるようになる
しかしまだ mysql -u root -h 127.0.0.1 --password=root では接続できない..と思ったら接続できてしまった
root@localhost じゃなく root@127.0.0.1 だからダメだろう、と思ったら大丈夫だった..
DockerのMySQLはどうなってる？
code:sh
CREATE USER 'root'@'${MYSQL_ROOT_HOST}' IDENTIFIED BY '${MYSQL_ROOT_PASSWORD}' ;
GRANT ALL ON *.* TO 'root'@'${MYSQL_ROOT_HOST}' WITH GRANT OPTION ;
code:sh
${passwordSet}
GRANT ALL ON *.* TO 'root'@'localhost' WITH GRANT OPTION ;
FLUSH PRIVILEGES ;
${rootCreate}
参考
Ubuntu 18.04 で MySQL サーバ（5.7）をインストールする際に実行するスクリプトの中で
ALTER USER 'root'@'localhost' IDENTIFIED WITH 'auth_socket';
を実行するように指定されています。
解決方法として「ALTER USER ... IDENTIFIED WITH」 を紹介している
Ubuntu-18.04 からmysqlインストール時のrootユーザーのパスワード設定がなくなり、auth_socket認証になったよ その他、rootのパスワード設定方法、リセット方法などなど紹介してくれている
IPアドレス指定、 --protocol による指定、my.cnf による指定、を紹介
IPアドレス指定、 --protocol による指定、my.cnf による指定、を紹介