MySQLに127.0.0.1で接続するとAccess denied for user 'root'@'localhost'となる時の対応

ホストをlocalhostで指定すると接続できるが、　127.0.0.1で指定すると接続できないときの解決方法です。
なお、コマンドの変数は以下のことを意味しています。

• $DB_USER: ユーザー名
• $DB_PW: パスワード
• $DB_NAME: DB名

現状の確認

以下のような状態になっているとします。

127.0.0.1だと接続できません

mysql -u$DB_USER -p$DB_PW -D$DB_NAME -h 127.0.0.1

ERROR 1698 (28000): Access denied for user 'root'@'localhost'

localhostと指定すると接続できます。

mysql -u$DB_USER -p$DB_PW -Disubata -h localhost

mysql>

mysqlのユーザーにはhost=127.0.0.1のユーザーがいません

mysql> SELECT user, host FROM mysql.user ORDER BY user, host;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| debian-sys-maint | localhost |
| isucon           | %         |
| isucon           | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+

解決方法

今回は2つの方法を紹介します。

方法1. 『127.0.0.1で接続できるようにする』

DNSを無効にすることでIPアドレスで接続できるようにします。そのためにはskip-name-resolveをtrueに設定します。

/etc/mysql/my.cnf

[mysqld]
skip-name-resolve

設定が完了したらmysqlを再起動して設定を反映させます

sudo systemctl restart mysql

方法2. 『127.0.0.1のmysql.userを作成する』

mysqlでは$DB_USER@localhostと　$DB_USER@127.0.0.1は別ユーザーとして識別されます。
そのため、127.0.0.1で接続できるようにユーザー権限の付与をします。

# 権限付与
mysql > GRANT ALL ON *.* TO $DB_USER@'127.0.0.1' IDENTIFIED BY 'root' WITH GRANT OPTION;

# 確認
mysql > SHOW GRANTS for $DB_USER@'127.0.0.1';
+---------------------------------------------------------------------+
| Grants for $DB_USER@127.0.0.1                                       |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO $DB_USER@'127.0.0.1'WITH GRANT OPTION|
+---------------------------------------------------------------------+

# 有効化
mysql > FLUSH PRIVILEGES;

さいごに

ツイッター(@nishina555)やってます。フォローしてもらえるとうれしいです！