Monday, July 6, 2009

My MySQL wishlist (revised)

I wrote about my MySQL wishlist on November 14th 2007 and now it's time for an update. I will copy-paste the old entry. The original text will be in italics.

1. Per user and/or per database quota
Would very useful in setups for shared hosting. This would also prevent one database from bringing down the whole server. Separate tablespaces on different mountpoint can ease the pain, but I consider that a nasty hack.

No update. Still problematic

2. External authentication
I've seen numerous scripts which fetch the authentication info from ldap, a file, another database or some other authentication store. This should be integrated into mysql. The mysql grant tables should be pluggable so it is possible to write a custom authentication plugin. We already have plugable engines and function (UDF) so this shouldn't be that hard is it?

No update. Still problematic

3. Database locator
So you've got hundreds of servers.... and a multitude of databases. How to connect to the right server to reach the database you needed? Something like oracle TNS would be helpful here. What about implementing TNS for MySQL or just using DNS? How do you solve this?

No update. Still problematic

4. Saving extra metadata about the database.
A common way to store stuff like customerid, data classification, service classification (development, production, etc) would be helpful. Many people are already storing this info but there is no way MySQL Administrator, phpMyAdmin or other tools can use it.

No update. Still problematic

5. better protection against run-away queries
I would like to set max_query_time to 2 minutes.... but how?

No update. Still problematic. There is a safe-updates (a.k.a. i-am-a-dummy) option, but this doesn't fix it.

6. restore manager
So you've created a backup using mysqldump for all databases and you've got your would be great if there was a tool which would filter one table or one database from your backup and replay the binlogs for that database. Somewhat like xfsrestore -i. I would call it the see-what-you've-got-and-pick-wha- you-like feature. Point in time recover is just too hard for many people starting with MySQL.

No update. Still problematic, especially with multiple types of backups (dump, hot, NDB)

7. SNMP for statistics
Monitoring and statistics for many kinds of software and devices are using SNMP, but not for MySQL. Many people are using the MySQL protocol to fetch performance counters from their databases for cacti statistics. For some setups this will require many firewall and access list updates/changes while SNMP is already active.

There is a software package which add this functionality to MySQL: mysql-snmp
MySQL Enterprise does some SNMP, but I don't know the exact details

8. SNMP for alerting
Alert reporting to IBM Tivoli, HP OpenView and such requires scripting. How nice would it be to just load a MIB and set the snmp manager address and community and see the alerts coming in

MySQL Enterprise does some SNMP, but I don't know the exact details

8. Auditing
For PCI, SOX404, HIPPA and other regulations auditing would be very helpfull. And ofcourse also for security forensics.
There are some scripts and third-party applications which do basic auditing, mostly to verify secure installation. And Worklog #3771, Worklog #1410 and Worklog #2878 could bring a long-term solution. There are guidelines for audit logging in applications available.

Please let me know if there are any solutions or work-arounds

Update 1: Yes, without maatkit this list would have been a bit longer :) it's also the default location to look for a work-around.