MySQL Introduction

Overview of MySQL Database Management System

MySQL is a database management system

A database is a structured collection of data. It may be anything from a simple shopping list to a picture gallery or the vast amounts of information in a corporate network. To add, access, and process data stored in a computer database, you need a database management system such as MySQL Server.

MySQL is a relational database management system

A relational database stores data in separate tables rather than putting all the data in one big storeroom. This adds speed and flexibility. SQL is the most common standardized language used to access databases and is defined by the ANSI/ISO SQL Standard. The SQL standard has been evolving since 1986 and several versions exist. In this manual, “SQL-92” refers to the standard released in 1992, “SQL:1999” refers to the standard released in 1999, and “SQL:2003” refers to the current version of the standard. We use the phrase “the SQL standard” to mean the current version of the SQL Standard at any time.

MySQL Software is Open Source

Open Source means that it is possible for anyone to use and modify the software. Anybody can download the MySQL software from the Internet and use it without paying anything. If you wish, you may study the source code and change it to suit your needs.

 History of MySQL

They started out with the intention of using the mSQL database system to connect to our tables using our own fast low-level (ISAM) routines. However, after some testing, we came to the conclusion that mSQL was not fast enough or flexible enough for our needs. This resulted in a new SQL interface to our database but with almost the same API interface as mSQL.

MySQL is named after co-founder Monty Widenius's daughter, My.

The name of the MySQL Dolphin (our logo) is “Sakila,” which was chosen from a huge list of names suggested by users in our “Name the Dolphin” contest. The winning name was submitted by Ambrose Twebaze, an Open Source software developer from Swaziland, Africa. According to Ambrose, the feminine name Sakila has its roots in SiSwati, the local language of Swaziland. Sakila is also the name of a town in Arusha, Tanzania, near Ambrose's country of origin, Uganda.

The Main Features of MySQL

• Written in C and C++.
• Tested with a broad range of different compilers.
• Works on many different platforms.
• Designed to be fully multi-threaded using kernel threads, to easily use multiple CPUs if they are available
• Provides transactional and nontransactional storage engines.
• Implements in-memory hash tables, which are used as temporary tables.
• Works in cross platform.

Why companies are preferring MySQL ?

          Before MySQL doesn't supported stored procedures, functions, triggers, views, subqueries and partitioning. But now they are supported. Small and middle level companies are relied on MySQL because it is opensource. Supports replication and clustering for High Availability.

MySQL Architecture

Client/Server Overview

The MySQL database system operates using a client/server architecture. The server is a central program that manages database contents, and client programs connect to the server      to retrieve or modify the data. MySQL also includes non-client utility programs and scripts.

MySQL Server: This is the mysqld program that manages database and tables. Most users choose binary MySQL distribution that includes a server ready to run with the capabilities they need, but it's also possible to compile MySQL from source.

Client Programs: These are programs that communicate with the server by sending requests to it over a network connection. The server acts on each request and returns a response to the client. For example you can use the mysql client to send queries to the server, and the server returns the query results. A client program can connect locally to a server running on the same machine or remotely to a server running on a different machine.

Non-client utility programs: These are programs that generally used for special purposes and do not acts as clients of the server. They do not connect to the server, for example mysqld_safe is a script for starting and stopping the server. Myisamchk is a standalone utility for table check and repair.

Application layer

This is where the client interact with the RDBMS. This layer is used by three users:

Administrator.

client.

User.

i) Administrative Interface and Utilities 

          This layer is used by Administrator. This includes utilities such as mysqladmin, myisamchk, mysqldump etc.

ii) Client Interface and Utilities

This layer is used by the clients to communicate with RBDMS. The client interface uses MySQL APIs for various different programming languages such as the  PHP API, Java API, etc.

iii) Query Interface

• This use the mysql as a “interactive tool” that issue sql statements to the server and displays results to the screen.

Logical layer

All ths DML statements that are sent from the application layer are parsed and optimized in the query processor.

