The method of solving the related error of the PID file loss under Mysql _mysql

Today colleague a found me, saying that MySQL server x is loaded very high and the query is slow. After a few moments of his own tinkering, we looked together.

[Root@redhat var]# uname-a
Linux xxx 2.6.18-128.el5 #1 SMP Wed Dec 11:41:38 EST 2008 x86_64 x86_64 x86_64 x

[Root@redhat var]# mysql-u root-p-E "select version ();"
+------------+
| version () |
+------------+
| 5.1.32-log |
+------------+

The operation of colleague A:
A a look at MySQL server has a problem the first response is to restart the MySQL server, embarrassing!! O (╯-╰)
But the wrong command was used.

[Root@redhat var]#/var/lib/mysql/libexec/mysqld Restart----Operation ① 100708 14:43:53
[ERROR] Fatal error:please read "S Ecurity "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 discovering the problem, he remembered that it should be restarted with the following command

[root@redhat var]# service mysql Restart----operation ②
MySQL manager or server PID file could not being found! [FAILED]
Starting MySQL ...

CTRL + C Cancel

When

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

As you can see, the system has started a MySQL process again, but after a while, will automatically disappear, this time to see the log can find the following error:

100708 15:26:52 [ERROR] Can ' t start server:bind on TCP/IP port:address already in use
100708 15:26:52 [ERROR] do Already have another MYSQLD server running on port:30017?
100708 15:26:52 [ERROR] Aborting

And then we look at the back.
First I connect using the client tool to find MySQL normal. The Web application connection database is normal, but the query is slow.
Next I am under the command, connect:

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

Tips:

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

At this point colleague a reminds me that it's okay to restart the service. Embarrassed, O (╯-╰) O, he was advising me to restart the problem.
Well, as he wishes. So I first:

[root@redhat var]# service MySQL stop
MySQL are running but PID file could not being found

Then go to the MySQL data dir below to see, there is no PID file.
At this point my first reaction was that the configuration file was wrong, causing the failure to stop and restart normally.
Because the server is good, I am not in a hurry to compare the previously backed/etc/my.cnf.bak and/ETC/MY.CNF.
Let's find out why the load is high. Because the command line cannot access MySQL, use under Client

Copy Code code as follows:

Show Processlist;

You can see that there are a lot of locked queries, where the longest waiting time is a select query that shows the sending data, and then the rest are locked.
It is assumed that the thread of sending data occupies "all resources assigned to MySQL", causing all subsequent threads to hang all the time, because "the query (thread) is executed sequentially", the following locked thread has been waiting for the thread at the front sending data to end. (This paragraph is conjecture ...)
This thread of sending data U is a select query that connects 6 tables (a query submitted by one of the company's interns), two of which have a data volume of about 10w, and a data table that is about 1000w, plus a sum ( DISTINCT), group By,order by ... Can you imagine ...? I do not know what year and month This query can be completed.
This sending data of the slow query ProcessID 799, a decisive move to run

Copy Code code as follows:

Kill 799

And then run it again.

Copy Code code as follows:

Show Processlist;

You can see that the previous locked thread executes sequentially, followed by a number of thread U-like select threads, all of which have been killed, and the other normal update,select,insert operations that have been blocked are quickly executed.
Then, Web applications get back to normal and fast.
Return to the Linux command line, using the

[Root@redhat var]# top
<shift+m by memory usage sort >
<1 display CPU usage >

At this point the server load is found to be back to normal.

The following resolves the failure to gracefully shut down the reboot.
Which is caused by the misoperation of the previous colleague.

ERROR 2002 (HY000): Can ' t connect to the local MySQL server through socket '/tmp/mysql.sock ' (2)
and MySQL manager or server PID file could not is found! [FAILED]

of errors.
I am not before suspecting that there are any unrecognized parameter options in the configuration file.
Pass

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

Found that there is no problem with the configuration file.

#我的server的hostname, the default name for the MySQL PID file is hostname.pid, and if you do not specify the specific and PID filename and PID files path in/etc/my.cnf, This file is in conjunction with MySQL data.

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

This time through

#切换到mysql Data dir (under the MySQL file directory)
#你们的mysql data dir may not be like mine. Oh, mine is/var/lib/mysql/var/
[root@redhat var]# CD/ var/lib/mysql/var/

Get the MySQL user running the PID of the MySQL process and import it into the Hostname.pid file

[Root@redhat var]# echo ' ps aux | grep MySQL | grep "User=mysql" | Grep-v "grep" | awk ' {print $} ' >> redhat.pid
#注意这里的redhat. PID is related to hostname, this is my hostname.pid.

Change the owner and owner group of the file to Mysql:mysql

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

And then run

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

Or will you be prompted:

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

I didn't mysql.sock this file.

But this time it runs

[Root@redhat var]# service MySQL Status

Show

MySQL running (10949) [OK]

Well, the PID file is back to normal, and then run

[root@redhat var]# service MySQL restart
shutting down MySQL. [OK]
Starting MySQL. [OK]

Then run

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

Can be found after the reboot,/tmp with the Mysql.sock this file.

You can search for the use of the next mysql.sock and to produce and so on.

Colleague A's operation ① causes the PID file to lose, the operation ② causes Mysql.sock file to lose, everybody is interested can go to VI mysqld script and the server MySQL script, then causes the service MySQL status/stop/start/ Restart run an exception, causing the command line mysql-u root-p login to MySQL exception.

That's all.