CN105227353A - A kind of MYSQL cluster implementation method and system - Google Patents
A kind of MYSQL cluster implementation method and system Download PDFInfo
- Publication number
- CN105227353A CN105227353A CN201510558643.2A CN201510558643A CN105227353A CN 105227353 A CN105227353 A CN 105227353A CN 201510558643 A CN201510558643 A CN 201510558643A CN 105227353 A CN105227353 A CN 105227353A
- Authority
- CN
- China
- Prior art keywords
- main frame
- node
- standby host
- layer
- sql
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
Landscapes
- Hardware Redundancy (AREA)
Abstract
The invention discloses a kind of MYSQL cluster implementation method and system, method comprises: in Linux server, set up load dispatch layer, and generating virtual node, unified entrance is externally provided; Load dispatch layer obtains the current connection sum of SQL layer each SQL node according to weighting minimum chained scheduling algorithm, and weighting proportion calculates, and up-to-date connection is sent to the SQL node that the current weighting of SQL layer is minimum; Checked the two-shipper state of load dispatch layer by keepalived, when in two-shipper, main frame breaks down, provide dispatch service by standby host.The High Availabitity of fulfillment database each side of the present invention and the load balancing in query script, be also configured with double-pipe type node simultaneously, in case Single Point of Faliure problem.
Description
Technical field
The present invention relates to database technical field, in particular a kind of MYSQL cluster implementation method and system.
Background technology
At present, mysqlcluster originally achieves High Availabitity and load balancing in accumulation layer, but does not realize High Availabitity and load balancing at management node and SQL node.
Therefore, prior art has yet to be improved and developed.
Summary of the invention
In view of above-mentioned the deficiencies in the prior art, the object of the present invention is to provide a kind of MYSQL cluster implementation method and system, be intended to solve mysqlcluster in prior art and do not realize the defect of High Availabitity and load balancing at management node and SQL node.
Technical scheme of the present invention is as follows:
A kind of MYSQL cluster implementation method, wherein, said method comprising the steps of:
A, in Linux server, set up load dispatch layer, and generating virtual node, unified entrance is externally provided;
B, load dispatch layer obtain the current connection sum of SQL layer each SQL node according to weighting minimum chained scheduling algorithm, and weighting proportion calculates, and up-to-date connection is sent to the SQL node that the current weighting of SQL layer is minimum;
C, the two-shipper state checking load dispatch layer by keepalived, when in two-shipper, main frame breaks down, provide dispatch service by standby host.
Described MYSQL cluster implementation method, wherein, described step C specifically comprises:
C1, by keepalived, the machine of two load dispatch layers is divided into main frame and standby host;
C2, the priority in host-confg file is adjusted to the priority being greater than standby host;
C3, detect main frame and standby host by keepalived and whether break down;
If C4 main frame breaks down, then keepalived automatically switches to standby host and provides dispatch service.
Described MYSQL cluster implementation method, wherein, described step C4 also comprises: judge whether standby host breaks down, if main frame discharges fault when standby host breaks down, main frame provides dispatch service automatically.
Described MYSQL cluster implementation method, wherein, keepalived timing scan two load dispatch machines in described step C1, if the master scheduling node of acquiescence is obstructed, then provide service from scheduling node.
A kind of MYSQL cluster realizes system, wherein, comprising:
Node generation module, for setting up load dispatch layer in Linux server, and generating virtual node, unified entrance is externally provided;
Calculate and sending module, obtain the current connection sum of SQL layer each SQL node according to weighting minimum chained scheduling algorithm for load dispatch layer, and weighting proportion calculates, and up-to-date connection is sent to the SQL node that the current weighting of SQL layer is minimum;
Control module, for the two-shipper state checking load dispatch layer by keepalived, when in two-shipper, main frame breaks down, provides dispatch service by standby host.
Described MYSQL cluster realizes system, and wherein, described control module specifically comprises:
Division unit, for being divided into main frame and standby host by keepalived by the machine of two load dispatch layers;
Configuration adjustment unit, for being adjusted to the priority being greater than standby host by the priority in host-confg file;
Whether the first judging unit, for breaking down by keepalived detection main frame and standby host;
Second judging unit, if break down for main frame, then keepalived automatically switches to standby host and provides dispatch service.
Described MYSQL cluster realizes system, and wherein, described step second judging unit is also for judging whether standby host breaks down, if main frame discharges fault when standby host breaks down, main frame provides dispatch service automatically.
Described MYSQL cluster realizes system, and wherein, keepalived timing scan two load dispatch machines in described first judging unit, if the master scheduling node of acquiescence is obstructed, then provide service from scheduling node.
A kind of MYSQL cluster implementation method of the present invention and system, method comprises: in Linux server, set up load dispatch layer, and generating virtual node, unified entrance is externally provided; Load dispatch layer obtains the current connection sum of SQL layer each SQL node according to weighting minimum chained scheduling algorithm, and weighting proportion calculates, and up-to-date connection is sent to the SQL node that the current weighting of SQL layer is minimum; Checked the two-shipper state of load dispatch layer by keepalived, when in two-shipper, main frame breaks down, provide dispatch service by standby host.The High Availabitity of fulfillment database each side of the present invention and the load balancing in query script, be also configured with double-pipe type node simultaneously, in case Single Point of Faliure problem.
Accompanying drawing explanation
Fig. 1 is the flow chart of MYSQL cluster implementation method of the present invention preferred embodiment.
Fig. 2 is the structured flowchart that MYSQL cluster of the present invention realizes system preferred embodiment.
Embodiment
The invention provides a kind of MYSQL cluster implementation method and system, for making object of the present invention, technical scheme and effect clearly, clearly, the present invention is described in more detail below.Should be appreciated that specific embodiment described herein only in order to explain the present invention, be not intended to limit the present invention.
Refer to Fig. 1, Fig. 1 is the flow chart of a kind of MYSQL cluster implementation method of the present invention preferred embodiment.As shown in Figure 1, it comprises the following steps:
Step S100, in Linux server, set up load dispatch layer, and generating virtual node, unified entrance is externally provided;
Step S200, load dispatch layer obtain the current connection sum of SQL layer each SQL node according to weighting minimum chained scheduling algorithm, and weighting proportion calculates, and up-to-date connection is sent to the SQL node that the current weighting of SQL layer is minimum;
Step S300, the two-shipper state checking load dispatch layer by keepalived, when in two-shipper, main frame breaks down, provide dispatch service by standby host.
In embodiments of the invention, adopt lvs(and Linux server) set up load dispatch layer, produce a virtual ip address (VIP) simultaneously, make VIP as the unified entrance of SQL layer, when accessing this VIP, request can be distributed to SQL node according to the result of calculation of the minimum chained scheduling algorithm of weighting by load dispatch layer, thus realizes load balancing and the High Availabitity of SQL layer; Adopt the machine of keepalived to load dispatch layer to do two-node cluster hot backup simultaneously, realize the High Availabitity of load dispatch layer.
In step s 200, the minimum chained scheduling of weighting (WeightedLeast-ConnectionScheduling) algorithm is the superset of Least-Connection Scheduling, and the corresponding weights of each server represent its handling property.The default weights of server are 1, and system manager dynamically can arrange the weights of server.The minimum chained scheduling of weighting makes the built vertical linking number of server and its weights proportional when dispatching new connection as far as possible.
Suppose to have one group of server S=S0, S1 ..., Sn-1}, W (Si) represent the weights of server S i,
C (Si) represents the current linking number of server S i.The summation of the current linking number of Servers-all is CSUM=Σ C (Si) (i=0,1 .., n-1).Current new connection request can be sent out server S m, if and only if, and server S m meets the following conditions (C (Sm)/CSUM)/W (Sm)=min{ (C (Si)/CSUM)/W (Si) } (i=0,1,., n-1), wherein W (Si) is non-vanishing.
Because CSUM this take turns search in be constant, so Rule of judgment can be reduced to C (Sm)/W (Sm)=min{C (Si)/W (Si) } (i=0,1., n-1), wherein W (Si) is non-vanishing.
Because the cpu cycle needed for division is more than multiplication, and floating-point division is not allowed in linux kernel, the weights of server are all greater than zero, so Rule of judgment C (Sm)/W (Sm) >C (Si)/W (Si) can be optimized for C (Sm) * W (Si) >C (Si) * W (Sm) further.When the weights of Deterministic service device are zero simultaneously, server is not scheduled.So, as long as algorithm performs following flow process.
for(m=0;m<n;m++){
if(W(Sm)>0){
for(i=m+1;i<n;i++){
if(C(Sm)*W(Si)>C(Si)*W(Sm))
m=i;
}
returnSm;
}
}
returnNULL。
Visible, the High Availabitity of fulfillment database each side of the present invention and the load balancing in query script, be also configured with double-pipe type node simultaneously, in case Single Point of Faliure problem.
Further, described step S300 specifically comprises:
Step S301, by keepalived, the machine of two load dispatch layers is divided into main frame and standby host;
Step S302, the priority in host-confg file is adjusted to the priority being greater than standby host;
Step S303, detect main frame and standby host by keepalived and whether break down;
If step S304 main frame breaks down, then keepalived automatically switches to standby host and provides dispatch service.
In the handoff procedure of main frame and standby host, newly-increased management node is by former SQL node, NDB node is to the dependence of management node, be synchronized to above an other management node, former like this management node is delayed after machine, newly-increased management node still has and keeps this dependence, allows MysqlCluster continue execution and goes down.And be also the effect that there is monitoring mutually between two management nodes, the ruuning situation of current management node mutually, can be seen.
During concrete enforcement, described step S304 also comprises: judge whether standby host breaks down, if main frame discharges fault when standby host breaks down, main frame provides dispatch service automatically.
Further, keepalived timing scan two load dispatch machines in described step S301, if the master scheduling node of acquiescence is obstructed, then provide service from scheduling node.
Based on said method embodiment, the present invention also provides a kind of MYSQL cluster to realize system, and as shown in Figure 2, it comprises:
Node generation module 100, for setting up load dispatch layer in Linux server, and generating virtual node, unified entrance is externally provided;
Calculate and sending module 200, obtain the current connection sum of SQL layer each SQL node according to weighting minimum chained scheduling algorithm for load dispatch layer, and weighting proportion calculates, and up-to-date connection is sent to the SQL node that the current weighting of SQL layer is minimum;
Control module 300, for the two-shipper state checking load dispatch layer by keepalived, when in two-shipper, main frame breaks down, provides dispatch service by standby host.
Further, realize in system at described MYSQL cluster, described control module 300 specifically comprises:
Division unit, for being divided into main frame and standby host by keepalived by the machine of two load dispatch layers;
Configuration adjustment unit, for being adjusted to the priority being greater than standby host by the priority in host-confg file;
Whether the first judging unit, for breaking down by keepalived detection main frame and standby host;
Second judging unit, if break down for main frame, then keepalived automatically switches to standby host and provides dispatch service.
Further, realize in system at described MYSQL cluster, described step second judging unit is also for judging whether standby host breaks down, if main frame discharges fault when standby host breaks down, main frame provides dispatch service automatically.
Further, realize in system at described MYSQL cluster, keepalived timing scan two load dispatch machines in described first judging unit, if the master scheduling node of acquiescence is obstructed, then from scheduling node, provide service.
In sum, a kind of MYSQL cluster implementation method of the present invention and system, method comprises: in Linux server, set up load dispatch layer, and generating virtual node, unified entrance is externally provided; Load dispatch layer obtains the current connection sum of SQL layer each SQL node according to weighting minimum chained scheduling algorithm, and weighting proportion calculates, and up-to-date connection is sent to the SQL node that the current weighting of SQL layer is minimum; Checked the two-shipper state of load dispatch layer by keepalived, when in two-shipper, main frame breaks down, provide dispatch service by standby host.The High Availabitity of fulfillment database each side of the present invention and the load balancing in query script, be also configured with double-pipe type node simultaneously, in case Single Point of Faliure problem.
Should be understood that, application of the present invention is not limited to above-mentioned citing, for those of ordinary skills, can be improved according to the above description or convert, and all these improve and convert the protection range that all should belong to claims of the present invention.
Claims (8)
1. a MYSQL cluster implementation method, is characterized in that, said method comprising the steps of:
A, in Linux server, set up load dispatch layer, and generating virtual node, unified entrance is externally provided;
B, load dispatch layer obtain the current connection sum of SQL layer each SQL node according to weighting minimum chained scheduling algorithm, and weighting proportion calculates, and up-to-date connection is sent to the SQL node that the current weighting of SQL layer is minimum;
C, the two-shipper state checking load dispatch layer by keepalived, when in two-shipper, main frame breaks down, provide dispatch service by standby host.
2. MYSQL cluster implementation method according to claim 1, it is characterized in that, described step C specifically comprises:
C1, by keepalived, the machine of two load dispatch layers is divided into main frame and standby host;
C2, the priority in host-confg file is adjusted to the priority being greater than standby host;
C3, detect main frame and standby host by keepalived and whether break down;
If C4 main frame breaks down, then keepalived automatically switches to standby host and provides dispatch service.
3. MYSQL cluster implementation method according to claim 2, it is characterized in that, described step C4 also comprises: judge whether standby host breaks down, if main frame discharges fault when standby host breaks down, main frame provides dispatch service automatically.
4. MYSQL cluster implementation method according to claim 2, is characterized in that, keepalived timing scan two load dispatch machines in described step C1, if the master scheduling node of acquiescence is obstructed, then from scheduling node, provides service.
5. MYSQL cluster realizes a system, it is characterized in that, comprising:
Node generation module, for setting up load dispatch layer in Linux server, and generating virtual node, unified entrance is externally provided;
Calculate and sending module, obtain the current connection sum of SQL layer each SQL node according to weighting minimum chained scheduling algorithm for load dispatch layer, and weighting proportion calculates, and up-to-date connection is sent to the SQL node that the current weighting of SQL layer is minimum;
Control module, for the two-shipper state checking load dispatch layer by keepalived, when in two-shipper, main frame breaks down, provides dispatch service by standby host.
6. MYSQL cluster realizes system according to claim 5, it is characterized in that, described control module specifically comprises:
Division unit, for being divided into main frame and standby host by keepalived by the machine of two load dispatch layers;
Configuration adjustment unit, for being adjusted to the priority being greater than standby host by the priority in host-confg file;
Whether the first judging unit, for breaking down by keepalived detection main frame and standby host;
Second judging unit, if break down for main frame, then keepalived automatically switches to standby host and provides dispatch service.
7. MYSQL cluster realizes system according to claim 6, it is characterized in that, described step second judging unit is also for judging whether standby host breaks down, if main frame discharges fault when standby host breaks down, main frame provides dispatch service automatically.
8. MYSQL cluster realizes system according to claim 6, it is characterized in that, keepalived timing scan two load dispatch machines in described step the first judging unit, if the master scheduling node of acquiescence is obstructed, then from scheduling node, provides service.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201510558643.2A CN105227353A (en) | 2015-09-06 | 2015-09-06 | A kind of MYSQL cluster implementation method and system |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201510558643.2A CN105227353A (en) | 2015-09-06 | 2015-09-06 | A kind of MYSQL cluster implementation method and system |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN105227353A true CN105227353A (en) | 2016-01-06 |
Family
ID=54996064
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201510558643.2A Pending CN105227353A (en) | 2015-09-06 | 2015-09-06 | A kind of MYSQL cluster implementation method and system |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN105227353A (en) |
Cited By (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN108241701A (en) * | 2016-12-26 | 2018-07-03 | 航天信息股份有限公司 | A kind of method for improving MySQL high availability |
| CN110019481A (en) * | 2017-12-28 | 2019-07-16 | 中国移动通信集团河北有限公司 | Memory database access method, device, equipment and medium |
| CN113190607A (en) * | 2021-05-21 | 2021-07-30 | 上海申铁信息工程有限公司 | HTTP request-based database load balancing method, device and medium |
Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN102254031A (en) * | 2011-08-03 | 2011-11-23 | 无锡浙潮科技有限公司 | Batch processing request-based Microsoft SQL server database cluster |
| CN102663017A (en) * | 2012-03-21 | 2012-09-12 | 互动在线（北京）科技有限公司 | Implementation system and implementation method for enhancing availability of MySQL database |
| CN103202002A (en) * | 2010-09-08 | 2013-07-10 | 思杰系统有限公司 | Systems and methods for self-load balancing access gateways |
| CN104331508A (en) * | 2014-11-21 | 2015-02-04 | 浪潮(北京)电子信息产业有限公司 | Management system and method for realizing load balance of relational database |
| CN104579996A (en) * | 2013-10-17 | 2015-04-29 | 中国电信股份有限公司 | Cluster load balancing method and system |
-
2015
- 2015-09-06 CN CN201510558643.2A patent/CN105227353A/en active Pending
Patent Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN103202002A (en) * | 2010-09-08 | 2013-07-10 | 思杰系统有限公司 | Systems and methods for self-load balancing access gateways |
| CN102254031A (en) * | 2011-08-03 | 2011-11-23 | 无锡浙潮科技有限公司 | Batch processing request-based Microsoft SQL server database cluster |
| CN102663017A (en) * | 2012-03-21 | 2012-09-12 | 互动在线（北京）科技有限公司 | Implementation system and implementation method for enhancing availability of MySQL database |
| CN104579996A (en) * | 2013-10-17 | 2015-04-29 | 中国电信股份有限公司 | Cluster load balancing method and system |
| CN104331508A (en) * | 2014-11-21 | 2015-02-04 | 浪潮(北京)电子信息产业有限公司 | Management system and method for realizing load balance of relational database |
Cited By (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN108241701A (en) * | 2016-12-26 | 2018-07-03 | 航天信息股份有限公司 | A kind of method for improving MySQL high availability |
| CN110019481A (en) * | 2017-12-28 | 2019-07-16 | 中国移动通信集团河北有限公司 | Memory database access method, device, equipment and medium |
| CN113190607A (en) * | 2021-05-21 | 2021-07-30 | 上海申铁信息工程有限公司 | HTTP request-based database load balancing method, device and medium |
| CN113190607B (en) * | 2021-05-21 | 2024-04-16 | 上海申铁信息工程有限公司 | HTTP request-based database load balancing method, device and medium |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN102694868B (en) | A kind of group system realizes and task dynamic allocation method | |
| CN102891868A (en) | Load balancing method and device for distributed system | |
| TW201535266A (en) | Resource adjustment methods and systems for virtual machines | |
| CN110120961B (en) | Distributed service cluster and route synchronization method thereof | |
| US10924538B2 (en) | Systems and methods of monitoring software application processes | |
| CN112948063B (en) | Cloud platform creation method and device, cloud platform and cloud platform implementation system | |
| CN104158707A (en) | Method and device of detecting and processing brain split in cluster | |
| CN105227353A (en) | A kind of MYSQL cluster implementation method and system | |
| CN109697078B (en) | Repairing method of non-high-availability component, big data cluster and container service platform | |
| CN106354566A (en) | Command processing method and server | |
| JP6007988B2 (en) | Standby system apparatus, operational system apparatus, redundant configuration system, and load distribution method | |
| CN102571311B (en) | Master-slave switching communication system and master-slave switching communication method | |
| WO2019019915A1 (en) | Scheduling solution configuration method and apparatus, computer readable storage medium thereof, and computer device | |
| CN106878356B (en) | Scheduling method and computing node | |
| KR20150104251A (en) | Airplane system and control method thereof | |
| CN105511952A (en) | Resource self-migration method and system based on cloud computing platform | |
| CN102868594B (en) | Method and device for message processing | |
| US10402234B2 (en) | Fine-grain synchronization in data-parallel jobs | |
| CN109347743A (en) | A kind of special line communication method, computer readable storage medium and terminal device | |
| CN103973811A (en) | High-availability cluster management method capable of conducting dynamic migration | |
| CN113821232B (en) | Model updating method and device | |
| CN104283943A (en) | Communication optimizing method for cluster server | |
| AU2019239150B2 (en) | Partitioning data in a clustered database environment | |
| CN103259829A (en) | Method for improving backup efficiency of cloud computing dispatching system | |
| CN104850795B (en) | A kind of key management system and the method for changing account information |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| C06 | Publication | ||
| PB01 | Publication | ||
| C10 | Entry into substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| RJ01 | Rejection of invention patent application after publication |
Application publication date: 20160106 |
|
| RJ01 | Rejection of invention patent application after publication |