Manage databases with MySQL

Especially in e-commerce, many different data, starting with the product assortment up to customer data, have to be organized partly in parallel. Since both the loading times and the security of the data are of high importance, especially in the context of online stores, the database management system used must always be reliable. In the e-commerce and CMS environment, MySQL has established and asserted itself as the standard over the last few years.

In this article, we therefore take an in-depth look at the technical functionality of MySQL as well as the advantages and disadvantages of the system in e-commerce. We also show how maxcluster can support you with MySQL hosting optimized for online stores.

What is MySQL?

MySQL is the world's most popular relational database management system (DBMS). MySQL is open source and has been on the market since 1997. The name is composed of the first name "My", a daughter of the co-founder Michael Widenius, and the language Structured Query Language. The latter provides the operations for querying and managing data.

Originally, development began in 1994 as an open source project by the Swedish company MySQL AB. In early 2008, Sun Microsystems took over the company and continued development of the DBMS before Sun itself was taken over by Oracle in 2010. Since then, MySQL has been part of the Oracle Corporation portfolio and is available as open source software (OSS) under the GNU GPL licence. In addition, MySQL is offered to companies in a proprietary enterprise version in order to be able to use the MySQL code in individual applications and to receive extended support.

MySQL is also one of the most widespread database systems because it is used particularly often for the data management of dynamic web services. In conjunction with the programming language PHP and web servers such as Apache or Nginx, MySQL is part of the so-called LAMP stack (Linux / Apache / MySQL / PHP). This system architecture is considered a prerequisite for the most common content management systems and e-commerce platforms, such as WordPress, Typo3, Shopware or Magento. Incidentally, MySQL is also used by the big players Google, Facebook and Twitter.

How does MySQL work?

As a DBMS, MySQL is responsible for managing one or more databases. A database (DB) is a systematic collection of content-related data. The combination of databases and their managing DBMS is called a database system (DBS).

One of the essential tasks of a DBMS is to provide tools and functions in its role as an interface between the databases and the developers. These tools enable the structuring or setup of the data model as well as the query, modification and input of data using SQL.

In addition, the DBMS controls all read and write access, manages parallel access and ensures compliance with data integrity and data security policies. This ensures that the stored data remains persistent and consistent at all times.

Client-server model

The provision of MySQL's functionalities is organised according to the client-server principle, with MySQL itself representing the server. The server receives different queries from several clients, executes the desired commands and returns the corresponding information. The requested data, confirmations or error messages can be returned in response.

Depending on the purpose, the clients are located on the Internet, in the internal network or even on the same machine as the database system. The access of the individual clients to the server is regulated by creating users with individually configurable rights.

In addition to the supplied command line programme, the possible clients mainly include applications with direct database access, e.g. CMS, e-commerce platforms and graphical administration tools such as phpMyAdmin. When these applications are installed for the first time, the required database parameters host name, user and password are requested and specified once, whereupon connections can be established by the applications to the corresponding MySQL instance.

Storage engines

Physically, MySQL stores each of the managed databases in its own file system folder, which contains the structure of the data and the data itself. The format of these files depends on the storage engine used for the table, of which there are ten officially supplied engines in MySQL as well as those from other manufacturers. Each engine is optimised for a specific purpose by supporting different functions and offering different performance. Depending on the intended use of a table, a suitable engine must therefore be selected.

In the early days of MySQL, MYISAM was the standard engine, which provides particularly fast access due to the lack of transaction security and the management of parallel queries, and is therefore still used today especially for read applications. With the release of MySQL Version 5.5, however, MYISAM was replaced by the new InnoDB engine.

Since then, InnoDB has been the storage engine activated by default and thus most frequently used in MySQL, because it offers transaction-safe read and write access in favour of data consistency. The supported transaction security ensures that commands are either executed completely or not at all.

In addition, InnoDB supports full-text search and indexing since version 5.6, for which MYISAM had to be used before. For optimum stability and performance of the MySQL instance, we recommend using InnoDB as the storage engine as far as possible. Experience shows that MySQL performance can often be significantly improved by adjusting the size of the InnoDB Buffer Pool.

Relational database model

A database model defines in which structure and with which relationships the data of a database is stored, whereby the relational database model is the most popular variant. In the case of MySQL, the data is thus stored as a two-dimensional relation, or as a table with a unique name.

In each database managed by MySQL, several such tables can be created, which in turn consist of several rows and columns. Each column represents an attribute and has a fixed data type. In turn, each row stores a single data set with concrete values for the attributes.

