Wednesday, 8 May 2013

MySql DBA interview question answer


MySQL DBA Interview Questions and Answers - Series 1



WHAT IS DATABASE:
A database is a collection of information that is organized so that it can easily be accessed, managed, and updated.

1. What is mysql.

Mysql is an most papular open source RDBMS(Relational Database Management System).It is using SQL (Structured Query Language). Any one can download it and easy to use and manage.

2.What are the features of Mysql.

1.Cross-platform support.
2.Wide range of application programming interfaces.
3. Stored procedures,Triggers,Cursor.

3.What is the default port for MySQL Server

3306.

4. Why Mysql Widely used.

Mysql is open source and free to use .Also it is reliable and fast.

5. What is DDL, DML and DCL ?

DDL:- DDL stands for Data Definition Language.Its deals with database schemas and descriptions of how the data should reside in the database.Example:- CREATE TABLE or ALTER TABLE,
DML:- DML stands for data manipulation Language.Its deals with SELECT, INSERT, UPDATE , DELETE etc.
DCL:- DCL stand for Data Control Language.Its contain commands like GRANT,Revoke etc.

6. Difference between FLOAT, DOUBLE and REAL.

Float:- FLOATs store floating point numbers with 8 place accuracy and take up 4 bytes.
Double:- DOUBLEs store floating point numbers with 16 place accuracy and take up 8 bytes.
Real:- Real contain 8 place accuracy and take 4 bytes.

7. What are the Joins in Mysql.

Joins are used in an MySql statement to query data from two or more tables, based on a relationship between certain columns in these tables.

8. Explain Different Joins in MySql.

The different joins in mysql are:-
Inner Join:- Return rows when there is at least one match in both tables.
Left Join:-  Return all rows from the left table, even if there are no matches in the right table.
Right Join:-  Return all rows from the right table, even if there are no matches in the left table.
Full Join:- Return rows when there is a match in one of the tables.

9. Is Mysql query is case sensitive.

No,mysql queries are not case sensitive.

10. What are all the Common MySql Function.

NOW():- Returns the current date and time as one value.
CURRDATE() :-  Returns the current date or time.
CURRTIME() :-  Returns the current date or time.
CONCAT(X, Y) :-  Concatenates two string values to create a single string output. Mostly used to combine two or more fields into one single field.
DATEDIFF(X, Y) :-  The difference between two dates.

11. Explain DISTINCT Statement in MySql.

In table some column may be contain the duplicates value but you want to show only same value once not again and again.he DISTINCT keyword can be used to return only distinct (different) values not have two same values.
Example:-
SELECT DISTINCT Column_name FROM table_name;

12. How MySQL Optimizes DISTINCT.

DISTINCT is converted to a GROUP BY on all columns and it will be combined with ORDER BY clause.
Example query:-
SELECT DISTINCT table1.a FROM table1,table2 where table1.a=table2.a;

13. Difference between CHAR and VARCHAR.

CHAR column length is fixed to the length that is declared while creating table. The length value ranges from 1 and 255.Varchar get space according to data interred in column.

14. Difference between CHAR_LENGTH and LENGTH

The CHAR_LENGTH for character count and LENGTH for byte count.

15.How get second highest salary from database table.

For getting second highest salary you will use limit.Example as:-
select salary from table order by salary desc limit 1,1;

16. What are HEAP tables.

HEAP tables are in-memory. They are usually used for high-speed temporary storage.
No TEXT or BLOB fields are allowed within HEAP tables.
Only use the comparison operators = and <=>.
HEAP tables do not support AUTO_INCREMENT.
Indexes must be NOT NULL.

17. How do you concatenate strings in MySQL.

CONCAT (string1, string2, string3)

18. How to control the max size of a HEAP table.

Using max_heap_table_size.

19. How many columns can be used for creating Index.

Maximum of 16 indexed columns can be created for any table.

20. Different  string types available for column in database.

SET
BLOB
ENUM
VARCHAR
TEXT
CHAR

