mysql -u rootがエラーとなる（ Authentication plugin 'caching_sha2_password' cannot be loaded: ）

Question

前提・実現したいこと

mysql -u rootを行うとエラーメッセージが出てログインができない

発生している問題・エラーメッセージ

ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded: dlopen(/usr/local/Cellar/mysql@5.6/5.6.43/lib/plugin/caching_sha2_password.so, 2): image not found

mysql --version

mysql  Ver 14.14 Distrib 5.6.43, for osx10.14 (x86_64) using  EditLine wrapper

試したこと

PATHの設定は行いました。

echo 'export PATH="/usr/local/opt/mysql@5.6/bin:$PATH"' >> ~/.bash_profile

my.cnfを削除するため下記をコマンドを実行後に再度バージョン指定(brew search mysql@5.6)でインストール後、brew services start mysql@5.6を実行してからmysql -u rootを行っても表題の結果です。

###my.cnfを削除するためのコマンド（rmの前にsudoが付いてましたが外して実行してます。）

 rm -rf /usr/local/Cellar/mysql*
 rm -rf /usr/local/bin/mysql*
 rm -rf /usr/local/var/mysql*
 rm -rf /usr/local/etc/my.cnf
 rm -rf /usr/local/share/mysql*
 rm -rf /usr/local/opt/mysql*
 rm -rf /etc/my.cnf

ls -l /usr/local/Cellar/mysql*

total 0
drwxr-xr-x  17 neko  staff  544  9 17 20:10 5.6.43

echo $PATH

/usr/local/opt/mysql@5.6/bin:/usr/local/opt/mysql@5.6/bin:/usr/local/opt/mysql@5.6/bin:/usr/local/opt/mysql@5.6/bin:/usr/local/bin:/usr/bin:/bin:/usr/sbin:/sbin

cat /etc/mysql/my.cnf

cat: /etc/mysql/my.cnf: No such file or directory

which mysql

/usr/local/opt/mysql@5.6/bin/mysql

mysql --help | grep my.cnf

order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/local/etc/my.cnf ~/.my.cnf 

mysqlアンインストールコマンド（上から順に実行）

$ brew uninstall mysql@5.6
　
$ rm -rf /usr/local/var/mysql
$ rm -rf /usr/local/var/mysql56/
　
$ brew install mysql@5.6
　
$ echo 'export PATH="/usr/local/opt/mysql@5.6/bin:$PATH"' >> ~/.bash_profile
$ . ~/.bash_profile
　
$ mysql.server start
=>Starting MySQL
... ERROR! The server quit without updating PID file (/usr/local/var/mysql/nekonoMacBook-Air.local.pid).
$ mysql.server stop
=>ERROR! MySQL server PID file could not be found!
 
$ brew services start mysql@5.6
 
$ launchctl list | grep mysql
42424	0	homebrew.mxcl.mysql@5.6

mysql.server start

sudo rm -rf /usr/local/var/mysqlを行ってアンインストール＝＞インストールしても下記エラー

Starting MySQL
... ERROR! The server quit without updating PID file (/usr/local/var/mysql/nekonoMacBook-Air.local.pid).

ls -l /usr/local/var/mysql/nekonoMacBook-Air.local.pid

ls: /usr/local/var/mysql/nekonoMacBook-Air.local.pid: No such file or directory

ls -l /usr/local/var/

total 0
drwxrwxr-x   4 neko  admin  128  9 18 05:03 homebrew
drwxr-xr-x  10 neko  admin  320  9 19 19:22 mysql

###my.cnf

$ cat /usr/local/etc/my.cnf
# Default Homebrew MySQL server config
[mysqld]
# Only allow connections from localhost
bind-address = 127.0.0.1

###brew services start mysql@5.6で起動

Service `mysql@5.6` already started, use `brew services restart mysql@5.6` to restart.

###brew services restart mysql@5.6

Stopping `mysql@5.6`... (might take a while)
==> Successfully stopped `mysql@5.6` (label: homebrew.mxcl.mysql@5.6)
==> Successfully started `mysql@5.6` (label: homebrew.mxcl.mysql@5.6)

