MySQL is one of the most popular open source databases in the world, according to DB-Engines. Its first version was released in 1995, and it has been under continuous development since then, making it a very popular relational database even today. MySQL owes its success to an ecosystem consisting of a wide range of open source and commercial editions. MySQL is used widely in both on-premises and the cloud. Major cloud service providers for MySQL are Google Cloud, Oracle, Amazon Web Services (AWS), and Microsoft Azure.
There are a wide variety of MySQL flavors available in the market today which extend MySQL by adding features for optimization, scalability, clustering, disaster recovery, and more. The two major databases that were built by forking MySQL and are currently active are MariaDB and Percona Server for MySQL.
MySQL Community Edition and Enterprise Edition
Oracle develops and maintains both MySQL Community Edition and Enterprise Edition. Enterprise Edition includes technical support provided by Oracle and also has some additional features like MySQL enterprise monitoring, PAM authentication, enhanced security, enhanced performance and scalability, enterprise backup, enterprise audit, enterprise cluster management, thread pooling, and more.
MySQL’s founders went on to create MariaDB with the guarantee that it will be kept open source. Some features in MySQL were in fact released earlier in MariaDB than they were in MySQL, like JSON support, multi-source replication, and parallel thread replication. Some major key benefits of MariaDB over MySQL are:
- introduction of new storage engines like ColumnStore for analytics, S3 engine to archive tables on AWS S3, Aria Engine, which is an optimized version of MyISAM, MyRocks for high compression and higher traffic applications, Cassandra, SphinxSE, and more
- capability of parallel execution of queries
- thread pooling, which is available in MySQL Enterprise Edition but not in Community Edition
- free to use router named MaxScale
- a multi-parent solution: Galera Cluster
A company named Percona develops and maintains Percona Server for MySQL. Percona did a vast amount of work over the years on top of MySQL to optimize it and introduce new features as well. Some major benefits of using Percona over MySQL are:
- improved InnoDB full text search, improved memory storage engine and flushing mechanism, more hash/digest functions, support for encryption built in InnoDB tablespace, binary logs, and temporary files
- introduction of a parallel double-write buffer to optimize it
- an XtraBackup tool for hot backup with full and incremental backup options
- support for MyRocks and Toku storage engines
- advanced troubleshooting instruments like per table, per index, per client, per user, and per thread performance counters
- PAM authentication and audit logging, which are available in MySQL's Enterprise Edition but not Community Edition
- Pt-toolkit, developed by Percona, a utility service that has many options to administer MySQL
Which flavor to choose?
The needs of an application dictate which database to choose as different databases have unique features targeting particular use cases. For example, if your application needs analytics you can use either clickhouse in Percona or columnstore in MariaDB, however, no such option is available in MySQL. Similarly, if you want to use the thread pool feature, which is not available in the Community version of MySQL, you have to choose either Enterprise MySQL or open source threadpool feature in Percona and MariaDB.
MySQL in the cloud
A cloud database is a database built to run in a public or hybrid cloud environment to help organize, store, and manage data within an organization. Cloud databases can be offered as a managed database-as-a-service (DBaaS) or deployed on a cloud-based virtual machine (VM) and self-managed by an in-house IT team. Major cloud providers for managed MySQL include:
Google Cloud offers both a virtual machine hosted version of MySQL, which a user would have to self manage, and Cloud SQL, which is a fully managed offering.
Amazon Web Services (AWS)
Amazon Web Services (AWS) offers a VM hosted option for MySQL and a fully managed solution in the form of RDS. AWS also has an offering of Aurora, which is a MySQL-compatible offering of MySQL with additional features.
Azure SQL Database
Azure offers two deployment models for MySQL that are similar to those of other cloud vendors: a virtual machine option in the form of MySQL on Azure VMs and the fully managed version in the form of Azure Database for MySQL.
Oracle offers the ability to host MySQL on its virtual machines. It also came up with a new fully managed offering called MySQL HeatWave Database Service.
Digital Ocean also has the ability to host MySQL on its virtual machines. It too offers a fully managed hosting option for MySQL.
High availability (HA) or cluster solutions
Many companies have worked to extend MySQL and created products which provide failover and high availability for the MySQL database. Some are direct tools used with MySQL, and some are modifications to the MySQL source code resulting in creation of separate products, which provide high availability or multi-parent clusters. Some popular MySQL HA and scalability solutions include:
- MySQL’s Group Replication and InnoDB Cluster
An InnoDB Cluster consists of at least three MySQL Server instances, and it provides high-availability and scaling features. InnoDB Clusters consist of MySQL Shell, MySQL Router, and MySQL servers with Group Replication. More details can be found here.
- Percona XtraDB Cluster
Percona XtraDB Cluster is a synchronous clustering solution that provides high availability, parallel replication, and scalability features. Percona XtraDB Cluster includes the ProxySQL admin tool that automatically configures Percona XtraDB Cluster nodes using ProxySQL. More details can be found here.
- MariaDB Galera Cluster
MariaDB Galera Cluster is a virtually synchronous multi-primary cluster for MariaDB that provides high availability and scalability features. It’s powered by MariaDB servers and the Galera wsrep provider library. It provides features like automatic node joining, true parallel replication, multi-primary topology, and more. More details can be found here.
- Continuent Tungsten cluster
Tungsten cluster provides a single easy package, which has all core components required to deploy and manage the MySQL database as clusters. It provides a geo clustering solution. It consists of Tungsten Manager, Replicator, Connector, Data nodes, and Dashboard. More details can be found here.
- MariaDB Xpand (formerly known as ClustrixDB)
MariaDB Xpand provides ACID-compliant distributed SQL, high availability, fault tolerance, write scaling, and horizontal scale-out for transactional workloads. MariaDB Xpand is a component of MariaDB Enterprise. It consists of one or more MaxScale nodes and three or more Xpand nodes. More details can be found here.
ScaleArc is a database load balancing software that provides high availability. More details can be found here.
Vitess has built-in sharding features that let you grow your database without adding sharding logic to your application to provide high availability and scalability. More details can be found here.
- Tools that provide standard replication for failover: MHA, ProxySQL, MaxScale, and Orchestrator
- Cloud-based services like Google Cloud, Oracle, AWS, and Azure provide in-house HA for MySQL
MySQL tools are present in all forms—web-based, command line interface (CLI) and the graphical user interface (GUI). MySQL has a wide range of open source and commercial tools for database designing, development, administration, backup, performance monitoring, and alerting.
Some popular MySQL monitoring tools with GUI are:
Percona Monitoring and Management, Prometheus, MySQL Enterprise Monitor, New Relic, SolarWinds, HeidiSQL, AppDynamics, Datadog, and SQLyog.
Some popular administration, development, or utility tools are:
- GUI based - MySQL Workbench, HeidiSQL, DBeaver, dbForge Studio, Navicat, SQLyog
- CLI based - Percona toolkit, ghost
Details of popular tools:
- MySQL Workbench - owned by MySQL, available in Community Edition and Enterprise Edition with advanced features
- Percona Monitoring and Management (PMM) - developed by Percona and available for free
- HeidiSQL - developed by Ansgar Becker and is available for free
- Percona Toolkit - developed by Percona and is available for free
- Toad - owned by Quest and it's a paid tool
- phpMyAdmin - free open source web-based tool to administer MySQL
- DBeaver - developed by open source community, available in Community Edition and Enterprise Edition with advanced features which are maintained by DBeaver
- Navicat - owned by CyberTech and it's a paid tool
- SQLyog - owned by Webyog and it's also a paid tool
Some other major monitoring tools with graphs to analyze the error rates, traffic and query patterns with alerting mechanisms are New Relic, Zabbix, Cortex, Prometheus, and Grafana.
Running MySQL with Containers / Kubernetes
Running a database over a Kubernetes cluster using Docker container images is becoming popular these days. It helps with managing the database using code, easy and fast deployments, data protection, isolation from the application, and shared resources usability. Basically all the advantages of Docker can be leveraged by the database system. There are also some Kubernetes operators already available for MySQL. However, there is a lot of scope for improvement in this area.