Docker rails mysql is not connecting

Question

I am trying to connect my rails app which is on my host to docker mysql image. But I am getting this error:

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

My Docker compose file is like this:

db:
  image: mysql
  restart: always
  ports:
    - "3306:3306"
  environment:
    MYSQL_ROOT_PASSWORD: password


adminer:
  image: adminer
  restart: always
  ports:
    - 8080:8080

I am using this inside my database.yml:

default: &default
adapter: mysql2
encoding: utf8
host: 127.0.0.1
username: root
password: password
pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
# socket: /Applications/MAMP/tmp/mysql/mysql.sock

development:
  <<: *default
   database: meal_plan_development

What else I should do in order to connect my rails app to mysql docker image.

I think the MySQL server is requiring an authentication method you don't have installed on your local machine (where I assume rails is running). So you can either try to update your mysql-client libraries (which can be hard) or you can change the authentication method on the mysql server: https://stackoverflow.com/questions/50892720/authentication-plugin-error-while-connecting-to-mysql-database - you could also downgrade the MySQL-image you're using by changing `image: mysql` to `image: mysql:5.7` in your `docker-compose.yml`. — vstm, Jun 23 '18 at 12:37

Hamid Asghari · Answer 1 · 2018-10-26T21:44:28.073

6

MySQL default-authentication-plugin

as of version 8, MySQL uses caching_sha2_password as the default authentication plugin. You can override it to use mysql_native_password by adding a command instruction in your docker-compose.yml file like this:

db:
   image: mysql
   command: --default-authentication-plugin=mysql_native_password
   restart: always
   ports:
      - "3306:3306"
   environment:
   MYSQL_ROOT_PASSWORD: password

adminer:
   image: adminer
   restart: always
   ports:
     - 8080:8080

edited Oct 26 '18 at 21:44

answered Oct 25 '18 at 17:15

Hamid Asghari

5,751
4
24
34

1

I had to wipe out the volume from the first time I started the db container because the user was created with the new authentication plugin. – nroose Jul 08 '21 at 21:40

Ash · Answer 2 · 2018-06-24T01:36:19.177

As @vstm pointed out, this seems to be the same problem I was having with a PHP client. After the container has been created you could try changing the authentication scheme to one which will likely be supported e.g.

docker exec <container_id> /bin/sh -c "mysql -uroot -ppassword 
-e ALTER USER root@localhost IDENTIFIED WITH mysql_native_password BY 'PASSWORD'"

I'm not overly familiar with Docker, but I believe you can also add something like this to a Dockerfile so that the authentication method will be changed during the container initialization:

RUN /bin/bash -c "mysql -uroot -ppassword 
    -e ALTER USER root@localhost IDENTIFIED WITH mysql_native_password BY 'PASSWORD'"

Docker rails mysql is not connecting

2 Answers2

MySQL default-authentication-plugin

Linked