Subnet calculation using pure mysql

You can easily aggregate your records by subnets using mysql thanks to bitwise operators, an inet_aton (ascii to number function) and some thinking...

Here you go:

RMySQL on OSX

I ran into an issue installing RMySQL on OSX. I am using a R from brew and MySQL from the Oracle download page. Trying to install RMySQL would throw up this error

Turns out that the right solution is really easy. Set your MySQL home R env var, make sure that libmysqlclient can be found by R, configure the RMySQL package with the proper inc & lib dirs for MySQL

Tests!

A simple munin graphing script that uses mk-heartbeat to monitor replication lag between two MySQL servers. Place it in your /etc/munin/plugins/ directory and configure your environment.

/etc/munin/plugin-conf.d//mysql

[mysql_*]

env.mysqlconnection DBI:mysql:mysql

env.mysqluser Reporter

env.mysqlpassword Blah

/etc/munin/plugins/mysql_replication_lag

#!/bin/sh
# 
# Plugin to monitor the mysql replication lag
#
# Parameters:
#       
#       config   (required)
#       autoconf (optional - used by munin-config)
#
# $Log$
# Revision 1: Tue Feb 15 17:04:59 MUT 2011
# Initial writeup and testing
#
#
#
# Magic markers (Used by munin-config and some installation scripts.
# Optional):
#
#%# family=auto
#%# capabilities=autoconf

if [ "$1" = "autoconf" ]; then
       echo yes
       exit 0
fi

if [ "$1" = "config" ]; then

       echo 'graph_title Replication Lag'
       echo 'graph_args --base 1000 -l 0'
       echo 'graph_vlabel Replication lag in seconds'
       echo 'graph_category mysql'
       echo 'graph_info This graph monitors the replication lag using mk-heartbeat.'
       echo 'lag.label Lag in seconds'
       echo 'lag.warning :300'
       echo 'lag.warning :3600'
       echo 'lag.info The number of currently open files.'
       exit 0
fi
echo -n "lag.value "
mysql -u ${mysqluser} -p${mysqlpassword} -B -N -e "select now()-ts as replication_lag from maatkit.heartbeat;"

VirtualEnv and CherryPy with MySQL-python

Make some directories
$ mkdir -p ~/project/src ~/project/builds && cd ~/project/src

Grab virtualenv
$ wget http://pypi.python.org/packages/source/v/virtualenv/virtualenv-1.5.1.tar.gz#md5=3daa1f449d5d2ee03099484cecb1c2b7

Build virtualenv
$ cd ../builds/

$ tar zxf ../src/virtualenv-1.5.1.tar.gz

$ cd virtualenv-1.5.1/

$ python virtualenv.py ~/project/virtual_python
New python executable in /home/laban/project/virtual_python/bin/python
Installing setuptools.............done.

$ ls ~/project/virtual_python
bin include lib lib64


