What is MySQL? How Does MySQL Work?

designlinux

4 years ago

MySQL is the world’s most popular enterprise-grade open-source relational database management system (RDBMS) that is being used at Facebook, Google, Adobe, Alcatel Lucent, and Zappos, and by many online websites/applications.

It is developed, distributed, and supported by Oracle Corporation. It is a cross-platform, powerful, flexible, and extensible relational database that is based on the SQL (Structured Query Language) standardized language used to create and manipulate databases.

The latest version of MySQL (version 8.0 at the time of writing) comes with support for NoSQL (“Not Only SQL”) document databases. It can be installed in Linux, macOS, and other UNIX-like operating systems, and Windows.

Download MySQL

The MySQL database software is open source, it uses the GPL (GNU General Public License). Importantly, it is offered in two different editions: the open-source MySQL Community Server which you can download, access the source code, and use for free and the proprietary MySQL Enterprise edition and other commercial products which require annual subscription and include professional support and many other benefits.

MySQL is used for a wide range of purposes, including web database (the most common use), data warehousing, e-commerce, and logging applications. It is one of the commonly installed software to set up a LAMP (Linux + Apache + MySQL + PHP) or LEMP (Linux + Engine-X + MySQL + PHP) stack used for web development and hosting online content management systems such as WordPress, Magneto, Joomla, Drupal, and many others. Apart from PHP, it also supports many other languages including Perl, Node.js, Python, and so on.

Check out these related guides in setting up your application with the MySQL database on Linux.

How Does MySQL Work?

Like most database management systems out there, MySQL has a client-server architecture and can be used in a networked environment. The server program resides on the same physical or virtual system where the database files are stored, and it is responsible for all interactions with the databases.

Various client programs such as MySQL tools for database administration or any applications that are written in other programming languages, can connect to the server and make database requests. The server processes client requests and returns the results back to the client.

A client can either reside on the same system as the server or on a remote host and send database requests over a network or internet connection to the server. Importantly, the MySQL server must be running for clients to connect to it.

MySQL Client-Server Structure

Key Features of MySQL

The MySQL uses a multi-layered server design with independent modules. The server is multi-threaded, multi-user, scalable, and robustly designed for mission-critical, heavy-load production systems. It provides both transactional and nontransactional storage engines and supports the addition of other storage engines.

MySQL uses very fast B-tree tables with index compression, a very fast thread-based memory allocation system, and executes very fast joins using optimized nested-loop join.
It supports many data types such as signed/unsigned integers, floating-point types(float and double), char and varchar, binary and varbinary, blob and text, Date, DateTime, and timestamp, year, set, enum, and OpenGIS spatial types.
MySQL also supports data redundancy, and high-availability (HA) via master-slave replication, multi-node clustering, and backup and recovery/restore. It offers a wide range of backup types and strategies from which you can choose the methods that best suit the requirements for your deployment.
Its security features include user account management and access control, host-based verification, encrypted connections, several components and plugins (such as authentication plugins, connection-control plugins, password-validation component and many more) that implement security, as well as FIPS (Federal Information Processing Standards 140-2 (FIPS 140-2)) mode on the server-side which applies to cryptographic operations performed by the server.

Besides, you can also ensure additional security by following MySQL/MariaDB security best practices for Linux. But as always, ensure that you have implemented good network and server security, to ensure all-round database server security.

MySQL Client and Tools

MySQL ships with several client programs such as the popular command-line utilities: mysql, mysqladmin and mysqldump, for administering databases. To connect to the MySQL Server, clients can use several protocols, for example, TCP/IP sockets on any platform or UNIX domain sockets on UNIX systems like Linux.

To connect and execute MySQL statements from another language or environment, there are standards-based MySQL connectors (that provide connectivity to the MySQL server for client applications), and APIs for most popular programming languages (to provide low-level access to MySQL resources using either the classic MySQL protocol or the X Protocol).

Some of the popular connectors and APIs include ODBC (Open Database Connectivity), Java (JDBC – Java Database Connectivity), Python, PHP, Node.js, C++, Perl, Ruby, and native C and embedded MySQL instances.

You will find the following articles about MySQL useful:

Sharing is Caring…
Share on Facebook Share on Twitter Share on Linkedin Share on Reddit