[Solved] ERROR 2002 (HY000): Can’t connect to local MySQL server through socket’/var/lib/mysql/mysql.sock’ (2)

Leave a reply

Sometimes, when we use “mysql”, “mysqladmin”, “mysqldump” and other commands to manage the database, the server throws an error similar to the following:

1. Error on-site restoration:
Below we connect in three ways, and then observe the prompt error message:

  • 1. Use the “mysql” command directly without the host name parameter;
  • 2. Use the “mysql -h localhost” command with the host name “localhost” parameter;
  • 3. Use the “mysql -h 127.0.0.1” command with the host name “127.0.0.1” parameter.

1. [root@lam7 opt]# mysql
ERROR 2002 (HY000): Can’t connect to local MySQL server through socket’/

var/lib/mysql/mysql.sock ‘ (2) 2. [root@lam7 opt]# mysql -h localhost
ERROR 2002 (HY000): Can’t connect to local MySQL server through socket’/var/lib/mysql/mysql.sock’ (2)

[root@lam7 opt]# mysql -h 127.0.0.1 (This method can be used to enter MariaDB, you can ignore this problem after entering)
Welcome to the MariaDB monitor. Commands end with; or \g.
Your MariaDB connection id is 244
Server version: 10.1.19-MariaDB Source distribution

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

Type’help;’ or’\h’ for help. Type’\c’ to clear the current input statement.

 

3. [root@lam7 opt]# mysql -h 127.0.0.1 (PS: Some users also have this problem)
ERROR 1045 (28000): Access denied for user’root’@’localhost’ (using password: NO)

Through the above experiment, it can be seen that the first two methods can produce errors in the title, and the third method of connection will not produce errors in the title (the third method here produces an error of denying access due to password problems. information)

2. Analysis of the cause of the error:
 　　This is because the host name parameter we use to connect to the database is “localhost”, or the host name parameter is not used, and the server uses “localhost” as the host name by default. When the hostname parameter is “localhost” to connect to the mysql server, the mysql client will think it is connecting to the machine, so it will try to connect in socket file mode (socket file connection method is more efficient than “ip: port” method) At this time, according to the path of the configuration file “/etc/mysql.cnf”, if the corresponding socket file is not found, this error will be triggered.

3. Preparation before repairing the fault:
1. Check whether the mysql service is running:
　　Since the “socket” file is created when the mysql service is running, if it prompts “ERROR 2002 (HY000): Can’t connect to local MySQL server through socket'” ***’ (2)”, the “socket” file cannot be found. The first thing we need to confirm is whether the mysql service is running.

#1: Is the port open?

[root@lam7 opt]# lsof -i:3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 57436 mysql 17u IPv6 160456 0t0 TCP *:mysql (LISTEN)

#2: Is the mysqld service running (Xiaoqi uses centos7 here, so it will prompt to use “/bin/systemctl status mysqld.service”)

[root@lam7 opt]# service mysqld status
Redirecting to /bin/systemctl status mysqld.service
mysqld.service
Loaded: not-found (Reason: No such file or directory)
Active: inactive (dead)

#3: If mariaDB, check whether the service is running in the same way:

[root@lam7 opt]# service mariadb status
Redirecting to /bin/systemctl status mariadb.service
mariadb.service-MariaDB database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled)
Active: inactive ( dead)

#4mysqld service is running (this status is the normal operation of mysql service)

[root@lam7 opt]# service mariadb status
Redirecting to /bin/systemctl status mariadb.service
● mariadb.service-MariaDB database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; vendor preset: disabled)
Active: active (running) since two 2016-11-22 20:09:01 CST; 10min ago

2. Determine the correct location of the “socket” file: After
　　confirming that the mysql service is running normally, the only cause of this error is that the path of the “socket” file is incorrect. We can use the “find” command or the “lsof” command to determine the socket file The correct path:

[root@lam7 opt]# lsof -c mysqld|grep sock$
lsof: WARNING: can’t stat() fuse.gvfsd-fuse file system /run/user/1000/gvfs
Output information may be incomplete.
mysqld 57436 mysql 18u unix 0xffff88000b55f440 0t0 160457 /opt/lampp/var/mysql/mysql.sock

[root@lam7 opt]# find / -name’*.sock’
/storage/db/mysql/mysql.sock

Fourth, the troubleshooting method:
Solution 1:
　　Modify the “/etc/my.cnf” configuration file, find “mysql.default_socket” under the “[MySQL]” item in the /etc/php.ini file, and set its value to point to The correct mysql service socket file is enough, add the “[client]” option and the “[mysql]” option in the configuration file, and use the “socket” parameter value under these two options, and the “[mysqld]” option under The “socket” parameter value points to the same socket file path. as follows: 

[mysqld]
datadir=/storage/db/mysql
socket=/storage/db/mysql/mysql.sock
… omit n rows

[client]
default-character-set=utf8
socket=/storage/db/mysql/mysql. sock

[mysql]
default-character-set=utf8
socket=/storage/db/mysql/mysql.sock

After modification, restart the mysqld service to solve this problem.

Solution 2: (Little Seven is a problem solved by this method)
　　Use the “ln -s /storage/db/mysql/mysql.sock /var/lib/mysql/mysql.sock” command to set the correct socket file location, This problem can be solved by soft linking to the path location of the socket file indicating the error:

[root@lam7 opt]# ls /var/lib/mysql/mysql.sock
ls: Cannot access /var/lib/mysql/mysql.sock: No such file or directory

[root@lam7 opt]# ln -s /storage/db/mysql/mysql.sock /var/lib/mysql/mysql.sock
[root@lam7 opt]# ls /var/lib/mysql/mysql.sock
/var /lib/mysql/mysql.sock

Read More:

Leave a Reply

Your email address will not be published. Required fields are marked *