Official Mafiree Blogs

Keeping you informed about Databases

 Home  /  MySQL  
How to achieve HA in MariaDB spider
By Murali   |   August 13, 2020   |   Posted in : MySQL

These days high availability is a must for any serious deployment. Spider HA is composed of data nodes, monitoring nodes, and spider nodes. Here we will explain how to set up spider as HA.

  • Data Node - It stores real data.

  • Monitoring Node - Monitoring the data node.

  • Spider Node - It’s for load balancing, failover, and transparently access to data node.


Architecture:


Steps to achieve HA:
 
  • Monitoring should be set up between Spider nodes participating in the cluster. We only have one Spider Node and spider_link_mon_servers represent the interconnection of all Spider nodes in our setup.

  • In a production setup, the number of Spider Nodes in the spider_link_mon_servers table should be at least 3 to get a majority consensus.

 

Background of the setup

  • 1 MySQL server (DB1) with Spider engine

  • 3 Remote servers (DB2,3,4) as Data nodes

  • 3 Remote servers (DB5,6,7) as Monitoring nodes

 

Table student_details is partitioned across data nodes.




Registering monitoring nodes information in MYSQL SERVER with spider and ALTER the table on spider with monitoring parameters. This is the Setup for High availability with spider storage engine.


Let’s see how to add a new node after failover happens in data nodes.
 

We need to create a new node in order to maintain redundancy. Here, we will explain how to add a table of a new node without stopping the service and also how to retrieve the failure node data into a new node using spider functions.



 

In this case, our data node DB2 has failed and so then we need to create a new backend DB8 and create the structure of the table as well. After that, we made a new server entry in mysql.Server table in monitoring nodes, Spider node and set the new server details instead of the existing server in the table (Students_Details).

 

If the Spider table is partitioned, the name must be of the format table_name#P#partition_name. The partition name can be viewed in the mysql.spider_tables table.

 NOTE: Returns 1 if the data was copied successfully, or 0 if copying the data failed


 

Conclusion:

Spider SE  is a good solution for MySQL sharding, especially if your table is huge and plan to keep it across multiple servers. At this point, Spider SE is generally available and works for most of the cases. However, one needs to explore all it’s features before using it in their environment.
 



0 Comments


Leave a Comment

Name *

Email *

Comment *



Search All Blogs



Need Support?


Solutions      Services      Resources      About Mafiree

More than 3000 servers monitored

Certified database Administrators

24X7X365 Support

Happy Clients


ENQUIRE NOW
For Sales Related Queries, Please Call Our Sales Experts at

 +91-80-41155993


Meet Mafiree on social networks!

     

PCI Certificate

Copyright © 2019 - All Rights Reserved - Mafiree