Alex Le’s Blog

I was trying to setup a MySQL cluster with CentOS 4.4 and VMWare and I was initially successful at getting the NDB storage engine to work. However, I could not start the MySQL API (or MySQL client, whatever you call it) because I ran into the infamous MySQL error of “ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (2)“. Googling didn’t help much either because people were just asking and very few got the answer.

After a while, I was desparate. My cluster was up and I had no way to connect to it and test out the exciting cluster’s replicate feature. I went to bed at 4:00am last night and kept thinking what could go wrong? Setting up the ndb engine for the nodes was pretty straightforward but why just getting mysql client to run is so problematic? Well, here’s my solution to the puzzle:

My Setup

• CentOS 4.4 installed from DVD with most packages unchecked, especially MySQL
• The newest MySQL 5.0.27-max was used. See, I’m on the bleeding-side of technology.
• MySQL is locaed in the default location at /usr/local/mysql/
• When I installed MySQL, the mysql.server script was cp from /usr/local/mysql/support-files/mysql.server to /etc/init.d/
• The my.cnf file is very simple like this

#file: /etc/my.cnf
ndbcluster
ndb-connectstring=’host=192.168.2.100′
[mysql_cluster]
ndb-connectring=’host=192.168.2.100′

Cause:

When the MySQL server (mysqld) starts, it created a sock file. Since I overwrote the original /etc/my.cnf without the socket parameter under [mysqld], the MySQL server (mysqld) created the socket file under /tmp/mysql.sock instead. Furthermore, I was also missing the socket parameter under [client] section in the my.cnf - I didn’t even have the [client] section.

What happens when I tried to start the mysql client (#mysql -u root) was that the mysql client was looking for the mysql.sock file but coudln’t find the path to the file. It then looked into the default path /usr/lib/mysql/ to find the mysql.sock file. Of course this doesn’t work because when mysqld was started, the socket file was created in /tmp instead. The client gave up, spit out the connect error prompt.

In brief, the cause of the infamous error is because the mysql client cannot find the socket file at run time.

Solutions

First of all, you should verify that mysqld is running by typing #ps aux | grep [m]ysqld. Then there are 2 ways to solve this: one is to fix the socket path in the /etc/my.cnf under [mysqld] and [client] like this

# file: /etc/my.cnf
[mysqld]
… # someother settings
socket=/var/lib/mysql/mysql.sock
[client]
… # some other settings
socket=/var/lib/mysql/mysql.sock

then restart the mysqld server so that the mysql.sock file is generated. The next time you start the client with #mysql -u root, you will magically get it. This fix is permanently but the catch is you will have to shutdown mysqld, the MySQL server itself.
The other way when you want to just login really fast to the mysql server, try this (actually I used this method to obtain the overall solution to the infamous error)

• #find / | grep mysql.sock
  this will do a look up on the entire disk and look for the mysql.sock file. You will get back something like /tmp/mysql.sock. This is the socket file we will need to use.
• #mysql -u root –socket=/tmp/mysql.sock
  this will tell the mysql client to login as root user and use the socket file located in /tmp/mysql.sock

Boom, you should get into the suddenly-loved mysql> prompt. I was so excited when I was able to fix the client and was able to access to the mysql prompt.

I hope this help you all. With this success in installing the MySQL 5.0.27 cluster, I am planning to do a screencast of setting the whole thing up (VMWare is just awesome. It allows me to have 3 CentOS 4.4 servers at the same time, making this installation a breeze)