Before I start a story about the data recovery case I worked on yesterday, here's a quick tip - having a database backup does not mean you can restore from it. Always verify your backup can be used to restore the database! If not automatically, do this manually, at least once a month. No, seriously - in most of the recovery cases I worked on, customers did have some sort of backup, but it just wasn't working, complete and what not. Someone set it up and never bothered to check if it still works after a while.

Anyway, this post is not really about the backups but rather about few interesting things I learned during last recovery case.

First, some facts about the system and how data was lost:

• MySQL had a dedicated partition on XFS file system
• Server was running innodb_file_per_table
• There was a …

I’ve just uploaded the new release of innotop to Google Code. Short version of the changelog: works on MySQL 5.1 with the InnoDB plugin; more efficient; supports Percona/MariaDB USER_STATISTICS data; fixes a bunch of small annoying bugs.

Longer version:

2010-11-06: version 1.8.0

   Changes:
   * Don't re-fetch SHOW VARIABLES every iteration; it's too slow on many hosts.
   * Add a filter to remove EVENT threads in SHOW PROCESSLIST (issue 32).
   * Add a timestamp to output in -n mode, when -t is specified (issue 37).
   * Add a new U mode, for Percona/MariaDB USER_STATISTICS (issue 39).
   * Add support for millisecond query time in Percona Server (issue 39).
   * Display a summary of queries executed in Query List mode (issue 26).

   Bugs fixed:
   * Made config-file …

I’ve written a tool to profile a process’s IO usage. It works by gathering lsof and strace from a process, and then figuring out how the file descriptors, function calls, and filenames are all related to each other. The manual page has examples. I’m curious to see how it works for you. Note that it might be a good idea to run this on your development or staging system before you go running it against your production MySQL server — there are rumors of strace misbehaving on some kernels.

Related posts:

1. How to find per-process I/O statistics on Linux
2. iopp: a tool …

Few days ago I was working on a case where we needed to modify a lot of data before pushing it to sphinx – MySQL did not have a function to do the thing so I thought I’ll write MySQL Stored Function and we’ll be good to go. It worked! But not so well really – building the index, which was taking 10 minutes, was now taking 16 minutes. Then we added another MySQL function for different set of attributes and indexing speed went from 16 minutes to 26 minutes. I knew using UDF would be faster, but I had no idea how much. Have you ever wondered?

So what were the modifications we needed? It was couple very simple things – (1) two varchar columns needed leading nonalpha characters trimmed, so “123 ^&* …

Seriously, it did.  Sorta.

I use Workbench for my daily work, and it’s a great tool.  If you haven’t tried the 5.2 release yet, you should.  While performing some maintenance, I happened to issue a DELETE statement against a table which had no indexes (it was 10 rows), and Workbench complained:

Error Code: 1175
You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

It turns out this is a new feature in 5.2.26 (and is still there in 5.2.27) – Workbench now uses the equivalent of –safe-updates mode for the mysql command-line client (also known as the –i-am-a-dummy option – seriously).  This wasn’t exactly convenient for me, …

We’re proud to announce the next release of MySQL Workbench, version 5.2.27. This is the second maintenance release for 5.2 GA (Generally Available). This maintenance release does not introduce any new features, but focuses on general product improvement and usability.  We hope you will make MySQL Workbench your preferred tool for Design, Development, and Administration of your MySQL database applications.

As always, we want to thank everyone for the great feedback we have received. This helps us to continuously improve and extend the functionality and stability of MySQL Workbench – please keep up on approaching us with any ideas to develop our product even further.

MySQL Workbench 5.2 GA

• Data Modeling
• Query (replaces the old MySQL Query Browser)
• Administration (replaces the old MySQL Administrator)

Please get your copy from our Download site. Sources and binary …

In my business (weather) we use lots map based (Geo) information.  Almost every table has latitude and longitude. Working with this kind of data can be exciting and frustrating.  This should give you a quick start into GIS with MySQL.

“A geographic information system (GIS), or geographical information system, is any system that captures, stores, analyzes, manages, and presents data that are linked to location. In the simplest terms, GIS is the merging of cartography, statistical analysis, and database technology. GIS systems are used in cartography, remote sensing, land surveying, …

To have more visibility of our systems I needed a method to quickly query large sets of MySQL servers. Though there are few methods like ‘xargs -P’ and dsh, they are overkill for the SQL-only job, and simply not fast enough.

So, I hacked pmysql (274 lines of C) – a tool that is able to run queries on multiple servers in parallel. It can run SQL query on 1000 servers in under 0.1s, and scales well with larger sets of servers.

Use cases can be various – from configuration/schema checks (I really enjoyed using information_schema), to ‘global processlist’ analysis.
Ability to crunch hundreds of megabytes of processlist info in few seconds – priceless, so is schema verification of millions of tables.

It is fast enough to use it as backend for some of real-time health check CGI …

For those of you who miss what Maatkit’s mk-audit tool (now retired) gave you, there’s a pair of tools in Aspersa that more than replaces it. I wrote previously about the summary tool. I don’t think I have mentioned the mysql-summary tool. It has been under development for a while, and at this point it has quite a lot of functionality. You can see a sample of the output on its wiki page.

Related posts:

1. Apsersa’s summary tool supports Adaptec and MegaRAID …

The Maatkit article on Wikipedia was removed some time ago, after being deemed not notable. I believe this is no longer the case. It’s hard to find a credible book published on MySQL in the last few years that doesn’t mention Maatkit, there’s quite a bit of blogging about it from MySQL experts and prominent community members, and the toolkit is certainly in wide use — it’s important enough that notable companies are supporting its development. It’s available through every major Unix-like operating system’s package repository. On Debian, it’s actually part of the mysql-client package, so if you install MySQL, you automatically get Maatkit too. I believe it’s probably the second most important set of MySQL command-line tools; the most …