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: Starting MySQL Community Server... Sep 14 09:59:05 hostname systemd: 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