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 tự the following command

1)

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

2) restart your connexion to 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 to 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 to lớn mysql as root@localhost, your OS session needs access to 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 to lớn MySQL with 127.0.0.1, you may need to 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 to lớn MySQL, run rẩy this:

SELECT USER(),CURRENT_USER();

What does this give you?

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

This may be one of following problems.

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

Like i am using xampp on linux so sánh my mysqld.sock tệp tin is in another location. so sánh 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 to 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 bủ.

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

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

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

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

mysql.server start
mysql -u root -p

The socket is located in /tmp. On Unix system, due to 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 to lớn relocate mysql.sock in a more neutral directory.

The fact that the problem occurs "randomly" (or not every time) let bủ 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 to lớn change the socket path
2. run mysqld_safe to lớn restart the server as it is the recommended way to lớn restart in case of errors. reference to lớn mysqld_safe

If you installed through Homebrew, try to 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 to 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 tự not specify a host name or if you specify the special host name localhost.

It also shows how to 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 tự this to 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 bủ, I'm sure mysqld is started, and command line mysql can work properly. But the httpd server show the issue(can't connect to 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 to lớn run rẩy mysqld.

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