MySQL DBA Interview Questions and Answers

In case you’re searching for MySQL DBA Interview Questions and answers for Experienced or Freshers, you are at the correct place. There is a parcel of chances from many presumed organizations on the planet. The MySQL DBA advertise is relied upon to develop to more than $5 billion by 2021, from just $180 million, as per MySQL DBA industry gauges. In this way, despite everything you have the chance to push forward in your vocation in MySQL DBA Development. Gangboard offers Advanced MySQL DBA Interview Questions and answers that assist you in splitting your MySQL DBA interview and procure dream vocation as MySQL DBA Developer.

Best MySQL DBA Interview Questions and Answers

Do you believe that you have the right stuff to be a section in the advancement of future MySQL DBA, the GangBoard is here to control you to sustain your vocation. Various fortune 1000 organizations around the world are utilizing the innovation of MySQL DBA to meet the necessities of their customers. MySQL DBA is being utilized as a part of numerous businesses. To have a great development in MySQL DBA work, our page furnishes you with nitty-gritty data as MySQL DBA prospective employee meeting questions and answers. MySQL DBA Interview Questions and answers are prepared by 10+ years experienced industry experts. MySQL DBA Interview Questions and answers are very useful to the Fresher or Experienced person who is looking for the new challenging job from the reputed company. Our MySQL DBA Questions and answers are very simple and have more examples for your better understanding.

By this MySQL DBA Interview Questions and answers, many students are got placed in many reputed companies with high package salary. So utilize our MySQL DBA Interview Questions and answers to grow in your career.

Q1) Define MySql

Answer: MySQL, a multi-user SQL Database Management System that contains eleven million plus installations. MySQL DBA is the second most widespread and extensively utilized open-source Database.

Q2) List out the technical specifications of MySql

Answer: Below are the MySql technical specifications:

• Security and Storing management
• Stretchy structure
• Replication and high accessibility
• Best performance
• Controllable and user-friendly

Q3) What is the purpose of using Mysql Database Server?

Answer: The MySQL Database server is quite rapid, consistent, and simple for usage. It is easy for the user to use and make changes to the software.

Q4) Provide the various database engines present in MySQL

Answer: Below are the different database engines in Mysql:

• Federated
• INNODB
• CSV
• Memory
• MyISAM

Q5) In Mysql Database, how many triggers are probable?

Answer: Only six (6) triggers are allowed in Mysql Database for use.

Q6) Mention the six (6) triggers allowed in Mysql Database

Answer: The six (6) triggers allowed in Mysql Database are given below:

• After Update
• After Insert
• Before Update
• After Delete
• Before Delete
• Before Insert

Q7) How many tables are present in Mysql Database?

Answer: There are five (5) tables in Mysql Database

Q8) Mention the different tables available in Mysql Database

Answer: Five (5) tables available in Mysql Database are provided below:

• Heap
• MyISAM
• Merge
• ISAM
• INNO DB

Q9) Which is the default database engine utilized in Mysql Database?

Answer: MyISAM is said to be the default database engine utilized in Mysql Database.

Q10) Define ‘Delete’ in Mysql Database

Answer: ‘DELETE’is for deleting data from a table. This command is used to delete only the rows of data from a table.

Q11) Define ‘Truncate’ in Mysql Database

Answer: ‘Truncate’ is to delete every row from a table permanently. Hence, this is said to be a dangerous command.

 Q12) Define ‘Heap’ table

Answer: Heap tables are the ones that are existing in memory. In Mysql, while creating a heap table, users must specify the type as ‘HEAP’.  Also, these tables are usually named as Memory tables. Heap tables are used for large speed storage in a momentary manner.

Q13) What are the fields not allowed in Heap table?

Answer: TEXT or BLOB fields are not allowed in Heap table.

Q14) Define BLOB

Answer: BLOB (Binary Large Object) is for holding a variable quantity of data.

Q15) What are the types of BLOB?

Answer: Four types of BLOBs are given below:

• TINYBLOB
• LONGBLOB
• MEDIUMBLOB
• BLOB

Q16) Explain TEXT inMysql DBA

Answer: TEXT, a case-insensitive BLOB and the text values are always non-binary strings, having a character set and values stockpiled and related depending on the character set collation.

Q17) How many TEXT types are available in Mysql DBA?

Answer:  There are four (4) types of TEXT available in Mysql DBA

• TINYTEXT
• LONGTEXT
• MEDIUMTEXT
• TEXT

Q18) Mention any one disadvantage of Mysql DBA

