Introduction

The error described in the title of this article is quite unique. Several attempts on solving the problem exist in other articles in the web. But in the context of this article, there is a specific condition that is quite unique so in the end it cause the error occurs. The error occurs with the specific error message. That error message is in the following output message :

root@hostname ~# mysql -uroot -p -h 127.0.0.1 -P 4406
Enter password: 
ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0
root@hostname ~#

The above error message is a consequence of connecting to a MySQL Database Server. It is actually a regular MySQL Database Server running in a machine. But the connection itself is a different one. The connection exist using docker container running process. Below is the actual running process of that docker container :

root@hostname ~# netstat -tulpn | grep 4406
tcp6       0      0 :::4406                 :::*                    LISTEN      31814/docker-proxy  
root@hostname ~#

There are already lots of article discuss abot this error. For an example in this link in the stackoverflow or in this link , and another one in this link , furthermore in this link . The general problem is actually the same. There is something wrong in the running process of the normal MySQL Database Server.

Step for solving the problem

There are several steps for solving the problem above. There are two parts for solving the problem. The first part is for detecting the root cause of the problem. Later on, the second part is the actual solution taken for solving the root cause of that problem. So, the following section which is the first part will focus on trying to search for the cause of the problem.

 

Searching the cause of the problem

In the case of this article, the following is the steps for solving the error :

1. Check whether the MySQL Database Server process is actually running. Execute it as follows using any command pattern available in the operating for checking a running process. In the context of this article, it is ‘systemctl status mysql’. So, the following is an example for the execution of the command pattern :

root@hostname ~# systemctl status mysql
● mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; bad; vendor preset: enabled)
   Active: active (running) since Mon 2019-09-16 13:16:12; 40s ago
  Process: 14867 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid (code=exited, status=0/SUCCESS)
  Process: 14804 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
 Main PID: 14869 (mysqld)
    Tasks: 31 (limit: 4915)
   CGroup: /system.slice/mysql.service
           └─14869 /usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid
...
root@hostname ~#

2. Before connecting to MySQL Database Server using a different port listening to any incoming requests where it is a docker container process handling, just test the normal connection. In other words, connect using the normal port listening in the machine for any incoming connection to MySQL Database Server. Normally, it exists in port ‘3306’. Do it 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 ~# 

The above error message is where the actual root problem is. Check for the actual file which is representing the socket file for mysql daemon process as follows :

root@hostname ~# cd /var/run/mysqld/
root@hostname ~# ls
mysqld.pid  mysql.sock  mysql.sock.lock
root@hostname ~#

Apparently, according to the above output, the file doesn’t exist. That is why the connection to MySQL Database Server is always failed. Eventhough the connection process is done through the default port of ‘3306’.

3. Try to restart the process and hope that it will solve the problem.

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

4. Unfortunately, the above process also ends in failure. Continue on the step for solving the problem, just check the MySQL Database configuration file. After checking the file configuration, apparently, it doesn’t fit at all. Eventually, spending hours for changing the configuration files, nothing happens.

For the cause happens above, check the correct configuration before to see which MySQL Database Server configuration used by the running MySQL Database Server.

 

Checking the MySQL Database Server configuration used by the running MySQL Database Server

In the previous section or part, there is a need to search for the actual configuration file used by the running MySQL Database Server. It is just to make sure that the configuration file used is the correct one. So, every change can bring the right impact on solving the error problem. Below is the step for searching for it :

1. Check the list of the service first by referring to the running process. In the previous part, the running process is the ‘mysql’ one. Execute the following command pattern to list the available running process :

systemctl list-unit-files | grep mysql

The output of the above command pattern for an example is in the following one :

user@hostname:~$ systemctl list-unit-files | grep mysql
mysql.service                                                    bad            
mysqld.service                                                   bad            
user@hostname:~$ 

2. Next, check the content of the service by executing the following command. Choose the correct service, in this context, it is ‘mysql.service’ :

user@hostname:~$ systemctl cat mysql.service
# /lib/systemd/system/mysql.service
# MySQL systemd service file
[Unit]
Description=MySQL Community Server
After=network.target
[Install]
WantedBy=multi-user.target
[Service]
Type=forking
User=mysql
Group=mysql
PIDFile=/run/mysqld/mysqld.pid
PermissionsStartOnly=true
ExecStartPre=/usr/share/mysql/mysql-systemd-start pre
ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid
TimeoutSec=600
Restart=on-failure
RuntimeDirectory=mysqld
RuntimeDirectoryMode=755
LimitNOFILE=5000
user@hostname:~$

