Description:TECHNICAL FIELD
[0001] The present disclosure relates, in general, to the database management system, and more specifically, relates to a system and method to automate replication and switching in the case of master-slave/multi-master MySQL database.

BACKGROUND
[0002] Data is pivotal to any system, and one of the major challenges that an organization faces is the availability of data round-the-clock. A 95% reliable system may not sound bad but in terms of availability out of 365 days, it would not be able to provide data services for 18 days which could affect an organization very badly. An unreliable system can cause corruption/loss of critical data and operational latency. It is desired that not only the system shall provide data with high availability but shall also be highly reliable, i.e., the data shall not be corrupted. Further, a highly reliable system is expected to be capable of detecting a failure as well as auto-provision itself to tolerate the same. Database replication solutions, whether original equipment manufacturer (OEM) or third party are intended to provide the above-mentioned functionality supporting the vision to build highly available, scalable and reliable systems. However, conventional replication solutions do incur significant overhead in terms of hardware/infrastructure planning and configuration, application-level design and interoperability.
[0003] MySQL is one of the most popular relational database management systems (RDBMS) used by the industry and academia primarily due to its ease of use, performance, and affordability. Although MySQL does not support multi-master replication by default, MySQL Network Database (NDB) cluster and MySQL Innodb Cluster do support multi-master replication.
[0004] A few existing methods known in the art involve master-slave data replication with very limited support for multi-master configuration. Methods involving multi-master configuration suffer from certain limitations at the infrastructure and implementation levels, such as manual intervention, and the absence of automatic failover. MySQL NDB cluster requires changes in table structure, while MySQL Innodb cluster solution provides no support for synchronous replication. Moreover, the solution can be rendered inoperable in case a node does not receive a message related to connectivity from another node.
[0005] An example of such a method is recited in the patent titled, “Multi-Master data replication in a distributed multi-tenant system”, which involves connecting databases with each other. The drawback of this approach is the increase in load on all database servers as each of them has to open its connection to another database server. Another example is recited in the Patent application titled “Parallel transaction messages for database replication”, which involves replication via a router or a single application. This is susceptible to a single point of failure and involves no method for clearing the backlog. Yet another example is recited in literature titled: “Lazy Database Replication with Snapshot isolation”, which involves lazy database replication. However, the existing literature suffers from limitations such as a lack of consistency between database servers. Most of the time the database servers may not be in a synchronized state thus the queries running on different database servers may give different results.
[0006] The existing system involves logging by using files, which has the following drawbacks -
• Segregation issue - By logging in a file it becomes difficult to maintain logs for multiple databases.
• Concurrency access – The client application must manage concurrency when using file-based logging. Typically, in file-based logging, when an application or thread opens a file, it must be locked so that other applications or threads cannot update the file simultaneously.
[0007] Therefore, it is desired to overcome the drawbacks, shortcomings, and limitations associated with existing solutions, and develop a mechanism which helps in replicating data among different MySQL database servers effectively, the mechanism helps in achieving fault tolerance as well as load balancing in MySQL database and calculating the number of records accurately in MySQL database.

OBJECTS OF THE PRESENT DISCLOSURE
[0008] An object of the present disclosure relates, in general, to a database management system, and more specifically, relates to a system and method to automate replication and switching in the case of a master-slave/multi-master MySQL database.
[0009] Another object of the present disclosure is to provide a system which helps in replicating data among different MySQL database servers effectively.
[0010] Another object of the present disclosure is to provide a system that helps in achieving fault tolerance as well as load balancing in the MySQL database.
[0011] Another object of the present disclosure is to provide a system that calculates number of records accurately in MySQL database.
[0012] Yet another object of the present disclosure is to provide a system that enables real-time replication, switchover, and conflict resolution with little overhead over the existing system design, thereby outlining a mechanism to automate failover and failback operations in MySQL databases.

