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 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.
Version 8.3.3, built on November 28, 2021. 112 MB
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:
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.
Version 8.3.3, built on November 28, 2021
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 141 terms. The current section Databases contains 17 terms.
Other “Databases” Terms