188

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)
Alex Gaynor
  • 14,353
  • 9
  • 63
  • 113
  • Maybe it's a bug in the particular binding that you're using? – YOUR ARGUMENT IS VALID May 01 '13 at 20:15
  • What happens when you restart it? – Henrik Andersson May 01 '13 at 20:28
  • 1
    Is it possible that you're somehow hitting the database with many concurrent connections? Maybe try increasing `max_connections` in your MySQL conf file? – dgel May 01 '13 at 20:36
  • If I enter a shell I can open over 100 connections without a problem. – Alex Gaynor May 01 '13 at 20:41
  • I can't provide a unique solution to the first part of the problem. What I can say is that using 127.0.0.1 is not the same as using localhost. localhost is the only entity that's going to get you the local socket, 127.0.0.1 is always going to be a TCP connection. You'll even need to configure users different in MySQL to accommodate this difference. – conrad10781 May 01 '13 at 20:44
  • 2
    does `mysql -h 127.0.0.1` work from the commandline? I'm not so sure your mysql server is actually listening on a TCP port. – Eli May 01 '13 at 21:08
  • Yes ``mysql -h 127.0.0.1`` works fine. – Alex Gaynor May 01 '13 at 21:18
  • What happens if you start the process normally, then `dtruss -p` it once it's running? – Glyph May 02 '13 at 16:20
  • @Glyph: If I put a ``raw_input`` before the connect, ``dtruss -p `` then I do observe the issue, there's nothing obviously wrong there though (unfortunately). – Alex Gaynor May 02 '13 at 18:12
  • 1
    Are you sure you have the right versions of the Python MySQL client libraries for your version of MySQL? Also, does `mysql -h localhost` work reliably? – Old Pro May 07 '13 at 06:25
  • 2
    Does MySQL log anything to the error log? Also, check file permissions on /tmp/mysql.sock and your mysql data directory. Do the errors also occur if you run the test suite as root (sudo)? – Erik Cederstrand May 07 '13 at 22:53
  • 2
    A lot of these suggestions are covered by the [official MySQL reference manual](http://dev.mysql.com/doc/refman/5.1/en/can-not-connect-to-server.html) which I reference in my response below. It's a better use of time to go through the MySQL reference manual suggestions systematically, rather than trying just one or two of those suggestions. – jtoberon May 08 '13 at 16:41
  • Are you using the built-in mysql server from a stack app like MAMP, a mysql server community edition from the mysql site, or an installed version from a package manager like homebrew or macports? – Francis Yaconiello May 09 '13 at 15:42
  • Do you have selinux enabled? What does the command `getenforce` return? – tMC May 09 '13 at 16:20
  • What user the test runner using? Is it running in virtualenv? – Burhan Khalid May 09 '13 at 18:09
  • In this case I would go for strace, on both the client and server proess, to get more information. – jpic May 10 '13 at 08:00
  • Is mysqld running prior to starting the test suite, or do your tests launch the server? If the latter, are you certain that it is available when connection is attempted? – eggyal May 11 '13 at 08:20

37 Answers37

178
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.

Pratyay
  • 2,176
  • 1
  • 15
  • 14
165

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
Lii
  • 11,553
  • 8
  • 64
  • 88
yask
  • 3,918
  • 4
  • 20
  • 29
159

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.

jtoberon
  • 8,706
  • 1
  • 35
  • 48
  • Although in general following established diagnostic procedures is a good idea, if you read the question (and the procedures) you see that the procedures have been followed and have determined this is not a problem with the MySQL server. This is something specifically to do with the Python client since all other access through the socket works fine, including other access from Python. – Old Pro May 11 '13 at 19:17
  • 3
    What an odd down vote. I posted the established procedure for several reasons: (1) other people were posting only part of the established procedure and it's better to be systematic about debugging, (2) there seemed to be some confusion about localhost vs 127.0.0.1, and (3) other people with the same "Can't connect to local mysql server" symptom are likely to stumble upon this question. I'm aware that it's likely the Python client, which is why I asked for more information, e.g. about how the connection is being instantiated. – jtoberon May 12 '13 at 00:21
  • 9
    +1 I was getting this error trying to connect to mysql through an ssh tunnel (using `localhost` as the host). Changing to `127.0.0.1` fixed it. – krock Mar 04 '16 at 00:01
  • 2
    For the record, this fixed my issue: "Can't connect to local MySQL server through socket '/tmp/mysql.sock'". – proinsias May 31 '16 at 16:25
  • 2
    Thanks! it worked for me this fix while trying to connect to a docker maridb container. – Lucian Oprea Oct 03 '19 at 13:53
  • +1 for advocating a systematic approach and the provision of the relevant code instead just posting "what worked for me was " – snakecharmerb Apr 10 '20 at 13:02
  • I would up-vote again if I didn't already! – ghchoi Sep 01 '22 at 02:49
35

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': '',
},
...
Sirbito X
  • 596
  • 5
  • 8