So let's install CherryPy
$ ~/project/virtual_python/bin/easy_install cherrypy
Searching for cherrypy
Reading http://pypi.python.org/simple/cherrypy/
Reading http://www.cherrypy.org
Reading http://download.cherrypy.org/cherrypy/3.1.0/
Reading http://sourceforge.net/project/showfiles.php?group_id=56099
Reading http://download.cherrypy.org/cherrypy/3.1.0rc1/
Reading http://download.cherrypy.org/cherrypy/3.0.1/
Reading http://download.cherrypy.org/cherrypy/3.1.0beta3/
Reading http://download.cherrypy.org/cherrypy/3.0.3/
Reading http://download.cherrypy.org/cherrypy/3.0.0/
Reading http://download.cherrypy.org/cherrypy/2.2.1/
Reading http://download.cherrypy.org/cherrypy/3.1.2/
Reading http://download.cherrypy.org/cherrypy/3.1.1/
Reading http://download.cherrypy.org/cherrypy/3.0RC1/
Reading http://trac.cherrypy.org/cgi-bin/trac.cgi/wiki/CherryPyDownload
Reading http://download.cherrypy.org/cherrypy/3.1beta/
Reading http://download.cherrypy.org/cherrypy/3.0.2/
Reading http://download.cherrypy.org/cherrypy/2.3.0/
Reading http://download.cherrypy.org/cherrypy/3.0.4/
Best match: CherryPy 3.1.2
Downloading http://download.cherrypy.org/cherrypy/3.1.2/CherryPy-3.1.2.zip
Processing CherryPy-3.1.2.zip
Running CherryPy-3.1.2/setup.py -q bdist_egg --dist-dir /tmp/easy_install-B4VfG0/CherryPy-3.1.2/egg-dist-tmp-EFtfdH
zip_safe flag not set; analyzing archive contents...
cherrypy._cptree: module references __file__
cherrypy._cpmodpy: module references __file__
cherrypy.lib.profiler: module references __file__
cherrypy.lib.covercp: module references __file__
cherrypy.process.plugins: module references __file__
cherrypy.test.test_states: module references __file__
cherrypy.test.test_logging: module references __file__
cherrypy.test.test_core: module references __file__
cherrypy.test.checkerdemo: module references __file__
cherrypy.test.test_misc_tools: module references __file__
cherrypy.test.test_routes: module references __file__
cherrypy.test.modpy: module references __file__
cherrypy.test.benchmark: module references __file__
cherrypy.test.test_config: module references __file__
cherrypy.test.test_tidy: module references __file__
cherrypy.test.test_wsgiapps: module references __file__
cherrypy.test.test: module references __file__
cherrypy.test.test_virtualhost: module references __file__
cherrypy.test.modwsgi: module references __file__
cherrypy.test.test_session: module references __file__
cherrypy.test.modfcgid: module references __file__
cherrypy.test.helper: module references __file__
cherrypy.test.test_caching: module references __file__
cherrypy.test.test_static: module references __file__
cherrypy.scaffold.__init__: module references __file__
cherrypy.tutorial.tut09_files: module references __file__
cherrypy.tutorial.tut06_default_method: module references __file__
cherrypy.tutorial.tut07_sessions: module references __file__
cherrypy.tutorial.tut02_expose_methods: module references __file__
cherrypy.tutorial.tut01_helloworld: module references __file__
cherrypy.tutorial.tut03_get_and_post: module references __file__
cherrypy.tutorial.tut05_derived_objects: module references __file__
cherrypy.tutorial.tut04_complex_site: module references __file__
cherrypy.tutorial.tut10_http_errors: module references __file__
cherrypy.tutorial.tut08_generators_and_yield: module references __file__
Adding CherryPy 3.1.2 to easy-install.pth file
Installing cherryd script to /home/laban/project/virtual_python/bin

Installed /home/laban/project/virtual_python/lib/python2.4/site-packages/CherryPy-3.1.2-py2.4.egg
Processing dependencies for cherrypy
Finished processing dependencies for cherrypy

OK, something a little more complex

$ ~/project/virtual_python/bin/easy_install MySQL-python
Searching for MySQL-python
Reading http://pypi.python.org/simple/MySQL-python/
Reading http://sourceforge.net/projects/mysql-python/
Reading http://sourceforge.net/projects/mysql-python
Best match: MySQL-python 1.2.3
Downloading http://download.sourceforge.net/sourceforge/mysql-python/MySQL-python-1.2.3.tar.gz
Processing MySQL-python-1.2.3.tar.gz
Running MySQL-python-1.2.3/setup.py -q bdist_egg --dist-dir /tmp/easy_install-PvF-1-/MySQL-python-1.2.3/egg-dist-tmp-MOqY_x
warning: no files found matching 'MANIFEST'
warning: no files found matching 'ChangeLog'
warning: no files found matching 'GPL'
In file included from _mysql.c:29:
pymemcompat.h:10:20: error: Python.h: No such file or directory
_mysql.c:30:26: error: structmember.h: No such file or directory
....

Fixed by installing the python-devel rpm
Second try :

$ ~/project/virtual_python/bin/easy_install MySQL-python
Searching for MySQL-python
Reading http://pypi.python.org/simple/MySQL-python/
Reading http://sourceforge.net/projects/mysql-python/
Reading http://sourceforge.net/projects/mysql-python
Best match: MySQL-python 1.2.3
Downloading http://download.sourceforge.net/sourceforge/mysql-python/MySQL-python-1.2.3.tar.gz
Processing MySQL-python-1.2.3.tar.gz
Running MySQL-python-1.2.3/setup.py -q bdist_egg --dist-dir /tmp/easy_install-30_Yax/MySQL-python-1.2.3/egg-dist-tmp-O4DYQW
warning: no files found matching 'MANIFEST'
warning: no files found matching 'ChangeLog'
warning: no files found matching 'GPL'
In file included from /usr/include/python2.4/Python.h:8,
from pymemcompat.h:10,
from _mysql.c:29:
/usr/include/python2.4/pyconfig.h:6:25: error: pyconfig-64.h: No such file or directory
In file included from /usr/include/python2.4/Python.h:55,
from pymemcompat.h:10,
from _mysql.c:29:
/usr/include/python2.4/pyport.h:612:2: error: #error "LONG_BIT definition appears wrong for platform (bad gcc/glibc config?)."
_mysql.c: In function ‘_mysql_ConnectionObject_affected_rows’:
_mysql.c:724: warning: implicit declaration of function ‘PyLong_FromUnsignedLongLong’
_mysql.c:724: warning: return makes pointer from integer without a cast
_mysql.c: In function ‘_mysql_ConnectionObject_insert_id’:
_mysql.c:1704: warning: return makes pointer from integer without a cast
_mysql.c: In function ‘_mysql_ResultObject_num_rows’:
_mysql.c:1774: warning: return makes pointer from integer without a cast
error: Setup script exited with error: command 'gcc' failed with exit status 1


