Interview questions for Mysql DBA
# Explain your responsibilities:
a) Installation and configuration.
b) Managing mysql user accounts.
c) Server Load monitoring.
d) Server space monitoring.
e) Mysql server tuning.
f) Schema creation and maintenance.
g) Backup/Restoration planning and monitoring.
h) Replication monitoring.
i) Server Logs monitoring.
j) Maintaining mysql log files.
k) Mysql Database maintenance and repair.
l) Query optimization.
m) Implement best DR policy.
n) Various DBA tasks automation through cronjob.
o) MIS reporting through scripts.
# Your Lead responsibilities: As per your role/profile.
# How many databases (servers) you are handling: As per your environment.
# Maximum size of server (DB) you have handled: As per your environment.
# Mysql version: As per your environment.
# Linux Version: As per your environment.
# Signed Vs Unsigned:
Signed : can contain -ve values (it is default with integer type datatype)
unsigned : can not contain -ve values and have values from 0 to as per integer type
Note: The “unsigned” types are only available in MySQL, and the rest just use the signed ranges, with one notable exception: tinyint in SQL Server is unsigned and has a value range of 0 to 255
•tinyint : 1 byte, -128 to +127 / 0 to 255 (unsigned)
•smallint : 2 bytes, -32,768 to +32,767 / 0 to 65,535 (unsigned)
•mediumint : 3 bytes, -8,388,608 to 8,388,607 / 0 to 16,777,215 (unsigned)
•int/integer: 4 bytes, -2,147,483,648 to +2,147,483,647 / 0 to 4,294,967,295 (unsigned)
•bigint : 8 bytes, -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 / 0 to 18,446,744,073,709,551,615 (unsigned)
Note: int value can be -2147483648 these are 11 digits so default size is 11 but unsigned int does not allow negative numbers so default size is 10.
# use of NULL data type : NULL means “a missing unknown value” : You can not use it like arithmetic operator instead have to use logical operator: like i_salary = NULL or i_salary <> NULL will not work instead we have to use as i_salary is NULL or i_salary is not NULL. (if there is only 3 values in any column like Y, N and NULL then col1<>’Y’ is not same as col1=’N’ or col1 is NULL as first condition will not check values where NULL is updated)
# Index using methodology with like operator: Index on c_name
select * from db.table where c_name like ‘%zafar%’ : No, index will not work
select * from db.table where c_name like ‘%zafar’ : No, index will not work
select * from db.table where c_name like ‘zafar%’ : Yes, index will work
# DDL : Data Definition Language (schema change like create, alter etc.), DML: Data Manipulation Language (select), DCL: Data Control Language (rights like grants etc.)
# Innodb Vs Myisam: Innodb supports foreign keys, row level locking, transactions but myisam is fast and support table level locking.
# What is ACID : Atomocity (either transaction should complete or rollback), Consistancy (Data should be in consistant state between multiple state of transaction), Isolation (No other query can acess the data during transaction i.e. proper locking), Durability (Data should not lost if system crash).
# How to maintain referential integrity constraint without using ‘on delete cascade’ and ‘on update cascade’: We can mange referential integrity by Triggers.
# Stored Procedure: A stored procedure is a set of sql statements that can be stored in the server and introduces in Mysql 5.4. Benefits are below-
a) User dont need to execute multiple statements again and again while can call sp.
b) It is re-usable.
c) Secure as users don’t need rights on database.
d) can be use in different applications in different languages and in different platforms i.e it is language and platform independent.
e) Reduce trafficking between server and user as resides on server.
# Stored Procedure Vs Functions:
a) Function returns value but sp not.
b) sp can call to other stored routines but function can’t.
c) sp returns result set but function not.
d) sp is called but function select — select fn_calculate(), call sp_changevalue().
e) normally functions are used to compute values while sp to execute tasks.
f) Functions are compiled and executed at run time while sp compiled only one time at the time of creation.
e) sp can change server variables but function can’t.
# Cursor: A cursor acts like a pointer to the set of rows returned by the select statement and executes one by one.
# Trigger: Triggers can not be called but executes automatically on an event.
Triggers Event: Insert, update, delete : A table can have max. 6 cursors i.e. before/after on insert/update/delete (2*3=6).
# How do you know last insert id: select last_insert_id(); — it will return last insert id from same connection.
# If getting foreign key constraint fail at the time of deleting a row, then what will you do, reason of this error: Data exists in child table while trying to delete from master and “on delete cascade no update” functionality updated, so first delete data from child then master. It can be automatically manage by on delete cascade functionality as it will automatically delete all corresponding rows from all child tables automatically. Forcefully we can delete rows from master after setting foreign_key_checks=0 and again 1 after deleting.
# How you get a portion of string: select substr(“this is my string”,1,10) = this is my (from first character to 10th including space)
# What happens when the column is set to AUTO INCREMENT and you reach the maximum value for the table: It stops incrementing and does not overflow to 0 to prevent data loss, but further inserts are going to produce an error, since the key has been used already.
# If you specify data type as Decimal(5,2), what’s the range can be stored = 999.99 or -99.99
# What is data type timestamp : automatically current date and time will be stored in this column at the time of new row insertion but if columns does not have user defined value.
# Some database objects are : table, views, sp, function, index.
# Table is an entity, column is an attribute.
# Sequence in a query: select * from emp_table where emp_id>100 group by emp_id having count(emp_id)>1 order by emp_id : where – group by – having – order by.
# User Rights: GRANT select, insert, update on mydb.mytable to ‘zafar’@'localhost’ identified by ‘default’ with grant options;
# Delete rights only a table but not on db : GRANT select, insert, update on mydb.* to ‘zafar’@'localhost’ identified by ‘default’; GRANT delete on mydb.mytable to zafar@’localhost’;
# Rights for stored procedure, function : CREATE ROUTINE, ALTER ROUTINE
# Rights for triggers: TRIGGER
# How can block few users. : GRANT SHUTDOWN command : GRANT SHUTDOWN ON *.* TO ’user’@’localhost’ IDENTIFIED BY ’multipass’;
# How can unblock if an user has been blocked : mysql> flush hosts; (If user block due to max_connect_errors)
# Default index type in various engines : BTREE : InnoDB for example is exclusively using BTREE for its index. Also, BTREE is the default index type for most storage engines.
# What engine will be used by temp table: MEMORY table but as it grows too big it will be automatically converted to MYISAM.
# Max. Index in a Table : mysql 5.5 can have upto 64 secondary index per table but All storage engines support atleast 16 index per table.
# Max. Column in a Table : 1000 columns in mysql 5.0 or higher.
# Max. Size of a db in innodb : Max. DB SIZE in 5.5 is 64 TB (also known as table space) in linux 2.4+ (depends on your system OS) and can have 2 billion tables. If we are using file_per_table option then each table have its own table space so max. size will be far more as each table can grow upto 64 TB.
# Max Size of a Table in innodb: Max. Table size is 4 TB (approx. max. rows 1 billion) in linux 2.4+ (depends on your system OS, also it can be extended). and can have (myisam table max size is 4 GB)
# Max single row size (regardless engine) : 65535 bytes per row.
# Clustered Vs Non Clustered Index: Typically primary key is clusterd index (a table can have only one clusterd index) otherwise first unique key which does not have null value otherwise mysql generates its own hidden clustered key which has rowid. Other index instead of clustered index are called secondary index.
# Primary Vs Unique Key/Candidate Key: A Candidate Key can be any column or a combination of columns that can qualify as unique key in database. There can be multiple Candidate Keys in one table. A table can have only single primary key and any candidate key can be primary key if that does not have null value.
# How index works: All indexes other than the clustered index are known as secondary indexes. In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index. InnoDB uses this primary key value to search for the row in the clustered index.
# mysql is single Threading or multi : Multi threading.
# Mysql version migration issues resolution through a single command. : mysql_upgrade
# difference between Mysql4.0 & 5.0 : innodb started from 5.0
# How we can keep our server smooth running : Proper filteration , indexing, optimization, partioning, use of slave etc.
# Storage engines in mysql :
innodb: Supports transactions, row-level locking, and foreign keys, need more storage, crash recovery.
myisam: fast, fast locking, table level locking, locking even by select query, low overhead.
CSV: (introduced in mysql 5.0 but functional in 5.1) The CSV storage engine stores data in text files using comma-separated values format. It does not support index. It can be open in excel.
a) It allows flat files to be referenced via SQL and used alongside other data that has been loaded into MySQL.
b) Editing data stored via the CSV engine can be performed even when the MySQL server is down through standard file editors.
c) Data from any CSV engine file can easily be imported into any standard spreadsheet program (e.g. Microsoft Excel).
d) It allows for the instantaneous loading of massive amounts of data into the MySQL server.
HEAP/MEMORY: Hash based, stored in memory, fast in speed and useful for temporary tables, used only RAM. But no text or blob fields are allowed with heap tables and it does not support auto_increment.
ARCHIVE: Stored data in archive form with ext .arm (along with .frm), supports select & insert but does not support update, delete & replace.
BLACKHOLE: /dev/null storage engine (anything you write to it disappears). Inserts into a BLACKHOLE table do not store any data, but if the binary log is enabled, the SQL statements are logged (and replicated to slave servers). This can be useful as a repeater or filter mechanism. Suppose that your application requires slave-side filtering rules, but transferring all binary log data to the slave first results in too much traffic. In such a case, it is possible to set up on the master host a “dummy” slave process whose default storage engine is BLACKHOLE, depicted as follows. The master writes to its binary log. The “dummy” mysqld process acts as a slave, applying the desired combination of replicate-do-* and replicate-ignore-* rules, and writes a new, filtered binary log of its own. This filtered log is provided to the slave.
FEDERATED: (Not supported by mysql) : The FEDERATED storage engine is not enabled by default in the running server; to enable FEDERATED, you must start the MySQL server binary using the –federated option. The FEDERATED storage engine lets you access data from a remote MySQL database without using replication or cluster technology. Querying a local FEDERATED table automatically pulls the data from the remote (federated) tables. No data is stored on the local tables. A remote server with a database table, which in turn consists of the table definition (stored in the .frm file) and the associated table. The table type of the remote table may be any type supported by the remote mysqld server.
# Few examples of session and global variables:
a) Session Variables : insert_id, last_insert_id, proxy_user, sql_buffer_result.
b) Both (Global & Session) Variables: Foreign_key_checks, group_concat_max_len, join_buffer_size, max_join_size.
c) Global Variables: flush, hostname, innodb_buffer_pool_size, innodb_file_per_table, max_connections, query_cache_size.
# How can check innodb status and what can be checked by this:
SHOW ENGINE INNODB STATUS; (innodb monitor output)
BACKGROUND THREAD : Threads running in backgroup information like flush logs, thread in sleep etc.
SEMAPHORES : OS and MUTEX SPIN waits information.
LATEST FOREIGN KEY ERROR : Foreign key related information like if any foreign key constraint fail etc.
TRANSACTIONS : Transaction information
FILE I/O :
INSERT BUFFER AND ADAPTIVE HASH INDEX :
LOG : Logs table details.
BUFFER POOL AND MEMORY : Information about memory usage.
ROW OPERATIONS : queries inside innodb, queries in queue, no of rows insertion, update, delete etc.
# Example of logical VS physical backup:
a) copy the server’s db directories and files via operating system means
b) usually does not guarantee you will be able to use the files with the db after copying them back
c) can be faster than logical backup
d) can take more place than logical backup, if you are copying also indexes.
a) extracts db structure and values via db backup utilities (frequently named ‘dumps’)
b) usually guarantees you will be able to use them with db after using db restore facility (provided the backup process went OK)
c) can be slower;
d) with some DBs can lock the DB objects while backing them up;
e) can take less or more place than physical backup, depending on their format (depends on DB)
# Steps to alter 70 GB (bulky) table with downtime.
# Alter a bulky 70 GB table with out downtime.
# Chalanges at the time of mysql migration-
a) At the time of 5.0 to 5.5 :
aa) _filename is key word in 5.5
ab) some extra columns in user & db tables.
b) At the time of 5.5 to 5.6 :
ba) Some extra column in user & db tables
bb) user’s password format has been changed (solution: Need to enable old_password varibale)
bc) Warning at the time of running commands from either dos prompt or linux as “Warning : Using a password on the command line interface can be insecure.”
bd) Not able to connect server through application : Need to upgrade ODBC driver.
# Different types of log shipping: Error log, slow log, general log, Binary Logs, logs through trigger and application log.
# If there is load on server how you track:
a) Track running queris on server.
b) monitor slowlogs.
c) monitor general logs as per requirement.
d) tracking with the help of cacti/nagios/monyog etc.
e) Top command.
# Profiling: examine the db or query : set profiling=1; then show profiles; (it will show all queries from that connection, we can get query_id from here) then show profile for query 1; (it will show all states of that query and its time, we can check in which state query is taking extra time and optimize accordingly.)
# How you know about slow queries: Will do slow logs enable and monitor them.
# What Type trouble shooting you are doing: replication issues, query optimization, server load issues, foreign key related issues, backup/restoration issues.
# Server Tracking Tool: cacti, Monyog, jetprofiler, nagios.
# Linux Installation:
# Is different platform replication is possible: Yes
# Is different Version replication is possible (master/slave different version): Yes if any keyword/functionality does not conflict.
# Is different RDBMS (mysql/mssql) replicatiob is possible: We can link server but this functionality available only in MS SQL but not in mysql.
# Active (Master) and Passive (Slave) replication : master – slave.
Possible Master/Slave replications:
a) Single Master to Single Slave
b) Single Master to Mulitple slave
c) Multiple Master to Single Slave
# Must variables for replication:
a) Skip-networking should be disable on master.
b) Binary logging must be enabled on master. (log_bin=ON, sql_log_bin=on OR update in my.cnf file log-bin=mysql-bin)
c) Each server (master/slave) must be configured with a unique id.
d) A User (need to create replication user) needed for replication on master server.
e) Other variables are used in CHANGE MASTER TO master_host=’m_hostname’, master_port=3306, master_user=’repl’ , master_password=’repl123′, master_log_file=’mysql-bin.000001′, master_log_pos=4, master_connect_retry=10;
# How can Slave start without starting replicatioin: skip-slave-start update in cnf and stat slave: It tells the slave server not to start the slave threads when the server starts. To start the threads later, use a START SLAVE statement.
# Important 2 threads used in replication: As both thead are slave thread means it used pull methodology.
SLAVE I/O THREAD: Read updates from master binlogs and copies them to slave relay logs file.
SLAVE SQL THREAD: It read relay log file and execute on slave.
# What’s functionality is used by sql thread in replication: Pull methodology.
# How can avoid a specific query execution on slave:
‘–replicate-wild-ignore-table=db_name.tbl_name : Tells the slave thread not to replicate a statement where any table matches the given wildcard pattern. To specify more than one table to ignore, use this option multiple times, once for each table. This works for cross-database updates.
For Example: –replicate-wild-ignore-table=foo%.bar% does not replicate updates that use a table where the database name starts with foo and the table name starts with bar.
# Important variables in master to master replication:
Unique server id
a) auto_increment_increment=2 on both servers
b) auto_increment_offset=1 on first and 2 on 2nd server
c) Need to create user rep1 on server 1, which should be able to connect server 2 and rep2 on server 2 which should be able to connect server 1.
# Mathmatical calculation of variables.
Total RAM (innodb) = innodb_buffer_pool_size +key_buffer_size +innodb_additional_mem_pool_size +innodb_log_buffer_size +max_connections *(sort_buffer_size +read_buffer_size +binlog_cache_size +maximum_thread_stack_size);
Total RAM (Myisam) = key_buffer+(sort_buffer_size+read_buffer_size)*max_connections
# Most common and important variables:
Max. size of innodb_buffer_pool_size should be 70% or total RAM for innodb server and key_buffer_size should be non-zero from 10 to 50 MB.
Max. size of key_buffer_size should be 20% of total RAM but not more than 3 GB for myisam server here innodb_buffer_pool_size can be 0.
# Max. Connections Vs Thread_stack : thread_stack_size = 0.2 MB / connection means 200 MB for 1000 connections.
# Swap Memory: 8 GB if RAM >= 8 GB otherwise 1/2 of RAM. (old calculation was double of RAM)
# How you will set engine: default_storage_engine = INNODB in my.cnf or SET default_storage_engine=INNODB or alter table mytable engine=innodb;
# Variable by which we can print log co-ordinates and timestamp in backup:
‘–master-data=1 or 2 (default is 1) : If 2 then binary log coordinates will be stored as comment like #CHANGE MASTER TO…. If 1 then this command will be enable in backup data and will replicate data on slave after these co-ordinates.
# Variables those control to heap table size: tmp_table_size, max_heap_table_size
# If heap table size goes out of given size: it will automatically converted to myisam.
# variable for own ibd for table: innodb_file_per_table
# Vraible for storing log in table or file : log_output = FILE (default) OR TABLE OR FILE,TABLE.
# Variable for type of replication : binlog_format = STATEMENT (DEFAULT) OR ROW OR MIXED.
# symbolic-links=0 : Disabling symbolic-links is recommended to prevent assorted security risks, by this (set 0) we can ensure that no one can use mysqld to drop or rename a file outside the data directory.
# skip-name-resolve : It uses IP addresses rather than host names when creating grant table entries. It is to fast net speed as MySQL grants authentication on users based on IP address or hostnames.
# Enabling different logs:
Enable general log:
general_log = 1
general_log_file = /home/genlog/general.log (rights : chown -R mysql:mysql /var/log/slowmysqld.log for safe side create file by “touch /var/log/slowmysqld.log” and then manage rights.)
Enable slow logs
slow_query_log = 1
slow_query_log_file = /var/log/slowmysqld.log (rights : chown -R mysql:mysql /var/log/slowmysqld.log for safe side create file by “touch /var/log/slowmysqld.log” and then manage rights.)
long_query_time = 2
Enable error logs
log-error = /var/log/mysqld.log : to log server errors.
#Important Server Variables:
# 2nd highest salary. :
SELECT salary FROM employee AS t1 WHERE 2 = (SELECT COUNT(DISTINCT salary) FROM employee AS t2 WHERE t1.salary<=t2.salary) GROUP BY salary; (This is a example of correlated subquery as it is subquery checking a condition out side of subquery)
# How can get only duplicate salary rows:
select salary from employee group by salary having count(salary) > 1
# Fetch product wise sub total of quantity along with grand total of all products quantity in a single query. (can be use rollup function).
SELECT IF(product_name IS NULL,’Grand Total’,product_name) AS ‘product_name’, COUNT(product_name) AS ‘Total Products’, SUM(prod_amt) AS ‘Product_Value’ FROM product GROUP BY product_name WITH ROLLUP;
# How can we check Physical health of database through linux.: (mysql is running or not) : mtop, show processlist, replication monitoring etc.
# How we can know last starting date/time of mysql (innodb) through command not error-log. : top command (it will show total no of days from how much days server is on)
# Linux filter :
a) grep, less, more etc.
b) directory Listing filters (ls) : -l (vertical listing with other information but size in bites), -lh (size in higher units), -m(horizontal listing), -S (sort by size)
# Command to install MYSQL:
shell> rpm -i MYSQL-server-VERSION.glibc23.ix86_64.rpm
shell> rpm -i MYSQL-client-VERSION.glibc23.ix86_64.rpm
Note: 1. As a mysql server we install only server.
2. Where -i (standard minimul installation) and -gpl (full installation)
3. glibc23 (Platform independent, should run on any Linux distribution that supports glibc 2.3)
4. ix86_64 (for 64 bit server) and i386, i586, i686 are for 32 bit servers (Pentium processor or better)
# difference in telnet & ftp : We can use any service through telnet but ftp is file transfer protocol so it will be only used for files.
# port for ftp (20,21), telnet (23), scp (22), ssh (22) etc. :
# To check disk input/output : iostat 2 (will provide results after every 2 seconds : iowait should be minimum <0.5% and %idle should be max. > 90%
# How do you start/stop mysql on windows and linux: service mysql start and service mysql stop (linux actually this service exists in /etc/init.d/mysql start), net start mysql and net stop mysql (windows).