31

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.

Francis Yaconiello
  • 10,829
  • 2
  • 35
  • 54
29

For me, the mysql server was not running. So, i started the mysql server through

mysql.server start

then

mysql_secure_installation

to secure the server and now I can visit the MySQL server through

mysql -u root -p

or

sudo mysql -u root -p

depending on your installation.

sh6210
  • 4,190
  • 1
  • 37
  • 27
15

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

aurny2420289
  • 481
  • 5
  • 16
9

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!!

Prashanth Sams
  • 19,677
  • 20
  • 102
  • 125
  • 1
    This worked for me on a iMac running High Sierra that had been upgraded to Mojave. What must have happened was the mysql.sock file had been in tmp and deleted on the upgrade. As the socket is created automatically on starting MySQL you just need to sure MySQL is shut down and then start it up in safe mode, as above. The mysql.sock file magically appears. – David Oct 12 '18 at 13:52
9

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!
Mark Lohr
  • 119
  • 1
  • 5
9

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.

Blaszard
  • 30,954
  • 51
  • 153
  • 233
7

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

Increase the open files limit and run again.

hsen
  • 427
  • 5
  • 7
  • i've run into this before as well, and you not be able just to do this via your .cnf file. you may actually need to `ulimit` to increase the number of open files your client and server are allowed to have open. if you are on a recent version of ubuntu this may require editing the mysql upstart script in /etc/init but hopefully you can just do it in the .cnf file. – underrun May 06 '13 at 13:29
7

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.

Chamith Malinda
  • 4,399
  • 5
  • 24
  • 28
7

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.

Community
  • 1
  • 1
RolandoMySQLDBA
  • 43,883
  • 16
  • 91
  • 132
4

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.

Martin
  • 86
  • 2
4

Make sure your /etc/hosts has 127.0.0.1 localhost in it and it should work fine

hd1
  • 33,938
  • 5
  • 80
  • 91
  • Amazingly (with respect) this sorted it for me - on checking this I found that when trying to set up webdav Mavericks had added some additional (totally garbled) lines to my host file - including one that reassigned localhost. – rob_was_taken Jan 02 '15 at 23:43
  • Checked this, but still it does not work fine. – Ralf Nov 12 '22 at 19:19
4

if you get an error like below :

django.db.utils.OperationalError: (2002, "Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)")

Then just find your mysqld.sock file location and add it to "HOST".

Like i am using xampp on linux so my mysqld.sock file is in another location. so it is not working for '/var/run/mysqld/mysqld.sock'

DATABASES = {

    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'asd',
        'USER' : 'root',
        'PASSWORD' : '',
        'HOST' : '/opt/lampp/var/mysql/mysql.sock',
        'PORT' : ''
    }
}
alexander.polomodov
  • 5,396
  • 14
  • 39
  • 46
Shyam
  • 547
  • 4
  • 11
3

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!

Ben Rollert
  • 1,564
  • 1
  • 13
  • 21
3

I had to kill off all instances of mysql by first finding all the process IDs:

ps aux | grep mysql

And then killing them off:

kill -9 {pid}

Then:

mysql.server start

Worked for me.

HomerPlata
  • 1,687
  • 5
  • 22
  • 39
