MySQL database Administrator interview question and Answer

Question: How to start MySQL Service?
Start MySQL Server using service

sudo service mysql start

Start MySQL Server using using init.d

sudo /etc/init.d/mysql start

Start MySQL Server using systemd

Start MySQL Server using systemd

Question: List the MySQL Users

select user from mysql.user;

Question: List all databases

show databases

Question: Grant insert,UPDATE,delete privileges on the mydb (DB) to dbuser (User)

GRANT INSERT, UPDATE, DELETE ON mydb.* TO bob@localhost;

Question: Grant all privileges on the mydb (DB) to dbuser(User)

grant all privileges on mydb.* to dbuser@localhost;

Question: Show all the privileges to dbuser(User)

SHOW GRANTS FOR dbuser;

Question: Revoke insert,UPDATE,delete privileges on the mydb (DB) from dbuser (User)

REVOKE INSERT, UPDATE ON mydb.* FROM dbuser@localhost;

Question: List all the Proesslist in MySQL

SHOW Full PROCESSLIST

Question: What are different MySQL storage engines

1. MyISAM: Before MySQL version 5.5, MyISAM is the default storage engine. MyISAM extends the former ISAM storage engine. MyISAM tables are optimized for compression and speed. MyISAM tables can be compressed into read-only tables to save spaces. The MyISAM tables are not transaction-safe. MySQL also checks and repairs InnoDB tables.
2. InnoDB: InnoDB tables fully support ACID-compliant and transactions. InnoDB table supports foreign keys, commit, rollback, roll-forward operations. MySQL also checks and repairs InnoDB tables.
3. MERGE: A MERGE table is a virtual table that combines multiple MyISAM tables that have a similar structure to one table. MERGE table does not have its own indexes;
4. MEMORY: The memory tables are stored in memory and use hash indexes so that they are faster than MyISAM tables The lifetime of the data of the memory tables depends on the uptime of the database server.

MySQL Stored procedure interview Questions and Answer

Question: What is stored procedure?
A stored procedure is Structured Query Language (SQL) statements which has a name, a parameter list, and SQL statement(s) stored in MySQL Server.



Question: Give example of stored procedure?

DELIMITER $$
CREATE PROCEDURE getUsersAscending()
BEGIN
	SELECT 
		first_name, 
		last_name, 
		email
	FROM
		users
	ORDER BY first_name;    
END$$
DELIMITER ;

Question: Give example of stored procedure with parameter details?

DELIMITER $$
CREATE   PROCEDURE `getUsersDetails`(IdSearch INT)    
    BEGIN
	SELECT 
		first_name, 
		last_name, 
		email
	FROM
		users
	WHERE id=IdSearch;      

    END$$
DELIMITER ;

Question: To show all stored procedures:

SHOW PROCEDURE STATUS;

Question: How to show stored procedures in a specific database

SHOW PROCEDURE STATUS WHERE Db = 'db_name';

Question: MySQL stored procedures advantages?

1. Reduce network traffic
2. Make database more secure

Question: MySQL stored procedures dis-advantages?

1. Resource usages: If you use many stored procedures, the memory usage of every connection will increase substantially.
2. Troubleshooting:MySQL does not provide any facilities to debug stored procedures like other enterprise database products such as Oracle and SQL Server
3. Maintenances Developing and maintaining stored procedures often requires a specialized skill set that not all application developers possess.

Question: How to call stored procedure with OR without arguments?
Call without argument

call getUsersAscending();

Call With argument

call getUsersDetails(10);

Question: How to delete the procedure?

DROP PROCEDURE IF EXISTS getUsersAscending;

Question: What if i am trying to delete the stored procedure for non-exist.?
It will give warning only, if you have used "IF EXISTS"
It will give Error, if you have used not used "IF EXISTS"


Question: How to Alter a stored procedure in mysql?
1: Delete the Stored procedure. 

2: Re-Create the Stored procedure.