Python : MySQL-Python , optparse

Here’s an example of how to use Python optparse.

# python –version Python 2.6.6

just referred to http://docs.python.org/library/optparse.html.

This script checks whether or not MySQL server return a string you specified.

# cat -n monitor_mySQL.py     1  #!/usr/bin/env python     2  # -*- coding: utf-8 -*-     3     4  ### usage     5  # monitor_mySQL.py --ipaddr=<ip address> --port=<port number> --user=<user> --pass=<password> --db=<DB name> --table=<table name> --checkstring=<string>     6  ###     7     8  import MySQLdb     9  import sys    10  import optparse    11    12  # parse command line options    13  parser = optparse.OptionParser()    14  parser.add_option('--ipaddr',type='string')    15  parser.add_option('--port',type='int')    16  parser.add_option('--user',type='string')    17  parser.add_option('--password',type='string')    18  parser.add_option('--db',type='string')    19  parser.add_option('--table',type='string')    20  parser.add_option('--checkstring',type='string')    21  options, args = parser.parse_args()    22    23  argvs = sys.argv    24  argc = len(argvs)    25    26  if ( 8 != argc ):    27          print "Usage: ./monitor_mySQL.py --ipaddr=<ip address> --port=<port number> --user=<user> --pass=<password> --db=<DB name> --table=<table name>"    28          sys.exit(1)    29    30  ipaddr = options.ipaddr    31  port_number = options.port    32  username = options.user    33  credentials = options.password    34  database = options.db    35  tablename = options.table    36  checkstring = options.checkstring    37    38  conn = None    39    40  try:    41          conn = MySQLdb.connect(    42                  host = ipaddr,    43                  user = username,    44                  passwd = credentials,    45                  db = database,    46                  port = port_number)    47          cur = conn.cursor()    48          cur.execute("SELECT * FROM  %s" % tablename)    49          numrows = int(cur.rowcount)    50    51  except MySQLdb.Error, e:    52          print "Result: Can't connect to MySQL";    53          sys.exit(1)    54    55  for i in range(numrows):    56          row = cur.fetchone()    57    58          if ( checkstring  == row[1]):    59                  print "Result: String matches"    60                  cur.close()    61                  conn.close()    62                  sys.exit(0)    63    64  print "Result: String does not match"    65  cur.close()    66  conn.close()    67  sys.exit(1)    68

- MySQL server

DB : testdb
Table : Testtable

mysql> use testdb mysql> select * from Testtable; +----+------+ | Id | Name | +----+------+ |  1 | foo  | |  2 | zzz  | +----+------+ 2 rows in set (0.00 sec)

Let’s test the script !

# ./monitor_mySQL.py --ipaddr=192.168.11.10 --port=3306 --user=user1 --password=pass --db=testdb --table=Testtable --checkstring=foo Result: String matches

# ./monitor_mySQL.py --ipaddr=192.168.11.10 --port=3306 --user=user1 --password=pass --db=testdb --table=Testtable --checkstring=zzz Result: String matches

# ./monitor_mySQL.py --ipaddr=192.168.11.10 --port=3306 --user=user1 --password=pass --db=testdb --table=Testtable --checkstring=abcde Result: String does not match

This script does not care about the order of the command line options.
So you can also use this script like this

# ./monitor_mySQL.py --table=Testtable –checkstring=foo --ipaddr=192.168.11.10 --port=3306 --user=user1 --password=pass --db=testdb Result: String matches

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

conncect to MySQL with MySQL-Python

just referred to http://zetcode.com/databases/mysqlpythontutorial/

[ MySQL configuration ]

DB name : testdb

table name : test

# mysql -u root -p Enter password: Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 10 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> show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | mysql              | | testdb             | +--------------------+ 3 rows in set (0.00 sec) 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> show tables; +------------------+ | Tables_in_testdb | +------------------+ | test             | +------------------+ 1 row in set (0.00 sec) mysql> select * from test; +------+------+ | num  | name | +------+------+ |    1 | foo  | |    2 | bar  | +------+------+ 2 rows in set (0.00 sec) mysql> quit Bye

[ MySQL-python example ]

install MySQL-python module.

# yum install -y MySQL-python

[ retrieve data ]

# cat retrieve_data_mySQL.py #!/usr/bin/env python import MySQLdb import sys conn = None try:        conn = MySQLdb.connect('localhost','test','pass','testdb')        cur = conn.cursor()        cur.execute("SELECT * FROM test") #       cur.execute("SELECT * FROM test where num=1")        numrows = int(cur.rowcount)        for i in range(numrows):                row = cur.fetchone()                print row[0], row[1] except:        print "error"        sys.exit(1) cur.close() conn.close()

# ./retrieve_data_mySQL.py 1 foo 2 bar

[ insert data ]

# egrep -v ^# insert_data_mySQL.py import MySQLdb import sys conn = None try:        conn = MySQLdb.connect('localhost','test','pass','testdb')        cur = conn.cursor()        sql = u"insert into test values('3','hoge')"        cur.execute(sql)        sql = u"insert into test values('4','hogehoge')"        cur.execute(sql)        conn.commit() except:        print "error"        sys.exit(1) cur.close() conn.close()