find / -name "my.cnf" -ls 2>/dev/null

3976441        8 -rw-r--r--    1 neko             admin                 113  9 19 20:58 /usr/local/etc/my.cnf
3976443        8 -rw-r--r--    1 neko             staff                 943  9 19 20:58 /usr/local/Cellar/mysql@5.6/5.6.43/my.cnf
3976187        8 -rw-r--r--    1 neko             staff                 113 12 21  2018 /usr/local/Cellar/mysql@5.6/5.6.43/.bottle/etc/my.cnf
2607770        8 -rw-r--r--    1 neko             staff                 202  7 21 09:04 /Users/neko/lara/laradock/mariadb/my.cnf
2607814        8 -rw-r--r--    1 neko             staff                 266  7 21 09:04 /Users/neko/lara/laradock/percona/my.cnf
2607792        8 -rw-r--r--    1 neko             staff                 272  7 21 09:04 /Users/neko/lara/laradock/mysql/my.cnf
3673914       16 -rw-rw-r--    1 root             admin                5405  9 11 21:26 /Applications/XAMPP/xamppfiles/etc/my.cnf

上記のmy.cnf 中身をすべて確認

nekonoMacBook-Air:~ neko$ cat /usr/local/etc/my.cnf | grep caching_sha2_password
nekonoMacBook-Air:~ neko$ cat /usr/local/Cellar/mysql@5.6/5.6.43/my.cnf| grep caching_sha2_password
nekonoMacBook-Air:~ neko$ cat /usr/local/Cellar/mysql@5.6/5.6.43/my.cnf | grep caching_sha2_password
nekonoMacBook-Air:~ neko$ cat /usr/local/Cellar/mysql@5.6/5.6.43/.bottle/etc/my.cnf | grep caching_sha2_password
nekonoMacBook-Air:~ neko$ cat /Users/neko/lara/laradock/mariadb/my.cnf | grep caching_sha2_password
nekonoMacBook-Air:~ neko$ cat /Users/neko/lara/laradock/percona/my.cnf | grep caching_sha2_password
nekonoMacBook-Air:~ neko$ cat /Applications/XAMPP/xamppfiles/etc/my.cnf | grep caching_sha2_password

補足情報（FW/ツールのバージョンなど）

OS:mac

同じ症状の方をtetratailで見つけましたが解決できず。

。service mysqld start

。service mysqld stop

。service mysqld restart
これらを行っても-bash: service: command not foundとエラーとなる。

Answer 1

原因は分かりませんが、mac自体を再起動したらmysql -urootで無事に入ることが出来ました。

Comments

[nandymak]

MySQL8.0がずっと起動していたのでしょうかね。
取り敢えず、過去にインストールした残骸的なものは綺麗にした方が良いでしょうね。

[Ms.yy]

一応アンインストール後に下記削除コマンドを実行しました。
$ rm -rf /usr/local/var/mysql/

$ sudo rm /usr/local/mysql
$ sudo rm -rf /usr/local/var/mysql
$ sudo rm -rf /usr/local/mysql*
$ sudo rm ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
$ sudo rm -rf /Library/StartupItems/MySQLCOM
$ sudo rm -rf /Library/PreferencePanes/My*

