Tuesday, 17 August 2010

Installing MySQLdb python module

MySQLdb is a Python wrapper around _mysql written by Andy Dustman. This wrapper makes it possible to interact with a MySQL Server performing all sorts of DDL and DML statements. I began my Python journey recently and stumbled at the installation of the MySQLdb module install. I was keen not to jump at an apt/yum installation as we have servers that have no outbound connections I decided I wanted to build the module from source.

You can download the MySQLdb files from SourceForge (70kb)

When downloaded you need to prep before your system is ready to build the file. Here are some prerequisites that will make life easier for you. I performed this particular install using an Ubuntu 10.04 64bit OS.

Before you start ensure you have the following installed (MySQL isn't actually required but for local Python development it's nice to have a database server to develop against!)
  • MySQL Server. I used the MySQL Community Server Version 5.1.49
  • gcc - The GNU Compiler Collections
My first attempt at a build resulted in the following error message

$> tar -xzvf MySQL-python-1.2.3.tar.gz
$> cd MySQL-python-1.2.3
$> python setup.py build

Traceback (most recent call last):
  File "setup.py", line 5, in
    from setuptools import setup, Extension
ImportError: No module named setuptools

This was resolved by installing another Python module namely 'python-setuptools' (I did take the short cut here using apt-get). I later found out that python-dev and libmysqlclient15-dev were more packages that I needed for the build so I'm tagging them on here.

$> sudo apt-get install python-setuptools python-dev libmysqlclient15-dev

