Rails app denied access to mysql for user root @ localhost

Question

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.

Palash Bera · Accepted Answer · 2020-10-27 07:07:44Z

2

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 :-)

answered Oct 27, 2020 at 7:07

Palash Bera

7165 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

C. Ball Over a year ago

Hi, this did not work. I followed the steps, but I still get the "Access denied for user 'root'@'localhost'" error when I try to run "rails db:create". I made sure to set the password in the file to the same string I entered in the query. I also made sure to leave the mysql prompt after running your query and log back in using my new password. I noticed that when I ran your "ALTER USER [...]" query in the mysql prompt, the result was: "Query OK, 0 rows affected (0.00 sec)". Does that mean nothing happened? I'm still able to log into mysql prompt with no password (as well as with new pw).

Palash Bera Over a year ago

Hi you can uninstall mysql and install it again. Then follow the steps.

C. Ball Over a year ago

I uninstalled mysql, reinstalled it, set a root password during installation, and found that everything was working when the installation was finished and I set the new password in the Rails file. I must have done something wrong during the first mysql installation (or when I subsequently tried to set the password to an empty string in the mysql prompt). Whatever the problem was, it's over now. Thank you very much for your help.

Palash Bera Over a year ago

I am happy to help you. :)

Collectives™ on Stack Overflow

Rails app denied access to mysql for user root @ localhost

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related