Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock

Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock

When I attempted to connect to a local MySQL server during my test suite, it
fails with the error:
OperationalError: (2002, "Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)")

However, I'm able to at all times, connect to MySQL by running the command line
mysql program. A ps aux | grep mysql shows the server is running, and
stat /tmp/mysql.sock confirm that the socket exists. Further, if I open a
debugger in except clause of that exception, I'm able to reliably connect
with the exact same parameters.
This issue reproduces fairly reliably, however it doesn't appear to be 100%,
because every once in a blue moon, my test suite does in fact run without
hitting this error. When I attempted to run with sudo dtruss it did not reproduce.
All the client code is in Python, though I can't figure how that'd be relevant.
Switching to use host 127.0.0.1 produces the error:
DatabaseError: Can't connect to MySQL server on '127.0.0.1' (61)

Solutions/Answers:

Answer 1:

sudo /usr/local/mysql/support-files/mysql.server start 

This worked for me. However, if this doesnt work then make sure that mysqld is running and try connecting.

Answer 2:

The relevant section of the MySQL manual is here. I’d start by going through the debugging steps listed there.

Also, remember that localhost and 127.0.0.1 are not the same thing in this context:

  • If host is set to localhost, then a socket or pipe is used.
  • If host is set to 127.0.0.1, then the client is forced to use TCP/IP.

So, for example, you can check if your database is listening for TCP connections vi netstat -nlp. It seems likely that it IS listening for TCP connections because you say that mysql -h 127.0.0.1 works just fine. To check if you can connect to your database via sockets, use mysql -h localhost.

If none of this helps, then you probably need to post more details about your MySQL config, exactly how you’re instantiating the connection, etc.

Answer 3:

For me the problem was I wasn’t running mysql server.
Run server first and then execute mysql.

$ mysql.server start
$ mysql -h localhost -u root -p

Answer 4:

I’ve seen this happen at my shop when my devs have a stack manager like MAMP installed that comes preconfigured with MySQL installed in a non standard place.

at your terminal run

mysql_config --socket

that will give you your path to the sock file. take that path and use it in your DATABASES HOST paramater.

What you need to do is point your

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'test',
        'USER': 'test',
        'PASSWORD': 'test',
        'HOST': '/Applications/MAMP/tmp/mysql/mysql.sock',
        'PORT': '',
    },
}

NOTE

also run which mysql_config if you somehow have multiple instances of mysql server installed on the machine you may be connecting to the wrong one.

Answer 5:

I just changed the HOST from localhost to 127.0.0.1 and it works fine:

# settings.py of Django project
...

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'db_name',
        'USER': 'username',
        'PASSWORD': 'password',
        'HOST': '127.0.0.1',
        'PORT': '',
},
...

Answer 6:

When, if you lose your daemon mysql in mac OSx but is present in other path for exemple in private/var do the following command

1)

ln -s /private/var/mysql/mysql.sock /tmp/mysql.sock

2) restart your connexion to mysql with :

mysql -u username -p -h host databasename

works also for mariadb

Answer 7:

Run the below cmd in terminal

/usr/local/mysql/bin/mysqld_safe

enter image description here

Then restart the machine to take effect. It works!!

Answer 8:

After attempting a few of these solutions and not having any success, this is what worked for me:

  1. Restart system
  2. mysql.server start
  3. Success!

Answer 9:

Check number of open files for the mysql process using lsof command.

Increase the open files limit and run again.

Answer 10:

This may be one of following problems.

  1. Incorrect mysql lock.
    solution: You have to find out the correct mysql socket by,

mysqladmin -p variables | grep socket

and then put it in your db connection code:

pymysql.connect(db='db', user='user', passwd='pwd', unix_socket="/tmp/mysql.sock")

/tmp/mysql.sock is the returned from grep

2.Incorrect mysql port
solution: You have to find out the correct mysql port:

mysqladmin -p variables | grep port

and then in your code:

pymysql.connect(db='db', user='user', passwd='pwd', host='localhost', port=3306)

3306 is the port returned from the grep

I think first option will resolve your problem.

Answer 11:

I think i saw this same behavior some time ago, but can’t remember the details.
In our case, the problem was the moment the testrunner initialises database connections relative to first database interaction required, for instance, by import of a module in settings.py or some __init__.py.
I’ll try to digg up some more info, but this might already ring a bell for your case.

Answer 12:

To those who upgraded from 5.7 to 8.0 via homebrew, this error is likely caused by the upgrade not being complete. In my case, mysql.server start got me the following error:

ERROR! The server quit without updating PID file

I then checked the log file via cat /usr/local/var/mysql/YOURS.err | tail -n 50, and found the following:

InnoDB: Upgrade after a crash is not supported.

If you are on the same boat, first install mysql@5.7 via homebrew, stop the server, and then start the 8.0 system again.

brew install mysql@5.7

/usr/local/opt/mysql@5.7/bin/mysql.server start
/usr/local/opt/mysql@5.7/bin/mysql.server stop

Then,

mysql.server start

This would get your MySQL (8.0) working again.

Answer 13:

I have two sneaky conjectures on this one

CONJECTURE #1

Look into the possibility of not being able to access the /tmp/mysql.sock file. When I setup MySQL databases, I normally let the socket file site in /var/lib/mysql. If you login to mysql as root@localhost, your OS session needs access to the /tmp folder. Make sure /tmp has the correct access rights in the OS. Also, make sure the sudo user can always read file in /tmp.

CONJECTURE #2

Accessing mysql via 127.0.0.1 can cause some confusion if you are not paying attention. How?

From the command line, if you connect to MySQL with 127.0.0.1, you may need to specify the TCP/IP protocol.

mysql -uroot -p -h127.0.0.1 --protocol=tcp

or try the DNS name

mysql -uroot -p -hDNSNAME

This will bypass logging in as root@localhost, but make sure you have root@'127.0.0.1' defined.

Next time you connect to MySQL, run this:

SELECT USER(),CURRENT_USER();

What does this give you?

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

If these functions return with the same values, then you are connecting and authenticating as expected. If the values are different, you may need to create the corresponding user root@127.0.0.1.

Answer 14:

Had this same problem. Turned out mysqld had stopped running (I’m on Mac OSX). I restarted it and the error went away.

I figured out that mysqld was not running largely because of this link:
http://dev.mysql.com/doc/refman/5.6/en/can-not-connect-to-server.html

Notice the first tip!

References