Linux – ERROR 2003 (HY000): Can’t connect to MySQL server on ‘127.0.0.1’ (111) [despite commenting out bind-address & skip-networking]

linuxMySQLmysql-workbenchUbuntu

Okay, so disclaimer: I'm an absolute mysql noob. Gimme any other development type area and I'm fine.

So, I just started using Ubuntu 12.04 an setup mysql. Never really used localhost since we have a staging environment at work that I usually access.
However, a client had a really large DB dump that I needed to access and I didn't want to put in on the server for everyone to deal with so I tried to use localhost

Whenever I try to access it through MySQL Workbench I get an error so I had a look at a couple of articles online and tried a couple of different things but if I run the following command in the terminal, I get an error:

mysql -u root -p -h 127.0.0.1

Produces the following

ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (111)

However, if I access mysql like so:

mysql -u root -p

and then enter my password, I can access mysql fine.

Here's what

/etc/mysql/my.cnf

contains at the moment (I've tried looking for other versions of this file). I've left out all the commented out bits for brevity's sake.

[client] 
port            = 3306 
socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe] 
socket          = /var/run/mysqld/mysqld.sock 
nice            = 0

[mysqld]
user            = mysql 
pid-file        = /var/run/mysqld/mysqld.pid 
socket          = /var/run/mysqld/mysqld.sock 
port            = 3306
basedir         = /usr 
datadir         = /var/lib/mysql 
tmpdir          = /tmp 
lc-messages-dir = /usr/share/mysql 
skip-external-locking

key_buffer              = 16M 
max_allowed_packet      = 16M
thread_stack            = 192K 
thread_cache_size       = 8

myisam-recover         = BACKUP

query_cache_limit       = 1M 
query_cache_size        = 16M

log_error = /var/log/mysql/error.log

expire_logs_days        = 10 
max_binlog_size         = 100M

[mysqldump] 
quick quote-names 
max_allowed_packet      = 16M

[mysql]

[isamchk] key_buffer              = 16M

!includedir /etc/mysql/conf.d/

Best Answer

When you log into mysql with 

mysql -u root -p

please run this command

SELECT USER(),CURRENT_USER();

USER() reports how you attempted to authenticate in MySQL

CURRENT_USER() reports how you were allowed to authenticate in MySQL

I have discussed this before : how do I see which user I am logged in as in MySQL?

In your case, my guess would be that CURRENT_USER() is 'root'@'localhost'.

There are two things you need to connect to MySQL using 127.0.0.1:

  • You need the user 'root'@'127.0.0.1' defined. That's probably what USER() said.
  • You need to explicit use the TCP/IP protocol

To create the user, login to MySQL and run SHOW GRANTS;

Whatever comes backup, copy and paste it into an editor, replace localhost with 127.0.0.1, command the edited command into mysql and execute it.

Now, to login using TCP/IP do this:

mysql -u root -p -h 127.0.0.1 --protocol=tcp

Once you login, run SELECT USER(),CURRENT_USER(); and you will see something different.

Give it a Try !!!

Related Question