Wednesday, May 1, 2013

MySQL Interview Questions


A good test taker does not make a good DBA. 
An interviewer should ask about the DBA's interests and reading materials. 
Do they know Planet.mysql.com? Do they know and read the active authors blog posts? 
If they are then they should be learning and becoming aware of other situations that could come in handy one day. Not just focused on their own environment. 


This is part of a multi-part blog post. 

MySQL Interview Questions

Before you even start asking questions pay attention to how YOU as well as the candidate pronounces MySQL. 
The reason I bring this up is it will show the dedication and experience with MySQL. It is allowed to pronounce it in different ways but it is is pronounced My Ess Que Ell then shows they know the history. 


Question 1:
What does ACID stand for?

Response should include some of the following :

A: atomicity.
  •     Autocommit setting.
  •     COMMIT statement.
  •     ROLLBACK statement.

C: consistency.
  •     InnoDB doublewrite buffer.
  •     InnoDB crash recovery.

I:: isolation.
  • Autocommit setting.
  • SET ISOLATION LEVEL statement.
  • InnoDB locking.

D: durability.
  •     InnoDB doublewrite buffer, turned on and off by the innodb_doublewrite
  •     Configuration option innodb_flush_log_at_trx_commit.
  •     Configuration option sync_binlog.
  •     Configuration option innodb_file_per_table.
  •     Write buffer in a storage device, such as a disk drive, SSD, or RAID array.
Question 2:
What can you tell me about the MySQL Architecture?

Response should reference some of the following : 
What can you tell me MySQL and Disk I/O ?

Let them explain their RAID level  preferences and why. 
Additional reading for you with this can be found here.


Question 4:
What are the steps involved in taking over a MySQL server when you do not have any credentials?
Response should be similar to some of the following : 
  • stop the service: # /etc/init.d/mysql stop
  • Restart with skip grand: # mysqld_safe ... --skip-grant-tables &
    • /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin -- user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/lib/mysql/kdlarson-pc.pid
  • Connect as root:    
    • # mysql -u root
  • To Setup a new password :
    • use mysql;
    • mysql> update user set password=PASSWORD("NEW-ROOT- PASSWORD") where User='root';
    • mysql> flush privileges; 
    • mysql> quit

Question 5:



How can you define MySQL Indexes to people who do not know what they are? 

Let them explain their definition of an index. The point is not to be overly technical but to explain the point and reason for them.

A common example for index to think of a phone book or to think of an index card in the library. 
They are used to find something quickly and logically. If a phone book had everyone listed by firstname instead of lastname it would be harder to find and make updates. 

People used to use the library card catalog to find books in the library. This was useful because they stored information on index cards with location information so it was easy to location the related information. 


Follow up question:
Is it better to have a lot of indexes across different types of data ?

Response could include some of the following : 

More indexes mean more work but it also means the more indexes mean more ways of looking up data fast. A DBA needs to determine the required amount of indexes per the related application and data needs.  

Question 6:
What is a MySQL Query
 and what makes a good query?

Allow them explain what a query is and how they prefer to write a query. 
It could include some of the following:
  • SQL queries are like little programs in and of themselves. 
  • They ask the database server to collect selections of records, cross tabulate them with other collections of records, then sort them, and slice and dice them. 
  • All of this requires MySQL to build temporary tables, perform resource intensive sorts and then organize the output in nice bite size chunks.

Question 7:
What storage engines have they used?

They are likely to reference some of the following. These are all links to the mysql.com website so you can read more about them, 


Bonus points if they can talk about these as well

Question 8:
What is has been the hardest problem you ever had to solve with MySQL?

Allow them explain and go into detail about the problem as well as the solution. 
  • Why was it a problem ?
  • How did you fix it? 
  • Was it a revenue dependent emergency fix? 
  • Did the problem reappear or stay fixed ?
Question 9:

What do they prefer MySQL, Percona or MariaDB?

Allow them to talk and explain what they like and why. This will allow you to see what they hold dear as values. Some might prefer MariaDB because they are dedicated to the open source message, others might prefer Percona because if offers open source tools while others prefer MySQL because that is the source and original. 


Question 10:

What is their experience with MySQL replication?

Allow them to talk about their different experiences and replicated setups. 
Some of their information could be like the following or these could be follow up questions.

Follow up questions:

How would you setup master/slave:
  •     full dump of the primary database,while it’s tables are locked.
  •     capture the master status, logfile & position at that time   (  --master-data[=#]   )
  •     import data on new machine
  •     CHANGE MASTER TO
  •     SHOW SLAVE STATUS
  •     Slave_IO_Running: Yes
  •     Slave_SQL_Running: Yes
How would you setup master/master replication:

    Master-master replication is similar to master/slave replication, except one additional step.
  •         On Slave SHOW MASTER STATUS
  •         Return to the primary box, and run the CHANGE MASTER TO
  •             | auto_increment_increment    | 1     |
  •             | auto_increment_offset       | 1     |
What is the command to set the Master on a Slave:
CHANGE MASTER TO
  -> MASTER_HOST='master_host_name',
  -> MASTER_USER='replication_user_name', 
 ->  MASTER_PASSWORD='replication_password',
 ->  MASTER_LOG_FILE='recorded_log_file_name',
 ->  MASTER_LOG_POS=recorded_log_position;


Do you know of a tool that helps with replication integrity checking?
        Percona’s pt-table-checksum is the preventative tool to use
            It can build checksums of all your tables, and then propagate those checksums through replication to the slave.

How to install Semisynchronous Replication
  • INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
  • master> SET GLOBAL rpl_semi_sync_master_enabled = on;
  • slave> SET GLOBAL rpl_semi_sync_slave_enabled = on;
  • slave> STOP SLAVE IO_THREAD; START SLAVE IO_THREAD;
  • master> SHOW STATUS LIKE 'Rpl_semi_sync_master_clients';
+------------------------------+-------+
| Variable_name    | Value |
+------------------------------+-------+
| Rpl_semi_sync_master_clients | 1    |
+------------------------------+-------+

How can you stop  replication on all slave servers at the same point in time? 

This will show if they use tools or hack methods. 
Normally, replication stops when an error occurs on the slave, so if they force an error that is replicated  it would stop the all slaves at the same point in time, an "old school" method to stoping the slaves. 



Question 11:
What else do they consider themselves, a sysadmin or a developer?

Allow them to talk about their experiences and how it has shaped their career. 
Historically MySQL DBA are created out of their work experience. Some are focused on System administration while others are Web Developers, sometimes they are all of the above. Either way they now have a focus on the MySQL database and you might be able to use that experience to your advantage. 



Question 12:
What are the major differences between MySQL 5.1 and 5.5?
  • InnoDB Becomes Default Storage Engine
  • Semi-Synchronous Replicatio
  • Improved Recovery Performance
  • InnoDB Stats in Performance Schema
  • Multiple Buffer Pool Instances
  • More Partitioning Options
  If you do not know the answers to these features then review more here.

 
Question 13:
What are the major differences between MyISAM and InnoDB?
 InnoDB follows the ACID model, with transactions featuring commit, rollback, and crash-recovery capabilities to protect user data. -- Manual