SUMMARY
[0013] The present disclosure relates in general, to the database management system, and more specifically, relates to a system and method to automate replication and switching in the case of master-slave/multi-master MySQL database. The main objective of the present disclosure is to overcome the drawback, limitations, and shortcomings of the existing system and solution, by providing a system and method which helps in replicating data among different MySQL database servers effectively. The system helps in achieving fault tolerance as well as load balancing in MySQL database and provides a novel technique for calculating the number of records accurately in MySQL database.
[0014] The present disclosure provides a system that comprises one or more client machines coupled to one or more database servers through a network, the one or more database servers adapted to receive request data from one or more client machines. A processor operatively coupled to the one or more client machines and one or more database servers, the processor configured to maintain at the one or more client machine individual queues for each database server in the event of link failure, wherein the queues are maintained to store the requests from the client side. The system can determine a periodic signal to check the connectivity and health status of the plurality of database servers to maintain load balancing to perform automatic switchover in case of connectivity errors.
[0015] The system can synchronize the faulty database server with the healthy database server as soon as the size of the backlog for a particular table crosses a predefined threshold limit, where the predefined threshold limit is 500MB and the table contains the backlog query and normal query that need to be executed. The system can manage the client-originated requests by converting the request into native SQL statements and transmits to one or more database servers. The system can request the connected one or more database servers through a set of instructions residing in the one or more client machine and execute SQL statements and return the result back to the one or more client machines through the set of instructions residing in the one or more database servers.
[0016] The set of instructions comprises middleware applications residing in the one or more client machine and the one or more database server, wherein the middleware applications are DBRepClient and DBRepServer. The one or more client machine communicates with the one or more database servers through transmission control protocol (TCP) socket communication.
[0017] Moreover, the processor is configured to reduce the load on each database server by reducing the number of connections from 𝒏(𝒏−𝟏)𝟐 to just 𝒏 where 𝒏 is the total number of database servers. The processor performs timestamp-based logging of statement execution occurring at both levels to enable real-time replication, switchover, and conflict resolution. Each client machine and each database server are responsible for replicating, switching, load balancing and finding the accurate account.
[0018] Besides, the processor is configured to get an accurate count of the table, the processor configured to maintain the last updated time for each table, receive the one or more client machine count requests, transmit the count request to all the connected database servers, receive the count request and last updated table time to the one or more client machine by the one or more database servers and execute the query by the one or more database servers and return the total number of records along with a time to the plurality of client machine.
[0019] Various objects, features, aspects, and advantages of the inventive subject matter will become more apparent from the following detailed description of preferred embodiments, along with the accompanying drawing figures in which like numerals represent like components.

BRIEF DESCRIPTION OF THE DRAWINGS
[0020] The following drawings form part of the present specification and are included to further illustrate aspects of the present disclosure. The disclosure may be better understood by reference to the drawings in combination with the detailed description of the specific embodiments presented herein.
[0021] FIG. 1 illustrates an exemplary overall architecture of a system for achieving multi-master replication with automatic switchover, in accordance with an embodiment of the present disclosure.
[0022] FIG. 2 illustrates an exemplary view of the block diagram depicting database/server-side applications, in accordance with an embodiment of the present disclosure.
[0023] FIG. 3 is an exemplary flow chart illustrating the steps for the processing of client requests at a client machine using a middleware agent, in accordance with an embodiment of the present disclosure.
[0024] FIG. 4 is an exemplary flow chart illustrating the steps for the processing of transactions at the server by DBRepServer, thereby persisting the data in the database, in accordance with an embodiment of the present disclosure.
[0025] FIG. 5 is an exemplary flow chart illustrating the steps for estimating count, in accordance with an embodiment of the present disclosure.
[0026] FIG. 6 is an exemplary flow chart for facilitating automatic replication and switching in multi-master/master-slave MySQL database, in accordance with an embodiment of the present disclosure.

