MySQL

MySQL
Basic data
developer	Oracle Corporation (until February 2008: MySQL AB , until January 2010: Sun Microsystems )
Publishing year	May 23, 1995
Current version	8.0.19 ; ( December 9, 2019 )
operating system	Linux , Microsoft Windows , macOS , FreeBSD , Solaris
programming language	C , C ++
category	Database management system
License	GNU General Public License, Version 2, proprietary license
German speaking	Yes
	www.mysql.com

MySQL [ ˌmaɪɛskjuːˈɛl ] is one of the world's most popular relational database management systems . It is available as open source software and as a commercial enterprise version for various operating systems and forms the basis for many dynamic websites .

MySQL has been developed by the Swedish company MySQL AB since 1994 . In February 2008, MySQL AB was acquired by Sun Microsystems , which in turn was bought by Oracle in January 2010 .

The name MySQL is made up of the first name My , which the daughter of MySQL AB co-founder Michael Widenius bears, and SQL .

Areas of application and distribution

A preferred area of application for MySQL is data storage for web services . MySQL is often used in connection with the web server Apache or Nginx and the scripting language PHP . One then speaks of a LAMP stack (Linux / Apache / MySQL / PHP) or of a LEMP stack (the "e" then stands for [e] nginx). Many web services make use of this architecture and, depending on their size and needs, operate a large number of MySQL servers, which are used to process access from the network. MySQL is used by Flickr , YouTube , Facebook and Twitter , among others . In addition, MySQL is used in many products as an embedded database system. MySQL is now increasingly being replaced by its fork MariaDB . MariaDB is compatible with MySQL, so MySQL databases can be migrated quite easily. The founder of MySQL Monty Widenius developed the new fork after Oracle bought MySQL and is expanding it together with a team of former MySQL core developers, so that further features are now available. A back migration is sometimes no longer possible after using these features. This is especially true when using storage engines like ColumnStores, which MySQL doesn't know about.

Platforms and interfaces

MySQL can run on many Unix variants, macOS and Linux , but also on Windows , OS / 2 and i5 / OS (formerly OS / 400). A Symbian version has also been available since the beginning of 2008 .

Structure of the storage system used

MySQL basically provides a MySQL server on which data is stored and one or more MySQL clients that send queries to the server, which it answers. The standard port for the MySQL server is 3306in the Transmission Control Protocol (TCP).

Several databases can be created on the database management system , the MySQL server. Several tables can be created in a database . In practice, MySQL creates a folder on the hard drive for each database in which files for the structure and data of the individual tables are stored. The exact format of these files depends on the storage engine used for the table in question.

The tables can each be of a different type. The table type determines which storage engine (storage subsystem) is used for queries to a table. Each table can contain columns in which data of a specified data type can be stored (e.g. integers (whole numbers) or strings (character strings)). The maximum size of the tables is basically only limited by the operating system .

Procedure for processing a request

A client can send database queries to a MySQL server. This is responsible for processing every request as efficiently as possible. The query cache is first queried and, if the result is not available, the query is parsed, optimized and finally executed; the result is then returned.

Query cache

To improve performance, MySQL can store the results of queries in a buffer, the query cache. If an identical query is later sent to the server without the data in the database having changed in the meantime, it will be answered from the cache. The database itself then does not have to be accessed.

parsing

If a query is to be executed, it is first checked whether its syntax is valid. It is broken down into its individual components. At the same time, some basic information about the query is collected, such as the type of query (e.g. SELECT, INSERT, SET or GRANT), the tables concerned or the contents of the WHERE clause. At the end of this step, MySQL knows the parse tree, which can then be optimized.

optimization

If a query is syntactically valid, then it is optimized next. The optimizer looks for the most efficient way to process the query. The main aim is to keep the number of data records to be read as low as possible. This is achieved, for example, when data records have to be read from several tables, by a clever query sequence ( JOIN ) of the tables; Tables that are not required are completely removed from the JOIN. Among other things, the optimizer takes into account whether it makes sense to use an index to localize the data records you are looking for (and if so, which one), or whether it would be better to perform a table scan instead, i.e. i.e., to go through the entire table.

The available alternatives are evaluated by the optimizer. In this case, due to heuristics estimated for each option, how long it would need to run. The alternative that has the lowest cost is then actually implemented.

