April 8th, 2009

Who is going to make MySQL easier to use?

This may appear a bit of a rant - but it's really intended as more of an observation from having trained people how to use MySQL, and noticing that everyone seems to make the same beginner mistakes. If you read the "Continued MySQL Values" on the MySQL Website, you'll notice that the third one in the list is:
  • The best and the most-used database in the world for online applications
  • Available and affordable for all
  • Easy to use
  • Continuously improved while remaining fast, secure and reliable
  • Fun to use and improve
  • Free from bugs

Note that "Fun to use" doesn't sound much like a database, and "free from bugs" will always be a distant dream ;).

It was Easy to Use that got me into MySQL, but I think this is one of the goals that has lost focus over the years. If you look at bugs.mysql.com, there are a lot of annoying little S5 (Feature Requests) that would probably take the right person only a few minutes to fix. The sort of things I am talking about are:
  • SHOW SLAVE STATUS - Has at least one annoying ease of use bug. If it refuses to connect to the master because it shares the same server id, it won't show you that here. You have to go to the log file.
  • SHOW SLAVE STATUS Also shows you the 'last error' it incurred, with no way to clear this error. It confuses beginners
  • What is the difference between this wait_timeout and interactive_timeout thing, and why is it that when I set wait_timeout to 0 it converts to wait_timeout =1 and disconnects me? - I would prefer wait_timeout=0 to mean unlimited - not that I can think of too many good reasons to use it.
  • The anonymous user serves almost no practical use.
  • Changing InnoDB log file size requires you to do some shutdown and rename trickery, when this should be automatically done for you.
  • When connecting to a server on -h localhost mysql decides that "oh, you want the socket file", and due to misconfiguration it might go to the wrong location and tell you it can't connect even though your server is running.
  • The error log file is very bad at offering levels of configurable verbosity, or consistency which would allow you to grep through it for errors easier. Often it tells me an error code, which I am supposed to look up in perror - but most beginners don't know about perror.
  • ...

But they are probably never going to be fixed. I think it is sad to see that many MySQL users have now seen that these issues don't get fixed, so they no longer submit feature request bug reports. My theory is that:

  • MySQL/Sun is busy implementing new features to snag new customers. If they update a feature (such as the subquery optimizations in 6.0) it is normally going to be one of those big compelling features that all of their customers are demanding.
  • The small number of Community Contributors are most likely going to be writing patches to scratch an itch (either better performance or diagnostics). It's not going to be one of these annoying little things they long ago discovered and will no longer care about.
It's good to see that Drizzle has actually been very good at attacking a large number of items on my list. It's just sad to have to wait until Drizzle is ready, because this part of MySQL isn't broken, it just needs some love.

Very Simple Introduction to Using XtraBackup on Max OS X

I've started using Xtrabackup to backup MySQL on my MacBook. Here's an example of a quick backup and restore:

1. Download the latest .tar.gz from Percona:

$ cd /tmp
$ wget http://www.percona.com/mysql/xtrabackup/0.5/xtrabackup-0.5-macos.x86_64.tar.gz
$ tar -xzf xtrabackup-*-macos.x86_64.tar.gz
$ cd xtrabackup*

2. This directory should contain innobackupex-1.5.1 and xtrabackup. You need to install these into a directory that appears in your $PATH. In my case, I am going to group it install it where my MySQL binaries are located (/usr/local/mysql/bin):

$ ls
innobackupex-1.5.1 xtrabackup
$ cp * /usr/local/mysql/bin/

3. Create a directory where you want your backup to go. In this case it's just a demo - so I'll use my tmpdir.

$ mkdir -p /tmp/backup

4. Test running the backup:

$ innobackupex-1.5.1 /tmp/backup/

Quick Explanation:
innobackupex-1.5.1 is a Perl script that insures that all of your non-InnoDB tables and other MySQL meta data is backed up. You can think of it as a wrapper around xtrabackup, which backs up the data inside InnoDB.

5. Check the data is backed up:

$ cd /tmp/backup
$ ls
$ cd 2009-04-08_15-12-52
$ ls
backup-my.cnf mysql-stderr xtrabackup_binlog_info
employees mysql-stdout xtrabackup_checkpoints
ibdata1 test xtrabackup_logfile

6. Attempt a recovery:

innobackupex-1.5.1 --copy-back /tmp/backup/2009-04-08_15-12-52

Disclaimer: In case you didn't know it, I work for Percona - the company that wrote xtrabackup.