lost and found ( for me ? )

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

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.