DETAILED DESCRIPTION
[0027] The following is a detailed description of embodiments of the disclosure depicted in the accompanying drawings. The embodiments are in such detail as to clearly communicate the disclosure. If the specification states a component or feature “may”, “can”, “could”, or “might” be included or have a characteristic, that particular component or feature is not required to be included or have the characteristic.
[0028] As used in the description herein and throughout the claims that follow, the meaning of “a,” “an,” and “the” includes plural reference unless the context clearly dictates otherwise. Also, as used in the description herein, the meaning of “in” includes “in” and “on” unless the context clearly dictates otherwise.
[0029] The present disclosure relates, in general, to a database management system, and more specifically, relates to a system and method to automate replication and switching in the case of a master-slave/multi-master MySQL database. The proposed system disclosed in the present disclosure overcomes the drawbacks, shortcomings, and limitations associated with the conventional system by providing a method or system to achieve fault tolerance and replication at the database layer, in particular using MySQL as the backend database. The present disclosure presents a novel approach for calculating count in distributed databases. The aforementioned is achieved using a combination of middleware replication agent applications, DBRepClient and DBRepServer, designed to run at the client and server machines respectively.
[0030] Each DBRepClient is connected to all the available DBRepServer(s) using TCP/IP. A request from a client is directed to DBRepClient, which parses the request into plain SQL statements and forwards the same to every DBRepServer, which executes the statements in the database and returns the execution acknowledgement. Timestamp-based logging of statement execution occurs at both levels to enable real-time replication, switchover, and conflict resolution with little/no overhead over the existing system design, thereby outlining a mechanism to automate failover and failback operations in MySQL databases. The present disclosure can be described in enabling detail in the following examples, which may represent more than one embodiment of the present disclosure.
[0031] To overcome the limitations of the existing system, table logging may be used to solve the abovementioned issue. In the case of DBRepClient, there may be 𝒏 threads where 𝒏 is equal to the number of databases. All these threads can access and update the table simultaneously, which is very difficult to manage in a file-based logging system.
[0032] The present disclosure provides an effective database replication method for fault tolerance and load balancing at the database layer with minimum configuration overhead and no/minimal extra infrastructure burden. The present invention is generally embodied in MySQL database because of its wide applicability and its ability to generate the transactional metadata in a form which can be easily parsed and converted to SQL statements and hence can be applied during replication.
[0033] The present disclosure is centred around a middleware application which sits on each of the database system. Whenever an update is initiated on any of the MySQL database, the middleware application captures the database update from the binary logs, holds them up in memory, checks points, and consequently applies the updates on the other MySQL databases, thereby enabling seamless synchronization among a topology of MySQL databases. The invention contains a novel method of getting accurate counts in a multi-master database, thus maintaining consistency. The middleware application can run on commodity hardware having the same configuration as MySQL and has minimum software requirements. In accordance with the embodiment, the system is capable to address the limitations associated with conventional MySQL database replication and/or cluster approaches as mentioned in the previous section, thus empowering to build of highly reliable and scalable systems. The invention further adds the dimension of real-time load balancing whereby concurrent update operations can be performed on multiple databases and the same shall be replicated to every other node present in the system. This creates the notion of multi-master replication possible in a cohort of MySQL databases and also allows for scalability within the system.
[0034] The present disclosure provides a system that includes one or more client machines coupled to one or more database servers through a network, the one or more database servers adapted to receive request data from the one or more client machines. A processor operatively coupled to the one or more client machines and the one or more database servers, the processor configured to maintain at the one or more client machine individual queues for each database servers in the event of link failure, wherein the queues are maintained to store the requests from the client side. The system can determine a periodic signal to check the connectivity and health status of the plurality of database servers to maintain load balancing to perform automatic switchover in case of connectivity errors.
[0035] The system can synchronize the faulty database server with the healthy database server as soon as the size of the backlog for a particular table crosses a predefined threshold limit, where the predefined threshold limit is 500MB and the table contains the backlog query and normal query that need to be executed. The system can manage the client-originated requests by converting the request into native SQL statements and transmits to one or more database servers. The system can request the connected one or more database servers through a set of instructions residing in one or more client machines and execute SQL statements and return the result back to the one or more client machines through the set of instructions residing in the one or more database servers.
[0036] The set of instructions comprises middleware applications residing in one or more client machine and the one or more database servers, wherein the middleware applications are DBRepClient and DBRepServer. The one or more client machine communicates with one or more database servers through TCP socket communication.
[0037] Moreover, the processor is configured to reduce the load on each database server by reducing the number of connections from 𝒏(𝒏−𝟏)𝟐 to just 𝒏 where 𝒏 is the total number of database servers. The processor performs timestamp-based logging of statement execution occurring at both levels to enable real-time replication, switchover, and conflict resolution. Each client machine and each database server are responsible for replicating, switching, load balancing and finding the accurate account.
[0038] Besides, the processor is configured to get an accurate count of the table, the processor configured to maintain the last updated time for each table, receive by the one or more client machine count request, transmit the count request to all the connected database servers, receive the count request and last updated table time to the one or more client machine by the one or more database servers and execute the query by the one or more database servers and return the total number of records along with a time to the plurality of client machine.
[0039] The advantages achieved by the system of the present disclosure can be clear from the embodiments provided herein. The system does not require manual intervention in the case of fallback anymore. The present disclosure allows automatic fallback without any human help, thus helping the system to achieve high availability. The middleware application can handle conflicts caused due to network latency that traditionally require human help. The description of terms and features related to the present disclosure shall be clear from the embodiments that are illustrated and described; however, the invention is not limited to these embodiments only. Numerous modifications, changes, variations, substitutions, and equivalents of the embodiments are possible within the scope of the present disclosure. Additionally, the invention can include other embodiments that are within the scope of the claims but are not described in detail with respect to the following description.
[0040] FIG. 1 illustrates an exemplary overall architecture of a system for achieving multi-master replication with automatic switchover, in accordance with an embodiment of the present disclosure.
[0041] Referring to FIG. 1, system 100 for replicating data across MySQL database management system running on different machines is disclosed. The system 100 can include one or more client machines (102-1, 102-2 (which are collectively referred to as client machine 102, herein)) and one or more DB servers (104-1, 104-2 (which are collectively referred to as DB server 104, herein)). The client machine 102 running DBRepClient Application. Each DBRepClient may be connected to all the DBRepServer via the network. The DB Server 104 running DBRepServer application, which is responsible for executing the queries in the database running and returning the result to DBRepClient.
[0042] The present disclosure presents a technique for handling automatic switchover in case of failure and presents a technique to find out the accurate count of tables in replicated setup. It is made sure that the database management system provides high availability of data along with switchover. Not only the system provides high availability, but also the credibility of data is maintained in the replicated system. The manual approach of switching to the database is completely removed and the system is capable of handling things on its own. Replication is done using a middleware layer that is responsible for running queries on all the systems registered in a cluster. The middleware works on each client as well as on each database and is responsible for replicating, switching, load balancing and finding the accurate account. The entire backlog is stored in table format. These logs contain operations queries performed on a table that can be either Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL) events.
[0043] In an embodiment, the replication is handled by the middleware application that runs on the client (DBRepClient) as well as on the database side (DBRepServer). Both DBRepClient and DBRepServer may be running for each instance of client 102 and server 104. Additionally, both DBRepClient and DBRepServer may be using a local table.
[0044] The network should have the following characteristics to enable synchronous sending of data such as low latency, minimal packet loss, resilient network, and scalable bandwidth. The communication between DBRepClient 102 and DBRepServer 104 happens via TCP Socket communication, and to avoid latency, a communication link of 1Gbit/sec is required. To enable replication properly, the following is the hardware requirement. This requirement is similar to that of a system running MySQL Server.

