2

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
  • Do you mean that host <host> is the host that runs Monit, and that is is blocked by MySql because <host> failed to connect to db1.server too many times ? – krisFR Feb 27 '14 at 14:22
  • <host> 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 8:08
1

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 :

  • 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 3 '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 3 '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 4 '14 at 10:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.