CODEWITHSUNDEEP
mysqlruby-on-rails
C. Ball
C. Ball

Reputation: 711

Rails app denied access to mysql for user root @ localhost

I'm running Linux Ubuntu 18.04.5, mysql server version 5.7.31, and Rails 6.0.3.4

I made my new app with rails new classic_models -d mysql -T

This is the database.yml file it generated for me:

default: &default
  adapter: mysql2
  encoding: utf8mb4
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  username: root
  password:
  socket: /var/run/mysqld/mysqld.sock

development:
  <<: *default
  database: classic_models_development

test:
  <<: *default
  database: classic_models_test

production:
  <<: *default
  database: classic_models_production
  username: classic_models
  password: <%= ENV['CLASSIC_MODELS_DATABASE_PASSWORD'] %>

When I run bundle exec rails db:create, rails db:create and other db: commands, I get this error:

Access denied for user 'root'@'localhost'
Couldn't create 'classic_models_development' database. Please check your configuration.
rails aborted!
Mysql2::Error: Access denied for user 'root'@'localhost'

I think it's ok that the config file doesn't specify a password for the development database, because when I run sudo mysql -p I'm able to access the mysql prompt just by pressing ENTER when it prompts me for a password. And I also ran some other commands to explicitly set the password to an empty string, just to be sure that wasn't the issue.

I tried adding host: localhost to the default part of the config file, but that didn't change anything. I also tried adding host: 127.0.0.1 to the config file instead, but that didn't help either.

When I run sudo mysqladmin variables | grep socket the socket string displayed is: /var/run/mysqld/mysqld.sock which I think means the socket bit of the config file is correct.

When I run netstat -an | grep 3306 the address 127.0.0.1:3306 appears and so does LISTEN, which I think means it's listening on the right port.

The only other possibly relevant thing I can think to mention is that when I run mysql or mysql -p without sudo in front of it, the error I get contains this same string: Access denied for user 'root'@'localhost'

I've been combing through other threads about what sounds like the same issue on here for hours, trying different things, but haven't found a solution. Please don't mark this question as a duplicate and link to any of those other pages.

If anyone could suggest anything else I could try, I would be extremely grateful.

Upvotes: 1

Views: 1247

Answers (1)

Palash Bera
Palash Bera

Reputation: 716

you have to set password for root user in mysql first. For this please follow these steps.

Step 1: Login into MySQL with root user. When mysql will ask for password just press enter.

$ sudo mysql -u root -p

Step 2: Set password for root user. Please replace NewPassword with your desired password. After setting this command please follow step 1 and when system will ask password, please put your new password and press enter.

ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword';

Step 3: Use this password in your database.yml file.

# config/database.yml

default: &default
  adapter: mysql2
  encoding: utf8mb4
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  username: root
  password: NewPassword
  socket: /var/run/mysqld/mysqld.sock

development:
  <<: *default
  database: classic_models_development

test:
  <<: *default
  database: classic_models_test

production:
  <<: *default
  database: classic_models_production
  username: classic_models
  password: <%= ENV['CLASSIC_MODELS_DATABASE_PASSWORD'] %>

I think this will help you. Thanks :-)

Upvotes: 2

Related Questions