MySQL: Frequently asked Interview Questions and Answers Part 7

Which tool are you using to monitor MySQL Server?

SQLyog is a GUI tool for the RDBMS MySQL.  SQLyog is being used by more than 30,000 customers worldwide and has been downloaded more than 2,000,000 times. It is one of the best tool to monitor MySQL Server.

What are your steps to handle foreign key constraint error during deletion of data?

Step 1: If cascade delete is require, then we can create foreign key with ON DELETE CASCADE.
Step 2: We can also set SET foreign_key_checks=0 before the delete command.
By default it is, SET foreign_key_checks=1.
Using this option you can disable referential constraint temporary.
But as a result, you can insert data that violate foreign key constraints, and when you enable the referential constraints (set FOREIGN_KEY_CHECKS to 1), MySQL does not re-validate the inserted rows.

Does MySQL foreign_key_checks affect the entire database?

Actually, there are two foreign_key_checks variables: a global variable and a local variable.
The command SET foreign_key_checks modifies the session variable.
To modify the global variable, use SET GLOBAL foreign_key_checks.

Which are the different tools to manage MySQL Server?

mysqld: MySQL server daemon. Used to start your MySQL server.
mysql : A command-line interface to manage mysql objects.
mysqldump: A command-line interface to export data or database backup.
mysqlimport: A command-line interface to import data into MySQL Database.
mysqlshow : A command-line interface to show meta data information of MySQL Server.
mysqlcheck : A command-line interface to check and repair database or table.
mysqladmin: A command-line interface for Database Administrator to perform administrator task.

Explain detailed use of Mysqld.

The Mysqld is a server daemon program which runs in the background of your computer. To invoke Mysqld will start the MySQL Server and To terminate Mysqld will shutdown the MySQL Server.
The Mysqld program has many options that can be specified at startup.
Mysqld is the main program that does most of the work in a MySQL installation like, create data directory for databases and tables, create a directory for log files and status files.

Explain detailed use of Mysqladmin.

The Mysqladmin is a command – line interface for Database Administrators to perform administrator related task.
A Database Administrator can perform this task:

  • Check server configuration.
  • Check whether the server is available.
  • Check current status of the server.
  • Create / Drop database.
  • Check the version information on the server.
  • Tell the server to write debug information to the error log.
  • Flush all information in the host cache.
  • Flush all logs.
  • Reload the grant tables.
  • Show a list of active server threads.
  • Kill server threads.
  • Stop replication on a slave server.

What is BDB (BerkeleyDB)?

The BDB is a one type of storage engine, which is developed by Sleepycat Software.
The BDB tables may have a greater chance of surviving crashes and are also capable of COMMIT and ROLLBACK operations on transactions.
Support for the BDB storage engine is included in MySQL source distributions, which come with a BDB distribution that is patched to make it work with MySQL.

What are the characteristics of BDB tables?

Each BDB table store two different files on disk, one is .frm file stores the table format and second is .db file stores table and index data.
The BDB storage engine provides transactional table in which you can enable/disable auto-commit mode, you can write transaction statements like, BEGIN / ROLLBACK.
The primary key is to require on each BDB table.
The BDB table perform locking at page level.

Anvesh Patel

Leave a Reply

Be the First to Comment!

Notify of