21. Advantages of MyISAM over InnoDB.

Much more conservative approach to disk space management – each MyISAM table is stored in a separate file, which could be compressed then with myisamchk if needed.
Thee COUNT(*)s execute slower than in MyISAM due to tablespace complexity

22. Advantages of InnoDB over MyISAM.

Transactions
Row-level locking
Crash recovery
Foreign key constraints

23. Where MyISAM table will be stored and also give their formats of storage.

Each MyISAM table is stored on disk in three formats:-
1. The data file has a ‘.MYD’ (MYData) extension.
2. The ‘.frm’ file stores the table definition.
3. The index file has a ‘.MYI’ (MYIndex) extension.

24. How do you get a portion of a string?

SELECT SUBSTR(title, 1, 10) from user.

25. How to get current MySQL version.

Using SELECT VERSION ();

26. Difference between mysql and mysqli interfaces in PHP.

mysqli is the object-oriented version of mysql library functions.

27. What storage engines are used in MySQL.

Storage engines are called table types and data is stored in files using various techniques.
Like Indexing,Locking levels,Capabilities and functions.

28. What do _ and % mean inside LIKE statement?

_ is exactly one character.
% corresponds to 0 or more characters.

29. Difference between primary key and candidate key.

Every row of a table is identified uniquely by primary key. There is only one primary key for a table.
Primary Key is also a candidate key. By common convention, candidate key can be designated as primary and which can be used for any foreign key references.

30. What are the different table present in MYsql?

MyISAM:- This is default. Based on Indexed Sequntial Access Method. The above SQL will create a MyISA table.
HEAP : - Fast data access, but will loose data if there is a crash. Cannot have BLOB, TEXT & AUTO INCRIMENT fields.
ISAM :-  Based on Indexed Sequntial Access Method.
InoDB :-  Supports Transactions using COMMIT & ROLLBACK
BDB :- Supports Transactions using COMMIT & ROLLBACK. Slower that others.

31. Difference between mysql_fetch_array and mysql_fetch_object.

mysql_fetch_object :- Fetch a result row as an OBJECT.
mysql_fetch_array :- Fetch a result row as an associative ARRAY, a numeric array, or both

32. How do you start MySQL on Linux.

/etc/init.d/mysql start

33. How do you login to MySql using Unix shell.

Using command
# [mysql dir]/bin/mysql -h hostname -u <UserName> -p <password>

34. How do you change a password for an existing user via mysqladmin.

Using command
mysqladmin -u root -p password “newpassword”

35. What does myisamchk do.

It compress the MyISAM tables, which reduces their disk or memory usage.

36. What are federated tables.

Federated tables which allow access to the tables located on other databases on other servers.

37. How can you see all indexes defined for a table.

SHOW INDEX FROM <tablename>;

38. What are ENUMs used for in MySQL.

You can limit the possible values that go into the table.
CREATE TABLE months (month ENUM ‘January’, ‘February’, ‘March’,…);
INSERT months VALUES (’April’);

39. How to Use mysqldump to create a copy of the database.

mysqldump -h mysqlhost -u username -p mydatabasename > dbdump.sql

40. How can we get the number of rows affected by query.

SELECT COUNT (ID) FROM users;

41. How to display top 100 rows from table.

Using query
SELECT * table_name FROM LIMIT 0,100

42. Difference between BLOB AND TEXT.

BLOB:- A BLOB is a binary large object that can hold a variable amount of data.
There are 4 types of BLOB.
Tinyblob ,Blob, Mediumblob and Longblob.
TEXT:- A TEXT is a case-insensitive BLOB.
There are also 4 types of text:-
TINYTEXT,TEXT,MEDIUMTEXT ,LONGTEXT

43. What is ISAM.

ISAM  is abbreviated as Indexed Sequential Access Method.It was developed by IBM to store and retrieve data on secondary storage systems like tapes.

44. What is InnoDB.

lnnoDB is a transaction safe storage engine developed by Innobase Oy which is a Oracle Corporation now.