Answer: Mysql DBA does not support STORED PROCEDURES and COMMIT functions which have a lesser version of 5.0.

Q19) What is the command to retrieve the current date in Mysql DBA

Answer: Below is the syntax to retrieve the current date in Mysql DBAnswer:
SELECT CURRENT_DATE();

Q20) List out the security alerts when using Mysql

Answer: Below are the security alerts while using Mysql

• Confine or deactivate the remote access
• Install antivirus and configure the operating security system
• Modify the origin username and password
• MySQL Server is never used as the UNIX root user

Q21) Mention the Mysql DBA Default Port Number

Answer: 3306 is the default port number of Mysql DBA

Q22) Provide the command required to view the table content in Mysql DBA

Answer: SELECT command is the one to view the table content in MySQL

Q23) Mention the advantage of Mysql_close()

Answer: Mysql_close() is used to close connection which are opened by mysql_connect().

Q24) What is the benefit of Enums In Mysql?

Answer: The purpose of ENUMs is to restrict and limit the probable values that get inside the table.

Q25) Give an example for Enums in Mysql

Answer: CREATE TABLE weeks (week ENUM ‘Monday’, ‘Tuesday’, ‘Wednesday’); INSERT weeks VALUES (‘Thursday’).

Q26) Describe Sqlyog

Answer: SQLyog program is the top-most GUI tool for admin. Also, Sqlyog is the widespread MySQL manager. MySQL administrator, PHPMyAdmin and MySQL GUI tools and others MySQL front ends are combined by Sqlyog.

Q27) What is the command to verify whether Mysql is running or not?

Answer: The command “service mysqld status” in RedHat and “service MySQL status” helps in verifying whether Mysql is running or not.

Q28) What is the command to stop/start the service in Mysql?

Answer: The command “service mysqld start” is to start MySql service and “service mysqld stop” to stop the service.

Q29) Mention the finest installation procedure for MySQL

Answer: RPM Installation, Binary Installation, and Source Code compilation are the best installation methods in Mysql.

Q30) What is the best RAID level suitable for Mysql?

Answer: RAID 10 is the best RAID level suitable for Mysql

Q31) Give the different types of logs available in Mysql DBA

Answer: Below are the different types of logs:

• Error Log
• General Log
• Slow Query Log
• Binary Log

Q32) What is the command to check the Mysql server uptime?

Answer: SHOW GLOBAL STATUS LIKE ‘UPTIME’ is the command to check Mysql server uptime

Q33) Provide the drawbacks of using big Query cache size?

Answer: Query cache pushes an extra load on the database. It forces the DB to perform the task on nullifying the queries from the cache.

Q34) Give the optimum size of InnoDB buffer cache

Answer: Optimum size of InnoDB buffer cache must be 70-80% of the existing memory.

Q35) How do you take incremental backup in Mysql DBA?

Answer: To take increment backup in Mysql DBA, use Using percona xtrabackup.

Q36) What is the process to perform if you find the data disk full?

Answer: In-case of data disk is full, create a soft link and try to move the .idb and .frm files to the linked place.

Q37) How many types of backup are available in Mysql DBA?

Answer: There are three main types of backup in Mysql DBA which are Cold backups, Logical backups, and Hot backups.

Q38) Explain Cold backup

Answer: Cold backups is used to shut down the database server and taking a backup of all the data files by creating a copy of those files to another directory. The whole data dir including log files, binlogs, and /etc/my.cnf config file is also backed up.

Q39) Explain Logical Backup

Answer: Logical backups used a tool called mysqldump tool. This tool locks the tables while it runs to uphold the reliability of modifying data and can result in downtime. The subsequent dump file contains CREATE INDEX, CREATE TABLE, and CREATE DATABASE statements for database build.

Q40) Explain Hot Backup

Answer: Hot backups permits to back up the physical database data files when the server is up and running. This kind of backup process can be achieved using xtrabackup tool which is available from Percona.

Q41) What is the primary key?

Answer: Primary Key is to exclusively recognize each row of the table and there can be only one primary Key in a table.

Q42) For creating an index, how many columns can be used?

Answer: Maximum of 16 columns can be used for creating index.

Q43) Describe Database White Box Testing

Answer: The Database Whitebox Testing handles data model, schema, and referential integrity rules. In addition, it also handles logical view with database uniformity, triggers, and ACID properties.

Q44) Describe Database Black Box Testing

Answer: Database Black Box Testing handles data storing & retrieving and data mapping. Also, this kind of testing is also used for techniques such as Boundary Value Analysis and Equivalence Partitioning.

