mysql – Wise Geek

Enable HTTP MySQL health checks with haproxy

The haproxy loadbalancer is amazing and can be used to reverse proxy a whole bunch of HTTP connections to a webserver farm behind it.

The HTTP proxy bit has some neat features like cookie handling so that a HTTP session “sticks” to one webserver in the pool and some smart healthcheck features. haproxy can also loadbalance TCP connections and this feature can be used with MySQL to loadbal connections. Being at TCP level though, one cannot really judge the health of a MySQL server in one of the backend pools. haproxy knows that port 3306 is open and thats about it. Couple that with the fact that haproxy is not really loadbalancing MySQL connections but “distributing them”, after all haproxy does not know if a connection is a simple one row lookup, an update to thousands of rows or a complex join returning thousands of rows.

So why not leverage HTTP to act as a middleman to MySQL and perform a healthcheck based on any parameters we choose? (eg. MySQL connection threads, replication status etc)

Well, don’t tell Perl but I have been continuing my secret love-affair with Python. Recently I have had some time to get hacking again. My effort is the first version of a MySQL health checker which implements a HTTP server on the MySQL server and returns a “pass” or “fail” assessment of it’s health. It’s pretty basic right now but should allow a sysadmin to perform HTTP healthchecks against it to look for the magic words: “pass” or “fail”.
Get it from my github: https://github.com/imran-uk/mysql-health-check

MyISAM “Table is marked as crashed” recovery

With my new job comes a significant element of MySQL DBA work. It is also opportunity to help my fellow MySQL DBAs by sharing tools, tips and tricks so expect more frequest blog posts!

Today I had to recover a from a bunch of corrupt MyISAM tables in a “cache database”. Client code will throw an exception if these tables are accessed so it is not a good idea to let corrupt tables hang around for long. The fix was in theory easy – just recreate the tables and then rename the corrupt table, rename the new table to the old and then run myisamchk on the old table.

The problem is that “create table foo as bar” does not work if the table is marked as crashed. Some Google-fu found this.
Refer to the MyISAM bit – it really does work. I wrote a bash script to automate it which you can grab here.

However, there is an even quicker way. I discovered that a simple “rename table foo to foo_corrupt” will create a fresh, clean copy of table foo. Not sure if this is intentional but thanks MySQL!

Oh, and sadly these tables cannot be converted to InnoDB – we’re using “INSERT DELAYED” and for some other reasons.