MySQL-Python : check MySQL availability from a remote host with MySQL-Python

Here’s a description of how to monitor MySQL over a remote host with MySQL-Python.

on the MySQL server , create a use who connects to the MySQL server from a remote host for health check ( in this case , user1 )

add all privileges to user1

# mysql –u root –p mysql> grant all privileges on *.* to user1@"%"    -> identified by 'pass' with grant option; mysql> mysql> grant all privileges on *.* to user1@localhost    -> identified by 'pass' with grant option; Query OK, 0 rows affected (0.00 sec) mysql> quit Bye

This script checks whether or not MySQL server returns the string “Alive”.

And If MySQL returns the string “Alive” , this script regards MySQL as Alive.

check DB : testdb

check table : Testtable

the remote host : 192.168.0.10

the SQL server : 192.168.0.1

# less -N monitor_mySQL.py      1 #!/usr/bin/env python      2      3 ### usage      4 # monitor_mySQL.py --ipaddr=<ip address> --port=<port number>      5 ###      6      7 import MySQLdb      8 import sys      9     10 argvs = sys.argv     11 argc = len(argvs)     12     13 #print argvs     14 #print argc     15     16 ipaddr = argvs[1].split('=')     17 port_number = argvs[2].split('=')     18     19 #print hostname[1]     20 #print int(port_number[1])     21     22 conn = None     23     24 try:     25         conn = MySQLdb.connect(     26 #               host = "localhost",     27                 host = ipaddr[1],     28                 user = "user1",     29                 passwd = "pass",     30                 db ="testdb",     31                 port = int(port_number[1]))     32 #               port = 3306)     33         cur = conn.cursor()     34         cur.execute("SELECT * FROM  Monitortable")     35         numrows = int(cur.rowcount)     36     37 except MySQLdb.Error, e:     38         print "Can't connect to MySQL";     39         sys.exit(1)     40     41 for i in range(numrows):     42         row = cur.fetchone()     43     44 if ( "Alive" == row[1]):     45         print "Alive"     46         cur.close()     47         conn.close()     48         sys.exit(0)     49 else:     50         print "String does not match"     51         cur.close()     52         conn.close()     53         sys.exit(1)     54

on the SQL server

# mysql -u root -p Enter password: Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 723 Server version: 5.1.61 Source distribution Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use testdb; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from Testtable where Id=1; +----+-------+ | Id | Name  | +----+-------+ |  1 | Alive | +----+-------+ 1 row in set (0.00 sec) mysql> quit Bye

when the SQL server is Alive and returns the string “Alive”.

on the remot host

connect to the MySQL server

# ./monitor_mySQL.py --ipaddr=192.168.0.1 --port=3306 Alive

cap data on MySQL server

# tshark -i eth0 port 3306 Running as user "root" and group "root". This could be dangerous. Capturing on eth0  0.000000   192.168.0.10 -> 192.168.0.1     TCP 57557 > mysql [SYN] Seq=0 Win=14600 Len=0 MSS=1460 TSV=110390452 TSER=0 WS=6  0.000025     192.168.0.1 -> 192.168.0.10   TCP mysql > 57557 [SYN, ACK] Seq=0 Ack=1 Win=14480 Len=0 MSS=1460 TSV=110430473 TSER=110390452 WS=5  0.000563   192.168.0.10 -> 192.168.0.1     TCP 57557 > mysql [ACK] Seq=1 Ack=1 Win=14656 Len=0 TSV=110390453 TSER=110430473  0.002412     192.168.0.1 -> 192.168.0.10   MySQL Server Greeting proto=10 version=5.1.61  0.002962   192.168.0.10 -> 192.168.0.1     TCP 57557 > mysql [ACK] Seq=1 Ack=57 Win=14656 Len=0 TSV=110390455 TSER=110430476  0.005341   192.168.0.10 -> 192.168.0.1     MySQL Login Request user=user1 db=testdb  0.005397     192.168.0.1 -> 192.168.0.10   TCP mysql > 57557 [ACK] Seq=57 Ack=71 Win=14496 Len=0 TSV=110430479 TSER=110390458  0.005508     192.168.0.1 -> 192.168.0.10   MySQL Response OK  0.006062   192.168.0.10 -> 192.168.0.1     MySQL Request Query  0.006154     192.168.0.1 -> 192.168.0.10   MySQL Response OK  0.006825   192.168.0.10 -> 192.168.0.1     MySQL Request Query  0.006970     192.168.0.1 -> 192.168.0.10   MySQL Response  0.008096   192.168.0.10 -> 192.168.0.1     MySQL Request Quit  0.008107   192.168.0.10 -> 192.168.0.1     TCP 57557 > mysql [FIN, ACK] Seq=129 Ack=238 Win=15680 Len=0 TSV=110390461 TSER=110430480  0.008184     192.168.0.1 -> 192.168.0.10   TCP mysql > 57557 [FIN, ACK] Seq=238 Ack=130 Win=14496 Len=0 TSV=110430481 TSER=110390460  0.008555   192.168.0.10 -> 192.168.0.1     TCP 57557 > mysql [ACK] Seq=130 Ack=239 Win=15680 Len=0 TSV=110390461 TSER=110430481 16 packets captured

If the string is not “Alive”

on the SQL server

mysql> use testdb; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from Monitortable; +----+------+ | Id | Name | +----+------+ |  1 | Yhea | +----+------+ 1 row in set (0.00 sec)

on the remote host

# ./monitor_mySQL.py --ipaddr=192.168.0.1 --port=3306 String does not match

when the SQL server is down.

on the SQL server

# /etc/init.d/mysqld stop

on the remote host

# ./monitor_mySQL.py --ipaddr=192.168.0.1 --port=3306 Can't connect to MySQL