The data sets are uniquely identifiable via a maximum of one primary key per table, consisting of one or more attributes. The primary key, which is typically defined via an ID when the table is created, must enable a unique assignment of the data records. For this purpose, each value of the primary key may only occur once per table.

By using a foreign key, i.e. an attribute with values of the primary key of another table, it is also possible to logically relate information from several tables and to link them in SQL queries.

Processing a query

The MySQL server is responsible for answering each SQL query of the clients as efficiently and quickly as possible. In order to be able to guarantee this performance, up to MySQL version 5.7 the query cache is queried first, which stores the results of the previous queries as a buffer.

If the requested query is identical to a previously executed query and the data has not changed in the meantime, the result stored in the cache is output directly without accessing the corresponding database. This process reduces the load on the server so that the queries can be processed much faster.

However, if no result is found in the cache, MySQL's parser first checks the query for formal correctness (syntax) and then breaks it down into its components in the form of a parse tree. At the same time, information about the type of query is displayed. At the same time, information about the type of query and the queried tables is collected.

Since the order and type of processing of the individual components can vary, the MySQL optimiser then searches for the most efficient way to process the query based on the parsing results. For this purpose, the required read accesses of all possible alternatives are determined and the time required in each case is estimated. Finally, the solution path determined by the optimiser with the lowest processing time is executed by the execution engine by using the memory engine. The result is stored in the cache and simultaneously returned to the client.

Advantages and disadvantages of MySQL

The use of open source software has become particularly established in e-commerce hosting and is present there in many areas. Due to the licensing of MySQL as an OSS, fundamental advantages and disadvantages arise, which we discuss in our blog post on the topic Open Source in the e-commerce.

Advantages of MySQL

A major advantage of MySQL is the broad support of different operating systems, including various Linux distributions, Mac OS and Windows. In addition, there are ready-made interfaces for using MySQL in almost every programming language. In addition, a variety of external tools are available for maintaining and operating a MySQL instance, as well as a large number of supported storage engines on the part of MySQL.

Because MySQL has been tested in many different scenarios over the last 25 years due to this high compatibility, the DBMS is considered to be particularly reliable and fast compared to its competitors, both when writing and reading data.

In addition, the handling of MySQL is quick and easy to learn due to the strong similarity to the standardised SQL and its widespread use. However, if problems should arise, there is a wide range of know-how within the large community to fall back on.

Disadvantages of MySQL

MySQL was not initially developed for very large amounts of data, such as those found in data warehousing, and is still not optimised for this purpose. Even though MySQL can manage these amounts of data in principle, it cannot work efficiently with them and quickly reaches the limits of the available resources. The result is often poor performance when processing queries.

In contrast to the paid competing products, MySQL's direct development and debugging tools also provide a rather smaller range of functions, which is why additional external tools often have to be used.

MySQL hosting at maxcluster

At maxcluster, MySQL is already pre-configured in our e-commerce stack and can be used immediately at no extra charge. MySQL is available in either version 5.7 or 8.0, whereby a later version upgrade from version 5.7 to 8.0 is possible at any time.

Regardless of the e-commerce or content management system used, MySQL can be easily configured with a few clicks via the Managed Center under Database & Search > MySQL. This allows new MySQL databases to be created and then managed with the help of the phpMyAdmin tool, which is also preconfigured and can be deactivated by just one click if desired.

In the Monitoring Center, the state of the MySQL instance can be retrieved and analysed retrospectively at any time in conjunction with the viewable log files. Our backup solution automatically creates daily backups for each MySQL instance with xtrabackup and checks them for correctness and consistency. Should problems occur, we will make the backup manually.

In the event of data damage or loss, the MySQL backups can be quickly restored via our Managed Center. In addition, our Linux administrators always have an overview of all MySQL values and further analysis tools at hand in order to be able to proactively intervene immediately in the event of problems.

If you would like to find out more about our e-commerce stack or our managed services, our consulting team looks forward to receiving your enquiry by email at beratung@maxcluster.de or by telephone on +49 5251 4141 350.

Conclusion

MySQL has had a significant influence on the structured management of data and has successfully established itself over the last decades as the standard in relational data management - especially for dynamic websites. This is mainly due to the fact that MySQL has proven to be reliable and performant in many situations. Thanks to its broad external compatibility and high popularity, the DBMS also offers an easy introduction to database systems.

As a rule, a MySQL database works very well "out of the box" with the included InnoDB storage engine and only rarely requires adjustments. However, those who attach great importance to the original open source philosophy should take a closer look at the MySQL fork MariaDB, which is meanwhile frequently traded as a potential successor to MySQL.

Published 20.07.2022 | FZ