MySQL database Administrator interview question and Answer for 6 year experienced

Question: How to reset AUTO_INCREMENT in MySQL?>

ALTER TABLE tablename AUTO_INCREMENT = 1

Question: How to concatenate multiple rows into one field?>

SELECT GROUP_CONCAT(id SEPARATOR ', ')   FROM users   WHERE city = 'india'  GROUP BY city;

(It will concatinate all the users)

Question: Which MySQL data type to use for storing boolean values?>

BIT

Question: What are best character-set for multi-language data?>

utf8mb4

Question: What is the difference between utf8mb4_unicode_ci and utf8mb4_general_ci?
utf8mb4_unicode_ci is based on the official Unicode rules for universal sorting and comparison, which sorts accurately in a wide range of languages.

utf8mb4_general_ciis a simplified set of sorting rules which aims to do as well as it can while taking many short-cuts designed to improve speed. It does not follow the Unicode rules and will result in undesirable sorting or comparison in some situations


Question: How do you set a default value for a MySQL Datetime column?>
Add the default CURRENT_TIMESTAMP
For example:

CREATE TABLE foo (
    `modification_time` DATETIME ON UPDATE CURRENT_TIMESTAMP
)

Question: What is the difference between physical and logical backup?>
physical backup is to copy for backing up all the physical files that belongs to database.(like data files,control files,log files, executables etc), we need to stop the mysql service temporary.>
In logical backup, you don't take the copies of any physical things,you only extract the data from the data files into dump files.(ex : using export )


Question: What are World's most popular Databases?>

1. Oracle
2. MySQL
3. Microsoft SQL Server
4. PostgreSQL
5. MongoDB

Question: Explain ACID?
Atomicity. All completed OR Nothing.
Consistency. Database protected from actions that may corrupt data.
Isolation. multiple actions occuring in parallel will not impact each other.
Durability. All commited statements data are safe.

Question: What does FLUSH TABLES WITH READ LOCK do?
1 set the global read lock - after this step, insert/update/delete/replace/alter statements cannot run.
2 close open tables - this step will block until all statements started previously have stopped.
3 set a flag to block commits.

FLUSH TABLES

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.