tail -f findings.out

I don’t know why I didn’t know about this before (or why I forgot about it, more likely), but I came across MySQLTuner recently and was most pleasantly surprised. It’s a Perl script that only requires your MySQL user and password to provide detailed and useful checks of a running MySQL instance. First though, check out the awesome URL you download it at:

Yeah, that’s the actual URL that works. Pretty sweet.

Anyway, next you make it executable, run it, enter creds:

Then the goodies appear:

Oh, that query_cache_limit is tiny! At this point, no changes have been made to your setup. But at a glance you get helpful stats, validate that changes you’ve decided to make are in place, and get alerted to potential improvements.

If I knew Perl now and refreshed my MySQL tuning knowledge, I’d love to help as a maintainer of this lovely script. Maybe sometime soon…

I’ve found that I often create several views that are variations on an initial one, providing a slightly different convenient slice of the data as the need arises. But when a view is created the syntax actually stored can be quite different than what you put in. The parser reorganizes and rewrites to make everything explicit. While better for performance in running the view, this has the unfortunate side effect of transmuting a once readable statement into a forest of parentheses and backticks that can and does induce tears.

First an example view that I created using the handy world database:

This is a view of the 10 cites with the lowest population in the Central Luzon region of Asia. Now let’s see what actually got stored. To get a view’s create statement you can use the MySQL Query Browser and right click the view name shown in the right under Schemata, selecting “Copy SQL to Clipboard”. Or you can run a query like:

Here’s the result for the view above:

I added the line breaks for a modicum of readablilty, the statement is stored without them. I sure don’t want to edit that version in making a variant! This example isn’t actually too bad, but for real world complicated views, it really is quite difficult to read views exported in this way. I often try to store the original create view query just to avoid this pain. But sometimes I only have the stored syntax.

I’ve created a Bash function to make this whole process less painful:

Now to get a cleaned up view you can call this function followed by the database and view names:

To get the create statements for multiple views you can use a loop. For example:

If you need some more flexibility than this I implemented it in Python after an initial fit of rage caused by trying to get various levels of nested strings properly escaped in Bash. That ended up being overly long and unnecessary, and a cooler head prevailed. But the script is here on GitHub if you want it. It creates the same file although without using sed and with some error handling.

MySQL’s “order by” keyword allows the order in which result rows are returned to be defined. This is quite useful in a wide variety of circumstances. You can specify any number of columns whose values you want to order the results by, as well as what sorting order to use (desc or asc; asc is default). However, you may also run into situations in which you want certain groups within the resultset displayed in a certain order based on particular values in the results, not just according to alphanumeric sorting. This is easy to achieve by use of the equal operator or the field keyword within the order by clause.

Some examples are in order. I’ll be using the world database. The following shows country names and a language spoken therein for European countries that speak Arabic, Dutch, and/or Greek:

This returns:

But what if we want Dutch speaking countries first? We can’t use order by Language desc or asc since Dutch results are in the middle of either sorting. Instead:

This orders the results with records having Language = “Dutch” first, followed by all the others ordered by their Language value. And this is the desired result:

You can specify as many values in the order by clause as you want, each also allowing an asc or desc modifier. If you have a number of values in one field you want to specify, the field keyword can save you some typing:

Returning the customized ordering:

Sometimes you just want a database to run some queries against. Any reasonable set of tables with data pertaining to something not terribly complicated will do. Perhaps you have an urge to tease out the intricacies of correlated subqueries, or you need to test some complicated pattern matching functions. MySQL provides a database dump for such recreation, called “world”. The world database contains global population data from Statistics Finland. The data itself is several years old, but it works fine as general test fodder.

Since I normally only use it on my own machines for testing, I’ve found setting it up to be a fixed process. So I created this alias for such situations:

This pulls down the world database dump, unzips it, creates a DB to hold it, imports it, then removes the temporary files. You’ll be prompted twice for the password of the root MySQL user. You could also alter it to include the password if you are so inclined. Add the above alias line to ~/.bashrc, reload it, and you can have a test DB up and waiting within seconds anytime you want.

MySQL replication has a wide range of uses, including debugging applications writing to the DB being replicated. Once set up, all statements “that update data or potentially could have updated it” are stored in one or more binary logs (MySQL manual on the binary log). So if something happens to your data and your application level logs aren’t cutting it, you can use the binary logs of the underlying DB as a source of information.

Assuming you have binary logging turned on, you first need to know where the binary logs are. The query “show binary logs;” will give you the filenames and sizes, but not the path. If you only set “log-bin” in my.cnf then the binary logs are placed in your data directory by default (find this by running “show variables like ‘datadir’;”). Otherwise they will be wherever you specified with “log-bin = /some/path”. If you passed the value via a command line option, run “ps aux | grep mysql” to view the options passed. If all else fails, run “sudo updatedb && locate mysqld-bin”.

Now how to view these logs? There is in fact a handy utility for just this purpose: mysqlbinlog. Just pass it the name of the log file and it returns the text version. So if you only had a single search you could use:

If you had a number of searches you could redirect to a file for processing at your own pace:

This gives you the ability to search across all of the statements affecting your DB, as well as across all of the data coming in.