MySQL Error Message : ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)

This is an article related on how to solve the error a specified in the title of this article. The error message is ‘ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2). These kinds of error happened exactly at the time of the execution command to log in to MySQL Database Server is executed. The command to log in to MySQL Database Server is shown as follows :

root@hostname:~# mysql -uroot
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
root@hostname:~#

First of all, to solve the problem shown in the above output command which is involving MySQL Database Server preventing user from logging in to MySQL Database Server, below is the steps taken :

1. Check the MySQL Database Server’s service. Checking the service can be done in the following article titled ‘Check MySQL Service Status’ in this link. And the result in checking MySQL Database Server turns out that the service is active as shown in the following output :

root@hostname:~# systemctl status mysql                                                                                                                                                                                         
● mysql.service - MySQL Community Server                                                                                                                                                                                          
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)                                                                                                                                            
   Active: active (running) since Tue 2017-09-14 09:59:05; 10s ago                                                                                                                                                            
  Process: 18710 ExecStartPost=/usr/share/mysql/mysql-systemd-start post (code=exited, status=0/SUCCESS)                                                                                                                          
  Process: 18701 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)                                                                                                                            
 Main PID: 18709 (mysqld)                                                                                                                                                                                                         
    Tasks: 13                                                                                                                                                                                                                     
   Memory: 102.9M                                                                                                                                                                                                                 
      CPU: 326ms                                                                                                                                                                                                                  
   CGroup: /system.slice/mysql.service                                                                                                                                                                                            
           └─18709 /usr/sbin/mysqld                                                                                                                                                                                               
Sep 14 09:58:35 hostname systemd[1]: Starting MySQL Community Server...                                                                                                                                                         
Sep 14 09:59:05 hostname systemd[1]: Started MySQL Community Server.
root@hostname:~# mysql -uroot

2. If MySQL Database Service is active, the next step is for checking the error which can specifies the cause of the problem in more detail description. Below is normally the error message is shown in a file named ‘error.log’ located in /var/log/mysql. Depends on the environment and the type of the operating system, the location and the name might be different. In this context of the article, at the time on opening the file named ‘error.log’, there is an error shown as follows :

2017-09-14T02:54:19.205958Z 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
2017-09-14T02:54:19.205993Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.

So, there are two different error message shown and in this context of article, to solve the above problem, the thing which is being done is the following action by stopping MySQL Database Server’s service by executing the following command :

root@hostname:/var/log/mysql# systemctl stop mysql

Start the service again :

root@hostname:/var/log/mysql# systemctl start mysql

After stopping MySQL Database Server, try to remove the file named ibdata1 from the location of /var/lib/mysql to another place such as /home/user/ibdata1. Followed with the action for moving the backup again right away to its original place with its original name. This step is taken using the reference shown in another website. It is assumed to clear any problem regarding the usage of data file ‘ibdata1’. The process can be shown in the following output execution :

root@hostname:/var/lib/mysql# mv ibdata1 /home/user/temporary/ibdata1.backup
root@hostname:/home/user/temporary# ls
boot.log ibdata1.backup server.log server.log.2017-08-31 source
root@hostname:/home/user/temporary# mv ibdata1 ibdata1.backup
root@hostname:/home/user/temporary# mv ibdata1.backup /var/lib/mysql
root@hostname:/home/user/temporary# cd /var/lib/mysql
root@hostname:/var/lib/mysql# mv ibdata1.backup ibdata1
root@hostname:/var/lib/mysql#

But it seems, it doesn’t solve the problem regarding the service access to MySQL Database Server. It looks like the service has met a deadlock situation where the service itself is currently active but it cannot process any request. So, below is another step taken to terminate the current service and try to start the service again :

root@hostname:/var/log/mysql# lsof -i:3306
COMMAND  PID  USER   FD   TYPE  DEVICE SIZE/OFF NODE NAME
mysqld  8534 mysql   16u  IPv4 2745668      0t0  TCP localhost:mysql (LISTEN)
root@hostname:/var/log/mysql# kill -9 8534
root@hostname:/var/log/mysql# systemctl start mysql

The step above is identifying the list of open file where it has port 3306. That number of port itself is representing MySQL Database Server’s service. By retrieving the PID of MySQL Database Server, it can be terminated by force using the ‘kill’ command as shown above.