The work of the optimizer can be traced with the MySQL command EXPLAIN. This command (followed by a specific query) instructs the optimizer to return its query plan . As output, the user receives information for each step that the optimizer would take to answer the query. In this way, with a little background knowledge, it can be determined to what extent the query can be optimized so that it can be executed faster.

history

Michael Widenius and David Axmark started developing MySQL in 1994. It was initially developed as a clone for mSQL in order to make databases of the mask- controlled database system UNIREG available in web applications. (UNIREG was developed by Michael Widenius in 1979 and rewritten in the C programming language around 1986 so that it also ran under UNIX systems.) MySQL was therefore fully compatible with both mSQL and UNIREG.

After an internal release on May 23, 1995, the software was immediately released in 1997 under version number 3.1 to signal that it is based on a core that has a long history. It was designed from the start for large amounts of data and very good performance, partly at the expense of stability and availability. The range of functions, however, was initially limited. There were only a few table types and no transactions. Typically, new properties are implemented at the request of the user, who thereby have a very large "say".

With Version 3.23 published in January 2001, MySQL had two types of tables with transactions , one of which ( InnoDB ) meets the ACID criteria. All operations that affect general SQL properties are the same for all table types, while the properties of the table types can be very different due to the different architecture. The MyISAM type has had a very powerful full-text search since the early version 3.23, which is not implemented in the InnoDB type. The replication system is also new in version 3.23. It is designed for use in a computer network and is ideal for uninterrupted operation. Several databases on different computer nodes are assigned to the database management system (DBMS). One of the databases acts as the master; the database contents are changed here. The replication system then distributes the data-changing SQL commands to the other databases, which track these changes locally on their tables. This is an asynchronous replication of the SQL commands. With the MySQL Cluster , a table type is available in which the entire database can be held in memory as an in-memory database . Synchronous replication between all cluster nodes and transaction processing is supported, but no full-text search.

MySQL 4.0, which was released in March 2003, allows the use of unions and introduced a query cache that caches the results of frequently used SQL queries.

MySQL 4.1 was released in October 2004. It offers data storage in different character sets per table; among other things, Unicode is also supported. Subqueries (subqueries / subselects) are possible.

Version 5.0 was released in October 2005. It supports all object types defined in the SQL3 standard . New in version 5 is the support of views , triggers , stored procedures and user-defined functions .

In February 2008, Sun Microsystems acquired MySQL AB. The purchase value is said to be one billion dollars; of which 200 million in stock options.

MySQL 5.1 was released in November 2008. The innovations include partition functions , with which very large tables can be divided into several physical files, an event scheduler, with the help of which previously defined SQL commands can be executed at regular intervals, and functions of the instance manager. The API has been revised so that it is now possible to load and unload components during runtime and without restarting the server.

In January 2010, Sun Microsystems was bought by Oracle. A few months later, Oracle announced that the development of the Falcon database engine , which MySQL AB had already started , will be discontinued.

MySQL 5.5 was released at the end of 2010. InnoDB became the standard storage engine. The performance was improved through the use of asynchronous I / O. The SIGNAL / RESIGNAL commands, which allow standard-compliant error handling in stored procedures, are also new.

MySQL 5.6, released in 2012, includes: a. Memcached APIs , global transaction IDs, and improvements to PERFORMANCE_SCHEMA.

In January 2009, before Oracle bought MySQL AB, Monty Widenius started a GPL fork, MariaDB . MariaDB is based on the code base of MySQL server 5.5 and has set itself the goal of largely maintaining compatibility with the MySQL versions published by Oracle.

The development of MySQL 5.7 took a total of two years until it was finally released at the end of October 2015. The new version is particularly characterized by increased performance, improved replication and logging. For example, the MySQL developers added a dynamic cost model to the query optimizer, which enables faster execution and offers the user more options for intervention.

criticism

Since the takeover of MySQL AB from Sun by Oracle, the database system has come under increasing criticism. The difference between the free and commercial versions of MySQL is becoming increasingly important. New functions are often only included in the commercial version of MySQL, plus the non-public database with error messages, outdated Bazaar archives and missing tests for troubleshooting.

