Technical screening questions for a MySQL DBA

In the past year, I have interviewed dozens of DBAs with a view to hiring a MySQL DBA. I have found that while many DBAs from other backgrounds, for example MS SQL or Oracle, claim to also have MySQL experience, a lot of them fail the screening once we get into the technical nuances of MySQL specifics.

For example, here are a few typical screening questions I ask, along with the approximate answers I expect to get back:

What are the two main storage engines in MySQL, and when would you use either?

MyISAM and InnoDB. InnoDB is transaction-safe, has row-level locking (MyISAM has table level locking), and supports foreign keys. MyISAM does not support foreign keys and is not ACID compliant, so you should only consider using MyISAM for new projects if you have specific reasons to do so (e.g. to use full-text support in MyISAM). This is basic MySQL stuff, but I have had candidates struggle to list off a few.

What kind of replication can you have with MySQL?

Here I am looking for experience in setting up master-slave and master-master configurations, along with using load balancers. It would be good if a candidate also knows what types of replication there are (row level versus query level, which is based on the query log). Another gotcha: trouble-shooting replication lag.

What are the different levels of transaction isolation level? What are they trying to prevent?

There are four levels, ranging from READ UNCOMMITED (not safe as it allows dirty reads) to SERIALIZABLE. READ COMMITED prevents dirty reads, REPEATABLE READ prevents non-repeatable reads, and SERIALIZABLE prevents phantom reads. A candidate should know what these are.

How would you make a default MySQL install secure?

Set the root user password, remove remote root user access, remove test database, remove anonymous user, set up new application-specific accounts with strong passwords and tight host and grant access, enable SSL etc.

I have a longer list that I work through, but this is a good sample. To be honest, given the widespread use of MySQL and the amount of highly-paid work available, I am surprised that there are not more DBAs building a career around it. If your are interested in pursuing this career however, the above areas are well worth studying.