Mysql PID file not found and Can't connect to MySQL through socket mysql. sock

Today, my colleague A found me that Mysql server X has A high load and queries are slow. After a while of failure, we watched it together.

[Root @ redhat var] # uname-

Linux xxx 2.6.18-128. el5 #1 SMP Wed Dec 17 11:41:38 EST 2008 x86_64 x86_64 x86_64 GNU/Linux

[Root @ redhat var] # mysql-u root-p-e "select version ();"

+ ------------ +

| Version () |

+ ------------ +

| 5.1.32-log |

+ ------------ +

 

Colleague A's operations:

A. Check that there is A problem with mysql server. The first response is to restart mysql server !! O (random □random)

However, an incorrect command is used.

[Root @ redhat var] #/var/lib/mysql/libexec/mysqld restart ---- operation ①

100708 14:43:53 [ERROR] Fatal error: Please read "Security" section of the manual to find out how to run mysqld as root!

100708 14:43:53 [ERROR] Aborting

100708 14:43:53 [Note]/var/lib/mysql/libexec/mysqld: Shutdown complete

 

After the problem was found, he thought again that he should restart it with the following command:

[Root @ redhat var] # service mysql restart ---- operation ②

MySQL manager or server PID file cocould not be found! [FAILED]

Starting MySQL ......

 

CTRL + C cancel

 

At this time

[Root @ redhat var] # ps aux | grep mysql

As you can see, the system started another Mysql process, but after a while, it will disappear automatically. At this time, you can see the following errors when reading the log:

100708 15:26:52 [ERROR] Can't start server: Bind on TCP/IP port: Address already in use

100708 15:26:52 [ERROR] Do you already have another mysqld server running on port: 30017?

100708 15:26:52 [ERROR] Aborting

 

Then let's take a look at it later.

First, I used the client tool to connect and found that mysql is normal. The web application connects to the database normally, but the query is slow.

Next, I connected to the following command:

[Root @ redhat var] # mysql-u root-p

Tip:

Enter password:

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

 

At this time, colleague A reminded me that it is okay to restart the service. Restart, o (restart □restart) o, he suggested that I restart to solve the problem.

Okay, as he wished. So I first:

[Root @ redhat var] # service mysql stop

MySQL is running but PID file cocould not be found

 

Then go to mysql data dir and check that there is no pid file.

At this time, my first response was that the configuration file was incorrect, resulting in abnormal stop and restart.

Because the server is good, I am not in a rush to compare the previously backed up/etc/my. cnf. bak and/etc/my. cnf.

First, find the cause of high load. Because mysql cannot be accessed under the command line, use

Show processlist;

 

We can see that there are many locked queries in it. One of the longest waiting periods is a select query, which shows that the data is being sending, and the rest are locked.

It is assumed that the sending data thread occupies "all resources allocated to mysql", causing all subsequent threads to be suspended, because "the query (thread) is executed in sequence ", the locked thread is waiting for the end of the sending data thread. (This is a conjecture ...)

The thread U of sending data is a select query, which connects 6 tables (a query submitted by an intern of the company ), there are two tables with a data volume of about, and one data table with a data volume of about, plus sum (distinct), group by, order... Imagine... I don't know what year and month the query will be executed.

The processid of this sending data slow query is 799, And it is running on the fly.

Killed 799

 

Then run

Show processlist;

 

We can see that the locked threads are executed one by one, and there are many select threads similar to thread U. After all the select threads are killed, the other normal Update, select, the insert operation will soon be completed.

Then, the web application returns to normal, and the speed becomes faster.

Return to the linux Command Line and use

[Root @ redhat var] # top

<Shift + m sort by memory usage>

<1 Display cpu usage>

 

At this time, we can find that the server load returns to normal.

 

The following solution fails to shut down and restart properly.

This is because of misoperations of previous colleagues.

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

And MySQL manager or server PID file cocould not be found! [FAILED]

.

I did not suspect that the configuration file contains any unrecognized Parameter options. Http://www.my400800.cn

Pass

[Root @ redhat var] # diff/etc/my. cnf/etc/my. cnf. bak

The configuration file is correct.

 

# My server's hostname. The default mysql pid file name is hostname. pid, if not in/etc/my. in cnf, If you specify a specific pid filename and pid file path, this file is associated with mysql Data.

[Root @ redhat var] # hostname

Redhat

 

MySQL is running but PID file cocould not be found Solution

At this time

# Switch to mysql data dir (under the mysql data file directory)

# Your mysql data dir may be different from mine. My options are/var/lib/mysql/var/

[Root @ redhat var] # cd/var/lib/mysql/var/

 

Obtain the pid of the mysql process run by the mysql user and import it to the hostname. pid file.

[Root @ redhat var] # echo 'ps aux | grep mysql | grep "user = mysql" | grep-v "grep" | awk '{print $2} ''> redhat. pid

# Note that the redhat. pid here is related to hostname. Here is my hostname. pid

 

Change the file owner and owner group to mysql: mysql.

[Root @ redhat var] # chown mysql: mysql redhat. pid

 

Then run

[Root @ redhat var] # mysql-u root-p

The following message is displayed:

Enter password:

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

 

[Root @ redhat var] # ls/tmp | grep sock

There is no mysql. sock file.

 

However

[Root @ redhat var] # service mysql status

Display

MySQL running (10949) [OK]

 

Well, the pid file resumes normal and then runs

[Root @ redhat var] # service mysql restart

Shutting down MySQL. [OK]

Starting MySQL. [OK]

 

Run again at this time

[Root @ redhat var] # ls/tmp | grep sock

You can find that the mysql. sock file is available under/tmp after the restart.

 

You can search for mysql. sock usage and usage.

 

Colleague A's operations (①) lead to the loss of the pid file, and operation (②) lead to mysql. the sock file is lost. If you are interested, go to vi mysqld script and server mysql script, and then run the service mysql status/stop/start/restart exception, an error occurred while logging on to mysql under mysql-u root-p in the command line.