What is MYSQL?

MySQL is a popular open-source relational database management system (RDBMS). It’s widely used because of its reliability, performance, and ease of use.

MYSQL Server

MySQL Server is a relational database management system (RDBMS) that runs on multiple platforms, including Linux, Windows, macOS, and FreeBSD which is not open source.

SQL Management Studio is used for establishing connection to MYSQL Server

Key features:

· Structured data: organizes data in tables with rows and columns

· SQL queries:

allows you to retrieve and manipulate data using a simple language

· Scalability:

can handle small to large databases

· Security:

offers features to protect your data

· Data replication:

MySQL Server can replicate data to other servers, which can be used for disaster recovery or to improve performance.

Why do we Use MYSQL Server?

To store and manage data:

Websites and applications often need to store and manage a large amount of data. MySQL Server is a great way to do this because it’s efficient and reliable.

To improve performance:

MySQL Server can help to improve the performance of websites and applications by caching data and optimizing queries.

To run complex queries:

MySQL Server supports a wide range of complex queries, which can be used to analyze data and generate reports.

To share data with other applications:

MySQL Server can be used to share data with other applications, such as web applications, mobile apps, and desktop applications.

Where is MySQL Server used?

1. Web Application

2. Financial Application

3. Content Management Systems like WordPress.

4. E-Commerce Websites

SQL Server Architecture

1. It is client server architecture

2. MYSQL Server process starts with client sending the request to the server

3. Server responds back to request with the processed data.

Major Components of SQL Server

1. Protocol Layer

2. Relational Engine

3. Storage Engine

Protocol Layer:

This layer handles communication between client and server. MySQL Server uses a custom binary protocol called MySQL Client/Server Protocol for communication

This includes:

Client connecting and authenticating: Establishing a connection and verifying the client’s identity.

Sending and receiving data: Sending queries and commands to the server and receiving results.

Error handling: Reporting errors and handling server issues.

Flow control: Managing data transmission and preventing overload.

Supports 3 types of architectures:

Shared Memory

In this Client and MYSQL Server runs on the same machine as in case of localhost. Both communicate via shared memory fool. For better understanding let’s look at an example:

Analogy: Lets map entities in the above two scenarios. We can easily map Tom to Client, Mom to SQL server, Home to Machine, and Verbal Communication to Shared Memory Protocol.

TCP/IP

MS SQL SERVER provides the capability to interact via TCP/IP Protocol, where CLIENT and MS SQL Server are remote to each other and installed on a separate machine. Let’s take an example:

Analogy: Lets map entities in the above two scenarios. We can easily map Tom to Client, Starbuck to SQL server, the Home/Market place to Remote location and finally Cellular network to TCP/IP protocol

For establishing these type of connections we use port 1433 which is default port for MYSQL Server. Below is the configuration picture attached:

Named Pipes

MS SQL SERVER provides the capability to interact via the Named Pipe protocol. Here the CLIENT and MS SQL SERVER are in connection via LAN. Let’s take an example:

Analogy: Lets map entities in the above two scenarios. We can easily map Tom to Client, Sierra to SQL server, Neighbor to LAN and finally Intra network to Named Pipe Protocol.

Note: This option is disabled by default and needs to be enabled by the SQL Configuration Manager.

What is TDS being used for communication?

· TDS: TDS stands for Tabular Data Stream.

· All 3 protocols use TDS packets. TDS is encapsulated in Network packets. This enables data transfer from the client machine to the server machine.

· TDS was first developed by Sybase and is now Owned by Microsoft

· The Tabular Data Stream (TDS) protocol is an application layer protocol used by clients to connect to SQL Server, while SQL Server uses Transport Layer Security (TLS) to encrypt data that is transmitted across a network between an instance of SQL Server and a client application.

Relational Engine

In Simple words it is responsible for optimized query execution and response.

The Relational Engine is also known as the Query Processor. It has the SQL Server components that determine what exactly a query needs to do and how it can be done best. It is responsible for the execution of user queries by requesting data from the storage engine and processing the results that are returned.

Components: It has 3 major components:

1. CMD Parser

2. Query Executor

3. Optimizer

1. CMD Parser:

“CMD Parser” is the first component of Relational Engine to receive the Query data. The principal job of CMD Parser is to check the query for Syntactic and Semantic error. Finally, it generates a Query Tree. Let’s discuss in detail.

Syntactic vs Semantic Error: Syntactic error occurs when syntax is wrong while semantic error refers to wrong logic. Semantic checks are done by Normalizer. Now a query tree is generated

2. Optimizer:

