"Access denied for user" username "@" localhost "(using password: YES)" when deploying Rails with Capistrano

Question

I get up on a new Ubuntu server with MySQL. I have Capistrano configured on my development server, and I'm trying to deploy: it is cold after running deploy: setup. After deployment, the script tries to run

executing "cd /home/adm1n/www/knowit/releases/20121112152400 && bundle exec rake RAILS_ENV=production RAILS_GROUPS=assets assets:precompile" 

I keep getting this message:

 Rake Aborted! Access denied for user 'specialusername'@'localhost' (using password: YES) Tasks: TOP => environment 

I have a "specialusername" created in my database on mysql server for both localhost and%. I deleted the empty string user @ 'localhost in the user database. I added the server IP address to my.cnf file and commented out the line 127.0.0.1.

Here is my deploy.rb file to create:

 production: adapter: mysql2 encoding: utf8 reconnect: false database: mydatabasename username: specialusername password: crazypassword socket: /tmp/mysql.sock pool: 5 timeout: 5000 

I think I may not understand how this works. On my Site5 server, I never had to indicate which node I was accessing mysql to. But in all of my reading, it seems that I should specify a specific user @hostname. This makes it difficult when I travel from different places around the country. OR Do I just need to use the hostname of my laptop, regardless of my current IP address? Thanks for any ideas and solutions you can provide. I did not find articles that provided me with exactly what I needed to fix this problem.

Answer 1

There are two ways to connect to a MySQL server.

First use a UNIX socket, for example /tmp/mysql.sock , and in this case there is no "host", so username@localhost is used for authentication. This will only work when connected from the same machine.

Secondly, using a TCP / IP connection. In this case, the server listens for a specific port in the box working with the database, and the way to connect to it is to provide the host number + port. The port number can be optional (used by default), and the host can be specified by the host name or by ip-address. This allows you to connect locally or remotely.

Note. I have no way to verify this (no access to your server), so below is just a possible explanation ...

In mysql -u knowitdbadmin -p -h192.168.0.50 host (-h) is set, therefore, the TCP / IP protocol is used, as a result of which authentication uses the username@hostname or username@ % rules, but this will not use username@localhost .

No host (-h) is specified in mysql -u knowitdbadmin -p , so the default UNIX socket is used, forcing authentication to use username@localhost (and therefore fails).

If you want to disable access using the UNIX socket (/tmp/mysql.sock) and the @localhost username and always use the TCP / IP connection, follow all the steps: * delete grants for the @localhost username (done) * always specify the host / IP (+ port) in the configuration files from your application.

I think the problem is related to:

 socket: /tmp/mysql.sock 

try using the host number + port number instead.

As a side note, if you somehow have SQL access to the database itself starting from 5.5, the new performance_schema.host_cache table will show you all the reasons for the connection to fail, which saves a lot of time when troubleshooting it.