MySQL Database Error Message after moving database file which is the caused of the problem where the name is ‘ibdata1’ and also force terminate the MySQL Database Server’sprocess, eventually solved the problem where it can be read as shown in the output of the log file of ‘error.log’ located in /var/log/mysql shown below :

...
2017-09-14T05:54:55.872243Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2017-09-14T05:54:56.899481Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2017-09-14T05:54:56.902868Z 0 [Note] InnoDB: Log scan progressed past the checkpoint lsn 2830091726
2017-09-14T05:54:56.902898Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 2830091735
2017-09-14T05:54:56.902906Z 0 [Note] InnoDB: Database was not shutdown normally!
2017-09-14T05:54:56.902911Z 0 [Note] InnoDB: Starting crash recovery.
2017-09-14T05:55:01.727902Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2017-09-14T05:55:01.727944Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2017-09-14T05:55:01.728004Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2017-09-14T05:55:01.888084Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2017-09-14T05:55:01.904778Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2017-09-14T05:55:01.904807Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2017-09-14T05:55:01.950408Z 0 [Note] InnoDB: Waiting for purge to start
2017-09-14T05:55:02.000557Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 19140ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
2017-09-14T05:55:02.001314Z 0 [Note] InnoDB: 5.7.19 started; log sequence number 2830091735
2017-09-14T05:55:02.009677Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2017-09-14T05:55:02.064334Z 0 [Note] Plugin 'FEDERATED' is disabled.
2017-09-14T05:55:02.082507Z 0 [Note] InnoDB: Buffer pool(s) load completed at 170914 12:55:02
2017-09-14T05:55:02.151886Z 0 [ERROR] Function 'archive' already exists
2017-09-14T05:55:02.151908Z 0 [Warning] Couldn't load plugin named 'archive' with soname 'ha_archive.so'.
2017-09-14T05:55:02.151916Z 0 [ERROR] Function 'blackhole' already exists
2017-09-14T05:55:02.151920Z 0 [Warning] Couldn't load plugin named 'blackhole' with soname 'ha_blackhole.so'.
2017-09-14T05:55:02.151933Z 0 [ERROR] Function 'federated' already exists
2017-09-14T05:55:02.151936Z 0 [Warning] Couldn't load plugin named 'federated' with soname 'ha_federated.so'.
2017-09-14T05:55:02.151940Z 0 [ERROR] Function 'innodb' already exists
2017-09-14T05:55:02.151943Z 0 [Warning] Couldn't load plugin named 'innodb' with soname 'ha_innodb.so'.
2017-09-14T05:55:02.182216Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
2017-09-14T05:55:02.182251Z 0 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
2017-09-14T05:55:02.182271Z 0 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
2017-09-14T05:55:02.182296Z 0 [Note] Server socket created on IP: '127.0.0.1'.
2017-09-14T05:55:02.305336Z 0 [ERROR] Incorrect definition of table performance_schema.replication_connection_status: expected column 'RECEIVED_TRANSACTION_SET' at position 7 to have type longtext, found type text.
2017-09-14T05:55:02.309839Z 0 [ERROR] Incorrect definition of table performance_schema.replication_group_member_stats: expected column 'COUNT_TRANSACTIONS_ROWS_VALIDATING' at position 6, found 'COUNT_TRANSACTIONS_VALIDATING'.
2017-09-14T05:55:02.331666Z 0 [Note] Event Scheduler: Loaded 0 events
2017-09-14T05:55:02.331881Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.7.19-0ubuntu0.16.04.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
2017-09-14T05:55:02.331898Z 0 [Note] Executing 'SELECT * FROM INFORMATION_SCHEMA.TABLES;' to get a list of tables using the deprecated partition engine. You may use the startup option '--disable-partition-engine-check' to skip this check.
2017-09-14T05:55:02.331905Z 0 [Note] Beginning of list of non-natively partitioned tables
2017-09-14T05:55:06.024603Z 0 [Note] End of list of non-natively partitioned tables
...

Try to relogin to MySQL Database Server as shown below :

root@hostname:/var/log/mysql# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.19-0ubuntu0.16.04.1 (Ubuntu)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> quit