Since the end of 2012, the support for MySQL in the open source community has also been eroding. After Fedora and OpenSUSE , Slackware and Arch Linux also replaced the MySQL package with MariaDB in early 2013 , a fork from co-founder Widenius. The trigger is the lack of respect for the community and the increasingly isolated development of the RDBMS. Likewise, the Wikimedia Foundation switched to MariaDB in early 2013.

Jaroslav Reznik, Red Hat's Fedora project manager, said that MySQL is evolving towards a closed project. All useful information on security issues (CVEs) would no longer be published. There were no longer any full regression tests and a very large part of the MySQL bug database was no longer public.

Michael Widenius, the former founder of MySQL AB, criticizes Oracle sharply: Oracle has made it clear that they have no interest in open source, that they refuse to work with the community and that they generally do not like MySQL. He mentions the commercial extensions for MySQL, the now non-public bug database and the lack of test cases for new MySQL code as examples of the disregard for the open source principles. Showcase functions such as online backup and foreign keys for all storage engines that were promised for MySQL 6.0 have not been released, although they are fully developed and ready. Instead of fixing bugs, remove Oracle functions. Most of the original MySQL developers would have left Oracle. As further evidence of the "contempt" of MySQL users, he cites the "sharp" increase in license and support fees and the lack of an open roadmap.

Storage subsystems

MySQL offers various storage subsystems (engines). Each engine is optimized for a special application scenario. Compared to the traditional multi-layer architecture of database systems, the engines are not a pure storage subsystem, but offer more functionality. The management of transactions, indices and referential integrity is in the hands of the engine.

The individual storage engines support different functions and have different performance depending on the application. Depending on what a table is used for (e.g. mainly read SELECT requests or mainly write INSERT / UPDATE requests), a suitable storage engine should be selected. Another criterion for the choice of the storage engine can also be the need to use a certain function, such as B. Transactions or referential integrity.

MySQL can also be expanded with your own storage engines. In addition to the engines published by MySQL and supplied with MySQL, there are also engines from other manufacturers.

Official engines

MyISAM

MyISAM offers quick access to tables and indexes without transaction protection. MySQL manages parallel database accesses (concurrency) at table level, that is, the entire table is locked for certain operations depending on the type of lock (read or write lock ). A large number of simultaneous read accesses is possible, since read accesses only acquire so-called READ locks. These allow other "READERS" to access the same data record at the same time. Write access must, however, wait until all current "READERS" have completed their read operations and thus release their READ lock. A “READER” only has to block other “WRITER”.

A data record that is changed can neither be read nor written in any other way. A "WRITER" (write access to data) must therefore block other "READER" and "WRITER". This is done using a "write lock". This also takes place at the table level, so during this time the entire table can neither be read nor written until the "write lock" is canceled.

MyISAM is a very efficient storage engine for a query mix that mainly consists of read access. Further advantages of MyISAM are:

most flexible autoincrement property of all storage engines
MyISAM tables can be converted into compressed read-only tables
MyISAM tables can have fixed line lengths, which enables faster line searches
MyISAM tables can be used to create MERGE tables
MyISAM is highly portable because the table files (.frm file (table structure), .MYD (data file), .MYI (index file)) can be copied to another machine and are immediately available there as database tables.
Powerful full text search

MySQL manages the access rights via grant tables in the database mysql, which (even in the latest version) are stored exclusively in MyISAM tables. MyISAM can therefore not be excluded when compiling or starting the server. Up to MySQL Version 5.1, MyISAM was also the standard storage engine, after which InnoDB took this place.

InnoDB

InnoDB offers transaction-safe read and write access, i. that is, it offers begin, commit and rollback functions. This ensures that a query, or a set of related queries, is either completely or not executed, but not incompletely. The desired isolation level of the transactions can be set. This can reduce the certainty of complete and correct execution, which has a positive effect on the execution speed.

InnoDB also offers the possibility to check foreign key relationships. InnoDB has been the standard storage engine since MySQL 5.5. As of MySQL 5.6, a full text search will also be possible in InnoDB tables. However, InnoDB does not fully comply with the SQL3 standard: foreign keys are only supported to a limited extent.

InnoDB saves the table structure in individual frm files, user data and indices in a table space. The tablespace is set before you start working with the database server and can span one or more files. The files in the table space can be distributed to different directories. The configuration of the table space cannot be adjusted afterwards without risking data loss. If the configuration of the table space is changed, the entire database is restored from a backup copy.

