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

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.


Recommended Solution

Handy Backup Small Server

Version 7.9.6, built on 13 November, 2017. 65.8 MB
Backup Software from Novosoft LLC. 249 USD per license.

Handy Backup Small Server

With the help of the Small Server edition you can back up and restore your MySQL databases without stopping them. See how powerful backup solutions for small business can be. Free 30-days trial!


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:

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

And disadvantages:

  1. 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.
  2. The Network DataBase (NDB) storage engine requires a lot of memory. It is recommended for storage nodes to have as much RAM as possible.
  3. 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.

See also:

Version 7.9.6, built on 13 November, 2017
65.8 MB

See all terms from Databases...

Backup Terms Glossary

Contact Us

support@handybackup.net

Term of the Day

Backup Software Glossary

Current glossary explains various terms that you may come across while working with Handy Backup or other backup software.

Handy Backup is a backup utility for Windows Servers and PCs that is famous for its flexibility and ease of use.

The glossary contains 220 terms. The current section Databases contains 18 terms.

Other “Databases” Terms

  • MySQL Login Scripts
    “ MySQL login scripts are vital part of software using MySQL such as websites and forums with dynamic content, business apps based on MySQL...”
  • Physical Backup
    “Physical backup is a copy of an application’s binary files that is performed on hard drive level using common file system queries, rather than...”
  • Database Replication
    “Database replication is a process of synchronization in which data added to one database is automatically copied to the other. Replication should not...”

Who uses our backup software?