How to connect mysql in a rails application with docker?

Question

I created a rails app use mysql database. Now want to put them into docker container. I am using docker, docker-machine, docker-compose.

My docker-compose.yml

db:
  image: mysql
  environment:
    MYSQL_ROOT_PASSWORD: <%= ENV['MYSQL_ROOT_PASSWORD'] %>
  ports:
    - "3306:3306"

web:
  build: .
  command: bundle exec rails s -p 3000 -b '0.0.0.0'
  volumes:
    - .:/myapp
  ports:
    - "3000:3000"
  links:
    - db

My config/database.yml

default: &default
  adapter: mysql2
  encoding: utf8
  pool: 5
  username: root
  password: <%= ENV['MYSQL_ROOT_PASSWORD'] %>
  host: <%= ENV['DB_PORT_3306_TCP_ADDR'] %>
  port: <%= ENV['DB_PORT_3306_TCP_PORT'] %>

development:
  <<: *default
  database: myapp_development

test:
  <<: *default
  database: myapp_test

production:
  <<: *default
  database: myapp_production

When boot app

$ docker-compose up
...
sidekiq_1 | Host '172.17.0.5' is not allowed to connect to this MySQL server
...

My web env

$ docker-compose run web env
...
DB_PORT=tcp://172.17.0.3:3306
DB_PORT_3306_TCP=tcp://172.17.0.3:3306
DB_PORT_3306_TCP_ADDR=172.17.0.3
DB_PORT_3306_TCP_PORT=3306
DB_PORT_3306_TCP_PROTO=tcp
WEB_PORT=tcp://172.17.0.6:3000
WEB_PORT_3000_TCP=tcp://172.17.0.6:3000
WEB_PORT_3000_TCP_ADDR=172.17.0.6
WEB_PORT_3000_TCP_PORT=3000
WEB_PORT_3000_TCP_PROTO=tcp
...

My docker-machine ip

$ docker-machine ip myapp
192.168.99.100

Access from browser: http://192.168.99.100:3000, then saw error:

Mysql2::Error
Host '172.17.0.6' is not allowed to connect to this MySQL server

172.17.0.3, 172.17.0.5, 172.17.0.6, etc. Why they use different ips? Then how to connect mysql? I can't understand the host and port written in the config/database.yml file.

Delve

Answer 1

In my case, I'm running Rails in host, and MySQL in docker, the official image.

Adding host: 127.0.0.1 solved the problem.

config/database.yml

default: &default
  adapter: mysql2
  encoding: utf8
  pool: 5
  username: root
  password:
  host: 127.0.0.1

Neuman

Answer 2

You have to add the container name (db) to the host, Example:

config/database.yml

default: &default
  adapter: mysql2
  encoding: utf8
  pool: 5
  username: root
  password: <%= ENV['MYSQL_ROOT_PASSWORD'] %>
  host: db
development:
  <<: *default
  database: myapp_development

test:
  <<: *default
  database: myapp_test

production:
  <<: *default
  database: myapp_production

Look at you this example: docker-to-rails-with-rbenv

Expansible

How is this not the accepted answer??? Thank you very much, sir. – Madwort

Answer 3

IPs are assigned by internal DHCP server. This is why they can changed...

I would recommend reading this post http://blog.carbonfive.com/2015/03/17/docker-rails-docker-compose-together-in-your-development-workflow/ which explains how to configure rails app using host name (and not IPs) to properly connect to a database while using docker-compose.

Democratic

Answer 4

Different approach:

by mapping the DB-Port:

ports:
  - "3306:3306"

you are connecting the port 3306 of your host to the same port inside the container.
Therefore, you can connect to the database via the port on your "localhost".

so the database.yml would look like:

default: &default
  adapter: mysql2
  encoding: utf8
  pool: 5
  username: root
  password: <%= ENV['dont use MYSQL_ROOT_PASSWORD'] %>
  host: localhost
  port: 3306
development:
  <<: *default
  database: myapp_development

test:
  <<: *default
  database: myapp_test

production:
  <<: *default
  database: myapp_production

You have to keep in mind, that users in the DB are not connected from localhost but from the docker network (default 172.x.x.x) so create your users for all hosts (user@`%`)

And never ever use root as your database user.
If you don't want to set up a user for your Rails as root with SQL commands, you can create one when starting the docker container via environment variables.
See DockerHub-mysql.
You can define these environment variables in your docker-compose.yml or when running docker compose up

Graff

Answer 5

If your db in on localhost and you are running Rails application inside Docker, then Add host: host.docker.internal in database.yml, Below is the sample file

default: &default
  adapter: mysql2
  encoding: utf8
  username: YOUR_USERNAME_HERE
  password: YOUR_PASSWORD_HERE
  host: host.docker.internal
  port: 3306
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %> 

Bothersome