Save time and energy: How to:

Listening to “Highway to Hell” in an Oxford University computer lab on a sunday afternoon writing a MySQL blog can be legitimally defined by some as sick but thats what a geek calls a relaxing yet productive sunday afternoon.

For the sake of all those CLI ambassadors like myself, I wanted to share a couple of tips and tricks I use quite often when doing my mysql db administration work / scripting. I strongly suggest any mysql dba / dev use CLI simply because thats the one thing you should always have on any machine on which mysql is installed, be it Unix, Linux, Mac, Microsoft or whatever platform you are using.

Cancelling a query you are typing without exiting to the terminal:
`\c` – How many times you ended up hitting `CTRL+C` (default process kill in most OSes) in order to cancel a command, finding yourself going back to the console and having to connect again? Nuisence? Hell …

This week I was the invited speaker to give a 4 hr presentation to the Federal Government Sector in Washington DC on “Best Practices in Migrating to MySQL“. This was a followup to my day long “MySQL for the Oracle DBA Bootcamp” which I presented in Washington DC last year. It was good to see a number of attendees from my first DC presentation.

There was good attendance across various government departments and companies providing services to the government sector, as well a variety of job descriptions.

Thanks to Carahsoft and Sun/MySQL for organizing and sponsoring the event. Thanks also to Phil Hildebrand who provided fantastic support during my preparation answering all my SQL Server questions.

Thanks also …

We have a MySQL University session later today, featuring me and Trond Norbye (who works on Memcached development).

Ostensibly we are talking about some of the tools and environment details of developing on Solaris and OpenSolaris, but with a MySQL focus.

I’ve just updated the Forge page with the requisite information and a copy of the slides if you need them. Here’s the abstract:

Developing MySQL on Solaris requires you to install a suitable compiler and other tools, but you may be surprised to know that most of the material is there already, or easy to install if it’s not. But even more so, there is a huge wealth of information that you can get about your application while it’s running, both with and without using more traditional debugging methods. We’ll cover setting up a suitable environment, where to find the things you …

Today I had to swap 2 columns in one of my MySQL tables. The task, which seems easily accomplishable by a temp variable, proved to be a bit harder to complete. But only just a bit.

Here are my findings:

1. The

   UPDATE swap_test SET x=y, y=x;

   approach doesn't work, as it'll just set both values to y.

   PostgreSQL seems to handle this query differently, as it apparently uses the old values throughout the whole query. [Reference]

2. Here's a method that uses a temporary variable. Thanks to Antony from the comments for the "IS NOT NULL" tweak. Without it, the query works unpredictably. See the table schema at the end of the post. This method doesn't swap the values if one of them is NULL. Use method #3 that doesn't have this limitation. …

Wow, check out what just came out from Linbit: The DRBD Management Console. Written in Java (so it runs anywhere), completely open source (GPLv3), and allows you to manage DRBD and Heartbeat based clusters. You can install, configure, see your systems graphically, and a lot more. I’m interested to try the beta out, as soon as I get back to my lab (sitting in the airport now). If you know how to use DRBD/Heartbeat, and use it in production for your MySQL setup, it might be a good application to test out, and improve if need be.

From the screenshots, I’m surprised this isn’t a value added extra that Linbit would like to charge for. Kudos, Linbit, for keeping it GPLv3!

…

I read Tokyo Cabinet: Beyond Key-Value Store today from one of the news sites, and it reminded me of Brian’s hack on Tokyo Cabinet == Tokyo Engine. Looking at TokyoEngine in Brian’s Mercurial repository, there have been no updates in over a year. Is anyone planning on taking up development of this? Tokyo Cabinet looks really interesting, and Brian has already started the enabling of making it a MySQL engine.

…

If you would like to contribute to MySQL development, you can read the relevant top-level page on the MySQL Forge.

This page has some useful links to various forms of contributing to MySQL, including contribution of code to MySQL. (The MySQL|Sun team have recently simplified some of these pages in order to make them more useful to community members and potential contributors.)

Note that after some simple paperwork submitted to Sun ("Sun Contributor Agreement" or "SCA"), any signatory can contribute to any Sun-sponsored …

If you would like to contribute to MySQL development, you can read the relevant top-level page on the MySQL Forge.

This page has some useful links to various forms of contributing to MySQL, including contribution of code to MySQL. (The MySQL|Sun team have recently simplified some of these pages in order to make them more useful to community members and potential contributors.)

Note that after some simple paperwork submitted to Sun ("Sun Contributor Agreement" or "SCA"), any signatory can contribute to any Sun-sponsored …

If you would like to contribute to MySQL development, you can read the relevant top-level page on the MySQL Forge.

This page has some useful links to various forms of contributing to MySQL, including contribution of code to MySQL. (The MySQL|Sun team have recently simplified some of these pages in order to make them more useful to community members and potential contributors.)

Note that after some simple paperwork submitted to Sun ("Sun Contributor Agreement" or "SCA"), any signatory can contribute to any Sun-sponsored …

Giving a bunch of mysql instances something you do everyday and you might think ….. how should I do it? Write a bunch of selects and inserts manually? nahh that takes s**tload of time, should I run binlogs collected from a live system on my test server? nahh thats not practical nor is it real since it doesn’t contain selects, should I gather the general query log and try that out? nahhh  …..

MySQL has been kind enough to supply us with their mysql_slap which does the job for us and given I needed to do a proof of concept on monitoring a group of 4 circular replicated servers I wrote a small script which does the job of slapping them with a varying level of concurrancy, iterations, number of queries and connections for as long as you like.

Here it is and I hope some of you might find it useful for slapping their own test servers :).

#!/bin/bash

NumberOfConcurrentLoads=4
…