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.