Published on 2015-02-06 by John Collins. Please follow me on Twitter for more:
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:
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.
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.
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.
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.