MySQL pager

There are a vast amount of tips and trick ode to the MySQL command line interface. One thing you'll be quick to notice with MySQL's client program is that the stdout formatting isn't always as readable as you may want. Also the fact that when viewing the list of variables set on the server or the statuses you'll surely find yourself scrolling and searching for the values you're specifically seeking. Well take the pain out of listing by making use of the mysql pager functionality. I'm not sure the right word for it, 'macro'?

From your mysql prompt change your pager by issuing the likes of;

Step through the INNODB STATUS output

mysql> pager more
mysql> SHOW ENGINE INNODB STATUS \G
...

grep-ing your output;

Sleeping Connections

mysql> pager grep -i sleep
PAGER set to 'grep -i sleep'
mysql> show processlist;
| 152 | squeezecenter | localhost | squeezecenter | Sleep | 36 | | NULL |
3 rows in set (0.02 sec)

Grouping variables/status counters together for easy eye-balling

mysql> pager grep -i 'key'
PAGER set to 'grep -i key'
mysql> SHOW GLOBAL STATUS;
| Com_assign_to_keycache | 0 |
| Com_preload_keys | 0 |
| Com_show_keys | 0 |
| Handler_read_key | 611 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 14343 |
| Key_blocks_used | 10 |
| Key_read_requests | 4197 |
| Key_reads | 13 |
| Key_write_requests | 144 |
| Key_writes | 140 |
291 rows in set (0.03 sec)

There is the potential to do some really cool things with this;

Using Maatkit/Percona toolkit or custom scripts

mysql> pager mk-visual-explain
PAGER set to 'mk-visual-explain'
mysql> EXPLAIN select a.title, c.name from albums a join contributors c on a.contributor = c.id where c.name LIKE '%Monkeys%';
JOIN
+- Filter with WHERE
| +- Bookmark lookup
| +- Table
| | table c
| | possible_keys PRIMARY
| +- Unique index lookup
| key c->PRIMARY
| possible_keys PRIMARY
| key_len 4
| ref db.a.contributor
| rows 1
+- Table scan
rows 483
+- Table
table a
2 rows in set (0.00 sec)

Don't forget to revert to normal;

mysql> nopager
PAGER set to stdout

Upcoming MySQL Events in the UK

As the song goes, "it's been a while".

I wanted to leave a note here for anyone that hasn't read it on other more active blogs but might stumble upon this in a freak search engine accident. There's a couple of notable dates for the UK MySQL scene over the next few months.

Firstly the `MySQL London Meetup` is hosting on the 18th of July 2011 and they have some MySQL rockstars talking and socialising. I'm looking forward to hearing both Zaitzev's Scaling MySQL talk and Andrew Hutchings' (@linuxjedi) words on Drizzle. There's loads of other good material too, so check out the event page.

Later in the year Peter returns to London for Percona Live in October. This is a one day intensive summit packed to the brim with expert talks, advice and all the tricks you can stuff a dolphin with.

There's also an 'open-bar' evening event which intriques me as I'm not sure if it's an excuse for a beer or something totally different. Head over to the Percona site for more details.

I should be attending both so hopefully see you there.

The MySQL Community Podcast and other MySQL Resources

Should you find yourself learning MySQL for professional gain or as a hobbyist interest there are some resources that you would be best advised engrossing yourself in to gain that extra inch. Considered a prime cut of opensource software, MySQL is very accessible and well documented. As you peel back the layers you will find a busy and extremely welcoming community ready to assist, collaborate and discuss reams of subject matter.

In terms of official resource you will find a plethora of mysql.com subdomains. One of my regular stops is planet.mysql.com. The custom built blog/news aggregator built by Arjen Lentz, is a rollup of articles submitted and labeled with the `mysql` keyword. Pop on over to read the latest musings from those giving their $0.02 on various topics. You will find plenty of articles on MySQL and frequently articles on forks, tools, findings and guides from some of the loudest voices in the MySQL ecosystem. Names like Lenz Grimmer, Guiseppe Maxia, Baron Schwartz and Ronald Bradford will promptly become familiar names of the MySQL database rockstars. Simply put it's an ace dynamic for the community and a real way to have your voice heard.

