Official Mafiree Blogs

Keeping you informed about Databases

Achieving High Availability Using Log Shipping
By Sujith   |   February 21, 2018   |   Posted in : SQL Server
Here we will get the detailed explanation of how we can achieve HA using Log Shipping.

SQL Server On Linux
By Sujith   |   March 30, 2019   |   Posted in : SQL Server
SQL Server 2017 brings the best features of the Microsoft relational database engine to the enterprise Linux ecosystem, including SQL Server Agent, Azure Active Directory (Azure AD) authentication, best-in-class high availability/ disaster recovery, and unparalleled data security.

Types of SQL Server Replication
By Sujith   |   February 19, 2018   |   Posted in : SQL Server
Understanding on the types of replication in SQL Server and the type apt for your traffic pattern.

Data File Splitting in SQL Server
By Murali   |   March 15, 2021   |   Posted in : SQL Server
Huge data blocks resides under single MDF file might cause a performance of the query and impact the application services. This blog help you to understand how we can the split the single MDF file into multiple data files.

Dynamic query routing with ProxySQL
By Sabareesh   |   March 15, 2021   |   Posted in : Proxysql
How ProxySQL helps in achieving query routing without making a application changes.

 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.




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.




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