Intro to MySQL

One of the numerous important aspects for the smooth functioning of a website or a web app is the strength of its database. How well the DB handles critical high-volume traffic, is there a data recovery mechanism in case of system failures, advanced functionalities, etc. are some of the parameters that define the working of a DB.

Our previous blog introduced the non-relational DBMS MongoDB. In this article, we will look at an introduction to the MySQL database (an RDBMS) and go through its architecture and various features that make it the best choice with regards to developing web apps or websites.

Architecture

MySQL, initially developed by MySQL AB, now backed by Oracle, is a relational database management system (RDBMS), based on the client-server model.

Image Source: ntu.edu.sg

Data is distributed in a tabular form (in different tables) using rows and columns. Tables are connected to each other via joins (relations).

SQL (Structured Query Language) is used as a mode of communication between the client and the server. All advanced functions of the SQL, like triggers, stored procedures, etc. can be used as part of querying.

MySQL is a multi-platform DB that can run on various operating systems like Windows, Linux, Unix, Mac etc. It is an open-source DB written in C and C++ but uses SQL as the language to communicate within the DB, and it supports various data types, namely Character-string, Numeric, Bit-string, Date & Time, Boolean, and Timestamp.

MySQL is the most widely used DB, along with PHP, to build websites, and popular examples of them include Facebook, WordPress, Drupal, etc. It is also compatible with other programming languages like Ruby, Python, Perl, etc.

Features

Developers are swearing by MySQL because of its wide range of features and benefits and are giving their systems an introduction to MySQL by migrating from previous DB to MySQL.

Performance

As mentioned above, the performance of a DB, while maintaining an enormous amount of data, is a significant parameter it is judged upon. MySQL provides high performance in such critical situations without compromising on data since it stores data on multiple engines like InnoDB, ISAM, CSV, etc. It offers faster loading because of its memory caching and indexing methodologies.

High Availability and Throughput

One of the major advantages of MySQL is the availability of the MySQL cluster servers that facilitate auto-sharding, wherein data is distributed across several servers. In this way, data is well-balanced and always accessible even during high-traffic situations.

In the event of any crash or system failure, data can be replicated onto secondary servers from the primary servers, thereby providing instant backup and recovery.

Compliance to ACID

MySQL is fully compliant to ACID (Atomicity, Consistency, Isolation, Durability), which means it guarantees reliable transaction processing and data accuracy even during adverse circumstances.

Leveraging SQL Functionality

Having SQL as its primary language for database interaction, MySQL supports the use of advanced SQL components like views, stored procedures, triggers, functions, etc. for enhanced application development. Perhaps this is one of the most powerful features of MySQL.

Security

MySQL provides robust security mechanisms like user account authorization, password encryptions, etc., which make it the most secure and reliable RDBMS.

Summary

The below survey shows the most popular databases in 2018.

Image Source: eversql.com

This introduction to MySQL can give developers a new horizon to explore and gain advanced expertise in RDBMS functionalities. MySQL displays a broad array of attributes and powerful data storage mechanisms, which has brought it to the number one position in the world of RDBMSs. With industry giants like Facebook and YouTube backing it and incorporating it as their DB, MySQL is here to stay and slay!

Further Reading

Essential MySQL