45. Difference between NOW() and CURRENT_DATE().

NOW () command is used to show current year,month,date with hours,minutes and seconds.
CURRENT_DATE() shows current year,month and date only.

46. How do I find out all databases starting with ‘user’ to which I have access to.

SHOW DATABASES LIKE ‘user%’;

47. How would you change a table to InnoDB.

ALTER TABLE user ENGINE innodb;

48. How do you convert a string to UTF-8.

SELECT (user USING utf8);

49. How would you delete a column.

ALTER TABLE user DROP user_id.

50. How many TRIGGERS are allowed in MySql table.

The various triggers allowed in mysql are:-
BEFORE INSERT
AFTER INSERT
BEFORE UPDATE
AFTER UPDATE
BEFORE DELETE
AFTER DELETE

51. How do you get the month from a timestamp.

SELECT MONTH(user_timestamp) from user_questions;

52. Difference between the LIKE and REGEXP operators.

Like are expressed using %.
For example:-
SELECT * FROM user WHERE user_name LIKE “%b”;
REGEXP are expressed using  ^.
For example:-
SELECT * FROM user WHERE user_name REGEXP “^b”;

53. Difference between Unix timestamps and MySQL timestamps.

Unix timestamps are stored as 32-bit integers.
MySQL timestamps are stored in a similar manner, but represented in readable YYYY-MM-DD HH:MM:SS format.

54. What is the difference between MyISAM Static and MyISAM Dynamic.

In MyISAM static all the fields will have fixed width. The Dynamic MyISAM table will have fields like TEXT, BLOB, etc. to accommodate the data types with various lengths.
MyISAM Static would be easier to restore in case of corruption.

  1. How to view a list of MySQL users and their privileges
mysql> select user from mysql.user;

  1. How to create user in Mysql / with previliges

mysql> create user 'arman'@'localhost' IDENTIFIED BY 'password';
mysql> grant all privileges on berkshire_new_cluster.* to barkat@'%' identified by 'password'


57. Describe MySQL architecture.

Answer: MySQL has got the logical architecture as follows

A. Connection Manager
B. Query Optimizer
C. Pluggable Engines.


58. What are the major differences between MySQL 5.1 and 5.5?

Answer: The Major difference I know is that the default engine was myisam in 5.1 and innodb in 5.5

59. What are the different database engines available in MySQL? (explain)

Answer: Following are the highly used engines available in mysql

A. MyISAM
B. INNODB
C. Memory
D. Federated
E. CSV

60. What are the major differences between MyISAM and InnoDB?

Answer: Following are the differences between InnoDB and MyISAM

A. MyISAM does not support transactions whereas InnoDB does.
B. InnoDB supports multi versioning concurrency control.
C. MyISAM uses table level locks and InnoDB uses row level locking.


61. Which engine would you prefer for production OLTP environment and why?

Answer:  InnoDB (to be transaction safe)

62. What are the best installation practices for MySQL?

Answer: there are following installation methods available

A. Binary Installation
B. RPM Installation
C. Source Code compilation

after installation you should change the location of bin logs and datafiles on the different physical disks.

63. Which RAID level is best suited for MySQL?

Answer: RAID 10

64. How do you upgrade from one mysql version to another mysql version?

Answer:  create a slave on newer version and change it to MASTER.

65. How many types of logs are there in mysql?

Answer: General Log, Error Log, Binary Log and Slow Query Log


66. How do you find out slow queries in mysql?

Answer : By enabling the slow query log as follows
SET GLOBAL slow_query_log = 1;


67. How do you go through the MySQL slow query log?

Answer : slow query log might be very huge in size and query could be listed thousand times. to summarize the slow query log in a very informative way there is third party tool available 'pt-query-digest' which is a part of percona tool kit freely downloadable.

68. How do you check the uptime of a mysql server?

Answer : following command gives you the system status

status


69. If the mysql server is performing slow than how to find out the process which is causing problem.

Answer : show processlist

