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.
Adding a new data node
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.
Failover
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 Engine 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 Engine 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.
