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.