Editing MySQL (section)

==Features==
MySQL is offered under two different editions: the [[Open-source software|open source]] MySQL Community Server<ref>{{Cite web|url=https://www.mysql.com/products/community/|title=MySQL Community Edition|website=www.mysql.com|access-date=2020-04-03|quote=MySQL Community Edition is the freely downloadable version [...]. It is available under the GPL license [...]}}</ref> and the proprietary [[MySQL Enterprise|Enterprise Server]].<ref>{{cite web | url=http://www.mysql.com/products/which-edition.html | title=Which Should I Use: MySQL Enterprise or MySQL Community Server? | publisher=MySQL AB | access-date=8 April 2009 | url-status=dead | archive-url=https://web.archive.org/web/20090409055908/http://www.mysql.com/products/which-edition.html | archive-date=9 April 2009}}</ref> MySQL Enterprise Server is differentiated by a series of proprietary extensions which install as server plugins, but otherwise shares the version numbering system and is built from the same code base.

Major features as available in MySQL 5.6:
* A broad subset of [[SQL:1999|ANSI SQL 99]], as well as extensions
* Cross-platform support
* [[Stored procedure]]s, using a procedural language that closely adheres to [[SQL/PSM]]<ref name="HarrisonFeuerstein2008">{{cite book|author1=Guy Harrison|author2=Steven Feuerstein|title=MySQL Stored Procedure Programming|url=https://books.google.com/books?id=YpeP0ok0cO4C&pg=PT75|year=2008|publisher=O'Reilly Media|isbn=978-0-596-10089-6|page=49}}</ref>
* [[Database trigger|Triggers]]
* [[Cursor (databases)|Cursors]]
* Updatable [[View (SQL)|views]]
* Online [[Data Definition Language]] (DDL) when using the InnoDB Storage Engine.
* [[Information schema]]
* Performance Schema that collects and aggregates statistics about server execution and query performance for monitoring purposes.<ref>{{cite web | url=https://www.datadoghq.com/blog/monitoring-rds-mysql-performance-metrics/ | title=Monitoring RDS MySQL performance metrics | publisher=[[Datadog]] | date=2015-10-20 | access-date=14 December 2015}}</ref>
* A set of SQL Mode options to control [[Run time (program lifecycle phase)|runtime]] behavior, including a strict mode to better adhere to SQL standards.
* [[X/Open XA]] [[distributed transaction processing]] (DTP) support; [[Two-phase commit protocol|two phase commit]] as part of this, using the default [[InnoDB]] storage engine
* Transactions with [[savepoint]]s when using the default InnoDB Storage Engine. The NDB Cluster Storage Engine also supports transactions.
* [[ACID]] compliance when using InnoDB and NDB Cluster Storage Engines<ref>{{cite web |url=http://dev.mysql.com/doc/innodb/1.1/en/glossary.html#glos_acid | archive-url=https://web.archive.org/web/20101225160519/http://dev.mysql.com/doc/innodb/1.1/en/glossary.html#glos_acid | url-status=dead | archive-date=25 December 2010 | title=MySQL :: InnoDB 1.1 for MySQL 5.5 User's Guide :: C InnoDB Glossary :: ACID | access-date=5 January 2011 }}</ref>
* [[Secure Sockets Layer|SSL]] support
* Query [[cache (computing)|caching]]
* Sub-[[Select (SQL)|SELECTs]] (i.e. nested SELECTs)
* Built-in [[Database replication|replication]] support
** Asynchronous replication: [[Master/slave (technology)|master-slave]] from one master to many slaves<ref>{{cite web|url=http://dev.mysql.com/doc/refman/5.6/en/replication.html|title=Replication|publisher=MySQL|url-status=dead|access-date=3 May 2013|archive-date=6 May 2013|archive-url=https://web.archive.org/web/20130506041941/http://dev.mysql.com/doc/refman/5.6/en/replication.html}}</ref><ref>{{Cite web|url=https://mariadb.com/kb/en/library/high-availability-performance-tuning-mariadb-replication/|title=MariaDB Replication|website=MariaDB KnowledgeBase|access-date=2019-03-09}}</ref> or many masters to one slave<ref>{{Cite web|url=https://dev.mysql.com/doc/refman/5.7/en/replication-multi-source.html|title=MySQL :: MySQL 5.7 Reference Manual :: 16.1.4 MySQL Multi-Source Replication|website=dev.mysql.com|access-date=2019-03-09}}</ref>
** Semi synchronous replication: Master to slave replication where the master waits on replication<ref>{{Cite web|url=https://dev.mysql.com/doc/refman/5.7/en/replication-semisync.html|title=MySQL :: MySQL 5.7 Reference Manual :: 16.3.9 Semisynchronous Replication|website=dev.mysql.com|access-date=2019-03-09}}</ref><ref>{{Cite web|url=https://mariadb.com/kb/en/library/semisynchronous-replication/|title=Semisynchronous Replication|website=MariaDB KnowledgeBase|access-date=2019-03-09}}</ref>
** Synchronous replication: [[Multi-master replication]] is provided in [[MySQL Cluster]].<ref>{{cite web|url=http://dev.mysql.com/doc/refman/5.6/en/mysql-cluster-replication-multi-master.html|title=MySQL Cluster Replication: Multi-Master and Circular Replication|publisher=MySQL}}</ref>
** [[Virtual synchrony|Virtual Synchronous]]: Self managed groups of MySQL servers with multi master support can be done using: Galera Cluster<ref>{{cite web|url=https://blogs.oracle.com/mysqlf/entry/mysql_university_mysql_galera_multi|title=MySQL University: MySQL Galera Multi-Master Replication|date=2010-02-09|publisher=[[Oracle Corporation]]|access-date=3 May 2013|archive-date=12 December 2013|archive-url=https://web.archive.org/web/20131212022901/https://blogs.oracle.com/mysqlf/entry/mysql_university_mysql_galera_multi|url-status=dead}}</ref> or the built in Group Replication plugin<ref>{{Cite web|url=https://dev.mysql.com/doc/refman/8.0/en/group-replication.html|title=MySQL :: MySQL 8.0 Reference Manual :: 18 Group Replication|website=dev.mysql.com|access-date=2019-03-09}}</ref>
* Full-text [[Index (database)|indexing]] and searching{{efn|Initially, it was a MyISAM-only feature; supported by InnoDB since the release of MySQL 5.6.}}
* Embedded database library
* [[Unicode]] support{{efn|name=utf8mb4|Prior to MySQL 5.5.3, [[UTF-8]] and [[UTF-16/UCS-2|UCS-2]] encoded strings are limited to the [[Basic Multilingual Plane|BMP]]; MySQL 5.5.3 and later use utf8mb4 for full Unicode support.}}
* Partitioned tables with pruning of partitions in optimizer
* [[Shared-nothing]] clustering through [[MySQL Cluster]]
* Multiple storage engines, allowing one to choose the one that is most effective for each table in the application.{{efn|In MySQL 5.0, storage engines must be compiled in; since MySQL 5.1, storage engines can be dynamically loaded at [[Run time (program lifecycle phase)|run time]].}}
* Native storage engines [[InnoDB]], [[MyISAM]], Merge, Memory (heap), [[MySQL Federated|Federated]], Archive, [[Comma-separated values|CSV]], Blackhole, NDB Cluster.
* Commit grouping, gathering multiple transactions from multiple connections together to increase the number of commits per second.

The developers release minor updates of the MySQL Server approximately every two months. The sources can be obtained from MySQL's website or from MySQL's [[GitHub]] repository, both under the GPL license.

===Limitations===
When using some storage engines other than the default of InnoDB, MySQL does not comply with the full [[SQL]] standard for some of the implemented functionality, including foreign key references.<ref>{{cite web
|url=https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html
|title=13.1.20.5 FOREIGN KEY Constraints
|publisher=MySQL
|access-date=10 March 2021}}</ref> Check constraints are parsed but ignored by all storage engines before MySQL version 8.0.15.<ref>{{cite web
|url=http://bugs.mysql.com/bug.php?id=3464
|title=Bug #3464 Constraints: support CHECK
|publisher=MySQL
|date=14 April 2004
|access-date=11 April 2015}}</ref><ref>{{Cite web|url=https://dev.mysql.com/doc/refman/8.0/en/create-table.html|title=MySQL 8.0 Reference Manual: CREATE TABLE Syntax |website=MySQL Reference Manual|publisher=Oracle|access-date=2018-05-07}}</ref>

Up until MySQL 5.7, triggers are limited to one per action / timing, meaning that at most one trigger can be defined to be executed after an {{mono|INSERT}} operation, and one before {{mono|INSERT}} on the same table.<ref name="dev.mysql.com">{{cite web
|url=http://dev.mysql.com/doc/refman/5.6/en/create-trigger.html
|title=CREATE TRIGGER Syntax
|publisher=MySQL
|access-date=11 April 2015}}</ref>
No triggers can be defined on views.<ref name="dev.mysql.com"/>

Before MySQL 8.0.28, inbuilt functions like {{mono|UNIX_TIMESTAMP()}} would return {{mono|0}} after 03:14:07 [[UTC]] on [[Year 2038 problem|19 January 2038]].<ref>{{cite web
|url=https://bugs.mysql.com/bug.php?id=12654
|title=MySQL Bugs: #12654: 64-bit unix timestamp is not supported in MySQL functions
|publisher=MySQL
|date=2005-08-18
|access-date=2017-10-06
}}</ref> In 2017, an attempt to solve the problem was submitted, but was not used for the final solution that was shipped in 2022.<ref>{{cite web
|url=https://github.com/mysql/mysql-server/pull/130
|title=Allow dates beyond 2038 by dveeden · Pull Request #130 · mysql/mysql-server
|publisher=GitHub, Inc.
|date=2017-03-22
|access-date=2017-10-06
}}</ref><ref>{{Cite web |title=MySQL Bugs: #12654: 64-bit unix timestamp is not supported in MySQL functions |url=https://bugs.mysql.com/bug.php?id=12654 |access-date=2023-08-29 |website=bugs.mysql.com}}</ref><ref>{{Cite web |title=MySQL :: MySQL 8.0 Release Notes :: Changes in MySQL 8.0.28 (2022-01-18, General Availability) |url=https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-28.html |access-date=2023-08-29 |website=dev.mysql.com}}</ref>

===Deployment===
MySQL can be built and installed manually from source code, but it is more commonly installed from a binary package unless special customizations are required. On most [[Linux distribution]]s, the [[package management system]] can download and install MySQL with minimal effort, though further configuration is often required to adjust security and optimization settings.

[[File:LAMP software bundle.svg|thumb|400px|[[LAMP (software bundle)|LAMP]] software bundle, displayed here together with [[Squid (software)|Squid]].]]

Though MySQL began as a low-end alternative to more powerful proprietary databases, it has gradually evolved to support higher-scale needs as well. It is still most commonly used in small to medium scale single-server deployments, either as a component in a [[LAMP (software bundle)|LAMP]]-based web application or as a standalone database server. Much of MySQL's appeal originates in its relative simplicity and ease of use, which is enabled by an ecosystem of open source tools such as [[phpMyAdmin]].
In the medium range, MySQL can be scaled by deploying it on more powerful hardware, such as a multi-processor server with gigabytes of memory.

There are, however, limits to how far performance can scale on a single server ('scaling up'), so on larger scales, multi-server MySQL ('scaling out') deployments are required to provide improved performance and reliability. A typical high-end configuration can include a powerful master database which handles data write operations and is [[Database replication|replicated]] to multiple slaves that handle all read operations.<ref>{{cite web |title = The future of replication in MySQL |publisher = Facebook |url= https://www.facebook.com/note.php?note_id=126049465932 |access-date =2009-12-09 }}</ref> The master server continually pushes binlog events to connected slaves so in the event of failure a slave can be promoted to become the new master, minimizing downtime. Further improvements in performance can be achieved by caching the results from database queries in memory using [[memcached]], or breaking down a database into smaller chunks called [[Shard (database architecture)|shards]] which can be spread across a number of distributed server clusters.<ref>{{cite web |title=Database Sharding |publisher=Code Futures |url = http://www.codefutures.com/dbshards-cloud/|access-date = 2009-12-09 |url-status=dead |archive-url = https://web.archive.org/web/20100116031912/http://codefutures.com/dbshards-cloud/|archive-date=16 January 2010}}</ref>

==={{anchor|HA}}High availability software===
Oracle MySQL offers a high availability solution with a mix of tools including the MySQL router and the MySQL shell. They are based on Group Replication, open source tools.<ref>{{Cite web|url=https://www.mysql.com/products/enterprise/high_availability.html|title=MySQL :: MySQL Enterprise High Availability|website=www.mysql.com|access-date=2019-03-09}}</ref>

MariaDB offers a similar offer in terms of products.<ref>{{Cite web|url=https://mariadb.com/kb/en/library/replication-cluster-multi-master/|title=High Availability & Performance Tuning|website=MariaDB KnowledgeBase|access-date=2019-03-09}}</ref>

===Cloud deployment===
{{Main article|Cloud database}}

MySQL can also be run on [[cloud computing]] platforms such as [[Microsoft Azure]], [[Amazon Elastic Compute Cloud]], and [[Oracle Cloud|Oracle Cloud Infrastructure]].<ref>{{Cite web |url=https://cloud.oracle.com/en_US/infrastructure/compute |title=Oracle Cloud infrastructure |access-date=18 January 2018 |archive-date=18 January 2018 |archive-url=https://web.archive.org/web/20180118185219/https://cloud.oracle.com/en_US/infrastructure/compute |url-status=dead }}</ref> Some common deployment models for MySQL on the cloud are:

; Virtual machine image
: In this implementation, cloud users can upload a [[Virtual machine image|machine image]] of their own with MySQL installed, or use a ready-made machine image with an optimized installation of MySQL on it, such as the one provided by Amazon EC2.<ref>{{cite web |url = http://aws.amazon.com/articles/1663 |title = Running MySQL on Amazon EC2 with EBS (Elastic Block Store) |website =[[Amazon Web Services]] |access-date=5 February 2013 }}</ref>

; MySQL as a service
: Some cloud platforms offer MySQL "as a service". In this configuration, application owners do not have to install and maintain the MySQL database on their own. Instead, the database service provider takes responsibility for installing and maintaining the database, and application owners pay according to their usage.<ref name="readwriteweb">{{cite web | first=Klint |last=Finley |url=http://www.readwriteweb.com/cloud/2011/01/7-cloud-based-database-service.php | title = 7 Cloud-Based Database Services |website = [[ReadWriteWeb]] | access-date =2011-11-09 | url-status=dead | archive-url = https://web.archive.org/web/20111109084453/http://www.readwriteweb.com/cloud/2011/01/7-cloud-based-database-service.php |archive-date=2011-11-09}}</ref> Notable cloud-based MySQL services are the [[Amazon Relational Database Service]]; Oracle MySQL HeatWave Database Service,<ref>{{Cite web|url=http://oracle.com/mysql|title=Oracle MySQL HeatWave Database Service}}</ref> Azure Database for MySQL,<ref>{{Cite web|url=https://azure.microsoft.com/en-us/services/mysql/|title=Azure Database for MySQL - Managed MySQL Database &#124; Microsoft Azure|website=azure.microsoft.com}}</ref> [[Rackspace]]; [[HP Converged Cloud]]; [[Heroku]] and [[Jelastic]]. In this model the database service provider takes responsibility for maintaining the host and database.