Abusing MySQL
May 8th, 2008 Posted in LinuxOne 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.
4 Responses to “Abusing MySQL”
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
By Jim Perrin on May 14, 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?
By Ken on May 18, 2008
Until I read your blog post, I was not aware of Maatkit (and mk-find is most definitely cool!)
ZRM, at this time, does not support using mk-parallel-dump to perform its dumps. That’s not to say that it can’t or won’t. We just released a new version that uses InnoHotCopy so we’re not opposed to adding support for more backup methods. The focus of the ZRM tool itself is the manageability of MySQL backups, independent of the underlying backup mechanism(s). Using mk-parallel-dump seems to be one of the most performant backup solutions out there, and so support for it would definitely be a plus. I’ve posed your question to the rest of our engineers to see what their thoughts are. I will follow-up when I hear more from them.
If you’re not interested, I apologize if I’ve wasted your time. (FWIW, no astroturfing was intended, which is why I linked to the community version, versus the paid version.)
By Jim Perrin on May 18, 2008
No apology needed, I was simply curious and wondering if you’d follow up. I know that I can come across as hostile to folks who mean well, and so the apology should be mine. I would very much be interested in seeing ZRM support maatkit’s parallel backups, or even improving upon them if possible. Thank you for your follow up, and I’ll try to not retaliate against folks who actually stop by here.