It is again sub-divided into following layers:

i) embedded DML precompiler

When a request is received from a client in the application layer, it is the responsibility of the embedded DML (Data Manipulation Language) precompiler to extract the relevant SQL statements embedded in the client API commands, or to translate the client commands into the corresponding SQL statements.

ii) DDL Compiler

• Request received from the administrator are processed by the DDL Compiler.
• The administrative utility does not expose any interface and hence executed directly by the server.

iii) Query Preprocessor 

• The query preprocessor parses the query. The parsing is of 2 types

1) syntactical

2) semantical

If the query syntax is right then it is sent to the next pipe . Else it inform to the client with an a error message.

iv)Query Optimizer

• Query optimizer is the brain of the MySQL.
• Once client has permission to execute query, the query is optimized by the query optimizer. The task of the query optimizer is analyze the processed query to see if it can take advantage of any optimizations that will allow it to process the query more quickly.MySQL query optimizer uses indexes.

v) Execution Engine

• Once the MySQL query optimizer has optimized the MySQL query, the query can then be executed against the database. This is performed by the query execution engine, which then proceeds to execute the SQL statements and access the physical layer. As well the database administrator can execute commands on the database to perform specific tasks such as repair, recovery, copying and backup, which it receives from the DDL compiler.

MySQL Transaction Management

i) Transaction Manager

A transaction is a single unit of work that has one or more MySQL commands in it. The transaction manager is responsible for making sure that the  transaction is logged and executed atomically.  And also prevents from deadlocks. Furthermore, the transaction manager is responsible for issuing the COMMIT and the ROLLBACK SQL commands. The COMMIT command commits to performing a transaction. Thus, a transaction is incomplete until it is committed to. The ROLLBACK command is used when a crash occurs during the execution of a transaction. If  a transaction were left incomplete, the ROLLBACK command would undo all changes made by that transaction. The result of executing this command is restoring the database to its last stable state.

• Ensures Atomicity
• Avoids Deadlocks
• Responsible for Commit & Rollback

ii) Concurrency- Control Manager

          The concurrency-control manager is responsible for making sure that transactions are executed separately and  independently. It does so by acquiring locks. Once the lock is acquired, only the operations in one transaction can manipulate the data. If a different transaction tries to manipulate the same locked data,  the concurrency-control manager rejects the request until the first transaction is complete .

Recovery Management

iii) Log Manager

The log manager is responsible for logging every operation executed in the database. It does so by storing the log on disk through the buffer manager. The operations in the log are stored as MySQL commands. Thus, in the case of a system crash, executing every command in the log will bring back the database to its last stable state.

 iv) Recovery Manager

The recovery manager is responsible for restoring the database to its last stable state. It does so by using the log for the database, which is acquired from the buffer manager, and executing each operation in the log. Since the log manager logs all operations performed on the database, executing each command in the log file would recover the database to its last stable state.

Storage Management

Storage is physically done on some type of secondary storage, however dynamic access of this medium is not  practical. Thus, all work is done through a number of buffers. The buffers reside in main and virtual memory  and are managed by a Buffer Manager.

i) Resource Manager

The purpose of the Resource Manager is to accept requests from the execution engine, put them into table requests, and request the tables from the Buffer Manager. The Resource Manager receives references to data within memory from the Buffer Manager and returns this data to the upper layers.

ii) Buffer Manager

The role of the Buffer Manager is to allocate memory resources for the use of viewing and manipulating data. The Buffer Manager takes in formatted requests and decides how much memory to allocate per buffer and how many buffers to allocate per request. All requests are made from the Resource Manager.

(key_buffer, sort_buffer, myisam_sort_buffer)

iii) Storage Manager

The storage manager acts as a mediator to send request between buffer manager and secondary storage.

Check Out Our Related Courses Sql Tutorials Oracle Pl SQL Tutorials Top 50 Sql Server Interview Questions SQL Server DBA Training In Hyderabad