70. What do you do if the end user complains about the performance of the DB?

Answer : show processlist will show the processes which taking resources at db server and the do the rest diagnosis.

71. What do you do about the slow queries?

Answer : study the explain plan and create necessary indexes if required.

72. Where do you change the performance parameters of mysql and by default where is the file located on a unix system?

Answer : my.cnf. this file is available under /etc/my.cnf

73. Which are the important performance parameters for MyISAM and InnoDB?

Answer : For MyISAM

key_cache_size
thread_cache_size
tmp_table_size
max_heap_table_size
read_buffer_size
query_cache

For InnoDB

innodb_buffer_pool_size
innodb_buffer_pool_instances
innodb_log_file_size
innodb_log_buffer_size
tmp_table_size
max_heap_table_size
table_open_cache

74. Should we have Query_cache enabled?

Answer : in many caches yes.
75. what are the disadvantages of having a big Query cache size?

Answer : query cache puts an additional overhead on the database. it required the db to work on invalidating the queries from the query cache.

76. what should be the optimum size of InnoDB buffer cache?

Answer : it should be the 70-80% of the memory available.

77. How do you backup InnoDB tables?

Answer : there are two ways of taking backup

1. mysqldump with --single-transaction
2. xtrabackup (part of percona)

78. How to take incremental backup in MySQL?

Answer : Using percona xtrabackup

79. Can the database be renamed in MySQL?

Answer :  No.

80. How to check the table fragmentation and resolve if the fragmentation is found?

Answer : following query will list all the fragmented tabes

SELECT TABLE_SCHEMA, TABLE_NAME, CONCAT(ROUND(data_length / ( 1024 * 1024 ), 2), 'MB') DATA, CONCAT(ROUND(data_free  / ( 1024 * 1024 ), 2), 'MB')FREE from information_schema.TABLES where TABLE_SCHEMA NOT IN ('information_schema','mysql') and Data_free < 0;

then run the following command on tables given by the query

alte table < table returned from pervious query > engine=innodb;

81. How to change the root password if the root password is lost?

Answer : start the Db with --skip-grants-table. change the password and restart the db in  normal mode.

82. What do you do if the data disk is full?

Answer : if the data disk is full then create a soft link and move the .frm and .idb files to the linked location.

83. How to take consistent backup in mysql?

Answer : take the backup at slave after stopping the slave.

84. How do you restrict the users in mysql?

Answer :  by grant and revoke commands.

85. what is advantage of having file per table parameter enabled?

Answer :  point # 26 can only be done in file per table is enabled at innodb level.

86. How do you setup replication?

Answer : I will post a separate BLOG for this.. Keep reading


87. How to add a new column in a Table

Answer: ALTER TABLE `RHCVA` ADD `roll_no` INT UNSIGNED NOT NULL

88. How to ALTER Table. If you need to change data type or any other schema.
Answer: ALTER TABLE `RHCVA` CHANGE `id_n` `id_n` SMALLINT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT 

4 comments:

  1. the answer for the below question is wrong...
    Can the database be renamed in MySQL
    No(Yes actually)

    rename db1 to db2;
    Syntax:
    RENAME {DATABASE | SCHEMA} db_name TO new_db_name;

    ReplyDelete
    Replies
    1. Hi Pavan,
      You are wrong! And my Answer is right.

      This statement was added in MySQL 5.1.7 but was found to be dangerous and was removed in MySQL 5.1.23.

      we can't rename database, If you need to rename then we will take database dump and create new DATABASE and Import , And we can remove old database.


      Delete
    2. This is perfect solution for RENAME Database

      mysqldump -u root -p originl_database > original_database.sql
      mysql -u root -p -e "create database my_new_database"
      mysql -u root -p my_new_database < original_database.sql
      mysql -u root -p -e drop database originl_database

      Delete
  2. very good set of questions and answers and yes yu are right This statement was added in MySQL 5.1.7 but removed in MySQL 5.1.23.

    ReplyDelete