Technical screening interview questions for a DBA

Below are some DBA Technical Screening questions. 

Most of the ones listed below are applicable to MySQL DBAs:

Question 1: 

How many masters can a replication slave database host have?

Answer: 

Slave can only have one master.

===================================================================================================

Question 2:

For replication purposes, you should set up a special user that has nothing but REPLICATION SLAVE privilege.  Where do you have to set up that user?

* on master

* on slave 

* on both master and slave

Answer: 

On master only

===================================================================================================

Question 3: 

Give an example of an operation that causes sorting in a DBMS?

Answer: 

ORDER BY / GROUP BY

===================================================================================================

Question 4: 

select * from users where name like ‘%elf%’; 

If you have this SQL and `name` column is indexed, will this cause index usage? why or why not?

Answer: 

No. It will not because of the wild card ‘%’ at the front of the search predicate so that a sorted order from an index can not be used.

===================================================================================================

Question 5: 

What are “Views” in the database world? 

Answer:

A view is a virtual table. Every view has a query associated with it.

===================================================================================================

Question 6: 

What is the function of the MySQL Optimizer? 

Answer:

The goal of the optimizer is to choose the most efficient way to execute a SQL statement.

===================================================================================================

Question 7: 

Out of Statement Based Replication and Row Based Replication, which replication format is slower for replicating data over to the slave when you have an update statement that is updating a 100k+ rows at a time? And why?

Answer:

Row Based Replication is slower. Because a large range of rows updated will be slower as the entire set of changed rows will be set across to the slave.

===================================================================================================

Question 8: 

What tool is used to replay the mysql binary logs?

Answer: 

mysqlbinlog

===================================================================================================

Question 10: 

What MySQL log would you enable to see all reads/writes going to the database on the database server?

Answer:

Enable general logging

===================================================================================================

Question 11: How can you check the DDL (data definition language) of a table in MySQL?

Answer:

DESCRIBE <table-name>;

SHOW CREATE TABLE <table-name>;

===================================================================================================

Question 12: What is/are the major goal(s) of normalization? 

Answer:

To remove redundant data

To enable more flexible access to data

To eliminate possibilities of anomalies when modifying data that would make the data become inconsistent

===================================================================================================

Question 13: What the minimum privilege needed to global system variables?

Answer: SUPER privilege

===================================================================================================

Question 14:  What is key cache efficiency? 

Answer: Measure of number of index reads from cache vs index reads from disk. Higher value is better (as close to 1 as possible).

===================================================================================================

Question 15: What parameter determines the length of time a transaction will wait for a resource before “giving up” ? 

Answer: MySQL parameter innodb_lock_wait_timeout.

===================================================================================================

Leave a Reply

Your email address will not be published. Required fields are marked *