Q45) Define CTE

Answer: CTE (Common Table Expression) is the one consisting of a momentary set of outcomes demarcated in a SQL statement.

Q46) If you want to display the maximum salary, what is the function to be used?

Answer: You can use the function MAX() to display the maximum salary.

Q47) Explain CSV tables

Answer: CSV is an abbreviation of Comma-Separated Values. CSV table is the one to store data in tabular format and plain text. CSV naturally holds only one record per line.

Q48) Describe Mysql_connect

Answer: Using Mysql_connect, user can open and close the database connection depending on the appeal.

Q49) Describe Mysql_pconnect

Answer: Mysql_pconnect is used to open a determined connection in a database and not possible to close such a connection.

Q50) Define ACID

Answer: ACID is the acronym of Atomicity, Consistency, Isolation, and Durability. ACID Property is the top-most vital segment of the database.

Q51) What are the different types of MySQL functions?

Answer:

• Strings functions
• Numeric functions
• Date functions
• Aggregate functions

Q52) Write a query to create, insert, update and delete?

Answer:
Create Query: CREATE DATABASE DatabaseName;
Insert Query : INSERT INTO table_name (tablecolumn1, tablecolumn1,
tablecolumn1,…)VALUES (value1, value2, value3,…);
Update Query: UPDATE table_name SET tablecolumn1=value, tablecolumn2=value2,…
WHERE this_column=this_value;
Delete Query: DELETE FROM table_name WHERE this_column = this_value;

Q53) What is the syntax to connect the mysql?

Answer: $connection = new mysqli($localhost, $username, $password);

Q54) What is the syntax for concatenating tables in MySQL?

Answer: concat(‘value1’, ‘ ’ , ‘value2’);

Q55) What is the difference between mysql_fetch_array and mysql_fetch_object?

Answer: Mysql_fetch_object is the function which helps to retrieve the values as an object from
the database, whereas Mysql_fetch_array will return the values as an array.

Q56) How Do You Get The Number Of Rows Affected By Query?

Answer: It can be got by using the Count();

Q57) Name any five combination queries that we use in MySQL?

Answer:

• Like
• Orderby
• Limit
• Between
• Group by

Q58) What is the query used to combine two tables and retrieve the value?

Answer:
Join query is used to combine two tables

Q59) What are the ways in which you can retrieve data in the result set of MySQL using PHP?

Answer: We can retrieve data in four ways. They are as follows.
mysql_fetch_array
mysql_fetch_assoc
mysql_fetch_object
mysql_fetch_row

Q60)What is the MySQL default port number?

Answer: 3306

Q61) What is the query to display the top 10 rows?

Answer: It can be displayed by using a limit function in the select query.

Q62) Write a query to count the number of rows of a table in MySQL

Answer: Select COUNT(*) FROM tableName

Q63) What is the use of the Primary key? Where we use it.?

Answer: The primary key is used to uniquely identify the table records. Mostly we use a primary key
for IDs.

Q64) What are the different types of joins?

Answer: There are four types of joins,

• Inner join
• Outer join
• Left join
• Right join

Q65) Explain the differences between delete, drop and truncate?

Answer:
Delete: Delete the row value where conditions meet.
Drop: remove the table or database completely.
Truncate: Remove all the rows in the table in time.

Q66) How to sort the value while retrieving records from the table?

Answer: The value can be sorted by using the OrderBy keyword in the select query.

Q67) What is the use of Now()?

Answer: It is used to return the current date and time.

Q68) Which MySQL function is used to concatenate string?

Answer: concat(‘value1’, ‘ ’ , ‘value2’);

Q69) What is the query used to add and remove any column of a table?

Answer: ALTER query is used to add and remove the column in the table.

Q70) List the Sailent features of MySQL?

Answer:

•  It is easy to use and reliable
• It is an open-source
• It mainly supports all programming languages

Q71) What would be the default port number of MySQL

Answer: Default port number is 3306

 Q72) What do you understand by the term myisamchk?

Answer: It is a database utility tool which is used to get some information about MyISAM database tables

Q73) Give the main difference between VARCHAR and CHAR data types?

Answer: Both are used to store string data under the field of a table

Q74) Is it possible to add or remove any column from a table?

Answer: Yes. It is possible by using the add column or alter column we can do it.

Q75) Define Index?

Answer: The index is defined as a data structure of a MySQL table and can speed up with queries.

Q76) Explain about the view in MySQL?

Answer: It works as a virtual table used to store query and returns a result.