Minimum
Recommended

CPU 2 CPU cores 4 CPU cores or more
RAM 2 GB Ram 8GB Ram or more
Storage HDD SSD
Table 1 – Hardware Requirement
[0045] On the client side, to replicate, the following steps are involved are as follows:
• DBRepClient may have parallel connections to every database. The main idea of middleware application is to avoid mesh topology, as mesh topology creates a burden on each database because they have to be connected in a bi-directional manner with all of the databases involved in the replication.
• DBRepClient may maintain individual queues for each database. These queues are maintained to store the requests from the client side.
• On receiving a query from the client side, DBRepClient may push the request as a query and the current time into the queue of all the databases. Time is added to ensure the handling of conflict as well as synchronisation issues that may arise due to connectivity or any other error.
• In each thread, the queue for the respective database may be handled and queries for each database may be sent.
• A client may get a result as soon as data from any database is received, and thus the client does not have to wait for each request to execute.
• If any database is not working or DBRepClient is unable to process a request for a database, then it may maintain requests in a table. This is basically a logging mechanism in which the table stores requests that could not be handled by DBRepClient at a given moment of time. This table may have the following mandatory fields:
Database IP Query Request Time
Table 2 – DBRepClientBacklog
Database IP – Database IP for which query is to be executed.
Query – DML/DDL/DCL query
Request Time – Time of request
• The maximum size allowed for a table is around 4 GB. In the case of DBRepClient as soon as it sees that the number of rows in DBRepClientBacklog is greater than 1 crore (approx. 500MB) for a database, it may replace those rows with a single command for synchronising the faulty server with a working server.
• As soon as DBRepClient is able to make sure that a database has come online, it may start reading entries from the backlog table and may try to send them to the particular DB Server IP mentioned in the backlog table. As soon as DBRepClient gets acknowledgement from the DB server 104, it may remove this entry from this table. DBRepClient may have a mechanism to avoid sending the same requests multiple times.
• DBRepClient may keep storing queries in its local storage until the server becomes ready to process new requests.
[0046] DB Server Side
• DBRepServer may be running on DB server 104. The middleware is responsible for synchronising the database and conflict handling. The following are the steps involved.
• On getting a normal request, that is, the client is not sending backlog data, it may simply store queries in the database and return back results to the client.
• In the case of a backlog, DBRepServer 104 may be storing requests based on timestamps in its local table, and after the entire backlog data has been received, DBRepServer may start executing queries one by one and may be acknowledging the client the same. The table. 3 shown below may be having following mandatory fields.
Request Time Client ID Query Query Status
Table 3 – DBRepServerPendingLog
Request Time – Query Request time from client.
Client ID – Client ID from which the request has been generated
Query – DML/DDL/DCL query
Query Status – Status of query execution
[0047] If the timestamp of the request for two queries is the same, then they can be executed in any order.
[0048] Before accepting, it is very important that the DBRepServer application should complete the processing of the entire backlog.
[0049] The present disclosure provides an effective database replication method for fault tolerance and load balancing at the database layer with minimum configuration overhead and no/minimal extra infrastructure burden. The present invention is generally embodied in MySQL database because of its wide applicability and its ability to generate the transactional metadata in a form which can be easily parsed and converted to SQL statements and hence can be applied during replication.
[0050] According to an embodiment, the present disclosure is centred around a middleware application which sits on each of the database system. Whenever an update is initiated on any of the MySQL databases, the middleware application captures the database update from the binary logs, holds them up in memory, checks points, and consequently applies the updates on the other MySQL databases, thereby enabling seamless synchronization among a topology of MySQL databases.
[0051] The present disclosure contains a novel method of getting accurate counts in a multi-master database, thus maintaining consistency. The middleware application can run on commodity hardware having the same configuration as MySQL and has minimum software requirements. In accordance with the embodiment, the system is capable to address the limitations associated with conventional MySQL database replication and/or cluster approaches as mentioned in the previous section, thus empowering to build of highly reliable and scalable systems. The invention further adds the dimension of real-time load balancing whereby concurrent update operations can be performed on multiple databases and the same shall be replicated to every other node present in the system. This creates the notion of multi-master replication possible in a cohort of MySQL databases and allows for scalability within the system.
[0052] Thus, the present invention overcomes the drawbacks, shortcomings, and limitations associated with existing solutions, and provides a system which helps in replicating data among different MySQL database servers effectively. The system helps in achieving fault tolerance as well as load balancing in the MySQL database. The system calculates a number of records accurately in the MySQL database. Further, the system enables real-time replication, switchover, and conflict resolution with little overhead over the existing system design, thereby outlining a mechanism to automate failover and failback operations in MySQL databases.
[0053] FIG. 2 illustrates an exemplary block diagram depicting database/server-side application, in accordance with an embodiment of the present disclosure.
[0054] As soon as the DB server 104 comes online 202, DBRepServer may also spawn automatically. At block 204, the DBRepServer may start processing and at block 206 DBRepServer may start processing by picking entries from the table. The table contains the query that needs to be executed.
[0055] At block 208, after all the backlog has been cleared it may start executing normal queries.
[0056] To handle switchover in the case of database connectivity errors, the following steps are taken by DBRepClient 102 and DBRepServer 104.
[0057] Client Side
• DBRepClient uses a periodic signal (heartbeat) to check the connectivity and health status of the DB server 104.
• If at any moment the DB server 104 goes down, the DBRepClient may know and may start writing logs for that DB server 104 in its local storage.
• There is no need for a switchover as the other connections would be working fine and the client will be getting data without disruption. This not only helps in handling switchover but also load balancing achieved as the client would be getting the result from the server that has executed first and it does not have to wait for every server’s result.
• As soon as DB server 104 comes online, all the clients having a backlog may send the data to that DB server 104.
• DBRepClient may not send new data to execute until the backlog has been cleared and may continue storing requests in its local storage.
• DBRepServer may get acknowledgement from DBRepServer and DBRepclient may remove the entry from its database.
• In case DBRepClient hangs or is not working then it would be the responsibility of Client Application to pause database-related operations.
[0058] Server Side
• If the request sent by DBRepClient 102 is not handled by a DBRepServer 104 or if the request is timed out then the queries would be stored in the backlog table at the client side 102 with the database IP, request time and other required information.
• After the database comes online, it may not start taking new requests until the backlogs have been processed. It may process requests based on request time and, after execution, may send an acknowledgement to the client so that client can remove the entry from its table.
• The initial state of the database is standby, and in standby mode, it may keep on processing the backlog until the backlog has been cleared, at which point its state changes to ready. In this state, the database can handle new requests.
[0059] FIG. 3 is an exemplary flow chart illustrating the steps for the processing of client requests at a client machine using a middleware agent, in accordance with an embodiment of the present disclosure.
[0060] Referring to FIG. 3 method 300 includes at block 302, the DBRepClient receives a socket request from the client application. At block 304, DBRepClient parses and converts the request into a valid SQL Query. At block 306, DBRepClient sends query to all the databases connected. It maintains an internal queue. At block 308 and 310, DBRepClient waiting for a response from DBRepServer 104. At block 312, DBRepClient receives the timely response from DBRepServer, which then is returned to the client application through socket communication at block 316. At block 320, after getting all responses, the DBRepClient may sleep for a fixed duration of time after which it may again check if there are any new queries to process.
[0061] At block 314 and 318, if DBRepClient does not receive a response from a DBRepServer it may store query, IP, request time in DBRepClient backlog table.
[0062] At block 324 if DBRepServer is still not working then DBRepClient may continue logging the queries in the table at block 322.
[0063] At block 326, if DBRepServer becomes available to process requests then DBRepClient 102 may send the queries stored in the table. These requests may be removed only when DBRepServer 104 may respond back with an acknowledgement.
[0064] FIG. 4 is an exemplary flow chart illustrating the steps for the processing of transactions at server by DBRepServer, thereby persisting the data in the database, in accordance with an embodiment of the present disclosure.
[0065] Referring to FIG. 4, the method 400 includes at block 402, DBRepServer first checks if it has any backlog. At block 404, if there is no backlog, DBRepServer may declare DB server 104 as healthy which means that DB server 104 may be ready to process new requests. At block 406, DBRepServer may check the status and at block 408, send response to DBRepClient
[0066] At block 414, after all the requests have been processed and sent back to DBRepClient, the DBRepServer may sleep for a fixed interval of time. At block 416, if DBRepServer finds out that there is backlog then it may declare DB Server 104 as unhealthy so that clients cannot send new requests.
[0067] At block 418, the DBRepServer may pick queries from DBRepServer PendingLog table and may start executing queries one by one. As soon as a query is executed DBRepServer may send acknowledgement to DBRepClient.
[0068] At block 420, if DBRepServer is not able to execute query in database then at block 422, it may start logging in DBRepServerPendingLog table.
[0069] FIG. 5 is an exemplary flow chart illustrating the steps for estimating count, in accordance with an embodiment of the present disclosure.
[0070] Referring to FIG. 5, finding out the total number of records is one of the basic and most used functions in a database. In the case of replicated systems, it becomes harder as database systems could be out of sync with each other and are not sure which database provides the count of the latest data.
[0071] At block 502, the client application sends a count request to DBRepClient. At block 504, the DBRepClient parses the query and sends it to all the databases connected. At block 506, DBRepServer receives a count request from DBRepClient and it may return the table count along with the last updated time for the table. At block 508, after receiving the response from DBRepServer, DBRepClient may sort result in descending order based on the last table updated time. It may return the latest record to the client application.
[0072] Client Side
• DBRepClient receives a request to get the count of a table and may forward the request to all the databases currently online.
• DBRepClient receives a response from the server side and it may return the count of the latest data to the client.
[0073] Server Side
• As soon as DBRepServer receives a request from the client side, it may execute the query and return the total number of records along with a time, which is the time of the last operation that happened on that table. This may help clients to know which count is on the latest data.
• DBRepServer may keep meta data containing the last update time on a specific table
[0074] FIG. 6 is an exemplary flow chart for facilitating automatic replication and switching in multi-master/master-slave MySQL database, in accordance with an embodiment of the present disclosure.
[0075] Referring to FIG. 6, the method 600 at block 602, the one or more client machine maintain individual queues for each database servers in the event of link failure, wherein the queues are maintained to store the requests from the client side. The one or more client machines 102 coupled to one or more database servers 104 through a network. The one or more database servers adapted to receive request data from the one or more client machines, a processor operatively coupled to the one or more client machines and the one or more database servers.
[0076] At block 604, determine periodic signal to check the connectivity and health status of the plurality of database servers to maintain load balancing to perform automatic switchover in case of connectivity errors. At block 606, synchronize the faulty database server with healthy database server one as soon as size of backlog for a particular table crosses a predefine threshold limit.
[0077] At block 608, manage the client originated requests by converting the request into native SQL statements and transmits to the one or more database servers. At block 610, request the connected one or more database servers through a set of instructions residing in the one or more client machine and at block 612, execute SQL statements and return the result back to the one or more client machines through the set of instructions residing in the one or more database servers.
[0078] It will be apparent to those skilled in the art that the system 100 of the disclosure may be provided using some or all of the mentioned features and components without departing from the scope of the present disclosure. While various embodiments of the present disclosure have been illustrated and described herein, it will be clear that the disclosure is not limited to these embodiments only. Numerous modifications, changes, variations, substitutions, and equivalents will be apparent to those skilled in the art, without departing from the spirit and scope of the disclosure, as described in the claims.

