Re: Killing MySQL connections on a specific interface from a specific host

[Dominik Klein]

I am not really sure why you would need this , but I am just throwing in 
a possible solution..
 
First I would say since you need to kill connections on a specific 
interface (eth0), It would be fair to assume that you have more than one 
interface, if thats the case and you don't have the "--bind-address" 
option set in my.cnf, then your mysqld daemon would listen on all the 
available IP's on all interfaces, 


ACK. And that's what I need.

then it is vey difficult to know on 
what IP did mysql serve a specific connection, unless you would do a 
netstat, then correspond that  IP to the clients IP in "show 
processlist", etc etc , so pretty cumbersume...


That's what I've done so far ...
I can list and grep all connections on the interface and get the client 
IP. I can also get the corresponding MySQL Thread-IDs from the 
mysql-processlist. But I could not find a way to only select connections 
from a specific Client IP to a specific Server IP, as the Processlist 
only shows the Client-address.


But unless there is a real need , you can just have the deamon to listen 
only on one specific IP residing on eth0, like this

--bind-address= xxx.xxx.xx.x ( this IP resides on eth0)


The Server has to listen on all (two) interfaces and clients can connect 
to both.


If this is feasible in your setup, then killing threads from a specific 
IP should be easy, if you need to kill threads manually then use a toll 
like mytop (http://jeremy.zawodny.com/mysql/mytop/ 
), or if you want it automated 
then you could easily write a perl script which would parse the output 
of "show full processlist", get all the connections from a specific 
client IP, and KILL them ...


Well, that's what I've done ...
But it also kills connections from the client to another interface.

I know this is somewhat special and it would take quite a while to 
explain why exactly I need this.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Killing MySQL connections on a specific interface from a specific host

[Kishore Jalleda]

On 4/28/06, Dominik Klein <[EMAIL PROTECTED]> wrote:


Hi

I'd like to know if there is anything to kill connections from a
specific client ip that came to the server on a specific interface.
I do not want to block them on layer 2 (which could easily be done with
netfilter), I would like to be able to kill active connections.

For example: I would like to kill connections from 192.168.50.3 that
came in on interface eth0.
Connections from that client IP to another interface should not be
affected.

Of course one could script something using lsof -i or netstat and the
mysql processlist, but that would end in some nasty shellscript and I
don't know how to only kill connections for one interface as the mysql
processlist only shows the client ip, not the ip, the client connected to.

Thanks for any ideas
Dominik

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]




I am not really sure why you would need this , but I am just throwing in a
possible solution..

First I would say since you need to kill connections on a specific interface
(eth0), It would be fair to assume that you have more than one interface,
if thats the case and you don't have the "--bind-address" option set in
my.cnf, then your mysqld daemon would listen on all the available IP's on
all interfaces, then it is vey difficult to know on what IP did mysql serve
a specific connection, unless you would do a netstat, then correspond
that  IP to the clients IP in "show processlist", etc etc , so pretty
cumbersume...

But unless there is a real need , you can just have the deamon to listen
only on one specific IP residing on eth0, like this
--bind-address= xxx.xxx.xx.x ( this IP resides on eth0)

If this is feasible in your setup, then killing threads from a specific IP
should be easy, if you need to kill threads manually then use a toll like
mytop (http://jeremy.zawodny.com/mysql/mytop/), or if you want it automated
then you could easily write a perl script which would parse the output of
"show full processlist", get all the connections from a specific client IP,
and KILL them ...

Hope this helps

Kishore Jalleda
http://kjalleda.googlepages.com/projects

Killing MySQL connections on a specific interface from a specific host

[Dominik Klein]

Hi

I'd like to know if there is anything to kill connections from a 
specific client ip that came to the server on a specific interface.
I do not want to block them on layer 2 (which could easily be done with 
netfilter), I would like to be able to kill active connections.


For example: I would like to kill connections from 192.168.50.3 that 
came in on interface eth0.

Connections from that client IP to another interface should not be affected.

Of course one could script something using lsof -i or netstat and the 
mysql processlist, but that would end in some nasty shellscript and I 
don't know how to only kill connections for one interface as the mysql 
processlist only shows the client ip, not the ip, the client connected to.


Thanks for any ideas
Dominik

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]