MERGE

MERGE offers the possibility of combining several tables of the MyISAM type with the same structure into one table and of accessing them. Compressed MyISAM tables can be combined with non-compressed MyISAM tables. In this way, data archiving can be implemented.

MEMORY (HEAP)

Management of temporary tables. The definition of the tables is permanently saved on the hard disk. The data is saved in the working memory. However, not all data types are supported. When the server is restarted, the table structures are still available and the content must be read in again, for example from permanent tables. Special memory management procedures are implemented in order to reuse the space of deleted records at the next insertion.

EXAMPLE

Code example for developing your own storage engine. EXAMPLE has functions for creating a table; the functions for writing and reading the data records are only indicated. A SELECT statement always returns an empty result set.

BDB

BDB is short for Berkeley DB . This storage engine was developed by Sleepycat Software and later sold to Oracle . The BDB offers transaction security and special precautions so that the stored data is retained in the event of a system failure.

The BDB storage engine is no longer supported from version 5.1.

FEDERATED

The FEDERATED engine offers access to tables that are on another server. If you create a table of type FEDERATED, the remote table must already exist on the other server. The local server behaves like a client accessing the remote server. The FEDERATED engine behaves like a federated information system , which means that it does not store the data itself, but grants access to the remote server, while the data can also be accessed on this. With version 5.0 only other MySQL servers can be accessed.

The access data to the remote database server is saved unencrypted in the local frm file. Access to the data directory must be restricted at the operating system level to prevent unauthorized persons from reading out the access rights.

CSV

With the CSV engine, the data is saved in CSV format : Numbers are not saved in binary but as sequences of digits; the individual values are separated by commas.

BLACKHOLE

BLACKHOLE was developed to check the syntax of SQL statements and to write a binary log. The data will not be saved. This allows syntax checks of SQL statements to be carried out without having to allocate space to store the data. The output of the binary log can be activated and deactivated via a parameter.

The BLACKHOLE engine is ideal for the following tasks:

Syntax check of dump files
Test the data replication by comparing the binary logs on the master server and the slave server.
Time measurements to determine the cost of writing the binary log.

NDB

NDB is the abbreviation for N etwork D ata B ase. The NDB storage engine is an independent component that enables the persistent storage of data and is responsible for the coordination of all accesses to data nodes in a MySQL cluster . Applications can access the NDB storage engine directly via the NDB API or via a MySQL node. Access via a MySQL node can be made much easier for application programmers, since standard SQL commands can be used in this case and learning the NDB specialties is not necessary.

The NDB-API is a multithreading -capable interface for accepting all incoming data requests. One or more threads are started for each request. Only sequential access is possible to the NDB-API, which presumably restricts the performance of the cluster if there are a large number of incoming requests.

Third-party engines

In addition to the official engines, several manufacturers also offer other engines with different properties or additional functions. Some are mentioned here as examples.

Revision engine

The DDEngine revision engine adds automatic versioning as a plug-in at the storage engine level. In addition to the pure storage of data, this engine can guarantee that data can be restored as it was at a specific point in time. This property can e.g. B. can be used to save the history of product properties or to meet legal requirements. The storage engines supplied are used to physically store the data.

Brighthouse Engine

The company Infobright provides the Brighthouse Engine. It is designed for data warehouse applications and is geared towards processing particularly large amounts of data. Indexes are not supported. The data is stored in compressed form, which, according to the manufacturer, can save up to 90% of the storage space.

Partitioning

As of version 5.1, MySQL tables can be partitioned . There are several types of partitioning to choose from.

Range partitioning

With range partitioning, value ranges are defined for the individual partitions.

The example creates a table with three partitions. With this syntax, the 'region' column may only contain values less than 30.

CREATE TABLE `kunde` (
  region int NOT NULL,
  nr int NOT NULL,
  name char(30),
  ed date NOT NULL
)
PARTITION BY range(region) (
  PARTITION p0 VALUES LESS THAN (10),
  PARTITION p1 VALUES LESS THAN (20),
  PARTITION p2 VALUES LESS THAN (30)
);

The partitioning can also be determined by an expression. The expression must generate an integer value as a result. If the last partition is defined with the value 'maxvalue', you can insert all integer numbers in the column 'region' (example above) or all date values in the column 'ed' (example below).

