MySQL ecosystem

There are a wide variety of MySQL flavors available in the market today that 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.

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

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.

Many companies have worked to extend MySQL and created products that 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 in Chapter 23 InnoDB Cluster.

• 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 at Percona XtraDB Cluster.

• 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 at MariaDB Galera Cluster.

• 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 at Continuent Tungsten cluster.

• 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 at MariaDB Xpand.

• ScaleArc

ScaleArc is a database load balancing software that provides high availability. More details can be found at ScaleArc.

• Vitess

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 at Vitess.

• 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 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.