Friday , December 15 2017
Home / Uncategorized / MySQL DBA Interview Questions and Answers

MySQL DBA Interview Questions and Answers

MySQL Interview Questions

Before you even start asking questions pay attention to how YOU pronounces MySQL.

The official way to pronounce “MySQL” is “My Ess Que Ell” (not “my sequel”), but we do not mind if you pronounce it as “my sequel” or in some other localized way.

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?

Ans: 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: Explain MySQL Architecture?

Ans: Response should reference some of the following :

Figure

What can they say about the MySQL Query Cache?
You can validate response with the information found here.

Queries must be exactly the same (byte for byte) to be seen as identical.
Queries that use different databases, different protocol versions, or different default character sets are considered different queries and are cached separately.

The cache is not used for queries of the following types:

Queries that are a subquery of an outer query
Queries executed within the body of a stored function, trigger, or event

Question 3: What are the steps involved in taking over a MySQL server when you do not have any credentials?

Ans: 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-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,

The InnoDB Storage Engine
The MyISAM Storage Engine

Bonus points if they can talk about these as well

The MEMORY Storage Engine
The CSV Storage Engine
The ARCHIVE Storage Engine
The BLACKHOLE Storage Engine
The MERGE Storage Engine
The FEDERATED Storage Engine
The EXAMPLE Storage Engine

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

DON’T MISS OUT!
Subscribe To Newsletter
Be the first to get latest updates and exclusive content straight to your email inbox.
Stay Updated
Give it a try, you can unsubscribe anytime.

Comments

comments

Leave a Reply

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

DON’T MISS OUT!
Subscribe To Newsletter
Be the first to get latest updates and exclusive content straight to your email inbox.
Stay Updated
Give it a try, you can unsubscribe anytime.
close-link
GET OUR LATEST CONTENT IN YOUR INBOX

SUBSCRIBE 
Your information will never be shared
close-link
Sign-up for exclusive content. Be the first to hear about DBA Centre Blogs.
Subscribe