ADVANTAGES OF THE PRESENT INVENTION
[0079] The present disclosure provides a system which helps in replicating data among different MySQL database servers effectively.
[0080] The present disclosure provides a system that helps in achieving fault tolerance as well as load balancing in the MySQL database.
[0081] The present disclosure provides a system that calculates the number of records accurately in the MySQL database.

[0082] The present disclosure provides a system that enables real-time replication, switchover, and conflict resolution with little overhead over the existing system design, thereby outlining a mechanism to automate failover and failback operations in MySQL databases.
, Claims:1. A system (100) for facilitating automatic replication and switching in multi-master/master-slave MySQL database, the system comprising:
one or more client machines (102) coupled to one or more database servers (104) through a network, the one or more database servers adapted to receive request data from the one or more client machines; and
a processor (106) operatively coupled to the one or more client machines and the one or more database servers, the processor configured to:
maintain at the one or more client machine individual queues for each database server in the event of link failure, wherein the queues are maintained to store the requests from the client side;
determine the periodic signal to check the connectivity and health status of the plurality of database servers to maintain load balancing and to perform automatic switchover in case of connectivity errors;
synchronizes the faulty database server with the healthy database server as soon as the size of the backlog for a particular table crosses a predefined threshold limit;
manage the client-originated requests by converting the request into native structured query language (SQL) statements and transmits to the one or more database servers;
request the connected one or more database servers through a set of instructions residing in the one or more client machine; and
execute SQL statements and return the result to the one or more client machines through the set of instructions residing in the one or more database servers.
2. The system as claimed in claim 1, wherein the set of instructions comprises middleware applications residing in the one or more client machines and the one or more database servers, wherein the middleware applications are DBRepClient and DBRepServer.
3. The system as claimed in claim 1, wherein the table contains the backlog query and normal query that need to be executed.
4. The system as claimed in claim 1, wherein the one or more client machine (102) communicates with the one or more database servers (104) through transmission control protocol (TCP) socket communication.
5. The system as claimed in claim 1, wherein the predefined threshold limit is 500MB.
6. The system as claimed in claim 1, wherein the processor (106) is configured to reduce the load on each database server by reducing the number of connections from 𝒏(𝒏−𝟏)𝟐 to just 𝒏 where 𝒏 is the total number of database servers.
7. The system as claimed in claim 1, wherein the processor (106) performs timestamp-based logging of statement execution occurring at both levels to enable real-time replication, switchover, and conflict resolution
8. The system as claimed in claim 1, wherein each client machine and each database server are responsible for replicating, switching, load balancing and finding the accurate account.
9. The system as claimed in claim 1, wherein the processor (106) is configured to get an accurate count of the table, the processor configured to:
maintain the last updated time for each table;
receive, by the one or more client machines, the count request;
transmit the count request to all the connected database servers;
receive the count request and last updated table time to the one or more client machine by the one or more database servers;
execute the query by the one or more database servers and return the total number of records along with a time to the one or more client machine.
10. A method (600) for facilitating automatic replication and switching in multi master/master slave MySQL database, the method comprising:
maintaining (602), at one or more client machine, individual queues for each database servers in the event of link failure, wherein the queues are maintained to store the requests from the client side, the one or more client machines (102) coupled to one or more database servers (104) through a network, the one or more database servers adapted to receive request data from the one or more client machines, a processor (106) operatively coupled to the one or more client machines and the one or more database servers;
determining (604) periodic signal to check the connectivity and health status of the plurality of database servers to maintain load balancing and to perform automatic switchover in case of connectivity errors;
synchronizing (606) the faulty database server with healthy database server as soon as the size of the backlog for a particular table crosses a predefined threshold limit;
managing (608) the client-originated requests by converting the request into native structured query language (SQL) statements and transmits to the one or more database servers;
requesting (610) the connected one or more database servers through a set of instructions residing in the one or more client machines; and
executing (612) SQL statements and returning the result to the one or more client machines through the set of instructions residing in the one or more database servers.