The work of the optimizer is to create an execution plan for the user’s query. This is the plan that will determine how the user query will be executed. Note that not all queries are optimized. Optimization is done for DML (Data Modification Language) commands like SELECT, INSERT, DELETE, and UPDATE. Such queries are first marked then send to the optimizer. DDL commands like CREATE and ALTER are not optimized, but they are instead compiled into an internal form. The query cost is calculated based on factors like CPU usage, Memory usage, and Input/ Output needs.

• Note The optimizer aims to find the execution plan that requires the least amount of resources to execute.

· This includes resources like CPU, memory, disk I/O, and network bandwidth

3. Query Executor

Query executer calls Access Method. It provides an execution plan for data fetching logic required for execution. Once data is received from Storage Engine, the result gets published to the Protocol layer. Finally, data is sent to the end user.

Storage Engine

The work of the Storage Engine is to store data in a storage system like Disk or SAN(Storage Area Network) and retrieve the data when needed.

Storage Engine has 3 components. It acts as an interface between query executor and Buffer Manager/Transaction Logs. Access Method itself does not do any execution. The first action is to determine whether the query is:

Ø Select Statement (DDL)

Ø Non- Select Statement (DDL & DML)

Depending upon the result, the Access Method takes the following steps:

If the query is DDL, SELECT statement, the query is pass to the Buffer Manager for further processing.

And if query if DDL, NON-SELECT statement, the query is pass to Transaction Manager. This mostly includes the UPDATE statement.

Buffer Manager

Buffer manager manages core functions for modules below:

Ø Plan Cache

Ø Data Parsing: Buffer cache & Data storage

Ø Dirty Page

Plan Cache

Existing Query plan: The buffer manager checks if the execution plan is there in the stored Plan Cache. If Yes, then query plan cache and its associated data cache is used.

First time Cache plan: Where does existing Plan cache come from? If the first-time query execution plan is being run and is complex, it makes sense to store it in in the Plane cache. This will ensure faster availability when the next time SQL server gets the same query. So, it’s nothing else but the query itself which Plan execution is being stored if it is being run for the first time.

Data Parsing: Buffer cache & Data Storage

Buffer manager provides access to the data required. Below two approaches are possible depending upon whether data exist in the data cache or not:

Buffer Cache — Soft Parsing:

Buffer Manager looks for Data in Buffer in Data cache. If present, then this Data is used by Query Executor. This improves the performance as the number of I/O operation is reduced when fetching data from the cache as compared to fetching data from Data storage.

Data Storage — Hard Parsing:

If data is not present in Buffer Manager than required Data is searched in Data Storage. If also stores data in the data cache for future use.

Transaction Manager is invoked when access method determines that Query is a Non-Select statement.

Log Manager

Log Manager keeps a track of all updates done in the system via logs in Transaction Logs.

Logs have Logs Sequence Number with the Transaction ID and Data Modification Record.

This is used for keeping track of Transaction Committed and Transaction Rollback.

Lock Manager

During Transaction, the associated data in Data Storage is in the Lock state. This process is handled by Lock Manager.

This process ensures data consistency and isolation. Also known as ACID properties.

Execution Process

Log Manager start logging and Lock Manager locks the associated data.

Data’s copy is maintained in the Buffer cache.

Copy of data supposed to be updated is maintained in Log buffer and all the events updates data in Data buffer. Pages which store the data is also known as Dirty Pages.

Checkpoint and Write-Ahead Logging: This process run and mark all the page from Dirty Pages to Disk, but the page remains in the cache. Frequency is approximately 1 run per minute. But the page is first pushed to Data page of the log file from Buffer log. This is known as Write Ahead Logging.

Lazy Writer: The Dirty page can remain in memory. When SQL server observes a huge load and Buffer memory is needed for a new transaction, it frees up Dirty Pages from the cache. It operates on LRU — Least recently used Algorithm for cleaning page from buffer pool to disk.

MYSQL Security

1. It uses Access control lists and user authentication mechanisms

2. It uses Transport Layer Security during transmission

3. It offers Transparent Data Encryption that encrypts data on disk in rest state.

Types of MYSQL Server

Express:

Free entry-level edition limited to 10GB database size and suitable for small applications and development.

Standard:

Offers more features and supports larger databases compared to Express. Good for mid-tier applications and workloads.

Enterprise:

Most powerful edition with comprehensive features and scalability. Ideal for demanding mission-critical workloads and large enterprises.

Developer:

Free edition for development and testing purposes, identical to Enterprise but with limitations on production use.

MySQL Server Editions Comparison Table

Ifyou enjoyed this piece, I’d appreciate it if you could take a moment to like and leave a comment. A special thanks to Guru99 for providing clear and simple explanations of these topics.