Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) on subsequent connection through terminal

Question

My sincere apologies for asking something that obviously has been answered before. The fact that I'm posting is that i would like to be absolutely sure since it's about a live server running currently.

This is what happened. I'm accidentally the sysadmin for a dedicated server. There is tomcat running on the machine and using mysql as database. I in the process of installing OpenCart happen to install curl, gd and mysql through

 apt-get install curl libcurl3 libcurl3-dev php5-curl
 restart apache2
 apt-get install php5-gd
 restart apache2
 apt-get install libapache2-mod-auth-mysql php5-mysql 
 restart apache2

I was connected before with mysql -u<username> -p and everything was just fine. Now it gives

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

Reading online and on ServerFault these are the facts:

#/var/lib# /etc/init.d/mysqld start
 -bash: /etc/init.d/mysqld: No such file or directory
#/var/lib# netstat -lntp | grep mysqld
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      29570/mysqld  
#/var/lib# ps -ef | grep mysqld
root     13684 13580  0 09:25 pts/3    00:00:00 grep mysqld
root     29321     1  0  2011 ?        00:00:00 /bin/sh bin/mysqld_safe --user=mysql
mysql    29570 29321  0  2011 ?        03:58:46 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/usr/local/mysql/data/server.host.com.err --pid-file=/usr/local/mysql/data/server.host.com.pid --socket=/tmp/mysql.sock --port=3306

This is how /etc/mysql/my.cnf looks like :

 [client]
 port            = 3306
 socket          = /var/run/mysqld/mysqld.sock

 [mysqld_safe]
 socket          = /var/run/mysqld/mysqld.sock
 nice            = 0

 [mysqld]
 user            = mysql
 socket          = /var/run/mysqld/mysqld.sock
 port            = 3306
 basedir         = /usr
 datadir         = /var/lib/mysql
 tmpdir          = /tmp
 skip-external-locking


 dpkg -l | grep mysql
 rc  libapache2-mod-auth-mysql       4.3.9-12ubuntu1              Apache 2 module for MySQL authentication
