Category: Databases Administration

Covering NoSQL, Relational Database, Data Visualization, and Reporting.

  • Quick Start To Using MongoDB with Python on Linux

    With it’s rapid growth in popularity MongoDB is quickly becoming one of the top NoSQL Databases out there and with Python being one of the top ten programming languages according to Tiobe Software’s Programming Community Index. I’ve decided to write a quick how-to to show you just how easy it is to get started with MongoDB and Python.

     

    Assuming that you all ready have Python installed on your system. We’ll start with downloading and installing MongoDB. The first that you will need to do is download the appropriate package from http://www.mongodb.org/downloads to your /tmp directory.

     

    $ curl http://fastdl.mongodb.org/linux/mongodb-linux-i686-1.6.3.tgz > /tmp/mongo.tgz

     

    Once you have downloaded the correct package, find a suitable directory to unpack it and move it to, such as /opt/mongodb.

     

    $ cd /tmp

    $ tar -zxf mongo.tgz

    $ sudo mv mongodb-linux-i686-1.6.3 /opt/mongodb

     

    Now you will need to create the data directory. By default, MongoDB stores it’s data in “/data/db”, but if for some reason you need to change the location of the data directory you will need to use the “–dbpath” option when starting the server. However I am sticking with the default directory.

     

    $ sudo mkdir -p /data/db

    $ sudo chown -R owerid /data/db

     

    You can start MongoDB with the following command.

     

    $ /opt/mongodb/bin/mongod

     

    Now, test it out by using the MongoDB shell to connect to the server as follows.

     

    $ /opt/mongodb/bin/mongo

    MongoDB shell version: 1.6.3

    connecting to: test

    > db.foo.save( { Message : “Hello World” } )

    > db.foo.find()

    { “_id” : ObjectId(“4cdd92bc4f6fb75dd2a7642d”), “Message” : “Hello World” }

    >

     

    Once you have verified that MongoDB is working you will need to download the Python driver for it which are called “PyMongo”. If you have the Python “setuptools” installed you should be able to do “easy_install pymongo” to install the drivers. Otherwise you can download the project source from http://pypi.python.org/pypi/pymongo/ to install the MongoDB drivers.

     

    $ curl http://pypi.python.org/packages/source/p/pymongo/pymongo-1.9.tar.gz  > /tmp/pymongo-1.9.tar.gz

    $ tar -zxf pymongo-1.9.tar.gz

    $ cd pymongo-1.9/

    $ python setup.py install

     

    After everything is installed it’s time to test it all out, so just pop open your favor Python IDE or the Python Console and give it a go. Here is a simple example.

     

    $ python

    Python 2.4.3 (#1, Nov 11 2010, 13:34:43)

    [GCC 4.1.2 20080704 (Red Hat 4.1.2-48)] on linux2

    Type “help”, “copyright”, “credits” or “license” for more information.

    >>> import pymongo

    >>> connection = pymongo.Connection( “localhost”, 27017 )

    >>> db = connection.test

    >>> db.foo.save({ “Message” : “Hello World 2” })

    ObjectId(‘4cdd95bfe1382330b5000000′)

    >>> for message in db.foo.find():

    … print message

    {u’Message’: u’Hello World’, u’_id’: ObjectId(‘4cdd92bc4f6fb75dd2a7642d’)}

    {u’Message’: u’Hello World 2′, u’_id’: ObjectId(‘4cdd95bfe1382330b5000000’)}

    >>>

    $

     

    For more information on the PyMongo check out the website athttp://api.mongodb.org/python/1.9%2B/index.html.

     

  • Connecting Python to Oracle.

    In the current world of heterogeneous networks it seems that you need to be able to connect to ever type of database system out there. In a recent project, I was asked, to take an existing MySQL and Python system and get it to connect to an existing Oracle Data Warehouse system. To be honest, I hadn’t actually ever connected Python to an Oracle Database before and from experience this would be either very painful or pretty straight forward.

    Of course like everything Python it was pretty straight forward. The first thing you will need to get is the correct cx_Oracle module from http://cx-oracle.sourceforge.net/. The cx_Oracle module allows you to connect to Oracle databases and it conforms to the Python database API specification. Which makes life easier for everyone. After you have installed the module the rest is just a matter having the right permission to connect to the Oracle Database and writing the code.

    Here is quick script showing you how to connect to Oracle with Python.

    #!/usr/bin/python

    import cx_Oracle

    connstr=’scott/tiger’
    conn = cx_Oracle.connect(connstr)
    curs = conn.cursor()
    curs.arraysize=50
    curs.execute(‘select 2+2 “aaa” ,3*3 from dual’)
    print curs.description
    print curs.fetchone()
    conn.close()

    For more information check out the Python Programming Language – Official Website

  • Calculating The Percentage of Max Connections in with MySQL 5.1.9 and higher

    For longest time it had bugged me that there wasn’t an easy way to put “SHOW STATUS”, “SHOW VARIABLES”, or “SHOW PROCESSLIST” variables into queries, so I could easily calculate stuff like max connections percentage. Then MySQL 5.1.9 came out and you could final start to do SELECT statements on information_schema.GLOBAL_STATUS, information_schema.SESSION_STATUS, information_schema.GLOBAL_VARIABLES, information_schema.SESSION_VARIABLES and information_schema.PROCESSLIST.

    The STATUS and VARIABLES tables have basically two columns VARIABLE_NAME and VARIABLE_VALUE. These correspond with the “SHOW STATUS” and “SHOW VARIABLES” command’s Variable_name and Value. The PROCESSLIST table has all the same columns as the “SHOW PROCESSLIST” command.

    So now with that being said here is a quick little query that returns the Max Connection Limit, Connection Count, and Connect Percentage of max allowed connections.

    /* requires MySQL 5.1.19 or higher */

    SELECT v.VARIABLE_VALUE as “Max Connections”,
    s.processcount as “Connection Count”,
    ((s.processcount/v.VARIABLE_VALUE)*100) as “% of max connections allowed”
    FROM information_schema.GLOBAL_VARIABLES v,
    (SELECT (@proc_connect:=count(*)) processcount FROM information_schema.PROCESSLIST) s
    WHERE v.VARIABLE_NAME = ‘max_connections’;

     

  • MySQL Functions for Converting IP Address to an Integer and Back Again

    So after my MySQL server-id idea. I was asked to come up with a function to Convert IP Address. So here it is.

    use test;
    DROP FUNCTION IF EXISTS IpToInteger;
    DELIMITER //
    CREATE FUNCTION IpToInteger ( ipAddress CHAR(15) )
    RETURNS INT UNSIGNED
    DETERMINISTIC
    BEGIN
    DECLARE o1,o2,o3,o4 char(3);
    DECLARE IpInteger INT UNSIGNED;
    SET o1 = REPLACE(SUBSTRING(SUBSTRING_INDEX( ipAddress, ‘.’, 1), LENGTH(SUBSTRING_INDEX( ipAddress, ‘.’, 1 – 1) ) + 1), ‘.’, ” );
    SET o2 = REPLACE(SUBSTRING(SUBSTRING_INDEX( ipAddress, ‘.’, 2), LENGTH(SUBSTRING_INDEX( ipAddress, ‘.’, 2 – 1) ) + 1), ‘.’, ” );
    SET o3 = REPLACE(SUBSTRING(SUBSTRING_INDEX( ipAddress, ‘.’, 3), LENGTH(SUBSTRING_INDEX( ipAddress, ‘.’, 3 – 1) ) + 1), ‘.’, ” );
    SET o4 = REPLACE(SUBSTRING(SUBSTRING_INDEX( ipAddress, ‘.’, 4), LENGTH(SUBSTRING_INDEX( ipAddress, ‘.’, 4 – 1) ) + 1), ‘.’, ” );
    SELECT ( o1 << 24 ) + ( o2 << 16 ) + ( o3 << 8 ) + o4 INTO IpInteger;
    RETURN IpInteger;
    END//
    DELIMITER ;

    use test;
    DROP FUNCTION IF EXISTS IntegerToIp;
    DELIMITER //
    CREATE FUNCTION IntegerToIp( ipInteger INT UNSIGNED )
    RETURNS CHAR(16) DETERMINISTIC
    BEGIN
    DECLARE o1,o2,o3,o4 INT UNSIGNED;
    SET o1 = ( ipInteger >> 24 );
    SET o2 = ( ipInteger >> 16 << 16 )-( ipInteger >> 24 << 24) >> 16;
    SET o3 = (ipInteger >> 8 << 8 ) – ( ipInteger >> 16 << 16 ) >> 8;
    SET o4 = ipInteger – ( ipInteger >> 8 << 8 );
    RETURN CONCAT( o1, “.” , o2, “.” , o3, “.”, o4 );
    END//
    DELIMITER ;

    mysql> SELECT IpToInteger ( '10.8.208.30' ); +-------------------------------+ | IpToInteger ( '10.8.208.30' ) | +-------------------------------+ | 168349726 | +-------------------------------+ 1 row in set (0.00 sec) mysql> SELECT IntegerToIp( 168349726 ); +--------------------------+ | IntegerToIp( 168349726 ) | +--------------------------+ | 10.8.208.30 | +--------------------------+ 1 row in set (0.00 sec) mysql>

     

  • Amazon Cloud Sends in the MySQL Clones

    Amazon is making it easier, and cheaper, to roll out copies of MySQL for very large websites across its cloud.

    On Tuesday, the company announced an update to its Amazon Relational Database Service that will let users create and delete multiple Read Replicas of MySQL instances in minutes, via a point-and-click interface.

    Full Story Here

  • Coming up with a unique MySQL server-id

    The server-id, is used in replication to give each master and slave a unique identity. For each server participating in replication, you should pick a positive integer in the range from 1 to 2^32 – 1 to act as that server’s ID. To come up with this id convert the IP address of the server to an integer as follows

    IP address 10.8.208.31

    First Octet: 10
    Second Octet: 8
    Third Octet: 208
    Fourth Octet: 31

    (first octet * 256^3) + (second octet * 256^2) + (third octet * 256) + (fourth octet)
    server-id = (10 * 16777216) + (8 * 65536) + (208 * 256) + (31)
    server-id = 168349727

  • Slacking off

    Well I haven’t really wrote anything in the last couple of months even though, I have had plenty to write about. I went to OSCON 2010 in July and had a great time. I learned more about MongoDB and picked up a few more bits of information on MySQL, Memcached, and Linux.

    In July I went to Novell’s Linux Days. I have to admit that Novell has some nice products. Not sure if I would personally pay for them, but if the company wanted to, I wouldn’t turn my nose up at them.

  • Couchdb vs. Cassandra.

    And Cassandra wins by a knock out and the crowd goes wild. http://couchdb.apache xenical buy online.org/ vs.http://incubator.apache.org/cassandra/.

    Couchdb is more user friendly, but Cassandra is way more advanced.

  • MySQL Workbench..

    The MySQL Workbench has been out for a little while now, and I kind of put it to the back of my mind, because when it come out it was on Windows only. I, myself use Linux about 90% of the time, so I figured I would wait until they had a Linux version. The other day I was surfing MySQL website and noticed that they have a Linux, and OS X version now. So I figured I would give it a try.

    I have use the MySQL GUI tools before and while they are great for simplifying administration and have a lot of basic functionality, they are lacking in the design and development areas. Now enters MySQL Workbench. It’s a data modeling and design tool. To quote the MySQL website.

    “MySQL Workbench enables a DBA, developer, or data architect to visually design, generate, and manage all types of databases including Web, OLTP, and data warehouse databases. It includes everything a data modeler needs for creating complex ER models, and also delivers key features for performing difficult change management and documentation tasks that normally require much time and effort” 

    All in all it’s not a bad tool. It’s easy to use and fills a hole in the MySQL toolkit. Of course there are two version of it Community and Standard, of which the standard has $99 a year subscription and has additional plugins.

    Check it out: http://www.mysql.com/products/workbench/

  • SchemaSpy

    “Do you hate starting on a new project and having to try to figure out someone else’s idea of a database? Or are you in QA and the developers expect you to understand all the relationships in their schema? If so then this tool’s for you.”

    This is actually really nice and fast. Also makes it easy to visually see what your developers are doing.

    http://schemaspy.sourceforge.net/