mysql - 如何找到拥有本地 Sleeping MySQL 连接的 Unix 进程？

Question

我的 MySQL 进程遇到“太多连接”问题，我已经到了要点 mysqladmin processlist -uroot -pXXXXX 结果:

+------+------------+-----------+------------+---------+------+-------+------------------+
| Id   | User       | Host      | db         | Command | Time | State | Info             |
+------+------------+-----------+------------+---------+------+-------+------------------+
| 842  | svcControl | localhost | svcObjects | Sleep   | 1772 |       |                  |
| 875  | svcControl | localhost | svcObjects | Sleep   | 1773 |       |                  |
| 884  | svcControl | localhost | svcObjects | Sleep   | 1770 |       |                  |
| 896  | svcControl | localhost | svcObjects | Sleep   | 1540 |       |                  |
| 951  | svcControl | localhost | svcObjects | Sleep   | 709  |       |                  |
| 1023 | svcControl | localhost | svcObjects | Sleep   | 708  |       |                  |
| 1063 | svcControl | localhost | svcObjects | Sleep   | 708  |       |                  |
| 1069 | svcControl | localhost | svcObjects | Sleep   | 708  |       |                  |
| 1075 | svcControl | localhost | svcObjects | Sleep   | 707  |       |                  |
| 1083 | svcControl | localhost | svcObjects | Sleep   | 707  |       |                  |
| 1091 | svcControl | localhost | svcObjects | Sleep   | 706  |       |                  |
| 1097 | svcControl | localhost | svcObjects | Sleep   | 706  |       |                  |
| 1107 | svcControl | localhost | svcObjects | Sleep   | 705  |       |                  |
| 1112 | svcControl | localhost | svcObjects | Sleep   | 702  |       |                  |
| 1120 | svcControl | localhost | svcObjects | Sleep   | 704  |       |                  |
| 1127 | svcControl | localhost | svcObjects | Sleep   | 704  |       |                  |
| 1136 | svcControl | localhost | svcObjects | Sleep   | 704  |       |                  |
| 1141 | svcControl | localhost | svcObjects | Sleep   | 644  |       |                  |
| 1202 | svcControl | localhost | svcObjects | Sleep   | 268  |       |                  |
| 1222 | svcView    | localhost | svcObjects | Sleep   | 8    |       |                  |
| 1224 | svcControl | localhost | svcObjects | Sleep   | 280  |       |                  |
| 1225 | svcControl | localhost | svcObjects | Sleep   | 8    |       |                  |
| 1265 | root       | localhost |            | Query   | 0    |       | show processlist |
+------+------------+-----------+------------+---------+------+-------+------------------+

很明显，我在某处泄露了连接，但我不知道在哪里。我一直在寻找告诉我如何从连接 ID 获取 Unix 进程 ID 的资源，但大多数方法都希望连接来自远程服务器，从而允许您使用 netstat 来调试它们。要么，要么他们要求您修改代码库以记录每个尝试的连接，这似乎有点矫枉过正。

那么...有没有任何人知道的方法？这个休眠连接将打开的任何文件，以便我可以使用 fuser，例如？或者...这些是否可能是没有拥有进程的僵尸连接？

回应以下回答

如果我运行 ps -ef | grep mysql ，我会看到:

root      5960     1  0 Oct14 ?        00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --user=mysql
mysql     6007  5960  0 Oct14 ?        00:24:12 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --socket=/var/lib/mysql/mysql.sock

运行建议命令，我得到:

# fuser -u /var/lib/mysql/mysql.sock
/var/lib/mysql/mysql.sock:  6007(mysql)

