Understanding MySQL Architecture

The architecture of the world’s most popular open source database system is very important for the Information Technology people. There are many reasons for MySQL’s popularity around the world, but one of the main reasons is its architecture, while there are many big players such as Oracle, Microsoft SQL and DB2, MySQL’s architecture makes it as unique and preferred choice for most of the developers. In this article, we are going to discuss about of the internal architecture of the MySQL relational database management system. The article is for novice database administrators, database developers, software developers and those who are interested to work with MySQL database.

Major components:

The MySQL architecture describes how the different components of a MySQL system relate to one another. The MySQL architecture is basically a client – server system. MySQL database server is the server and the applications which are connecting to MySQL database server are clients. The MySQL architecture contains the following major components.

MySQL Architecture

Application Layer:

This layer is the top most layers in MySQL architecture; you can see this same layer in many of the client – server architecture. This layer includes some of the services which are common to most of the client – server applications, some of the services are given below:
  • Connection Handling.
  • Authentication.
  • Security.

Connection Handling:

When a client connects to server, the client gets its own thread for its connection. All the queries from that client executed within that specified thread. The thread is cached by the server, so they don’t need to created and destroyed for each new connection.


Whenever a client connects to a MySQL server, 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.


root@localhostroot          : Username of the client.
localhost  : host name, from where it is originated.


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.


mysql> show privileges \G
*************************** 1. row ***************************
  Privilege: Alter
  Context: Tables
  Comment: To alter the table
*************************** 2. row ***************************
  Privilege: Alter routine
  Context: Functions,Procedures
  Comment: To alter or drop stored functions/procedures

MySQL 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:
  • MySQL services and utilities.
  • SQL Interface.
  • SQL Parser.
  • Optimizer.
  • Caches & buffers.

MySQL services and utilities:

MySQL comparatively provides wide range of services and utilities. This is one of the main reasons for the popularity of the MySQL. This layer provides the services and utilities for administration and maintenance of MySQL system, some of them are mentioned below:
  • Backup & recovery.
  • Security.
  • Replication.
  • Cluster.
  • Partitioning.
  • Workbench.

SQL Interface:

Structured Query Language (SQL) is a query language, used to query MySQL server. 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.


MySQL parses queries to create an internal structure (the parse tree).  The MySQL parser behaves as a single pass compiler. As per the MySQL internals, the parser structure is given below.
  • Lexical analysis (making words or tokens from a character stream) is implemented at first stage, when parsing regular statements.
  • Syntactic analysis (making “sentences”), semantic analysis (making sure these sentences do make sense), and code generation (for compilers) – all of them – are done at once, during the phase of code. 


After creating the internal parse tree, 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. Actually you can ask the server to explain the various aspects of optimization.




The MySQL cache (query cache) stores complete result sets for SELECT statements. Even before parsing the query, the MySQL server consults the query cache. If any client issues a query that is identical to one already in the in the cache, the server simply skip the parsing, optimization and even execution, it just simply display the output from the cache.

Storage Engine Layer:

The pluggable storage engine feature makes the MySQL as unique and preferred choice for most of the developers. This is the feature which makes the MySQL to reach an edge over the big player. MySQL allows us to choose the variety storage engines for different situations and requirements. We are going to discuss about the features of each storage engine in upcoming article, just the list of supported storage engines are mentioned below.
  • MyISAM.
  • InnoDB.
  • Federated.
  • Mrg_MyISAM.
  • Blackhole.
  • CSV.
  • Memory.
  • Archive.
  • Performance_schema.

MySQL provides this as a pluggable storage engines, various storage engines can be used at table level. A database can contain the tables with the multiple storage engines.
Show engines command will list all the storage engines supported by your server. I hope this post will give you an overall understanding of the MySQL architecture. In next post let us discuss about the different storage engines and their features.


  1. This is an awesome post.Really very informative and creative contents.
    Wordpress Development company in Chennai

  2. The blog was having very informative content and very useful for me. Well done post and keep it up...
    Web Development Minnesota

  3. Hi Rathish.
    very useful and good explanation for people who want to kick start their career in MySql.
    Thanks for sharing.

    1. @goutham, Welcome and thank you for the kind words.

  4. You should continue writing such blogs, it was informative. Also, within your appreciation, I just want to add that you can buy World Zip Code Database from World Cities Database at very reasonable prices.

  5. Excellent website! I adore how it is easy on my eyes it is. I am questioning how I might be notified whenever a new post has been made. Looking for more new updates. Have a great day! vmware srm technology

  6. Fantastic content! Thank you Rathish!

  7. Rathish_Kumar teaching like *Anand_Kumar* really helpful stuff for someone who is a noobie in MySQL like me.
    Keep going!!

  8. This is very informative, you should keep writing these blogs

  9. Thank you, @Ashtosh Singh :)

  10. Thanks Rathish for this explanation. May I use this for my group project presentation? Thanks

  11. Thanks Rathish for this explanation. May I use this for my group project presentation? Thanks

  12. Through this post, I know that your good knowledge in playing with all the pieces was very helpful. I notify that this is the first place where I find issues I've been searching for. You have a clever yet attractive way of writing. nisartech.com

  13. It is perfect time to make some plans for the future and it is time to be happy. I've read this post and if I could I desire to suggest you some interesting things or suggestions. Perhaps you could write next articles referring to this article. I want to read more things about it! Car Accessories

  14. Hi! This is my first visit to your blog! We are a team of volunteers and new initiatives in the same niche. Blog gave us useful information to work. You have done an amazing job! meal prep 7 privacy policy

  15. I wanted to leave a little comment to support you and wish you a good continuation. Wishing you the best of luck for all your blogging efforts. Couple Shooting

  16. Architecture is commonly gotten from the non-useful prerequisites and is intended to work in attachment with practical necessities so as to accomplish the general business objective. berlin germany

  17. It is especially decent, though look into the tips during this home address. חוזה עם קבלן שלד

  18. World Tourism We welcome unsolicited news articles, analysis pieces, and op-eds. If you want to write for us, then send your articles to globeobservers@gmail.com. Your articles should be exclusive to us.