MySQL Interview Questions -

i've been asked screen candidates mysql dba / developer position role requires enterprise level skill set.

i myself sql server person know looking point of view regards scalability / design etc there specific should asking regards mysql?

i ideally ask them enterprise level features of mysql typically use when working on big database. need separate out enterprise developers home / small website kind of guys.

thanks.

although sql server , mysql both rdbms, mysql has many unique features can illustrate difference between novice , expert.

your first step should ensure candidate comfortable using command line, not gui tools such phpmyadmin. during interview, try asking candidate write mysql code create database table or add new index. these basic queries, type gui tools prevent novices mastering. can double-check answers more familiar mysql.

can candidate demonstrate knowledge of how joins work? example, try asking candidate construct query returns rows table 1 no matching entries exist in table two. answer should involve left join.

ask candidate discuss backup strategies, , various strengths , weaknesses of each. candidate should know backing database files directly not effective strategy unless tables myisam. candidate should mention mysqldump cornerstone backups. more sophisticated backup solutions include ibbackup/innobackup , lvm snapshots. ideally, candidate should discuss how backups can affect performance (a common solution use slave server taking backups).

does candidate have experience replication? of common replication configurations , various advantages of each? common setup master-slave, allowing application offload select queries slave servers, along taking backups using slave prevent performance issues on master. common setup master-master, main benefit being ability make schema changes without impacting performance. make sure candidate discusses common issues such cloning slave server (mysqldump + notation of binlog position), load distribution using load balancer or mysql proxy, resolving slave lag breaking larger queries chunks, , how promote slave become new master.

how candidate troubleshoot performance issues? have sufficient knowledge of underlying operating system , hardware diagnose whether bottleneck cpu bound, io bound, or network bound? can demonstrate how use explain discover indexing problems? mention slow query log or configuration options such key buffer, tmp table size, innodb buffer pool size, etc?

does candidate appreciate subtleties of each storage engine? (myisam, innodb, , memory main ones). understand how each storage engine optimizes queries, , how locking handled? @ least, candidate should mention myisam issues table-level lock whereas innodb uses row-level locking.

what safest way make schema changes live database? candidate should mention master-master replication, avoiding locking , performance issues of alter table creating new table desired configuration , using mysqldump or insert ... select followed rename table.

lastly, true measurement of pro experience. if candidate cannot point specific experience managing large data sets in high availability environment, might not able knowledge possess on purely intellectual level.