🐳<docker, mysql, rails> error: 'Plugin caching_sha2_password could not be loaded' when run 'db:create'

🤔 Problem

error: 'Plugin caching_sha2_password could not be loaded'

😆 Solution

change the IDENTIFIED to mysql_native_password

👌Procedure

1.

$ docker cp hoge.sql [cotaier-id]:/hoge.sql

2. Login to Docker

$ docker ps
$ docker exec -it [container-id] /bin/bash

3. Login to MySQL

$ mysql -u root -p

4. check and alter the root user ones.

Assume that the password is 'password'.

mysql> SELECT user, host, plugin FROM mysql.user;
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| root             | %         | caching_sha2_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
| root             | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+
5 rows in set (0.00 sec)

mysql> alter user 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
Query OK, 0 rows affected (0.00 sec)

mysql> alter user 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT user, host, plugin FROM mysql.user;
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| root             | %         | mysql_native_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
| root             | localhost | mysql_native_password |
+------------------+-----------+-----------------------+
5 rows in set (0.00 sec)

---

Parent Note

My Docker note

n350071🇯🇵 ・ Oct 29 '19 ・ 1 min read

#docker