# lsof -p 5960
COMMAND    PID USER   FD   TYPE DEVICE     SIZE    NODE NAME
mysqld_sa 5960 root  cwd    DIR    8,1     4096       2 /
mysqld_sa 5960 root  rtd    DIR    8,1     4096       2 /
mysqld_sa 5960 root  txt    REG    8,1   735004  117216 /bin/bash
mysqld_sa 5960 root  mem    REG    8,1    50848   90123 /lib/libnss_files-2.5.so
mysqld_sa 5960 root  mem    REG    8,1   129832   87812 /lib/ld-2.5.so
mysqld_sa 5960 root  mem    REG    8,1  1689388   90858 /lib/libc-2.5.so
mysqld_sa 5960 root  mem    REG    8,1    20668   90874 /lib/libdl-2.5.so
mysqld_sa 5960 root  mem    REG    8,1    13276   91815 /lib/libtermcap.so.2.0.8
mysqld_sa 5960 root  mem    REG 253,18 56418144  656494 /usr/lib/locale/locale-archive
mysqld_sa 5960 root  mem    REG 253,18    25462 1573763 /usr/lib/gconv/gconv-modules.cache
mysqld_sa 5960 root    0r   CHR    1,3             1432 /dev/null
mysqld_sa 5960 root    1w   CHR    1,3             1432 /dev/null
mysqld_sa 5960 root    2w   CHR    1,3             1432 /dev/null
mysqld_sa 5960 root  255r   REG 253,18    13077 1181028 /usr/bin/mysqld_safe

