lost and found ( for me ? )

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



[ 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