CREATE TABLE `kunde` (
  region int NOT NULL,
  nr int NOT NULL,
  name char(30),
  ed date NOT NULL
)
PARTITION BY range(year(ed)) (
  PARTITION p0 VALUES LESS THAN (1990),
  PARTITION p1 VALUES LESS THAN (2000),
  PARTITION p2 VALUES LESS THAN maxvalue
);

List partitioning

In the case of the list partition, the values are listed individually.

Example:

CREATE TABLE `kunde` (
  region int NOT NULL,
  nr int NOT NULL,
  name char(30),
  ed date NOT NULL
)
PARTITION BY list(region) (
  PARTITION p0 VALUES IN (1, 3, 5 ),
  PARTITION p1 VALUES IN (2, 4, 6 ),
  PARTITION p2 VALUES IN (10, 11, 12 )
);

Hash partitioning

With hash partitioning, the distribution of the records over the individual partitions is determined by the DBMS. With regular hash partitioning, the modulo function is used as the hash function (region modulo 4). It has the advantage that all integer numbers can be inserted in the 'region' column.

CREATE TABLE `kunde` (
  region int NOT NULL,
  nr int NOT NULL,
  name char(30),
  ed date NOT NULL
)
PARTITION BY hash(region) PARTITIONS 4;

There is also a 'linear' hash partitioning. Another hash function is used.

Key partitioning

A hash function is implicitly used for key partitioning . The primary key of the table is used as input for the function.

CREATE TABLE `kunde` (
  nr int NOT NULL primary key,
  name char(30),
  ed date NOT NULL
)
PARTITION BY key() PARTITIONS 4;

Key partitioning is used implicitly for all tables in the NDB storage engine. This makes the internal coordination of the replication easier.

Sub-partitions

'Subpartitions' can also be defined for every type of partitioning. This enables an even more granular breakdown of the data.

administration

On Linux, MySQL installs itself in the directory / var / lib / mysql / . Under Windows, the user defines the storage location for the data files - the default is the % ProgramFiles% \ MySQL folder . Basic settings are made by the administrator in the my.cnf file .

Command line tools

The supplied command line client (commands mysqland mysqladmin) is used to manage MySQL databases . The range of functions also includes the following command line tools :

mysqlimport: can be used as a replacement for 'LOAD DATA INFILE…'. By specifying parameters, users can log in and control the behavior of the program.
mysqldump: can be used as a replacement for 'SELECT * INTO OUTFILE'. The table structure can also be output as a dump. By specifying parameters, users can log in and control the behavior of the program.
perror: shows extended information on error codes. The error code is required as a parameter when the program is started.
mysqlshow: Outputs metadata on databases, tables or individual table columns.

Graphic tools

Oracle offers the MySQL Workbench software as graphical administration software . It is available for the Windows , macOS and Linux operating systems.

One of many alternatives is the open source application phpMyAdmin, written in the PHP scripting language . The graphical user interface can be operated via a browser . phpMyAdmin is mainly used to manage MySQL databases on web servers, on which individual users have no rights, and to run directly. To create and manage backups of databases on Web servers are - if there are no rights for the execution of the written also in PHP open source applications often - be phpMyBackupPro or MySQLDumper used. mysqlmysqladminmysqldump

License and support

Oracle uses a dual license system for the distribution of MySQL Server : On the one hand, the program is free software that is under the General Public License (GPL) , on the other hand, it is also offered under a commercial license .

The official manual provides support for the use of MySQL. There are also several forums and IRC channels where questions are answered free of charge.

Oracle also offers fee-based support licenses in three performance levels: MySQL Enterprise Silver, Gold and Platinum. They differ in the scope of services and price.

MySQL related products

The MariaDB database project was split off from MySQL in order to continue to make the source code used for MySQL freely available and to actively develop it further.

Another fork followed in 2008 with Drizzle .

MySQL Cluster enables MySQL to be installed on a computer cluster in a shared nothing architecture .

literature

Stefan Pröll, Eva Zangerle, Wolfgang Gassler: MySQL: The manual for administrators . Rheinwerk Verlag, 2015, ISBN 978-3-8362-3753-6 .

Web links

Commons : MySQL - collection of pictures, videos and audio files

MySQL.com official website
MySQL Documentation - official documentation

Individual evidence