ii  libmysqlclient16                5.1.63-0ubuntu0.10.04.1      MySQL database client library
ii  mysql-common                    5.1.63-0ubuntu0.10.04.1      MySQL database common files (e.g. /etc/mysql
ii  php5-mysql                      5.3.2-1ubuntu4.17            MySQL module for php5

[EDIT]

apparently there are 2 my.cnf files

# ls -al /etc/my.cnf
-rw-r--r-- 1 root root 4715 2011-11-30 12:52 /etc/my.cnf
# ls -al /etc/mysql/my.cnf 
-rw-r--r-- 1 root root 3564 2012-06-11 14:11 /etc/mysql/my.cnf

below is the configuration of /etc/my.cnf

[client]
#password       = your_password
port            = 3306
socket          = /tmp/mysql.sock

# The MySQL server
[mysqld]
datadir         =/usr/local/mysql/data
port            = 3306
socket          = /tmp/mysql.sock

and the content of `/usr/local/mysql/ is below

/usr/local/mysql# ls -l
total 80
drwxr-xr-x  2 root  root  4096 2011-11-30 07:56 bin
-rw-r--r--  1 root  root 17987 2011-10-12 12:10 COPYING
drwx------ 11 mysql root  4096 2012-08-21 07:43 data
-rw-------  1 root  root   346 2011-11-30 13:09 DEADJOE
drwxr-xr-x  2 root  root  4096 2011-11-30 07:55 docs
drwxr-xr-x  3 root  root  4096 2011-11-30 07:55 include
-rw-r--r--  1 root  root  7604 2011-10-12 12:10 INSTALL-BINARY
drwxr-xr-x  3 root  root  4096 2011-11-30 07:56 lib
drwxr-xr-x  4 root  root  4096 2011-11-30 07:55 man
drwxr-xr-x 10 root  root  4096 2011-11-30 07:56 mysql-test
-rw-r--r--  1 root  root  2552 2011-10-12 12:10 README
drwxr-xr-x  2 root  root  4096 2011-11-30 07:56 scripts
drwxr-xr-x 27 root  root  4096 2011-11-30 07:56 share
drwxr-xr-x  4 root  root  4096 2011-11-30 07:56 sql-bench
drwxr-xr-x  2 root  root  4096 2011-11-30 07:55 support-files

There is no mysqld.sock in /var/run/mysqld and there is no /var/lib/mysql but there is /usr/local/mysql/data containing mysql data and /tmp/mysqld.sock

So, what are my options here ?

Step 1 back up /etc/mysql/my.cnf

Step 2 remove /etc/mysql/my.cnf

Step 4 call /etc/init.d/mysql start (actually there is no mysql in /etc/init.d/ by mysql.server, am totally confused about this one)

So if i understand well. installing php5-mysql installed mysql-commons which created /etc/mysql/my.cnf meanwhile i already have /etc/my.cnf

Please advise on the issue as I don't want to make mistake and running commands with my own presumptions. Thanks for reading this.

Solution 1

OK, this is a mess.
To address some of your issues/questions:

Why is tomcat still running ok?
Tomcat is possibly connecting via the network and not via the socket.
Also mysqld will not re-read the my.cnf while it is running.
The missing/moved datadir will be a problem on the next restart, not during runtime.

Why can't you login via mysql -u ... anymore?
You now have 2 my.cnf files.
The order of files is important, mysql will first read /etc/my.cnf and then /etc/mysql/my.cnf. If options are defined in both locations, the last one will win (http://dev.mysql.com/doc/refman/5.1/en/option-files.html).

What are your options now?
If you remove /etc/mysql/my.cnf, or replace the content of it with /etc/my.cnf, you will have the same behavior as before.
This would work for now, but I would advice to change to a clean installation from a package in the future.

Solution 2

Use the following command in the terminal.

sudo ln -s {your current .sock location} {path in the error}

e.g.

sudo ln -s /opt/lampp/var/mysql/mysql.sock /var/run/mysqld/mysqld.sock

This will correct the error.

N.B : you have to check whether the mysqld folder exists before running this command. if does not exist do create the folder before running the command.

Solution 3

MySQL server is not installed yet according to dpkg, you need to install it using apt-get install mysql-server

Solution 4

As @faker said /etc/init.d/mysql.server and /usr/local/mysql indicates that your Mysql Server is installed from sources. It's not worth to maintain compiled mysql instance if you have pretty mysql binary with Ubuntu compatibile scripts (like debian start scripts for upgrades , repairing tables on boot etc).

Also - do not ever copy sockets - it's better to make symbolic link :)

View more solutions

Comments

[black sensei]

But how come the tomcat application is still running? before i run into this issue i could log in mysql server through command line. So where could it vanish to? am more confused

[Alex]

Most probably MySQL has been installed from sources not via the standard package manager.

[faker]

To me this looks like someone manually installed MySQL into /usr/local/mysql, installing php5-mysql will have pulled mysql-common, possibly overwriting the already present /etc/my.cnf (although it should use /etc/mysql/).

[Alex]

So basically you need to tweak the [client] section of your my.cnf to set up the correct location of mysqld.sock (it should be /tmp/mysqld.sock I guess). You can't just copy the socket file because it will be recreated in the old location on MySQL restart.

[Alex]

I recommend you to perform a full database dump and reinstall MySQL from packages.

[black sensei]

Hello i've just edited the file, it looks like mysql was installed from source, i think reason was to get mysql 5.5 on ubuntu 10.04 @faker actually pointed me on that direction. i've copied all in /usr/local/mysql/data to somewhere else. Reinstalling mysql is a decision i can't make right now. Since Tomcat server is still runing. Any other options?

[Alex]

Well if the server is running and if you can connect to it after editing the [client] section of /etc/mysql/my.cnf you should probably leave everything as is.

[black sensei]

Am actually thinking of removing the /etc/mysql/my.cnf after making a back up and leave the rest to /etc/my.cnf

[faker]

With "same behavior as before" I mean before the incident, so it will work correctly.

[black sensei]

by the way will the /etc/mysql/my.cnf be recreated when mysql restart?

[faker]

No, a restart will not recreate it. It will be recreated when you install/update the mysql-common package.