Architecture of MySQL

It is a free and open source relational database management system that is licensed under the GNU General Public License. It  is a fast, scalable, and easy to use database management system that is also supported by the Oracle Company. There are many operating systems that MySQL supports, such as Windows, Linux, MacOS, etc.

This is a structured query language that is used to manipulate, manage, and retrieve data using various queries and is known as Structured Query Language (SQL).

MySQL is developed and supported by  MySQL AB, a Swedish company whose programming language is C and C++. Developed by Michael Widenius and David Hughes, MYSQL is often referred to as being named after the daughter of its co-founder Michael Widenius whose name is My.

Architecture of MySQL:

The architecture of MySQL describes the relationship between the different components of the system.  The MySQL database follows a Client-Server architecture. This system is designed so that end users, referred to as clients, are able to access resources through a network from a computer, referred to as the server. MYSQL’s architecture is composed of the following major layers:

• Client
• Server
• Storage Layer

Client Layer:

In the above diagram, this is the topmost layer. With the help of Client Layer, the Client can give instructions to the Serve. The Client can make a request via a Command Prompt or GUI screen by using valid MYSQL commands and expressions. When the expressions and commands are valid, an output is displayed.  There are several important services provided by the client layer, including:

• • Connection Handling.
  • Authentication.
  • Security.

• Connection Handling :  

The client sends a request to the server, the server accepts the request, and the client is connected. When a client is connected to the server at that time, it receives its own thread for the connection. This thread is used to execute all queries from the client side.

• Authentication : 

When a client connects to a MYSQL server, authentication is performed on the server side. Usernames and passwords are used for authentication.

• Security : 

As soon as the client is authenticated and connected successfully to the MySQL server, the server will check to see if the particular client is authorized to issue certain queries on the server.

Server Layer:  

MYSQL’s second layer is responsible for all logical functionality of its relational database management system. This layer of the MySQL System is also known as the “brain” of the MySQL architecture. A client sends instructions to a server, and the server responds with the output as soon as the instructions are matched.  MYSQL server consists of the following subcomponents:

• Thread Handling –
  When a client sends a request to the server and the server accepts the request. As soon as a client is connected to the server, that client will get its own thread for it’s connection to the server at that time. In this case, the thread is provided by the thread handling of the server layer. In addition to that, the Thread Handling module is also responsible for handling the queries of the client side as they are executed by the thread.
• Parser –
  It is a type of Software Component which builds a data structure (parse tree) based on the input that is given to it. Prior to parsing, lexical analysis is performed i.e. the input is broken into a number of tokens before it is parsed. Upon receiving the smaller elements, the parser performs a syntax analysis and a semantic analysis, following which a parse tree is generated.
• Optimizer –
  At Optimizer Block, various optimization techniques are applied after parsing. A few of these techniques include the rewriting of the query, the order in which the tables are scanned, as well as selecting the correct indexes to use.
• Query Cache –
  In the query cache, the entire result set for the inputted query statement is stored. MYSQL Server consults the query cache even before it starts parsing a query. The server simply displays the output from the cache when the query written by the client is identical in the cache, so it skips parsing, optimization and even execution.
• Buffer and Cache –
  The previous query or problem asked by the user is cached and buffered. A user writes a query, then it goes to the query cache, and the query cache checks if the same query or problem is there. In this manner, output can be provided without interfering with the parser or optimizer.
• Table Metadata Cache –
  Metadata caches are reserved areas of memory that keep track of information about databases, indexes, and objects. Metadata cache size increases with the number of open databases, indexes, or objects.
• Key Cache –
  In a cache, a key cache is an index entry that uniquely identifies an object. A query string and the entire resource path are cached by edge servers by default.

Storage Layer: 

The Storage Engine Layer of MYSQL Architecture makes it unique and most preferred by developers. This Layer makes MySQL the most widely used RDBMS. Depending on the situation and requirements, MySQL server uses different types of storage engines such as InnoDB, MyiSAM, NDB, Memory, etc. Tables created by users are plugged into these storage engines as pluggable storage engineers.

Features of MYSQL:

1. The MySQL language is easier to use than other programming languages such as C, C++, Java, etc. Using some basic commands, we can work with, create, and interact with databases.
2. There is a Data Security layer in MySQL which protects the data from unauthorized access. In addition, passwords are encrypted in MySQL.
3. A Client-Server Architecture is used by MySQL, where the Client sends commands and instructions, and the Server responds as soon as the instructions are matched.
4. Under MYSQL’s Community version, it is free to use. Therefore, we can download it from the MySQL website and work on it at our own discretion.
5. The multithreading feature of MySQL makes it scalable. It can handle any amount of data. The default file size limit is 4 GB, but it can be increased if necessary.
6. MySQL is considered to be one of the fastest databases. Several benchmark tests have been conducted to determine its speed.
7. MySQL supports a large number of embedded systems, which makes it very flexible.
8. MySQL can be run on a wide range of operating systems, including Windows, MacOS, and Linux.
9. The MySQL database supports the rolling back of transactions, the committing of transactions, and the recovery of cash.
10. It has a low memory leakage problem, which increases memory efficiency.
11. The MySQL version 8.0 provides dual password support, one is a current password and the second is a secondary password. This will allow us to create a new password.
12. Partitioning is a feature of MySQL that improves the performance of large databases.