↑ github.com . December 9, 2019.

↑ ^a ^b ^c ^d ^e Supported Platforms: MySQL Database . (English, accessed July 28, 2018).

↑ The mysql open source project on Open Hub: Languages Page . In: Open Hub . (English, accessed July 28, 2018).

↑ The mysql open source project on Open Hub: Languages Page . In: Open Hub . (accessed July 19, 2018).

↑ github.com . (accessed on September 27, 2017).

↑ www.mysql.com . (accessed on September 27, 2017).

↑ Michael Widenius: Michael Widenius interviewed at MySQL Conference 2010. O'Reilly, April 2010, accessed on November 16, 2016 (English).

↑ DB Engines Ranking . Retrieved September 8, 2013.

↑ Help system for version MySQL 5.0, Chapters 1.3 and 1.4 or http://dev.mysql.com/doc/refman/5.1/en/history.html

↑ mysql.de

^ YouTube, Flickr, and Wikipedia to Share their Secrets of Success at the 2007 MySQL Conference & Expo . O'Reilly. April 10, 2007. Retrieved September 29, 2012.

^ MySQL Customers by Industry: Web: Social Networks . Retrieved January 5, 2012.

↑ Jason Sobel: Keeping Up . In: Facebook Blog . December 21, 2007. Retrieved October 30, 2008.

↑ Om Malik: Facebook's Insatiable Hunger for Hardware . GigaOM . April 25, 2008. Retrieved October 30, 2008.

↑ Big and Small Data at Twitter: MySQL CE 2011 . myNoSQL . April 17, 2011. Retrieved October 20, 2011.

↑ Information on MySQL Embedded Server 5.1 , accessed September 27, 2010

↑ https://www.informatik-aktuell.de/betrieb/datenbanken/mariadb-und-mysql-vergleich-der-features.html Comparison of MySQL and MariaDB

↑ ^a ^b For further details see especially Sasha Pachev: Understanding MySQL Internals, Chapter 9: Parser and Optimizer, O'Reilly

↑ The modules responsible for parsing are located in the files sql / sql_yacc.yy, sql / gen_lex_hash.cc and sql / gen_lex.cc.

^ Five Questions With Michael Widenius - Founder And Original Developer of MySQL . Opensourcereleasefeed.com. Archived from the original on March 13, 2009. Retrieved June 8, 2009.

↑ Reference manual of version 4.0 ( Memento of the original from April 4, 2013 on WebCite ) Info: The archive link was inserted automatically and has not yet been checked. Please check the original and archive link according to the instructions and then remove this notice. @1@ 2
↑ MySQL Reference Manual Version 4.1 ( Memento of the original from April 4, 2013 on WebCite ) Info: The archive link was inserted automatically and has not yet been checked. Please check the original and archive link according to the instructions and then remove this notice. @1@ 2
↑ Sun takes over MySQL - News at TecChannel
↑ Sun buys MySQL AB ( Memento from July 31, 2008 in the Internet Archive )
↑ heise.de
↑ dev.mysql.com ( Memento of the original from February 17, 2012 in the Internet Archive ) Info: The archive link was inserted automatically and has not yet been checked. Please check the original and archive link according to the instructions and then remove this notice. @1@ 2
↑ Oracle press release: Oracle Completes Acquisition of Sun
↑ ^a ^b oracle.com
↑ heise.de
↑ New Features in MySQL 5.6 ( Memento of the original from April 23, 2011 in the Internet Archive ) Info: The archive link was inserted automatically and has not yet been checked. Please check the original and archive link according to the instructions and then remove this notice. (English), accessed June 23, 2012 @1@ 2

^ MariaDB versus MySQL compatibility . In: MariaDB KnowledgeBase . ( mariadb.com [accessed February 8, 2017]).

↑ Press Release: Oracle Announces General Availability of MySQL 5.7 , accessed on August 30, 2017

↑ pro-linux.de

↑ golem.de

↑ pro-linux.de Slackware switches to MariaDB By Mirko Lindner, Mon, March 25, 2013, 2:07 pm

↑ blog.wikimedia.org Wikimedia Foundation switches to MariaDB

↑ zdnet.com

↑ computerworld.com.au

↑ Reference Manual for MySQL Version 5.0 Chapter 14. Storage Engines and Table Types