3

Is there any tweak for monit or another way to monitor whether one server can access mysql on another server?

I tried it with monit but as you probably know, after 10 attempts, mysql blocks the server.

check host db1.server with address db1.server
   if failed port 3306 protocol mysql then alert

.

mysqli: host <host> is blocked because of many connection errors; unblock with mysqladmin flush-hosts
Christian
  • 746
  • 3
  • 13
  • 30
  • Do you mean that host `` is the host that runs Monit, and that is is blocked by MySql because `` failed to connect to `db1.server` too many times ? – krisFR Feb 27 '14 at 14:22
  • is the source server. and it doesnt fail to connect but it doesnt do what mysql considers a successful connection. it only happens with the newer versions of mysql. – Christian Feb 27 '14 at 15:06
  • I have also seen this issue with nagios, and would be interested in an elegant solution – Daniel Widrick Feb 28 '14 at 08:08

1 Answers1

2

Edit (total new answer)

I have Googled a bit about this issue and found some clue :

Seems that MySql does not really like when :

  • a connection is made on port 3306 without authentication
  • a socket is openened and then dropped without any SQL handshaking

So, a workaround could be to perform a real MySql connection and run a query.

Here is a way to do it. You will need to install mysql-client on the monitoring server and grant correct access on MySql server.

Monit check setup :

check program mysql with path "/root/mysql_check.sh"
  if status != 0 then alert

Script mysql_check.sh :

#!/bin/sh
mysql -u<user> -p<password> --host=xx.xx.xx.xx <<END
SHOW VARIABLES LIKE "%version%";
END

Script mysql_check.sh should also be valid for a custom Nagios check.

I have tested this successfully (means can run the query remotely), but as far as i cannot reproduce the blocked host issue on my MySql 5.5 server i cannot tell if this actually solves the issue.

Give it a try and tell me what the result is.

My references :

krisFR
  • 12,830
  • 3
  • 31
  • 40
  • Nope. that doesnt work. – Christian Feb 28 '14 at 11:19
  • @Christian I've edited my answer. Please let me know. – krisFR Feb 28 '14 at 21:03
  • Thats an interesting concept @user.. that should work but unfortunately its not a secure method cause the password would have to be stored in the sh file. of course, i could create a user with VERY basic privileges but still.. not my recommended way cause any access is a security flaw in my view. – Christian Mar 03 '14 at 15:30
  • @Christian Yes, i admit it is a security hole. You could also create a specific DB for monitoring purpose. By now i cannot see another way, sorry... – krisFR Mar 03 '14 at 15:51
  • thanks for your time anyway. great concept you came up with but as our servers are PCI Compliant, I think that would cause us issues – Christian Mar 04 '14 at 10:03
  • thanks for this detail – Kamran Shahid Aug 24 '21 at 07:25