unix 连接mysql命令行_如何找到拥有本地休眠MySQL连接的Unix进程?

我正在和我的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

mysqld 6007 mysql 15u unix 0xf5188c80 30430332 /var/lib/mysql/mysql.sock

mysqld 6007 mysql 16u REG 253,19 3072 98468

mysqld 6007 mysql 17u unix 0xf5925680 30433174 /var/lib/mysql/mysql.sock

mysqld 6007 mysql 18u REG 253,19 1024 65605

mysqld 6007 mysql 19u unix 0xf5188880 30430336 /var/lib/mysql/mysql.sock

mysqld 6007 mysql 20u REG 253,19 40 98469

mysqld 6007 mysql 21u unix 0xf5925280 30433176 /var/lib/mysql/mysql.sock

mysqld 6007 mysql 22u REG 253,19 39344 65558

mysqld 6007 mysql 23u REG 253,19 1024 65623

mysqld 6007 mysql 24u REG 253,19 0 65624

mysqld 6007 mysql 25u unix 0xf51f6880 30430339 /var/lib/mysql/mysql.sock

在上面的输出中,

是我的编辑。

我可能遗漏了一些东西,但我看不到任何东西,那就是我保持联系的过程…除非这些已删除的/tmp文件是密钥。