英文:
Docker + Rails 6 + MySQL : Mysql2::Error::ConnectionError: Access denied for 'user'@'%' to database
问题
以下是您要翻译的内容:
Context : Rails 6
app hosted in Docker
, using docker-compose
When the Rails container starts, I get :
Mysql2::Error::ConnectionError: Access denied for user 'user'@'%' to database 'db_production'
Even after MySQL is fully initalized
My docker-compose.yml :
services:
app_production:
image: app_production
build:
context: .
dockerfile: ./dockerfiles/production.dockerfile
container_name: app_production
volumes:
- ./public:/app_production/public
- mysql_socket:/var/run/mysqld
command: bash -c "sleep 5 && bundle exec rails db:create db:migrate && bundle exec puma -C config/puma.rb"
restart: always
environment:
RAILS_ENV: production
SECRET_KEY_BASE: ${PRODUCTION_SECRET_KEY_BASE}
APP_DATABASE_PASSWORD: ${PRODUCTION_MYSQL_PASSWORD}
PORT: 5000
ports:
- "5000:5000"
depends_on:
- database_production
links:
- database_production
database_production:
image: mysql:8.0.20
command: mysqld --default-authentication-plugin=mysql_native_password
restart: always
container_name: database_production
environment:
MYSQL_USER: user
MYSQL_PASSWORD: ${PRODUCTION_MYSQL_PASSWORD}
MYSQL_RANDOM_ROOT_PASSWORD: "yes"
volumes:
- dbdata_production:/var/lib/mysql
- mysql_socket:/var/run/mysqld
ports:
- "3307:3306"
volumes:
dbdata_production:
mysql_socket:
My Dockerfile :
FROM ruby:2.6.1
RUN echo "deb http://archive.debian.org/debian stretch main" > /etc/apt/sources.list
RUN curl https://deb.nodesource.com/setup_12.x | bash
RUN curl https://dl.yarnpkg.com/debian/pubkey.gpg | apt-key add -
RUN echo "deb https://dl.yarnpkg.com/debian/ stable main" | tee /etc/apt/sources.list.d/yarn.list
RUN apt-get update && apt-get install -y nodejs yarn
RUN apt-get update && apt-get install -y gconf-service libasound2 libatk1.0-0 libc6 libcairo2 libcups2 libdbus-1-3 libexpat1 libfontconfig1 libgcc1 libgconf-2-4 libgdk-pixbuf2.0-0 libglib2.0-0 libgtk-3-0 libnspr4 libpango-1.0-0 libpangocairo-1.0-0 libstdc++6 libx11-6 libx11-xcb1 libxcb1 libxcomposite1 libxcursor1 libxdamage1 libxext6 libxfixes3 libxi6 libxrandr2 libxrender1 libxss1 libxtst6 ca-certificates fonts-liberation libappindicator1 libnss3 lsb-release xdg-utils wget
RUN apt-get install -y default-mysql-client
WORKDIR /app_production
RUN export RAILS_ENV=production
RUN gem install bundler:2.2.31
COPY Gemfile Gemfile.lock ./
COPY package.json ./
COPY vendor/gems/ vendor/gems/
RUN bundle install
COPY . ./
RUN yarn install --check-files
EXPOSE 5000
CMD ["RAILS_ENV=production", "bundle", "exec", "puma", "-C", "config/puma.rb"]
database.yml
default: &default
adapter: mysql2
encoding: utf8mb4
pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
username: root
password:
production:
<<: *default
host: database_production
port: 3306
database: db_production
username: user
password: "some_password"
socket: "/var/run/mysqld/mysqld.sock"
Notes :
- This configuration is working fine on another server on Ubuntu 22.04
- I'm now trying to make it works on this server on Ubuntu 20.04
- I had to add
RUN apt-get install -y default-mysql-client
to the Dockerfile in order to have MySQL working in the Rails container, otherwise it was not found, but on my other server I did not have to do this - I also had to create a shared volume for
mysql_socket:/var/run/mysqld
otherwise socket was not found - I am able to log to MySQL with CLI on the Rails container, using the same credentials
- If I put a
phpMyAdmin
service in docker-compose, I am also able to login to MySQL using the same credentials - If I try to connect to MySQL via container's rails console, it works
- If I remove
bundle exec rails db:create db:migrate
from rails container command, rails is launched
So I don't understand much what exactly is happening, since I can successfully connect to MySQL through container's CLI and PhpMyAdmin.
How come Rails can't connect to it ? There are no detailed logs.
It fails at the step rake db:create
and when trying to run it manually via rails console I got the same error. Very stange.
Any clue ?
英文:
Context : Rails 6
app hosted in Docker
, using docker-compose
When the Rails container starts, I get :
Mysql2::Error::ConnectionError: Access denied for user 'user'@'%' to database 'db_production'
Even after MySQL is fully initalized
My docker-compose.yml :
services:
app_production:
image: app_production
build:
context: .
dockerfile: ./dockerfiles/production.dockerfile
container_name: app_production
volumes:
- ./public:/app_production/public
- mysql_socket:/var/run/mysqld
command: bash -c "sleep 5 && bundle exec rails db:create db:migrate && bundle exec puma -C config/puma.rb"
restart: always
environment:
RAILS_ENV: production
SECRET_KEY_BASE: ${PRODUCTION_SECRET_KEY_BASE}
APP_DATABASE_PASSWORD: ${PRODUCTION_MYSQL_PASSWORD}
PORT: 5000
ports:
- "5000:5000"
depends_on:
- database_production
links:
- database_production
database_production:
image: mysql:8.0.20
command: mysqld --default-authentication-plugin=mysql_native_password
restart: always
container_name: database_production
environment:
MYSQL_USER: user
MYSQL_PASSWORD: ${PRODUCTION_MYSQL_PASSWORD}
MYSQL_RANDOM_ROOT_PASSWORD: "yes"
volumes:
- dbdata_production:/var/lib/mysql
- mysql_socket:/var/run/mysqld
ports:
- "3307:3306"
volumes:
dbdata_production:
mysql_socket:
My Dockerfile :
FROM ruby:2.6.1
RUN echo "deb http://archive.debian.org/debian stretch main" > /etc/apt/sources.list
RUN curl https://deb.nodesource.com/setup_12.x | bash
RUN curl https://dl.yarnpkg.com/debian/pubkey.gpg | apt-key add -
RUN echo "deb https://dl.yarnpkg.com/debian/ stable main" | tee /etc/apt/sources.list.d/yarn.list
RUN apt-get update && apt-get install -y nodejs yarn
RUN apt-get update && apt-get install -y gconf-service libasound2 libatk1.0-0 libc6 libcairo2 libcups2 libdbus-1-3 libexpat1 libfontconfig1 libgcc1 libgconf-2-4 libgdk-pixbuf2.0-0 libglib2.0-0 libgtk-3-0 libnspr4 libpango-1.0-0 libpangocairo-1.0-0 libstdc++6 libx11-6 libx11-xcb1 libxcb1 libxcomposite1 libxcursor1 libxdamage1 libxext6 libxfixes3 libxi6 libxrandr2 libxrender1 libxss1 libxtst6 ca-certificates fonts-liberation libappindicator1 libnss3 lsb-release xdg-utils wget
RUN apt-get install -y default-mysql-client
WORKDIR /app_production
RUN export RAILS_ENV=production
RUN gem install bundler:2.2.31
COPY Gemfile Gemfile.lock ./
COPY package.json ./
COPY vendor/gems/ vendor/gems/
RUN bundle install
COPY . ./
RUN yarn install --check-files
EXPOSE 5000
CMD ["RAILS_ENV=production", "bundle", "exec", "puma", "-C", "config/puma.rb"]
database.yml
default: &default
adapter: mysql2
encoding: utf8mb4
pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
username: root
password:
production:
<<: *default
host: database_production
port: 3306
database: db_production
username: user
password: "some_password"
socket: "/var/run/mysqld/mysqld.sock"
Notes :
- This configuration is working fine on another server on Ubuntu 22.04
- I'm now trying to make it works on this server on Ubuntu 20.04
- I had to add
RUN apt-get install -y default-mysql-client
to the Dockerfile in order to have MySQL working in the Rails container, otherwise it was not found, but on my other server I did not have to do this - I also had to create a shared volume for
mysql_socket:/var/run/mysqld
otherwise socket was not found - I am able to log to MySQL with CLI on the Rails container, using the same credentials
- If I put a
phpMyAdmin
service in docker-compose, I am also able to login to MySQL using the same credentials - If I try to connect to MySQL via container's rails console, it works
- If I remove
bundle exec rails db:create db:migrate
from rails container command, rails is launched
So I don't understand much what exactly is happening, since I can successfully connect to MySQL through container's CLI and PhpMyAdmin.
How come Rails can't connect to it ? There are no detailed logs.
It fails at the step rake db:create
and when trying to run it manually via rails console I got the same error. Very stange.
Any clue ?
答案1
得分: 1
找到解决方法:
这是与MySQL用户权限相关的问题。
必须以root用户连接到MySQL并运行以下命令:
GRANT ALL PRIVILEGES ON db_production.* TO 'user'@'%';
英文:
Found the solution:
It was a privileges issue with the MySQL user.
Had to connect to MySQL as root and run the following command :
GRANT ALL PRIVILEGES ON db_production.* TO 'user'@'%';
答案2
得分: 1
在Docker文件中设置环境变量MYSQL_USER
只是创建了用户,但没有授予任何权限。为了解决这个问题,你可以创建一个包含以下内容的文件:
GRANT ALL PRIVILEGES ON db_production.* TO 'user'@'%';
然后将其挂载到/docker-entrypoint-initdb.d
文件夹中。这样,当首次运行mysql时,它将执行该文件并授予适当的权限。这种方式将在容器/卷被删除时保留权限,而不是手动授予权限。
来自mysql - Official Image | Docker的摘录
初始化一个全新的实例
当首次启动容器时,将创建一个具有指定名称的新数据库,并使用提供的配置变量进行初始化。此外,它将执行在/docker-entrypoint-initdb.d中找到的具有扩展名.sh、.sql和.sql.gz的文件。文件将按字母顺序执行。你可以通过将SQL转储挂载到该目录并提供包含贡献数据的自定义镜像来轻松填充你的mysql服务。默认情况下,SQL文件将导入到由MYSQL_DATABASE变量指定的数据库中。
英文:
Setting the environment variable MYSQL_USER
in the docker file just creates the user, but grants no privileges to it. To solve the problem you can create a file with the following content
GRANT ALL PRIVILEGES ON db_production.* TO 'user'@'%';
and mount it in the /docker-entrypoint-initdb.d
folder. That way when mysql is run for the first time it will execute the file and grant the appropiate privileges. This way will endure any container/volume deletion as opposed to manually granting the privileges.
Excerpt from mysql - Official Image | Docker
> Initializing a fresh instance
>
> When a container is started for the first time, a new database with the specified name will be created and initialized with the provided configuration variables. Furthermore, it will execute files with extensions .sh, .sql and .sql.gz that are found in /docker-entrypoint-initdb.d. Files will be executed in alphabetical order. You can easily populate your mysql services by mounting a SQL dump into that directory and provide custom images with contributed data. SQL files will be imported by default to the database specified by the MYSQL_DATABASE variable.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论