MySQL Architecture

The MYSQL architecture defines the relationship between the various components of the MYSQL system. Client-Server Architecture is used by MYSQL. It’s designed so that end-users, or clients, can use different networking services to access resources from a server computer.

 

MYSQL’s architecture Layers:

1. Application Layer
2. Server Layer
3. Storage Layer

1. Application Layer:

               The client-end of the MYSQL architecture is the part being interacted with by end-users of the database system. The user makes use of the graphic user interface screen or the command prompt for submission of various MYSQL commands to the server end.

For every valid command submission, there is a valid output on the screen; for every wrong command submission, there is an error message sent as feedback to the screen. When a user sends a request to the server-end and the server accepts the request, a connection is established at once to enable the user carry out further requests. This could be termed connection handling, and it’s a function rendered by the client-side of the architecture.

• Connection Handling
• Authentication
• Security

1.1. Connection Handling:

When a client send request to the server and server will accept the request and the client is connected. When Client is connected to the server at that time, a client get its own thread for its connection. With the help of this thread all the queries from client side are executed.

1.2. Authentication:

The server performs the authentication in the server side. The authentication is based on the username, host of the client and password of the client user.

1.3. Security:

After the client gets connected successfully to MySQL server, the server will check whether that particular client has the privileges to issue certain queries against MySQL server.

2. Server Layer:

This layer takes care of all the logical functionalities of the MySQL relational database management system. The brain of the MySQL server is resides in this layer. The logical layer of the MySQL is divided into various sub components, which are given below:

• Services and Utilities
• SQL Interface
• SQL Parser
• Optimizer
• Caches & buffers

2.1. Services and Utilities:

MySQL Provides wide range of services and utilities for administration and maintenance, some of them are mentioned below:

• Backup & recovery
• Security
• Replication
• Cluster
• Partitioning
• Workbench

2.2. SQL Interface:

It is a tool to interact between MySQL client user and server. Some of the SQL interface components are given below.

• Data Manipulation Language (DML)
• Data Definition Language (DDL)
• Stored Procedures
• Views
• Triggers

2.3. SQL Parser:

The query parse tree, as obtained from the query parser, is then used by the query pre-processor to check the SQL syntax and check the semantics of the MySQL query to determine if the query is valid. If it is a valid query, then the query progresses down the pipeline. If not, then the query does not proceed and the client is notified of the query processing error.

2.4. Optimizer:

Once the parsing is complete, the MySQL applies a variety of optimization techniques. These techniques may include, rewriting the query, order of scanning of tables and choosing the right indexes to use.

2.5. Query Caches and Buffers:

Query Cache saves the entire result set for the query statement that was entered. MYSQL Server consults the query cache before parsing. When a client writes a query, if the query in the cache matches the query written by the client, the server skips parsing, optimization, and even execution and simply displays the output from the cache.

The cache and buffer will save the user’s previous query or issue. When a user types a query, it first goes to the Query Cache, which checks to see if the same query or problem exists in the cache. If the same question is open, it will produce results without interfering with the Parser and Optimizer.

3. Storage Layer:

The MYSQL database contains a different kind of storage engines which exist as a result of varying needs of databases. The storage engines are used to hold every user-created table in the database system. The storage-Layer facilitates the storing and retrieving of MYSQL data.

InnoDB: This is the default storage engine for MySQL 5.5 and higher. It provides transaction-safe (ACID compliant) tables, supports FOREIGN KEY referential-integrity constraints. It supports commit, rollback, and crash-recovery capabilities to protect data. It also supports row-level locking. It's "consistent nonlocking reads" increases performance when used in a multiuser environment. It stores data in clustered indexes which reduces I/O for queries based on primary keys.

MyISAM can handle non-transactional tables and support table-level locking and full-text search indexes. It is mainly used on the Web.

Federated can create a single logical database from many physical servers. It stores data only on the remote server.

MEMORY can create tables and store data in memory for faster performance and data access. It supports table-level locking and non-transactional tables and can be used for creating temporary tables.

MERGE can logically group a set of similar MyISAM tables into one table. The storage engine can manipulate large volumes of data.

EXAMPLE is used to teach developers how to create new storage engines.

Archive is used to store large volumes of unindexed data.

CSV tables are really text files with comma-separated values. CSV tables let you import or dump data in CSV format, to exchange data with scripts and applications that read and write that same format. Because CSV tables are not indexed, you typically keep the data in InnoDB tables during normal operation, and only use CSV tables during the import or export stage.

The Blackhole storage engine accepts but does not store data, similar to the Unix /dev/null device. Queries always return an empty set. These tables can be used in replication configurations where DML statements are sent to replica servers, but the source server does not keep its own copy of the data.

NDBCLUSTER: This clustered database engine is particularly suited for applications that require the highest possible degree of uptime and availability.