With this installed I decided I was ready to build again but again another splurge of error code and this time my system was complaining about 'mysql_config' (you might not incur this issue after the previous apt-get installs but I'm including it anyway just in case you see this message.)

EnvironmentError: mysql_config not found

I updated my PATH environment variable and I was ready to try again...

$> export PATH=$PATH:/usr/local/mysql/bin

You should be ready (properly ready this time!) to build and install your MySQLdb wrapper

$> sudo python setup.py build
{various output}

$> sudo python setup.py install
{...Installed /usr/local/lib/python2.6/dist-packages/MySQL_python-1.2.3-py2.6-linux-x86_64.egg}

Now open your python command line and import the new MySQLdb module

>>> import MySQLdb


Wednesday, 28 July 2010

Install MySQL from Tar ball

Download your favourite version of MySQL Server from your local mirror. We can use the latest GA (5.1.49) or development release (5.5.5m3). Move your tarball to the /usr/local directory. I'm assuming that you're logged into your box using the root account. If you're not then you might need to utilize sudo.

Add the mysql user and group
   groupadd mysql
   useradd -g mysql mysql

Untar and Create a symbolic link
   cd /usr/local/
   tar -zxvf mysql-{version}.tar.gz
   ln -s mysql-{version} mysql

File and Directory creation/permissions 
   mkdir -p /var/lib/mysql *
   chown -R mysql:mysql /usr/local/mysql
   chown -R mysql:mysql /var/lib/mysql

Install the system databases and place my.cnf
  cp /usr/local/mysql/support-files/my-medium.cnf  /etc/my.cnf**
  cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql***
   scripts/mysql_install_db --user=mysql --datadir=/var/lib/mysql --basedir=/usr/local/mysql ****
Start mysqld
   /usr/local/mysql/bin/mysqld_safe &

Check your error log file to keep abreast of any issues that might arise.
Some house keeping is needed to ensure that MySQL will start a boot and also adding mysql into the path. These are basic Linux tasks. Please let me know if you'd like to see my recommendations for these tasks.

* This command is going to create your datadir location. This might not fit your preferences so this is something that you will choose to include or omit. You should review your my.cnf and make any changes to accommodate your desired location.
** This will copy a default my.cnf to the preferred my.cnf location. If you have written your own or have something you wish to use, copy that and not the example file. Your my.cnf is a pretty big deal as it's going configure your server to run like a two-legged dog or Usain Bolt!
*** Enables the 'service mysql [start|stop|status]' command to be executed
**** These parameters can be augmented, omitted or kept. You're going to want to use the values in your my.cnf or change your my.cnf to reflect what you use here.

Friday, 2 July 2010

South West UK MySQL User Group interest...anyone?

I recently found out that there's a MySQL User Group in London that regularly meet to discuss all things MySQL. I found out when I attended the MySQL Breakfast Seminar last month on the 24th June 2010 at the Sun Customer Briefing Centre on King William Street, London. I have since visited the MySQL User Group Facebook page and it seems that there's a nice buzz on there from community members and MySQL employees...sorry, Oracle Employees.

Due to a missing South West UK string to the User Group bow, is there any interest in starting one up? We could begin with at least a Facebook page with some South West flavoured buzz. Future visions of meets and maybe even MySQL celebrity visits could occur one day!

Good idea? Bad idea?

Friday, 28 May 2010

MySQL Replication for Backups and more

You might be fortunate enough to allow yourself some downtime, it is dependent on your application and business model. During this window it's possible for you to stop your MySQL daemon or lock your tables to give yourself a consistent backup of your data. Quite often this is a luxury that you cannot afford. If you are tied to a strict uptime that doesn't permit any interruption to your data availability then MySQL Replication could be the answer you need to grab that essential backup file. Once you've enabled Replication to a slave then you have the chance to backup by stopping the replication thread and  mitigate the risk of corruption whilst securing your latest dataset. Using the slave will also negate any overhead a backup like mysqldump would have on your active Master server.

Although in this case we are deploying Replication to take consistent backups of our data, there are many uses for the mechanism such as scaling out you solution, analytics and reporting, data distribution over geographically disperse locations and high availability.

Replication at a high level overview works like so...

For the sake of the guide we will use Server1 and Server2, Master and Slave respectively.
  • Changes are made to data on the Master (Server1). These changes are logged in the Binary Log of the Master and are referred to as binary log events. 
  •  The Slave (Server2) will then copy the Master's binary log events into it's Relay Log.
  •  The Slave will then replay the events that it has copied into the Relay Log to it's own data. The result is an identical dataset.  
There are many guides online to setting up replication but it's essentially an easy feat to complete. On your journey into replication you will encounter all sorts of extra options such as type of replication (statement, row, mixed), exclusion of certain tables or even whole databases.

Setting Up Replication (quick guide)

Before you read on, I assume you have MySQL installed on both Server and the servers are on the same network, communicating. This guide takes you down the basic route of replicating to a single slave.

To ensure that Replication will work you need to ensure a few configuration settings are set. Check your my.cnf for the following attributes:

log_bin = {filename} e.g. mysql-bin

log_bin = {filename}
relay_log = mysql-relay-bin
log_slave_updates = 1
read_only = 1

The server-id attribute needs to be a unique integer. You may incur problems if you have duplicates on your network so set this to something unused. Best practice is to ensure you set the servier-id in my.cnf on all your servers as the MySQL will default to 1 and think it's a master. I frequently see the use of the last quartet of the server's IP address. Ensure that you're using binary logging on your master too, Replication will not work without it. Make sure that the lines skip-networking and bind-address are either commented out or deleted as these will make it impossible to connect to your network. Once your my.cnf complies with Replication's needs, restart the mysql daemon;

OS command
shell$ mysqladmin -uroot -p shutdown
shell$ service mysql start
The previous OS commands will ensure graceful cycle of your daemon. Run them on your command line.

1. Authentication
At the beginning we need to create a user on the Master so that our slave's thread can access our data. Login to your master MySQL node and then add your replication user

MySQL master command
mysql> GRANT REPLICATION SLAVE ON *.* TO `slave`@`Server2` IDENTIFIED BY 'password';
This command will add a user to your Master that allows the Slave to connect.

**It's best practice that this is account is used for no other purpose then replication.**

2. Load Data
This step is optional. If you've got data in your tables already then this is a sensible step to undertake. Dump your data into your slave using the mysqldump tool;

OS master command
shell$ mysqldump -u{user} -p{password} {--single-transaction|--lock-all-tables}--all-databases --master-data=1 --host=server1 | mysql -u{user}-p{password} --host=server2
This mysqldump command will dump your data from Server1 and the pipe the output into Server2. You can also dump to a file using the *nix redirect;

OS master command
shell$ mysqldump -u{user} -p{password} {--single-transaction|--lock-all-tables} --all-databases --master-data=1 > masterdata_dump.sql
and then copy the output file to your slave and import it.

3. Configure Slave
Start a MySQL session on your slave and enter the following command. This coordinates your replication. It tells the slave how to connect to your master server.

MySQL slave command

4. Start Slave
MySQL slave command
All going well you should be up and running. Check your replication using command

MySQL slave command

Backing Up
Now you have an operational Replication Setup you can explore the different tools on the market for backing up your data. A robust and thorough disaster recovery policy is a gem in the crown of the DBA. Without it one will need to keep an up-to-date CV handy at all times because disasters do happen! Plan a full backup and restore strategy, consider what backup window is available to you, how much data you can afford to lose, if you need 'point in time' restoration. Check out ProductionDBA.com's MySQL Blog for a great summary on MySQL Backup/Restore tools.

Tuesday, 11 May 2010

MySQLDump from A to B with pipes

Moving your data and tables around comes in many different flavours. The use of mysqldump is common practice to dump your data and schema out to a file. It is also possible to pipe your mysqldump into a 2nd server. Try the code below (adapting the users and passwords!) in a test environment;
$ mysqldump -u UserA -p p455w0rd --single-transaction --all-databases --host=Server1 | mysql -u UserA -p p455w0rd --host=Server2

As you can see from the command we are taking all the databases in a single transaction into Server2 from Server1. If you're not using transactional tables substitute the --single-transaction for --lock-all-tables to ensure you get a consistent copy.

Remember; You must be able to see the 'other' server over the network and there must be permissions set for remote access from your feeding Server. For large databases this technique may not be suitable because of the performance restrictions surrounding mysqldump. For smaller databases this can be an ideal tool for transporting data to a test machine for some manipulation.

Friday, 30 April 2010

Internet Explorered

Internet Explorer, terminally ill?
If you were given the task of picking your favourite browser, would IE be top of the list? Would you opt for the bundled, closed source offering of the worlds most popular operating system or would you opt for something more exciting and bleeding edge? There are many options out there and to name some big players in the browser field, Chrome, Firefox, Opera, Sea Monkey and many more. I for one would steer clear of IE simply down to it's flaws. It's buggy, it's insecure and I want something that performs better. How does a browser that has hundreds and thousands of add-ons sound? Would you really put much thought into how you surf the web?

Cross Browser Compatible 
With CSS3 and HTML5 nearing release, the web development community are far from surprised that Microsoft have shot wide yet again. A brief Google search on the subject of browser functionality will reveal that IE7, IE8 and potentially IE9 are lacking some of the essentials that the Web Standards Project have been busying themselves constructing. Angry and frustrated Web Developers have been getting by using various hack to make websites look consistent across the main offering of browsers. The new standards aim to pull the drawstrings in and render many of these hacks unnecessary but this might not be the case where IE is concerned. I sincerely hope, if only for end-user sake, that MS manage to wake up and avoid forcing more design hacks by adopting the basics for the updated standards.

Louis Lazaris describes some of the flaws in the IE browsers to date in the article CSS3 Solutions for Internet Explorer

If it's so horrid why do people use it?
IE had been my choice of browser up until a couple of years ago. Being a Server Administrator in my previous position I was the proud user and implementer of Microsoft Products. 

It is both reluctantly and occasionally that I open IE these days. Usually if I've just rebuilt my partners laptop after malware, spyware and viral infestations. I double click that 'e' in order download Firefox, Chrome and a driver or two. I had found it somewhat romantic that my new firefox had underdog status and the average Windows Home PC user continued to block up their desktop like a constipated toddler with IE windows. I could easily entertain myself by watching friends and family bumble around the net tripping over flash banner ads and pop-ups. Unknowingly clicking, opening and infecting their system with viral all-sorts along the way.

So why dislike IE? Millions already do and millions more will learn. It has been the home user's choice in the past simply because it's there by default on a Windows PC. Maybe novice users are unaware that there are other browsers out there. Maybe it's fair to say that it's a trust issue with 3rd party software for some users. The operating system is Microsoft so use the Microsoft Browser. A computing uniform? The features, speed and compatibility of other browsers will ensure that if MS fail to keep up with the standards that Mozilla and Webkit are pushing they will flail in the race for top browser. Being a closed source project they just can't compete.

Whats next, Mobile?
It's now 2010 and we are at facing another IE installment. We've seen IE8 and MS' continuation of ignorance by way of non-compliance with CSS and Web Standards and by that I mean that they are still developing away from the industry tide. It's a long running joke in the Web Development Community that IE is the browser that doesn't fit in with the way the web is built and the standards that they strive to keep.

This year will see the highly anticipated Google Chrome OS, based on their open source Chrome web browser. Many Netbooks will be sold with this preinstalled and this will take Google's share higher as this OS become's more and more popular. We have also seen recently the release of Firefox Mobile to the Maemo based Nokia N900. This enters the mobile arena along with iPhone's Mobile Safari, Opera Mobile and native N900 Browser MicroB (powered by Mozilla). Designers are now catering for an increasing number of mobile web clients will Microsoft's IE Mobile compete?

It's clear that a well driven open source project is more agile and will be supported with much more vigour by the serious web users then a closed source MS delivery. The future of the web is open source. Expect IE's market share to crumble before your very eyes.

It's your choice what to use but if it down to me, you would be using my favourite from Google. It's made the web feel lighter, faster and with it's add-on library it has 'extended' past just a web page display tool.

Ubuntu 10.04, Lucid Lynx arrives

The Ubuntu Lynx is here and Canonical's latest Long Term Supported OS has made it onto my production laptop. This means like the Heron, it will be around for a while.

I spent most the day on the Ubuntu website yesterday refreshing the page waiting for the OS to appear. I was side tracked and returned in the evening to gather the 64bit ISO from a local mirror. My installation was delayed until 6.30am this morning, after I had fed the nipper and she was happily bouncing away in front of her favourite morning cartoons. As expected the installation was very straight forward. I'm sure my 4 month old could have sailed through the process too if she wasn't only interested in how far she could get the mouse into her mouth!

I confess that I am regularly one to jump on the 'it's shiny and new' band-wagon and this release was absolutely no different. I performed a clean install of Lucid over the top of my previously installed Lucid Alpha2 partition and plugged-in my /home partition from my Karmic installation. All went seamlessly and I was able to access all the files from my 200gb /home partition without having to do anything clever. 

So to the big question...am I impressed? Well...no, not yet. I have not seen any huge differences between 9.10 and 10.04 BUT that's not all bad news.

Very simple, standard wizard based installation directly from the ISO I downloaded. I'm sure that novices and experts alike will have no problems installing the Lynx.

As it proclaims, a fast boot time allows you to head straight for your email and documents within seconds of powering up. Canonical aimed for a 10 second boot but I'm sure that this will vary depending on what it's installed on.

The logo has had a spring clean. The colour (or color if you're in the States) scheme is also different from that of it's predecessor. A new purple theme greets you at the door. It is the fur of our new Lynx which mustn't be mistaken for that of a certain snow leopard...hmm! There are also some small menu tweaks, UbuntuOne has appeared along with some account management tools in the Indicator Applet Session menu. There's nothing to persuade you to write a letter to your Mother and Father though. One thing I will mention too is that Ubuntu's default theme seems to have moved the window controls from the top right of the windows to the top left...very mac-like but this can be changed as can the theme.

Default Application Set
It's not a major concern to anyone that can use apt, but there are some changes to the application roster. There are certain applications missing from the default 10.04 installation and there are some new faces too. The most noticeable crater left is that of the GNU Image Manipulation Program, gimp but a few clicks later and your package manager will put that right. Gwibber social client now lives in the internet menu tab and a video editing package named PitiVi joins the party too.

Suffice to say I will be knocking around on the Ubuntu forums to see if there are any gotchas I should be worried about.

I will be filling my Friday evening upgrading my Ruby on Rails development Environment and digging into the new features of the Lynx!

Happy Lynx-ing!

Thursday, 22 April 2010

Ubuntu 10.04 Countdown

Add your Countdown banner for Lucid Lynx, Ubuntu's all-singing, all-dancing LTS (long time support) release. Lots of new features heading to a desktop near you soon!

Tuesday, 23 March 2010

Bonjour tout le monde

Coming soon, all my MySQL comments, tips, tricks and musings!