before inserting data

# ./retrieve_data_mySQL.py 1 foo 2 bar

insert data

# ./insert_data_mySQL.py # echo $? 0

after inserting data

# ./retrieve_data_mySQL.py 1 foo 2 bar 3 hoge 4 hogehoge

[ delete data ]

# cat delete_data_mySQL.py #!/usr/bin/env python import MySQLdb import sys conn = None try:        conn = MySQLdb.connect('localhost','test','pass','testdb')        cur = conn.cursor() #       cur.execute("SELECT * FROM test") #       cur.execute("SELECT * FROM test where num=1")        cur.execute("DELETE FROM test WHERE num = 3")        cur.execute("DELETE FROM test WHERE num = 4")        conn.commit() except:        print "error"        sys.exit(1) cur.close() conn.close()

# ./delete_data_mySQL.py # ./retrieve_data_mySQL.py 1 foo 2 bar

[ update data ]

mysql> select * from test; +------+----------+ | num  | name     | +------+----------+ |    1 | foo      | |    2 | bar      | |    3 | hoge     | |    4 | hogehoge | +------+----------+ 4 rows in set (0.00 sec) mysql>

I’ll update name “foo” to “foobar”

mysql> select name from test where num=2; +------+ | name | +------+ | bar  | +------+ 1 row in set (0.00 sec) mysql>

# vi update_data_mySQL.py #!/usr/bin/env python import MySQLdb import sys conn = None try:        conn = MySQLdb.connect('localhost','test','pass','testdb')        cur = conn.cursor() #       cur.execute("SELECT * FROM test") #       cur.execute("SELECT * FROM test where num=1")        cur.execute("UPDATE test SET name = %s WHERE num = %s",("foobar","2"))        conn.commit() except:        print "error"        sys.exit(1) cur.close() conn.close()

before updating

# ./retrieve_data_mySQL.py 1 foo 2 bar 3 hoge 4 hogehoge

update

# ./update_data_mySQL.py

after updating

# ./retrieve_data_mySQL.py 1 foo 2 foobar 3 hoge 4 hogehoge

[ create table and insert data ]

# cat create_table_insert_data_mySQL.py #!/usr/bin/env python import MySQLdb import sys conn = None try:        conn = MySQLdb.connect('localhost','test','pass','testdb')        cur = conn.cursor()        cur.execute("CREATE TABLE IF NOT EXISTS Testtable(Id INT PRIMARY KEY AUTO_INCREMENT, Name VARCHAR(20))")        sql = u"INSERT INTO Testtable VALUES('1','foo')"        cur.execute(sql)        sql = u"INSERT INTO Testtable VALUES('2','zzz')"        cur.execute(sql)        conn.commit() except:        print "error"        sys.exit(1) cur.close() conn.close()

# ./create_table_insert_data_mySQL.py

# mysql -u root -p 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> show tables; +------------------+ | Tables_in_testdb | +------------------+ | Testtable        | | test             | +------------------+ 2 rows in set (0.00 sec) mysql> select * from Testtable; +----+------+ | Id | Name | +----+------+ |  1 | foo  | |  2 | zzz  | +----+------+ 2 rows in set (0.00 sec) mysql> quit Bye

[ monitor DB ]

DB : testdb

table : Testtable

mysql> select * from Testtable; +----+-------+ | Id | Name  | +----+-------+ |  1 | Alive | +----+-------+ 1 row in set (0.00 sec)

If the string of row #1 matches Alive , this script regards MySQL as Alive.

# cat monitor_mySQL.py #!/usr/bin/env python import MySQLdb import sys conn = None try:        conn = MySQLdb.connect(                host = "localhost",                user = "test",                passwd = "pass",                db ="testdb",                port = 3306)        cur = conn.cursor()        cur.execute("SELECT * FROM  Testtable")        numrows = int(cur.rowcount) except MySQLdb.Error, e:        print "Can't connect to MySQL";        sys.exit(1) for i in range(numrows):        row = cur.fetchone() if ( "Alive" == row[1]):        print "Alive"        cur.close()        conn.close()        sys.exit(0) else:        print "String does not match"        cur.close()        conn.close()        sys.exit(1)

when the string is Alive

mysql> select * from Testtable where Id=1; +----+-------+ | Id | Name  | +----+-------+ |  1 | Alive | +----+-------+ 1 row in set (0.00 sec)

# ./monitor_mySQL.py Alive

when the string is not “Alive”

mysql> select * from Testtable where Id=1; +----+--------+ | Id | Name   | +----+--------+ |  1 | foobar | +----+--------+ 1 row in set (0.00 sec)

# ./monitor_mySQL.py String does not match

when MySQL is down

# /etc/init.d/mysqld stop Stopping mysqld:  [  OK  ]

# ./monitor_mySQL.py Can't connect to MySQL