2

Check that your mysql has not reached maximum connections, or is not in some sort of booting loop as happens quite often if the settings are incorrect in my.cnf.

Use ps aux | grep mysql to check if the PID is changing.

beiller
  • 3,105
  • 1
  • 11
  • 19
2

Looked around online too long not to contribute. After trying to type in the mysql prompt from the command line, I was continuing to receive this message:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

This was due to the fact that my local mysql server was no longer running. In order to restart the server, I navigated to

shell> cd /user/local/bin

where my mysql.server was located. From here, simply type: 

shell> mysql.server start

This will relaunch the local mysql server.

From there you can reset the root password if need be..

mysql> UPDATE mysql.user SET Password=PASSWORD('MyNewPass')
->                   WHERE User='root';
mysql> FLUSH PRIVILEGES;
preynolds
  • 780
  • 8
  • 13
1

The socket is located in /tmp. On Unix system, due to modes & ownerships on /tmp, this could cause some problem. But, as long as you tell us that you CAN use your mysql connexion normally, I guess it is not a problem on your system. A primal check should be to relocate mysql.sock in a more neutral directory.

The fact that the problem occurs "randomly" (or not every time) let me think that it could be a server problem.

  • Is your /tmp located on a standard disk, or on an exotic mount (like in the RAM) ?

  • Is your /tmp empty ?

  • Does iotopshow you something wrong when you encounter the problem ?

Stéphane Bruckert
  • 21,706
  • 14
  • 92
  • 130
Koreth
  • 681
  • 5
  • 15
1

If it's socket related read this file

/etc/mysql/my.cnf

and see what is the standard socket location. It's a line like:

socket = /var/run/mysqld/mysqld.sock

now create an alias for your shell like:

alias mysql="mysql --socket=/var/run/mysqld/mysqld.sock"

This way you don't need root privileges.

MaxV
  • 588
  • 1
  • 4
  • 13
1
# shell script ,ignore the first 
$ $(dirname `which mysql`)\/mysql.server start

May be helpful.

陈也在哦
  • 11
  • 2
  • Same as the most star answer 'sudo /usr/local/mysql/support-files/mysql.server start', but different OS may have different path of the 'mysql.server'.So I tried to write one command line which might be useful in many OS. – 陈也在哦 Dec 10 '19 at 03:37
1

I had faced similar problem recently. Went through many answers. I got it working by following steps.

  1. change the socket path in /etc/my.cnf (as i was repeatedly getting error with /tmp/mysql.sock ) reference to change the socket path
  2. run mysqld_safe to restart the server as it is the recommended way to restart in case of errors. reference to mysqld_safe
Arjun K R
  • 427
  • 1
  • 6
  • 25
1

If you installed through Homebrew, try to run

brew services start mysql
ztyreg
  • 139
  • 2
  • 4
1

This worked for me :

mysql.server start
mysql -u root -p
Yeshi
  • 281
  • 3
  • 5
0

Configure your DB connection in the 'Manage DB Connections dialog. Select 'Standard (TCP/IP)' as connection method.

See this page for more details http://dev.mysql.com/doc/workbench/en/wb-manage-db-connections.html

According to this other page a socket file is used even if you specify localhost.

A Unix socket file is used if you do not specify a host name or if you specify the special host name localhost.

It also shows how to check on your server by running these commands:

If a mysqld process is running, you can check it by trying the following commands. The port number or Unix socket file name might be different in your setup. host_ip represents the IP address of the machine where the server is running.

shell> mysqladmin version 
shell> mysqladmin variables 
shell> mysqladmin -h `hostname` version variables 
shell> mysqladmin -h `hostname` --port=3306 version 
shell> mysqladmin -h host_ip version 
shell> mysqladmin --protocol=SOCKET --socket=/tmp/mysql.sock version
Only You
  • 2,051
  • 1
  • 21
  • 34
0

in ubuntu14.04 you can do this to slove this problem.

