Solution to errors related to PID File loss in MySQL _ MySQL

This article describes how to solve the problems related to PID File loss in MySQL. the specific prompt may be mysqlPIDfilenotfoundandCan 'tconnecttomysqlthroughsocketmysql. sock, A friend who needs it can refer to my colleague A today to find me, saying that Mysql server X has A high load and the query is slow. After a while of failure, we watched it together.

[root@redhat var]# uname -aLinux 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 1 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] Aborting100708 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 use100708 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 stopMySQL is running but PID file could 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

The code is as follows:

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.

The code is as follows:

Killed 799

Then run

The code is as follows:

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
 
  
<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.
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]# diff /etc/my.cnf /etc/my.cnf.bak

At this time

# Switch to mysql data dir (in 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 the redhat here. pid 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 restartShutting 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.

That's all.