MySQL Clustering

MySQL clustering is a technology that allows distributing the MySQL database across multiple independent nodes, to eliminate every possibility of failure. It allows for almost infinite scaling of your MySQL-based website or application, which can be done horizontally, i.e. with very inexpensive machines.

Clustering is available since MySQL version 5.0. To create it, you need to set up three groups of nodes: one for storage, one for query processing, and one for management. To ensure maximum availability, each group should contain at least two nodes.

MySQL Clustering: Storage, Query processing and Management nodes

MySQL Clustering and Backups

It is important to understand that setting up MySQL clustering does not remove the need for backups. Clusters are good for high-availability and performance, but you may still lose important data due to such factors as viruses, SQL injections, human mistakes, etc. If your application issues a DROP DATABASE statement, there will be no way to restore deleted data – no matter how many storage nodes you have. To learn about backing up MySQL databases, please see MySQL Backup.

One other important thing to be noted about MySQL backup is that some MySQL storage engines perform badly on concurrent reading and writing. This means that if your application writes into the database a lot, performing backups can noticeably slow it down. Basically, this should be solved by MySQL replication which doesn′t expect all nodes to behave similarly and allows assigning different roles for different computers: e.g. one to handle all writing operations (Master Server) and the other for reading (Slave Servers).

However, a combination of clustering and backup can give good results too. MySQL clusters use a storage engine called Network DataBase (NDB) which is an in-memory engine designed for distributed storage and processing. Each record saved into NDB is automatically copied to storage nodes. The system automatically distributes the load between different nodes, so in write-heavy scenarios the backup can be taken from one storage node, while the other will continue receiving data. This scheme is generally slower than the backup built above replication, but is still a lot better than “raw” backup of a single MySQL server. To find instructions on configuring MySQL clustering, please refer to the official MySQL documentation.

To sum up, let′s review MySQL cluster advantages:

MySQL clustering is a good solution for increasing database availability.
The Network DataBase (NDB) storage engine employed by MySQL clusters has very high performance.
Load balancing mechanisms allow scaling web applications very effectively and greatly optimize high write loads.
The cluster supports almost infinite horizontal scaling, which enables you to target bottlenecks of your applications (CPU, disk I/O, memory, etc.)

And disadvantages:

Clustering engine requires nodes to be replicated synchronously, which means that they have to share one geographic location and communicate over a high bandwidth network.
The Network DataBase (NDB) storage engine requires a lot of memory. It is recommended for storage nodes to have as much RAM as possible.
MySQL clustering is relatively hard in installation, setting up and administering.

Video Example

In the following video tutorial you can learn how to back up and restore MySQL databases with Handy Backup.

Note: Instructions in this video suggest that you already have Handy Backup installed on your computer. If you still haven′t done so, don′t hesitate to download it.

Download for Free

Version 8.5.3 , built on March 5, 2024. 116 MB
30-day full-featured trial period

See also:

MySQL Clustering

MySQL Clustering and Backups

Handy Backup Small Business

Video Example

Who uses Handy Backup?