zack@zack:~/pycodes/python-scraping/chapter5$ **mysqladmin -p variables|grep socket**
Enter password: 
| socket                                            | ***/var/run/mysqld/mysqld.sock***                                                                                            |
zack@zack:~/pycodes/python-scraping/chapter5$***ln -s  /var/run/mysqld/mysqld.sock /tmp/mysql.sock***
zack@zack:~/pycodes/python-scraping/chapter5$ ll /tmp/mysql.sock 
lrwxrwxrwx 1 zack zack 27 11月 29 13:08 /tmp/mysql.sock -> /var/run/mysqld/mysqld.sock=
Mi-Creativity
  • 9,554
  • 10
  • 38
  • 47
ZackZang
  • 11
0

For me, I'm sure mysqld is started, and command line mysql can work properly. But the httpd server show the issue(can't connect to mysql through socket).

I started the service with mysqld_safe&.

finally, I found when I start the mysqld service with service mysqld start, there are issues(selinux permission issue), and when I fix the selinux issue, and start the mysqld with "service mysqld start", the httpd connection issue disappear. But when I start the mysqld with mysqld_safe&, mysqld can be worked. (mysql client can work properly). But there are still issue when connect with httpd.

Robin LI
  • 41
  • 7
0

Simply try to run mysqld.

This was what was not working for me on mac. If it doesn't work try go to /usr/local/var/mysql/<your_name>.err to see detailed error logs.

eloone
  • 4,248
  • 2
  • 32
  • 35
0

Using MacOS Mojave 10.14.6 for MySQL 8.0.19 installed via Homebrew

  • Ran sudo find / -name my.cnf
  • File found at /usr/local/etc/my.cnf

Worked for a time then eventually the error returned. Uninstalled the Homebrew version of MySQL and installed the .dmg file directly from here

Happily connecting since then.

Patience Mpofu
  • 433
  • 4
  • 9
0

In my case what helped was to edit the file /etc/mysql/mysql.conf.d/mysqld.cnfand replace the line:

socket      = /var/run/mysqld/mysqld.sock

with

socket      = /tmp/mysql.sock

Then I restarted the server and it worked fine. The funny thing is that if I put back the line as it was before and restarted it still worked..

jav
  • 583
  • 5
  • 15
0

Following steps would help:

  1. mysql.server start
  2. List item

for more details go to ref on medium

Sunil Kumar Singh
  • 301
  • 3
  • 15
0

I'm using macOS Monterey, I fixed it by changing the file content which locate in /opt/homebrew/etc/my.cnf from "bind-address = 127.0.0.1" to "bind-address = localhost"

eagler
  • 1
  • 1
0

You can try $ mysql.server start then $ mysql -h localhost -u root -p in password just press enter and it will start, then u can change password

milvflor
  • 1
  • 1
    Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Sep 09 '22 at 10:39
0

I installed MySQL 8.0 on macOS Monterey 12.5.1 using homebrew brew install mysql

When I ran mysql -uroot it gave me that error.

I fixed it like this:

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

It showed some error pointing to a given error file so I just deleted the error file:

rm -rf /usr/local/var/mysql/*.err

and then reran the start command

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

And then mysql -uroot worked again

psyanite
  • 89
  • 2
  • 10
-2

Mac user here running mac os mojave 10.14. In my case what helped me was to uninstall MySQL from within the system preferences pane and then reinstalling and selecting the legacy password MySQL system during the installation wizard.

I did this by going to apple menu > system preferences >> MySQL >> and then hitting uninstall.

Once I successfully uninstalled I went back to MySQL's website and into the archive downloads and downloaded mysql Ver 8.0.18 for macos10.14 on x86_64 (MySQL Community Server - GPL).

Now, when going through the install wizard you will come to a point where the installer asks you to choose the password type that you'd like to use and here you MUST SELECT THE LEGACY SYSTEM that allows you to sign into MySQL using the root user and a password that you will set at that moment.

After I finished going through the install wizard I restarted my terminal (in my case I was using vs code's terminal) and then ran "mysql -u root -p" and was able to enter the password I created during installation and got into MySQL with no errors.

PM 77-1
  • 12,933
  • 21
  • 68
  • 111
gassyhero
  • 1