Turns out that I had a 32bit python-devel package instead of the 64 bit.
One more try
$ ~/project/virtual_python/bin/easy_install MySQL-python
Processing MySQL-python-1.2.3.tar.gz
Running MySQL-python-1.2.3/setup.py -q bdist_egg --dist-dir /tmp/easy_install-E9CKk4/MySQL-python-1.2.3/egg-dist-tmp-tO6Qjb
warning: no files found matching 'MANIFEST'
warning: no files found matching 'ChangeLog'
warning: no files found matching 'GPL'
zip_safe flag not set; analyzing archive contents...
Adding MySQL-python 1.2.3 to easy-install.pth file

Installed /home/laban/project/virtual_python/lib/python2.4/site-packages/MySQL_python-1.2.3-py2.4-linux-x86_64.egg
Processing dependencies for MySQL-python==1.2.3
Finished processing dependencies for MySQL-python==1.2.3

Now on to some coding :)

Monitoring MySQL Replication with mk-heartbeat.

A quick howto on using mk-heartbeat to monitor MySQL Replication and graphing it with zenoss/any other snmp grapher/monitor


Master End

• Download Mk-heartbeat

wget http://www.maatkit.org/get/mk-heartbeat

• Create a database and grant for maatkit

create database maatkit;
grant all on maatkit.* to maatkit@localhost identified by 'Whatever';

• Start the heartbeat and let it auto-create the heartbeat table

perl mk-heartbeat -u maatkit -pWhatever --database maatkit --table heartbeat --update --create-table

Slave End

• Verify that the DDL replicated

mysql> select * from maatkit.heartbeat;
+----+---------------------+
| id | ts                  |
+----+---------------------+
|  1 | 2010-03-02 16:30:32 | 
+----+---------------------+

• Let mk-heartbeat run

mk-heartbeat -u maatkit -pWhatever --database maatkit --table heartbeat --monitor  --interval 15 --file /tmp/heartbeat --daemonize

• Fix file selinux context (I don't think it's the right one but it works )

chcon system_u:object_r:snmpd_var_run_t:s0 /tmp/heartbeat

• Why not look at how far we are lagging by querying (Wow! We are lagging)

mysql> select ts,now() from maatkit.heartbeat;
+---------------------+---------------------+
| ts                  | now()               |
+---------------------+---------------------+
| 2010-03-16 12:52:45 | 2010-03-17 17:59:23 |
+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> select ts,now() from maatkit.heartbeat;
+---------------------+---------------------+
| ts                  | now()               |
+---------------------+---------------------+
| 2010-03-16 12:53:15 | 2010-03-17 17:59:29 |
+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> select ts,now() from maatkit.heartbeat;
+---------------------+---------------------+
| ts                  | now()               |
+---------------------+---------------------+
| 2010-03-16 12:53:30 | 2010-03-17 17:59:32 |
+---------------------+---------------------+  

• Integrate with SNMP using dedicated to this purpose

• Extend snmpd.conf

extend .1.3.6.1.4.1.30630.2.2.1 mk-heartbeat-lag /usr/local/bin/repl_lag 1
extend .1.3.6.1.4.1.30630.2.2.2 mk-heartbeat-lag /usr/local/bin/repl_lag 2
extend .1.3.6.1.4.1.30630.2.2.3 mk-heartbeat-lag /usr/local/bin/repl_lag 3
extend .1.3.6.1.4.1.30630.2.2.4 mk-heartbeat-lag /usr/local/bin/repl_lag 4

• Create repl_lag script

#!/bin/sh
#Monitor file
LAGFILE=/tmp/heartbeat
case "$1" in 
'1')
     #Current LAG
      cat $LAGFILE|awk '{print $1}'|sed -e "s/s//"