$ rm -rf ~/Library/PreferencePanes/My*
$ sudo rm -rf /Library/Receipts/mysql*
$ sudo rm -rf /Library/Receipts/MySQL*
$ sudo rm -rf /private/var/db/receipts/*mysql*

[Ms.yy]

ただ、以前からこれは何回も行っていたので、再起動で治ったのは腑に落ちない結果となりました。

[nandymak]

＞brew cleanup
とかもしておいた方が良いですね。

※コマンドは内容を理解して実行してくださいね。判らなければドキュメント等を調べるなりが必要です。

[Ms.yy]

nandymakさん数日間本当に助かりました。
はい、mysql？brew?コマンド確認してみます！

Answer 2

「find / -name "my.cnf" -ls 2>/dev/null」とコマンドを打つと、my.cnfと言うファイルを探しに行きます(時間が掛かります)。

nandymak$ find / -name "my.cnf" -ls 2>/dev/null
3586232        8 -rw-r--r--    1 nandymak         admin                 212  9 19 19:43 /usr/local/etc/my.cnf
7830222        8 -rw-r--r--    1 nandymak         staff                 145  6 27 04:02 /usr/local/Cellar/mysql/8.0.17_1/.bottle/etc/my.cnf
7819521        8 -rw-r--r--    1 nandymak         staff                 113 12 21  2018 /usr/local/Cellar/mysql@5.6/5.6.43/.bottle/etc/my.cnf
nandymak$

そこで見つかったmy.cnfの中身をすべて確認してください。上記の例であれば

$ cat /usr/local/etc/my.cnf | grep caching_sha2_password
$ cat /usr/local/Cellar/mysql/8.0.17_1/.bottle/etc/my.cnf | grep caching_sha2_password
$ cat /usr/local/Cellar/mysql@5.6/5.6.43/.bottle/etc/my.cnf | grep caching_sha2_password

上記の結果、以下の文字列が見つかったら、修正します。

default_authentication_plugin=caching_sha2_password

↓以下のように修正する。「=」の後ろです。

default_authentication_plugin=mysql_native_password

MySQLを再起動する。

$ brew services restart mysql@5.6
$ mysql -uroot

Comments

[Ms.yy]

了解しました。
試させていただきますね！

[Ms.yy]

ターミナルでmy.cnfの中身をすべて確認したところただ段が１つ下がるだけで「上記の結果、以下の文字列が見つかったら、修正します。」の部分に記載されているコードは一つも見つかりませんでした。

「find / -name "my.cnf" -ls 2>/dev/null」行うところから内容を追記させて頂きましたので、よろしくお願い致します

[nandymak]

/Users/neko/lara/laradock/mysql/my.cnf
と言うファイルがありますが、laradockは何ですか？
Docker関連でしょうか？
現状使っているのでしょうか？

※関係ないと思いますが。。。

[nandymak]

そろそろ手詰まりなので、お近くにMacに詳しい方はいませんか？
実機を見ながら一旦綺麗な状態にした方が良いと思います。

お役に立てずすいません。

[Ms.yy]

はい、Docker関連です。
Dockerは以前laravel使ってた際に導入しておりました。
今は使っていないので消しても構いません。

nandymak様、この度は数日間初心者にもわかりやすくて丁寧に回答して頂きありがとうございます。＾＾

プログラミングしてる方は近くにいないので
ネットのサービスを駆使してでmysqlのこちらの問題がわかる方探してみたいと思います。

[nandymak]

勉強会などに参加して聞ける人を捜したらよいかも。
https://connpass.com/
などを見てみればどうでしょう。

[Ms.yy]

勉強会もありですね！
前々から気になってました^^;

Answer 3

ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded: dlopen(/usr/local/Cellar/mysql@5.6/5.6.43/lib/plugin/caching_sha2_password.so, 2): image not found

「Authentication plugin 'caching_sha2_password'」はMySQL8.0から導入されたようですが、以前MySQL8.0をインストールされたことはありますか？
その時にmy.cnfに何か書き込まれたおかもしれません。

/etc/mysql/my.cnfを確認してみてください。
内容が理解できるのであれば修正すればいいですが、自信がなければmy.cnfを削除またはリネームし、MySQLはアンインストールして、対象のバージョンを再インストールする方がよいと思います。

大丈夫だと思いますが、

echo 'export PATH="/usr/local/opt/mysql@5.6/bin:$PATH"' >> ~/.bash_profil

最後の「.bash_profil」に「e」が欠けているのはコピペミスですよね。

コマンドにPATHが通っているかは以下のコマンドで確認してください。

$ which mysql

思い込みでセットされていないことが多々あります。
また、どのディレクトリを順番に見に行くかは以下のコマンドで確認できます。

$ echo $PATH

---

2019/09/19 20:00追記

MySQL@5.6をHomebrewでインストールしてみました。

現状の確認

nandymak:~ nandymak$ brew uninstall mariadb
Error: No such keg: /usr/local/Cellar/mariadb
nandymak:~ nandymak$ brew list
cmake		graphviz	libgcrypt	libxmlsec1	p11-kit
fontconfig	gts		libgpg-error	mpfr		pcre
freetype	heroku		libidn2		netpbm		python
gcc		heroku-node	libmpc		nettle		r
gd		icu4c		libpng		node		readline
gdbm		isl		libtasn1	nodebrew	sqlite
gettext		jasper		libtiff		oath-toolkit	unbound
glib		jpeg		libtool		openblas	webp
gmp		libevent	libunistring	openssl		wget
gnutls		libffi		libxml2		openssl@1.1	xz
nandymak:~ nandymak$

MySQL5.6をインストール

nandymak:~ nandymak$ brew search mysql@5.6
==> Formulae
mysql@5.6
nandymak:~ nandymak$ brew install mysql@5.6
Updating Homebrew...
==> Auto-updated Homebrew!
Updated 1 tap (homebrew/core).
==> Updated Formulae
etcd                           pilosa                         teleport

==> Downloading https://homebrew.bintray.com/bottles/[e-mail]
==> Downloading from https://akamai.bintray.com/8d/8d276249e7adf93c716505d9959c690db014693480
######################################################################## 100.0%
==> Pouring [e-mail]
==> Caveats
A "/etc/my.cnf" from another install may interfere with a Homebrew-built
server starting up correctly.

MySQL is configured to only allow connections from localhost by default

To connect:
    mysql -uroot
(途中省略)
To have launchd start mysql@5.6 now and restart at login:
  brew services start mysql@5.6
Or, if you don't want/need a background service you can just run:
  /usr/local/opt/mysql@5.6/bin/mysql.server start
==> Summary
????  /usr/local/Cellar/mysql@5.6/5.6.43: 341 files, 153.6MB
nandymak:~ nandymak$

動作を確認

nandymak:~ nandymak$ mysql -uroot
-bash: mysql: command not found
nandymak:~ nandymak$ which mysql
nandymak:~ nandymak$ type mysql
-bash: type: mysql: not found
nandymak:~ nandymak$

PATHを通す

nandymak:~ nandymak$ echo 'export PATH="/usr/local/opt/mysql@5.6/bin:$PATH"' >> ~/.bash_profile
nandymak:~ nandymak$ source ~/.bash_profile
nandymak:~ nandymak$ type
nandymak:~ nandymak$ mysql
ERROR 1045 (28000): Access denied for user 'nandymak'@'localhost' (using password: NO)
nandymak:~ nandymak$ mysql --version
mysql  Ver 14.14 Distrib 5.6.43, for osx10.14 (x86_64) using  EditLine wrapper

起動する

nandymak:~ nandymak$ brew services start mysql@5.6
==> Successfully started `mysql@5.6` (label: homebrew.mxcl.mysql@5.6)

ログインする

nandymak:~ nandymak$ mysql -uroot -pXXXXXXXX
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 27
Server version: 5.5.5-10.4.6-MariaDB Homebrew

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> quit
Bye
nandymak:~ nandymak$

my.cnfを確認する。

nandymak:etc nandymak$ cat /usr/local/etc/my.cnf
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

#
# include all files from the config directory
#
!includedir /usr/local/etc/my.cnf.d

nandymak:etc nandymak$

Comments

[Ms.yy]

MySQL8.0は一番最初に導入しました。
その後ドットインストールとverが違ったので、5.6に切り替えました。

上記の件、帰り次第試してみます！

[Ms.yy]

やはりダメでした。

my.cnfを消すためにコマンドを削除コマンドを実行後にアンインストール、再度インストールしてmysql -u rootを行っっても同じ結果となりました。

[nandymak]

＞my.cnfを消すためにコマンドを削除コマンドを実行後にアンインストール、再度インストールしてmysql -u rootを行っっても同じ結果となりました。

何を行ったか、どんなコマンドを入力してどんなエラーが返ってきたか、正確に書いてくれますか？
同じ結果と言われても、こちらからは見えないので何が同じか判断できません。

ちなみに
$ ls -l /usr/local/Cellar/mysql*
を行うと、何が表示されますか？MySQL8.0とか残ってませんか？

$ echo $PATH
$ cat /etc/mysql/my.cnf
$ which mysql
の結果も貼り付けてください。

[Ms.yy]

失礼しました^^;

$ ls -l /usr/local/Cellar/mysql*の結果にMySQL8.0という表示はありませんでした。
上記４点の結果追記させて頂きましたのでよろしくお願いします。

[nandymak]

my.cnfが無いとなってますがどこかにあるはずですので以下を参考にして探してください。
＞cat: /etc/mysql/my.cnf: No such file or directory

my.cnfの場所を調べる
https://qiita.com/is0me/items/12629e3602ebb27c26a4

[Ms.yy]

urlまですみません汗
すぐに調べさせて頂きますよ。

[Ms.yy]

調べた結果「/etc/my.cnf /etc/mysql/my.cnf /usr/local/etc/my.cnf ~/.my.cnf 」このように出たので/etcフォルダの中にmy.cnfがあるらしいですが、macのfinderで「my.cnf」と検索を掛けても見当たりませんでした。

[nandymak]

すいません。私の伝え方が悪かったのかも知れませんが、環境がおかしくなっているようです。

以下のQiitaの記事を参考に綺麗にインストールし直してください。
コマンドの意味を考えながら実行してくださいね。

＞MacのMySQL環境を整理した
https://qiita.com/sigwyg/items/bed1fdfeee3c8b52dec9#%E7%B6%BA%E9%BA%97%E3%81%AB%E3%81%99%E3%82%8B

(以下は抜粋です)
■綺麗にする
brew services start mysql で mysql デーモンが起動しないと思ったら datadir が古かった
↑を参考にスパッと消したらスッキリした。
# 古いやつは消す
$ brew uninstall mysql@5.6
$ brew uninstall mysql

(途中省略)
# brew infoでecho文が提示されているので、必要ならパスを通しておく　←不要のはず
$ echo 'export PATH="/usr/local/opt/mysql@5.6/bin:$PATH"' >> ~/.bash_profile　←不要のはず
(途中省略)

# 動いていることを確認
$ launchctl list | grep mysql
この後に「10694 0 homebrew.mxcl.mysql@5.6」みたいに出ればOK.

[Ms.yy]

こちらこそ長文とんでもないです(;´･ω･)

了解しました。
これは少し時間掛かりそうですね。
終わり次第また、ご報告させて頂きます。

[nandymak]

こちらは急ぎませんので(^_^)

今後のこともあるので、コマンドの意味は少しづつ確認していってください。
Homebrewの使い方も一から学習した方が宜しいかと思います。

念のための確認ですが、業務用のPCでやたらとアンインストールすると業務に支障があるようなことはないですよね？最悪何もかも動かなくなって、AppleStoreでリカバリってことになるかも知れませんので。

[Ms.yy]

いえ個人のmacですので大丈夫ですよ！
お気遣いありがとうございます。

[Ms.yy]

途中のmysql.server startとmysql.server stopで実行後エラーが出ましたが、

無事最後まで行うとしっかり表示することができました！
=>$ launchctl list | grep mysql
42424 0 homebrew.mxcl.mysql@5.6

[Ms.yy]

その後どうしたらいいでしょうか？
あれからも個人で調べていますがこちらの問題が解決できず

[nandymak]

$ mysql -u root
を打つと同じエラーが出ると言うことですか？

[Ms.yy]

はい、そうです。
ちなみに、教えた頂いたサイトのmysql.server startはエラーとなりうまくいきませんでしたがこれが関係しているのでしょうか？

nekonoMacBook-Air:~ neko$ mysql -uroot
ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded: dlopen(/usr/local/Cellar/mysql@5.6/5.6.43/lib/plugin/caching_sha2_password.so, 2): image not found

[nandymak]

>plugin 'caching_sha2_password' cannot be loaded

は別の問題ですが、

>Starting MySQL
>... ERROR! The server quit without updating PID file (/usr/local/var/mysql/nekonoMacBook-Air.local.pid).

は以下のサイトが参考になると思います。

MySQL起動できなくなった　The server quit without updating PID file
https://qiita.com/yuki0208/items/1d5554d98e9ec76084bd

最初からひとつづつ見直した方がよさそうですね。
自宅に帰ったら自分のMacbookAirで検証してみます。
とりあえず、上記のエラーはつぶしてみてください。

[Ms.yy]

The server quit without updating PID fileはある程度調べましたが解決ならず。。。
上記のサイトも検索上位にあったので試しましたが「$ usr/local/var/mysql/mysql > sudo mysql.server restart」でエラーとなり進めませんでした。

$ usr/local/var/mysql/mysql > sudo mysql.server restart
-bash: usr/local/var/mysql/mysql: No such file or directory

[nandymak]

$ ls -l /usr/local/var/mysql/nekonoMacBook-Air.local.pid
$ ls -l /usr/local/var/
はどうなりますか？

[Ms.yy]

了解です。
帰り次第試させて頂きます＾＾

[Ms.yy]

上記コマンドを実行し内容も追加させて頂きました。
下記の通りになりましたが、いかがでしょうか？

$ls -l /usr/local/var/mysql/nekonoMacBook-Air.local.pid

ls: /usr/local/var/mysql/nekonoMacBook-Air.local.pid: No such file or directory

$ls -l /usr/local/var/

total 0
drwxrwxr-x 4 neko admin 128 9 18 05:03 homebrew
drwxr-xr-x 10 neko admin 320 9 19 19:22 mysql

[nandymak]

MySQL5.6をインストールしてみました。
「PATHを通す」までは出来ていると思うので、その後の「起動する」をやってみてください。
駄目なら最後の「my.cnfを確認する」をして内容を教えてください。

[Ms.yy]

実際に行っていただきありがとうございます。
大変わかりやすいです。

手順通り行いましたが、やはりmysql -urootの部分でエラーが出てしまいます。
「ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded: dlopen(/usr/local/Cellar/mysql@5.6/5.6.43/lib/plugin/caching_sha2_password.so, 2): image not found
」

mysql -urootから先に進まないのでmy.cnfを確認するの結果を載せさせてもらいます。

$ cat /usr/local/etc/my.cnf
# Default Homebrew MySQL server config
[mysqld]
# Only allow connections from localhost
bind-address = 127.0.0.1

よろしくお願いします。

[Ms.yy]

すみません「起動する」の結果を載せていませんでした。

$ brew services start mysql@5.6
Service `mysql@5.6` already started, use `brew services restart mysql@5.6` to restart.

[nandymak]

>already started
既に起動しています。
>use `brew services restart mysql@5.6` to restart.
「brew services restart mysql@5.6」と入力して再起動してください、と言う意味です。

ですので、「brew services restart mysql@5.6」と入力して再起動してください。
それでエラーが出ないことを確認してください。

[Ms.yy]

そういうことでしたか、すみません英語がわからず。。。

おっしゃる通りエラーは出ませんでした！

brew services restart mysql@5.6
Stopping `mysql@5.6`... (might take a while)
==> Successfully stopped `mysql@5.6` (label: homebrew.mxcl.mysql@5.6)
==> Successfully started `mysql@5.6` (label: homebrew.mxcl.mysql@5.6)

[nandymak]

それで
$ mysql -uroot
は駄目でしょうか？

[Ms.yy]

行ってみたのですが変わらず同じ結果でした・・・

mysql -uroot
ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded: dlopen(/usr/local/Cellar/mysql@5.6/5.6.43/lib/plugin/caching_sha2_password.so, 2): image not found