lsof -p 6007
COMMAND  PID  USER   FD   TYPE     DEVICE      SIZE     NODE NAME
mysqld  6007 mysql  cwd    DIR     253,19      4096    33005 /var/lib/mysql
mysqld  6007 mysql  rtd    DIR        8,1      4096        2 /
mysqld  6007 mysql  txt    REG     253,18   7413588  1998921 /usr/libexec/mysqld
mysqld  6007 mysql  mem    REG        8,1   1296932    91720 /lib/libcrypto.so.0.9.8e
mysqld  6007 mysql  mem    REG     253,18    612180  1514570 /usr/lib/libkrb5.so.3.3
mysqld  6007 mysql  mem    REG        8,1    293108    91722 /lib/libssl.so.0.9.8e
mysqld  6007 mysql  mem    REG        8,1   3200288    87758 /lib/libnss_ldap-2.5.so
mysqld  6007 mysql  mem    REG        8,1     21948    90121 /lib/libnss_dns-2.5.so
mysqld  6007 mysql  mem    REG        8,1    129832    87812 /lib/ld-2.5.so
mysqld  6007 mysql  mem    REG        8,1   1689388    90858 /lib/libc-2.5.so
mysqld  6007 mysql  mem    REG        8,1    216544    90877 /lib/libm-2.5.so
mysqld  6007 mysql  mem    REG        8,1     20668    90874 /lib/libdl-2.5.so
mysqld  6007 mysql  mem    REG        8,1    137908    90866 /lib/libpthread-2.5.so
mysqld  6007 mysql  mem    REG     253,18     75284  1514584 /usr/lib/libz.so.1.2.3
mysqld  6007 mysql  mem    REG        8,1     48156    90867 /lib/librt-2.5.so
mysqld  6007 mysql  mem    REG        8,1     50848    90123 /lib/libnss_files-2.5.so
mysqld  6007 mysql  mem    REG        8,1    245376    90881 /lib/libsepol.so.1
mysqld  6007 mysql  mem    REG        8,1     93508    91676 /lib/libselinux.so.1
mysqld  6007 mysql  mem    REG        8,1     46636    90847 /lib/libgcc_s-4.1.2-20080825.so.1
mysqld  6007 mysql  mem    REG     253,18    936908  1514611 /usr/lib/libstdc++.so.6.0.8
mysqld  6007 mysql  mem    REG        8,1    109740    90873 /lib/libnsl-2.5.so
mysqld  6007 mysql  mem    REG        8,1     80636    90879 /lib/libresolv-2.5.so
mysqld  6007 mysql  mem    REG        8,1      7748    91684 /lib/libcom_err.so.2.1
mysqld  6007 mysql  mem    REG     253,18    190712  1514583 /usr/lib/libgssapi_krb5.so.2.2
mysqld  6007 mysql  mem    REG     253,18    157304  1514569 /usr/lib/libk5crypto.so.3.1
mysqld  6007 mysql  mem    REG        8,1      8072    90878 /lib/libkeyutils-1.2.so
mysqld  6007 mysql  mem    REG     253,18     33712  1509918 /usr/lib/libkrb5support.so.0.1
mysqld  6007 mysql  mem    REG        8,1     45432    90876 /lib/libcrypt-2.5.so
mysqld  6007 mysql    0r   CHR        1,3               1432 /dev/null
mysqld  6007 mysql    1w   REG     253,19    251337   163863 /var/log/mysqld.log
mysqld  6007 mysql    2w   REG     253,19    251337   163863 /var/log/mysqld.log
mysqld  6007 mysql    3u  IPv4   26825288                TCP monstermunch.ssd.hursley.ibm.com:59850->hurgsa.hursley.uk.ibm.com:ldap (CLOSE_WAIT)
mysqld  6007 mysql    4uW  REG     253,19 161480704    33142 /var/lib/mysql/ibdata1
mysqld  6007 mysql    5u   REG     253,16         0       13 /tmp/ibmydRNj (deleted)
mysqld  6007 mysql    6u   REG     253,16        20       14 /tmp/ib59yuxj (deleted)
mysqld  6007 mysql    7u   REG     253,16         0       15 /tmp/ib0Vp8gj (deleted)
mysqld  6007 mysql    8u   REG     253,16         0       16 /tmp/ibjkCG1i (deleted)
mysqld  6007 mysql    9uW  REG     253,19   5242880    33143 /var/lib/mysql/ib_logfile0
mysqld  6007 mysql   10uW  REG     253,19   5242880    33144 /var/lib/mysql/ib_logfile1
mysqld  6007 mysql   11u  IPv4   26825294                TCP *:mysql (LISTEN)
mysqld  6007 mysql   12u   REG     253,16         0       17 /tmp/ibKpCJ1i (deleted)
mysqld  6007 mysql   13u  unix 0xea432900           26825295 /var/lib/mysql/mysql.sock
mysqld  6007 mysql   14u   REG     253,19     30720    65557 <DB Table>
mysqld  6007 mysql   15u  unix 0xf5188c80           30430332 /var/lib/mysql/mysql.sock
mysqld  6007 mysql   16u   REG     253,19      3072    98468 <DB Table>
mysqld  6007 mysql   17u  unix 0xf5925680           30433174 /var/lib/mysql/mysql.sock
mysqld  6007 mysql   18u   REG     253,19      1024    65605 <DB Table>
mysqld  6007 mysql   19u  unix 0xf5188880           30430336 /var/lib/mysql/mysql.sock
mysqld  6007 mysql   20u   REG     253,19        40    98469 <DB Table>
mysqld  6007 mysql   21u  unix 0xf5925280           30433176 /var/lib/mysql/mysql.sock
mysqld  6007 mysql   22u   REG     253,19     39344    65558 <DB Table>
mysqld  6007 mysql   23u   REG     253,19      1024    65623 <DB Table>
mysqld  6007 mysql   24u   REG     253,19         0    65624 <DB Table>
mysqld  6007 mysql   25u  unix 0xf51f6880           30430339 /var/lib/mysql/mysql.sock

在上面的输出中，<DB Table> 是我的编辑。

我可能遗漏了一些东西，但我没有看到任何我保持打开连接的过程......除非那些已删除的/tmp 文件是关键。

Answer 1

如果连接是通过本地套接字建立的，那么使用 sudo fuser -u/tmp/mysql.sock 可能会有些运气。您还可以找到 mysql 的进程 ID(我们称之为 $MYSQL_PID)，然后运行 ​​sudo lsof -p $MYSQL_PID，这将包括所有打开的套接字。