3. Apparently, the file responsible for starting the service is in the file ‘/usr/share/mysql/mysql-systemd-start’ according to the output message above. The following is the content of that file which is only part of it :

...
  if [ ! -r /etc/mysql/my.cnf ]; then
    echo "MySQL configuration not found at /etc/mysql/my.cnf. Please create one."
    exit 1
  fi
...

4. After checking the content of the file ‘/etc/mysql/my.cnf’, apparently it is not the correct file. So, in order to be more accurate, there are another way to find out the configuration file used by the running MySQL Database Server. The reference or the information exist in this link. So, according to the information in that link, just execute the following command pattern to get the right one. It is for getting the process ID and also the right MySQL Database Server running process :

root@hostname ~# netstat -tulpn | grep 3306
tcp6       0      0 :::3306                 :::*                    LISTEN      21192/mysqld        
root@hostname ~# ps aux | grep 21192
root@hostname ~# ps aux | grep 21192
mysql    21192  0.2  0.1 3031128 22664 ?       Sl   Sep16   1:39 /usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid
root     25442  0.0  0.0  23960  1068 pts/20   S+   01:41   0:00 grep 21192
root@hostname ~#

After getting the right running process, do the following command of ‘strace file_name_process’ :

root@hostname ~# cd /usr/sbin/
root@hostname ~# strace ./mysqld 

The following is part of the output of the command :

stat("/etc/my.cnf", 0x7fff2e917880)     = -1 ENOENT (No such file or directory)
stat("/etc/mysql/my.cnf", {st_mode=S_IFREG|0644, st_size=839, ...}) = 0
openat(AT_FDCWD, "/etc/mysql/my.cnf", O_RDONLY) = 3
fstat(3, {st_mode=S_IFREG|0644, st_size=839, ...}) = 0
brk(0x35f6000)                          = 0x35f6000
read(3, "#\n# The MySQL database server co"..., 4096) = 839
openat(AT_FDCWD, "/etc/mysql/conf.d/", O_RDONLY|O_NONBLOCK|O_CLOEXEC|O_DIRECTORY) = 4
fstat(4, {st_mode=S_IFDIR|0755, st_size=4096, ...}) = 0
getdents(4, /* 4 entries */, 32768)     = 120
getdents(4, /* 0 entries */, 32768)     = 0
close(4)                                = 0
stat("/etc/mysql/conf.d/mysql.cnf", {st_mode=S_IFREG|0644, st_size=629, ...}) = 0
openat(AT_FDCWD, "/etc/mysql/conf.d/mysql.cnf", O_RDONLY) = 4
fstat(4, {st_mode=S_IFREG|0644, st_size=629, ...}) = 0
read(4, "[mysqld]\n\n# Connection and Threa"..., 4096) = 629
read(4, "", 4096)                       = 0
close(4)                                = 0
stat("/etc/mysql/conf.d/mysqldump.cnf", {st_mode=S_IFREG|0644, st_size=55, ...}) = 0
openat(AT_FDCWD, "/etc/mysql/conf.d/mysqldump.cnf", O_RDONLY) = 4
fstat(4, {st_mode=S_IFREG|0644, st_size=55, ...}) = 0
read(4, "[mysqldump]\nquick\nquote-names\nma"..., 4096) = 55
read(4, "", 4096)                       = 0
close(4)                                = 0
read(3, "", 4096)                       = 0
close(3)                                = 0
stat("/root/.my.cnf", 0x7fff2e917880)   = -1 ENOENT (No such file or directory)

The correct one is eventually in ‘/etc/mysql/conf.d/mysql.cnf’. After checking the content of the file, it is actually an empty file. This is the main cause of it. There has been some update and reverse install version of the MySQL Database Server, it causing some mess to the MySQL Database Server. The solution is just to fill that empty configuration file with the correct configuration. The reference for the correct configuration of MySQL Database Server exist in this link. Restart the MySQL Server again, the above error problem will be solved.