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

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

1)

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

2) restart your connexion lớn mysql with :

mysql -u username -p -h host databasename

works also for mariadb

I have two sneaky conjectures on this one

CONJECTURE #1

Look into the possibility of not being able lớn access the /tmp/mysql.sock tệp tin. When I setup MySQL databases, I normally let the socket tệp tin site in /var/lib/mysql. If you login lớn mysql as root@localhost, your OS session needs access lớn the /tmp thư mục. Make sure /tmp has the correct access rights in the OS. Also, make sure the sudo user can always read tệp tin 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 lớn MySQL with 127.0.0.1, you may need lớn 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 lớn MySQL, lập cập this:

SELECT USER(),CURRENT_USER();

What does this give you?

• USER() reports how you attempted lớn authenticate in MySQL
• CURRENT_USER() reports how you were allowed lớn 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 lớn create the corresponding user [email protected].

This may be one of following problems.

1. Incorrect mysql lock. solution: You have lớn 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 lớn 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.

if you get an error lượt thích below :

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

Then just find your mysqld.sock tệp tin location and add it lớn "HOST".

Like i am using xampp on linux ví my mysqld.sock tệp tin is in another location. ví 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' : ''
    }
}

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

I had lớn 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 bu.

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 lớn kiểm tra if the PID is changing.

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

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

This was due lớn the fact that my local mysql server was no longer running. In order lớn 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;

This worked for bu :

mysql.server start
mysql -u root -p

The socket is located in /tmp. On Unix system, due lớn modes và 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 kiểm tra should be lớn relocate mysql.sock in a more neutral directory.

The fact that the problem occurs "randomly" (or not every time) let bu 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 ?

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.

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 lớn change the socket path
2. run mysqld_safe lớn restart the server as it is the recommended way lớn restart in case of errors. reference lớn mysqld_safe

If you installed through Homebrew, try lớn run

brew services start mysql

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 lớn this other page a socket tệp tin is used even if you specify localhost.

A Unix socket tệp tin is used if you bởi not specify a host name or if you specify the special host name localhost.

It also shows how lớn kiểm tra on your server by running these commands:

If a mysqld process is running, you can kiểm tra it by trying the following commands. The port number or Unix socket tệp tin 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

in ubuntu14.04 you can bởi this lớn 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=

For bu, I'm sure mysqld is started, and command line mysql can work properly. But the httpd server show the issue(can't connect lớn 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.

Simply try lớn lập cập mysqld.

This was what was not working for bu on mac. If it doesn't work try go lớn /usr/local/var/mysql/.err lớn see detailed error logs.