Admin Spotting for Fun and Profit

Abusing MySQL

May 8th, 2008 Posted in Linux

One of the larger complaints about mysql for me has always been the hoops required to find out basic information. I want to check my GRANTS periodically to check permissions. I want backups to not take forever, and I want to be able to use find. Turns out, I can have everything I want and all I have to do is use maatkit.

I discovered this a few nights ago while reading through Jeremy Cole’s blog and noticed some of the functionality he was talking about. While I don’t fully trust their repositories (I prefer to stick with the distro mysql releases), I was very interested in the maatkit functionality.

After talking with Karanbir and waiting patiently for a few minutes, I had a nice shiny build of the maatkit MySQL toolkit in the testing repository to play with. It works exactly as advertised, and I could not be happier with it.

The majority of the maatkit tools are geared toward mysql replicated environments, so there’s a load of master/slave tracking capability including some new ways to track latency, lag, and more. This is all neat, but it’s not something I’m overly interested in just yet, because I don’t have any replication problems.

What does interesting me is this: mk-find

mk-find is essentially a perl implementation of find for mysql. I can now search for and sort tables by size, row, create/modify time, and perform actions based on what returns with –exec.

Another tool within maatkit which caught my eye was mk-parallel-dump, which while exceptionally long to type in (yes, I’m lazy) is quite a good tool for speeding up backups. Instead of simply dumping the entire db all at once into a single file and waiting for it to spool through huge amounts of information, I can pass this utility a directory, run a thread per table, and cut my backup time by a significant percentage of time. Simply run: mk-parallel-dump –tab –basedir /path/to/backups/ and you’re off, but if you want to get even more hi-tech, they include some sample scripts for scheduled backups.

If you’re looking for a way to make your mysql management tasks easier, have a look at maatkit, or just go get it from the CentOS-Testing repository and see how it can simplify your life.

  1. 2 Responses to “Abusing MySQL”

  2. By Ken Crandall on May 14, 2008

    If you’re interested in good, fast MySQL backup, check-out the Zmanda Recovery Manager (ZRM) at http://www.zmanda.com/backup-mysql.html

    [Reply]

    Jim Perrin reply on May 14, 2008 7:49 pm, 14 May 2008:

    Initially I considered this comment an astroturfing incident, and was going to block it, but instead I want to give this post the attention it deserves. Zmanda does do good things for mysql backups, there’s no question about that; however from their own documentation, it uses mysqldump, hotcopy, or snapshotting depending on configuration.

    That it uses mysqldump is the part I want to focus on here, and invite Ken to reply. Will it support maatkit’s mk-parallel-dump, or will it be restricted to mysqldump proper?

    Using mk-parallel-dump is certainly beneficial for faster backups as well as table level archives instead of the more common ‘one file for all’ approach.

    Ken, can Zmanda use mk-parallel-dump (since I spent this post expounding upon its virtues) or were you simply driving by and slapping up a sticker for your project?

    [Reply]

Post a Comment