;;
'2')
     #1 minute average
     cat $LAGFILE|awk -F[ '{print $2}'|awk '{print $1}'|sed -e "s/s,//"
;;
'3')
     #5 Minute average
     cat $LAGFILE|awk -F[ '{print $2}'|awk '{print $2}'|sed -e "s/s,//"
;;
'4')
     cat $LAGFILE|awk -F[ '{print $2}'|awk '{print $3}'|sed -e "s/s//"
;;
esac

• Restart snmpd and test

snmpwalk -c Community -v2c my.mu.company.net .1.3.6.1.4.1.30630.2.2.4
SNMPv2-SMI::enterprises.30630.2.2.4.1.0 = INTEGER: 1
SNMPv2-SMI::enterprises.30630.2.2.4.2.1.2.16.109.107.45.104.101.97.114.116.98.101.97.116.45.108.97.103 = STRING: "/usr/local/bin/repl_lag"
SNMPv2-SMI::enterprises.30630.2.2.4.2.1.3.16.109.107.45.104.101.97.114.116.98.101.97.116.45.108.97.103 = STRING: "4"
SNMPv2-SMI::enterprises.30630.2.2.4.2.1.4.16.109.107.45.104.101.97.114.116.98.101.97.116.45.108.97.103 = ""
SNMPv2-SMI::enterprises.30630.2.2.4.2.1.5.16.109.107.45.104.101.97.114.116.98.101.97.116.45.108.97.103 = INTEGER: 5
SNMPv2-SMI::enterprises.30630.2.2.4.2.1.6.16.109.107.45.104.101.97.114.116.98.101.97.116.45.108.97.103 = INTEGER: 1
SNMPv2-SMI::enterprises.30630.2.2.4.2.1.7.16.109.107.45.104.101.97.114.116.98.101.97.116.45.108.97.103 = INTEGER: 1
SNMPv2-SMI::enterprises.30630.2.2.4.2.1.20.16.109.107.45.104.101.97.114.116.98.101.97.116.45.108.97.103 = INTEGER: 4
SNMPv2-SMI::enterprises.30630.2.2.4.2.1.21.16.109.107.45.104.101.97.114.116.98.101.97.116.45.108.97.103 = INTEGER: 1
SNMPv2-SMI::enterprises.30630.2.2.4.3.1.1.16.109.107.45.104.101.97.114.116.98.101.97.116.45.108.97.103 = STRING: "0.41"
SNMPv2-SMI::enterprises.30630.2.2.4.3.1.2.16.109.107.45.104.101.97.114.116.98.101.97.116.45.108.97.103 = STRING: "0.41"
SNMPv2-SMI::enterprises.30630.2.2.4.3.1.3.16.109.107.45.104.101.97.114.116.98.101.97.116.45.108.97.103 = INTEGER: 1
SNMPv2-SMI::enterprises.30630.2.2.4.3.1.4.16.109.107.45.104.101.97.114.116.98.101.97.116.45.108.97.103 = INTEGER: 0
SNMPv2-SMI::enterprises.30630.2.2.4.4.1.2.16.109.107.45.104.101.97.114.116.98.101.97.116.45.108.97.103.1 = STRING: "0.41"

Monitor End

• Log into zenoss and navigate to Devices->Server->[Linux o r whatever you use]

• Click on the Templates tab

• From the Available Perfomance Templates tab, click on add template

• Type in an in. Mine was MySQL Replication

• Add a Description and save

• Under Data sources, Add a new snmp datasource. Example: ID: MySQL Replicationg Lag

• In the Data Souce add the OID derived from the snmpwalk e.g 1.3.6.1.4.1.30630.2.2.1.4.1.2.16.109.107.45.104.101.97.114.116.98.101.97.116.45.108.97.103.1

• The type is a gauge (Like a speed-o-meter ) rather than a counter (Which is like the miles covered)

• Click Save

• You may add the IP/Hostname of a test device and click on test (One of your MySQL snmpd hosts)

• Save and iterate for the other counters (1min avg, 5 min avg and 15 min avg)

• Go back to the Performance Template page

• Under Thresholds, Add a new threshold say MySQL Lag Warn

• Click on the datapoints to apply the threshold to. I opted to track the realtime Lag

• Put the number of seconds on the max field. 120 is 2 minutes of lag. Any more and I will be paged

• Add a critical threshold

• Go back to the Performance Template page

• Add a Graph Definition.

• Add the datapoints created earlier under Graph points. The relevant associated thresholds should also be auto-added.

• You may want to resequence the points

• Click save.

• Naviagate back to Device List using the menu bar on the left.

• Click on a MySQL host that you want to add a replication lag graph for

• On the dropdown menu, select More->Templates

• Under Perfomance Templates, Select Bind Templates

• Select all the templates you are interested in (Mine was Device and MySQL Replication)

• Click OK

• Click on the perf tab should give you a graph.

Here's mine showing a tunnel break that caused a > 24 hour lag and the subsequent catching up

And the network graph.

Yeah... It's a slow link :)