There are few more channels that I won't explore too much as they are pretty self explanatory if you're familiar with any interactive web platforms. The forums.mysql.com do exactly as it says on the tin and lists.mysql.com allows you to join and read the MySQL mailing lists. Both are places that can fast become bookmarks in your fave browser. They certainly have made it into my Chrome Bookmark Bar. So besides the 'official' channels (official in appearance anyway) there are some other forms of connecting with the community in an educational or troubleshooting capacity. If you're an IRC user already you can point your fave IRC client at the Freenode MySQL channel. Here you will find many of the most experienced DBAs and DEVs hiding behind their carefully chosen guises. If you're struggling with something in realtime try the IRC channel for some friendly advice. But be forewarned, if you've not tried to solve your own problem before hitting the channel (RTFM) don't be shocked when you're hounded out. I've seen lazy newbies shot down fast by asking shallow questions born from thinking freenode == free support.

My next mention on this whistle-stop tour of MySQL resources is the countless amount of great books written about the product and it's implementations. Browsing a few of the search results on Amazon (or your favourite online book retailer) you will notice that there have been hundreds of books written for MySQL. There is usually a coupling of MySQL with PHP, the popular 'M' & 'P' of LAMP. So depending what you want to learn there's something out there for you. Should you visit my office you'll be sure to find something with Paul DuBois' name on the spine and also the awesome O'Reilly published offerings; "High Performance MySQL" & "MySQL High Availability". I've also got "Expert PHP and MySQL", co-authored by the one and only Ronald Bradford, awaiting my attention. [ ISBN: 9780596807306, 9780596101718 and 9780470563120 respectively ]

So onwards to a format of MySQL ingestion that I've been enjoying and certainly learning from lately. It is the sole contribution to the audio movement in the MySQL community. The OurSQL Podcast is delightfully brought to us weekly by two more MySQL stars, Sheeri Cabral of Palominodb and Sarah Novotny from Blue Gecko. Available from iTunes or the OurSQLcast website so you can copy it to your non-branded mp3 player. Sheeri and Sarah have vast experience with MySQL, working in consultancy roles on one hand and feeding the community with the other. From the haunting title music (trust me it will be bouncing around in your head hours after hearing the latest episode) to the 'Ear Candy' and out-takes, the podcast is a great way to learn from some bonafide experts in the field without the consultancy bills. Sheeri and Sarah guide their listeners through various subjects on the show and are often joined by community favourites such as Brian Aker of Data Differential, Patrick Galbraith of Membase fame and Lenz Grimmer recently retired MySQL Community Relations Specialist. I'm sure that their contact list is far from exhausted and they will brandish many more interesting special guests for our listening pleasure. All the show notes are posted online so if you miss anything first time around you can bet your primary key that they have posted it on the episode's page. As the podcast continues to build popularity with the community the ladies are asking for your feedback via iTunes or their phone line (which you will hear on the podcast) dangling a "swag" shaped carrot on front of our noses for encouragement. So if you listen, be sure to let them know what you think, you might be boasting a shiny new OurSQL mug by the end of the week. A cool little feature that they run on the show is "Where you can see us" which is a run-through of their travel and physical appearances (in actual human form, no less!) across this shiny little globe. This leads nicely into meet ups and conferences.

Conferences and meetups are sweet! Although light on the ground here in the west of England they are certainly another avenue to connect with other MySQL evangelists and gurus alike. Keep your eyes peeled on London if in the UK or if you're lucky enough to be in an established MySQL locality there's a wealth of MySQL tracks through various conferences in the States and Europe. A fine way to see what-is-what conference and meetup-wise is keeping abreast of developments on planet.mysql.com or by tuning in to the podcast. One of the largest MySQL conferences is the O'Reilly MySQL Conference which is like the MySQL version of Disney World and this year is 11th - 14th of April hosted in Santa Clara CA.

I can't say I've been remotely thorough today. I've missed out the superb documentation, MySQL Forge, the mysqlperfomanceblog.com, I didn't even mention Mark Callaghan's patching rants and probably loads more that you kind readers could mention in comments should you have the time to. I am going to sit in the dark and wait for the next installation of... "Sarah, Sheeri Optimise an SQL Query. Database is making me weary so I...." Ahhh get it out of my head!!!!!

MySQL seminar in London [Feb 16th 2011]

There's another (FREE) chance to meet in London and find out what MySQL 5.5 has to offer you. Get to the registration page as the spaces are limited. Registration online and the agenda is also published there. Looks like there's some info on the Enterprise Backup Tool which offers means to a native online backup. Real exciting stuff (if you're a DBA battling MySQL Backups). Not forgetting to mention lunch and the chance to talk MySQL with fellow users face to face.

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

$>python
>>> import MySQLdb
>>>

Success!

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.

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?