CN108234602A - The multi-layer data synchronous method of MySQL - Google Patents
The multi-layer data synchronous method of MySQL Download PDFInfo
- Publication number
- CN108234602A CN108234602A CN201711304154.XA CN201711304154A CN108234602A CN 108234602 A CN108234602 A CN 108234602A CN 201711304154 A CN201711304154 A CN 201711304154A CN 108234602 A CN108234602 A CN 108234602A
- Authority
- CN
- China
- Prior art keywords
- data
- node
- mysql
- layer
- master
- 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.)
- Granted
Links
Classifications
-
- H—ELECTRICITY
- H04—ELECTRIC COMMUNICATION TECHNIQUE
- H04L—TRANSMISSION OF DIGITAL INFORMATION, e.g. TELEGRAPHIC COMMUNICATION
- H04L67/00—Network arrangements or protocols for supporting network services or applications
- H04L67/01—Protocols
- H04L67/10—Protocols in which an application is distributed across nodes in the network
- H04L67/1095—Replication or mirroring of data, e.g. scheduling or transport for data synchronisation between network nodes
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/27—Replication, distribution or synchronisation of data between databases or within a distributed database system; Distributed database system architectures therefor
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/28—Databases characterised by their database models, e.g. relational or object models
- G06F16/284—Relational databases
-
- H—ELECTRICITY
- H04—ELECTRIC COMMUNICATION TECHNIQUE
- H04L—TRANSMISSION OF DIGITAL INFORMATION, e.g. TELEGRAPHIC COMMUNICATION
- H04L41/00—Arrangements for maintenance, administration or management of data switching networks, e.g. of packet switching networks
- H04L41/06—Management of faults, events, alarms or notifications
- H04L41/0654—Management of faults, events, alarms or notifications using network fault recovery
- H04L41/0663—Performing the actions predefined by failover planning, e.g. switching to standby network elements
Abstract
The invention discloses the multi-layer data synchronous method of MySQL a kind of, are related to data base read-write field.Steps of the method are：Node is written for each data and builds ceph, it is each to create 1 part of data write-in catalogue on the DB1 and DB2 of each data write-in node, the data of DB1 and DB2 are written on the equal carry of catalogue to the ceph of affiliated data write-in node；Under the data write-in catalogue of the DB1 and DB2 of each data write-in node, 1 set MySQL is respectively installed, after the data write-in node reception data of first layer, is write data into DB master, the DB master of the data synchronization node of remainder layer sequentially synchrodata.When the present invention carries out multi-layer data synchronization, synchronisation offset can be avoided to change, ensure data synchronization accuracy.
Description
Technical field
The present invention relates to data base read-write fields, and in particular to a kind of MySQL (Relational DBMS) it is more
Layer data synchronous method.
Background technology
Due to IPTV (Interactive Internet TV) and OTT business, (Over The Top, are provided a user respectively by internet
Kind of application service) fast development, EPG (Electronic Program Guide, Electronic Program Guide) is caused to need extensive
Distributed deployment thus will appear Centroid EPG and fringe node EPG.Centroid EPG needs to receive CMS
The data of (Content Management System, Content Management System) transmission simultaneously write data into database, centromere
The database of point EPG and the database of fringe node EPG need shared data；Because Centroid EPG and fringe node EPG do not have
There is interface transmission data, so needing the database progress data to Centroid EPG with fringe node EPG synchronous.
To cope with the fast development of IPTV and OTT business and enhancing the promptness of user experience, fringe node EPG may reach
To tens or even hundreds of, each fringe node EPG may have more EPG again, and (node EPG is concentrating type arrangement, such as Shenzhen
City is fringe node EPG, and more other EPG for being under the jurisdiction of fringe node EPG are had in Shenzhen).It follows that all edges
When node EPG synchronizes the data of Centroid EPG, data volume is larger, in order to mitigate the data simultaneous pressure of Centroid EPG,
Need between Centroid EPG and fringe node EPG increase by 1 layer of cache node, i.e. Centroid EPG, fringe node EPG,
Cache node is required to carry out data synchronization, hereinafter referred to as three layers of synchronization.
The database of EPG system is essentially MySQL, and data synchronization is carried out by the mechanism of MySQL itself.And MySQL
It is all that two layer datas synchronize to synchronize more, and MySQL generally passes through 1 primary server and 1 standby server carries.
But by MySQL carry out three layers it is synchronous when, can there are following defects：
When the primary server failure of MySQL, need to switch to standby server progress data synchronization, due to MySQL
Primary server and standby server respectively using 1 data CACHE DIRECTORY, therefore primary server failure can occur, spare clothes
Business device can not learn synchronous progress, and then synchronisation offset is caused to change by synchronous directory.If the it follows that second layer
The primary server of (cache node) breaks down, it is likely that leads to the data synchronization errors of third layer (fringe node EPG)
(i.e. subordinate's synchronous abnormality), so that mistake occurs for EPG.
Invention content
For defect in the prior art, present invention solves the technical problem that being：How in MySQL progress multilayer numbers
During according to synchronizing, synchronisation offset being avoided to change, ensureing data synchronization accuracy.
To achieve the above objectives, the present invention provides the multi-layer data synchronous method of MySQL, and this method is by undermost data
Synchronization node is defined as reading back end, and the data synchronization node of the remainder layer other than lowest level is defined as data write-in
Node；Each primary data synchronization server for reading back end is known as DB1, by each spare number for reading back end
It is known as DB2 according to sync server, each data synchronization server currently in use for reading back end is known as DB master；The party
Method includes the following steps：
S1：Node is written for each data and builds ceph, goes to S2；
S2：It is each to create 1 part of data write-in catalogue on the DB1 and DB2 of each data write-in node, by DB1's and DB2
Data are written on the equal carry of catalogue to the ceph of affiliated data write-in node, go to S3；
S3：Under the data write-in catalogue of the DB1 and DB2 of each data write-in node, 1 set of MySQL is respectively installed, is gone to
S4；
S4：After the data write-in node of first layer receives data, write data into DB master, the data of remainder layer synchronize section
DB master's sequentially synchrodata of point；Sequentially the mode of synchrodata is：The DB master of lower data synchronization node, synchronous upper layer data
Data in the DB master of synchronization node；Read the data synchronization server of back end, the DB of synchronous upper layer data write-in node
Data in master；
It is further comprising the steps of after S3：
S3a：The operating status of the DB1 and/or DB2 of each data write-in node are detected, by operating status to be used
DB as DB master, start the MySQL processes of DB master；When the MySQL runnings state of a process for detecting DB master are out of service
When, the operating status of another DB is revised as being used.
Based on the above technical solution, the undermost data synchronization node is the fringe node EPG of third layer；
The data synchronization node of the remainder layer includes the Centroid EPG of first layer and the cache node of the second layer.
Based on the above technical solution, the flow of S4 includes：CMS sends data to Centroid EPG；Centromere
Point EPG is write data into DB master, the data in the DB master of the main synchronous Centroid EPG of DB of cache node, fringe node EPG
Data synchronization server synchronization caching node DB master in data.
Based on the above technical solution, it is further comprising the steps of after S4：Client request obtains fringe node EPG
Data when, the data in data synchronization server are back to client by fringe node EPG.
Based on the above technical solution, the operating status of the DB1 and/or DB2 of node is written in data described in S3a
Detection method is：A set of keepalived is respectively installed on the DB1 and DB2 of data write-in node, passes through keepalived's
The operating status of notify Function detections DB1 and/or DB2.
Based on the above technical solution, the detection method of MySQL processes described in S3a is：On keepalived
Increase monitoring script " check_mysql.sh ", the operation of MySQL processes is detected by script " check_mysql.sh "
State.
Based on the above technical solution, described in S3a using operating status for DB currently in use as DB master after,
It is further comprising the steps of：By VIP and DB main associations.
Based on the above technical solution, the flow of S1 includes：Node is written for each data and selects at least 1 service
Device builds ceph.
Based on the above technical solution, the server for building ceph is 3：1 primary server, 1 it is standby
With server and 1 number of units according to dilatation server.
Compared with prior art, the advantage of the invention is that：
Referring to S1 of the present invention to S4 it is found that mesh is written in the data of the DB1 and DB2 of each data write-in node by the present invention
Record, equal carry to the data are written on the ceph of node, so that the data write-in catalogue of DB1 and DB2 is realized and shared.Herein
On the basis of, when the data write-in node of lower floor synchronizes upper layer data, the needs even if DB1 of upper layer data write-in node breaks down
DB2 is switched to, the data that DB2 also can read DB1 by ceph are written catalogue, and then know the synchronization progress of DB1, are avoided with this
Synchronisation offset changes, and ensures data synchronization accuracy.
Description of the drawings
Fig. 1 is the flow chart of the multi-layer data synchronous method of MySQL in the embodiment of the present invention.
Specific embodiment
The present invention is described in further detail with reference to the accompanying drawings and embodiments.
Undermost data synchronization node is defined as first to read back end, is the edge of third layer in the present embodiment
Node EPG；The data synchronization node of remainder layer other than lowest level is defined as data write-in node, is the in the present embodiment
One layer of Centroid EPG and the cache node of the second layer.By each primary data synchronization server for reading back end
Each preliminary data sync server for reading back end is known as DB2 by referred to as DB1, by each reading back end just
It is known as DB master in the data synchronization server used.
On this basis, it is shown in Figure 1, the multi-layer data synchronous method of the MySQL in the embodiment of the present invention, including with
Lower step：
S1：Node is written for each data and builds ceph (distributed file system), idiographic flow is：It is write in each data
In the computer room of ingress, at least 1 server is selected to build ceph, 3 servers of selection build ceph in the present embodiment, wherein
1 is primary, and 1 is spare, and 1 is made data dilatation use, goes to S2.
S2：It is each to create 1 part of data write-in catalogue (/mnt/data/ on the DB1 and DB2 of each data write-in node
Mysql), the data of DB1 and DB2 are written on the equal carry of catalogue to the ceph of affiliated data write-in node, go to S3.
S3：Under the data write-in catalogue of the DB1 and DB2 of each data write-in node, 1 set of MySQL is respectively installed, is gone to
S4。
S4：After the data write-in node of first layer (i.e. top layer) receives data, write data into DB master, remainder layer
The DB master of data synchronization node sequentially synchrodata；Sequentially the mode of synchrodata is：The DB master of node is written in lower data, together
Walk the data in the DB master of upper layer data write-in node；The data synchronization server of back end is read, synchronous upper layer data is write
Data in the DB master of ingress.
The idiographic flow of S4 is in the present embodiment：CMS sends data to Centroid EPG；Centroid EPG is by data
It is written in DB master, the data in the DB master of the main automatic synchronization Centroid EPG of DB of cache node, the data of fringe node EPG
Data in the DB master of sync server automatic synchronization cache node, go to S5.
S5：Client (specially STB, Set Top Box, digital video switching box) acquisition request fringe node EPG's
During data, the data in data synchronization server are back to client by fringe node EPG, are shown for client.
Referring to S1 to S4 it is found that catalogue is written in the data of the DB1 and DB2 of each data write-in node by the present invention, hang
It is loaded onto on the ceph of data write-in node, so that the data write-in catalogue of DB1 and DB2 is realized and shared.On this basis,
When the data write-in node of lower floor synchronizes upper layer data, even if the DB1 of upper layer data write-in node breaks down, needs switch to
Catalogue is written, and then knows the synchronization progress of DB1 in the data that DB2, DB2 also can read DB1 by ceph, avoids synchronizing partially with this
Shifting amount changes, and ensures data synchronization accuracy.Further, since undermost reading back end is not needed to by other node institutes
It is synchronous, therefore read back end and do not need to build ceph.
It is further comprising the steps of after S3 (during performing S4 to S5)：
S3a：Detect the operating status of the DB1 and/or DB2 of each data write-in node：
The operating status of DB1 or DB2 for be used when, using operating status for DB currently in use as DB master (for the first time
Generally using DB1 as DB master during operation), VIP (Virtual IP Address, virtual ip address) and DB main associations start
The MySQL processes of DB master.When the MySQL runnings state of a process for detecting DB master are out of service, (event occurs for i.e. current DB
Barrier, the operating status of current DB is revised as not using), by another DB (current DB is DB1, then another DB is DB2,
Current DB is DB2, then another DB is DB1) operating status be revised as being used.
The operating status of DB1 or DB2 is not in the MySQL processes of the DB used when in use, not close operating status.
The detection method of the operating status of the DB1 and/or DB2 of data write-in node is in the present embodiment S3a：It is write in data
The a set of keepalived of each installation on the DB1 and DB2 of ingress (it is used for the state of detection service device, the machine if a server is delayed,
Or work is broken down, Keepalived will be detected, and faulty server is rejected from system, while use other
Server generation replaces the work of the server, and server is added to server by Keepalived automatically after server is working properly
In group, these work are all automatically performed, and do not need to artificial interference, need the server for only repairing failure manually done), lead to
Cross the operating status of notify functions (increasing script " notify.sh ") the detection DB1 and/or DB2 of keepalived.
The detection method of MySQL processes is in the present embodiment S3a：Increase monitoring script on keepalived
" check_mysql.sh " detects MySQL runnings state of a process by script " check_mysql.sh ".
Further, the present invention is not limited to the above-described embodiments, for those skilled in the art,
Without departing from the principles of the invention, several improvements and modifications can also be made, these improvements and modifications are also considered as the present invention
Protection domain within.The content not being described in detail in this specification belongs to existing skill well known to professional and technical personnel in the field
Art.
Claims (9)
1. the multi-layer data synchronous method of MySQL a kind of, undermost data synchronization node is defined as reading data by this method
The data synchronization node of remainder layer other than lowest level is defined as data write-in node by node；Data section will each be read
The primary data synchronization server of point is known as DB1, and each preliminary data sync server for reading back end is known as DB2,
Each data synchronization server currently in use for reading back end is known as DB master；It is characterized in that, this method include with
Lower step：
S1：Node is written for each data and builds ceph, goes to S2；
S2：It is each to create 1 part of data write-in catalogue on the DB1 and DB2 of each data write-in node, by the data of DB1 and DB2
It is written on the equal carry of catalogue to the ceph of affiliated data write-in node, goes to S3；
S3：Under the data write-in catalogue of the DB1 and DB2 of each data write-in node, 1 set of MySQL is respectively installed, goes to S4；
S4：After the data write-in node of first layer receives data, write data into DB master, the data synchronization node of remainder layer
DB master's sequentially synchrodata；Sequentially the mode of synchrodata is：The DB master of node, synchronous upper layer data write-in is written in lower data
Data in the DB master of node；The data synchronization server of back end is read, synchronous upper layer data is written in the DB master of node
Data；
It is further comprising the steps of after S3：
S3a：The operating status of the DB1 and/or DB2 of each data write-in node are detected, is DB currently in use by operating status
As DB master, start the MySQL processes of DB master；It, will when the MySQL runnings state of a process for detecting DB master are out of service
The operating status of another DB is revised as being used.
2. the multi-layer data synchronous method of MySQL as described in claim 1, it is characterised in that：The undermost data are same
Walk the fringe node EPG that node is third layer；Centroid EPG of the data synchronization node of the remainder layer including first layer,
And the cache node of the second layer.
3. the multi-layer data synchronous method of MySQL as claimed in claim 2, which is characterized in that the flow of S4 includes：CMS will
Data transmission is to Centroid EPG；Centroid EPG is write data into DB master, the main synchronous Centroids of DB of cache node
Data in the DB master of EPG, the data in the DB master of the data synchronization server synchronization caching node of fringe node EPG.
4. the multi-layer data synchronous method of MySQL as claimed in claim 3, which is characterized in that further include following step after S4
Suddenly：When client request obtains the data of fringe node EPG, fringe node EPG returns to the data in data synchronization server
To client.
5. the multi-layer data synchronous method of MySQL as described in claim 1, it is characterised in that：The write-in section of data described in S3a
The detection method of operating status of the DB1 and/or DB2 of point is：It is respectively installed on the DB1 and DB2 of data write-in node a set of
Keepalived passes through the operating status of notify the Function detections DB1 and/or DB2 of keepalived.
6. the multi-layer data synchronous method of MySQL as claimed in claim 5, which is characterized in that MySQL processes described in S3a
Detection method be：Increase monitoring script " check_mysql.sh " on keepalived, pass through script " check_
Mysql.sh " detects MySQL runnings state of a process.
7. such as the multi-layer data synchronous method of claim 1 to 6 any one of them MySQL, it is characterised in that：Described in S3a
Using operating status for DB currently in use as DB master after, it is further comprising the steps of：By VIP and DB main associations.
8. such as the multi-layer data synchronous method of claim 1 to 6 any one of them MySQL, which is characterized in that the flow packet of S1
It includes：Node, which is written, for each data selects at least 1 server to build ceph.
9. the multi-layer data synchronous method of MySQL as claimed in claim 8, it is characterised in that：The service for building ceph
Device is 3：1 primary server, 1 standby server and 1 number of units are according to dilatation server.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201711304154.XA CN108234602B (en) | 2017-12-11 | 2017-12-11 | MySQL multi-layer data synchronization method |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201711304154.XA CN108234602B (en) | 2017-12-11 | 2017-12-11 | MySQL multi-layer data synchronization method |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN108234602A true CN108234602A (en) | 2018-06-29 |
| CN108234602B CN108234602B (en) | 2021-02-09 |
Family
ID=62654004
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201711304154.XA Active CN108234602B (en) | 2017-12-11 | 2017-12-11 | MySQL multi-layer data synchronization method |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN108234602B (en) |
Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN103491192A (en) * | 2013-09-30 | 2014-01-01 | 北京搜狐新媒体信息技术有限公司 | Namenode switching method and system of distributed system |
| CN103944763A (en) * | 2014-04-25 | 2014-07-23 | 国家电网公司 | Network-assistant management system and method of electrical power system |
| CN106372221A (en) * | 2016-09-07 | 2017-02-01 | 华为技术有限公司 | File synchronization method, equipment and system |
| CN106503158A (en) * | 2016-10-31 | 2017-03-15 | 深圳中兴网信科技有限公司 | Method of data synchronization and device |
| US20170279886A1 (en) * | 2016-03-25 | 2017-09-28 | Lsis Co., Ltd. | Method of Synchronizing User Setting Data Among Multiple Centers |
-
2017
- 2017-12-11 CN CN201711304154.XA patent/CN108234602B/en active Active
Patent Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN103491192A (en) * | 2013-09-30 | 2014-01-01 | 北京搜狐新媒体信息技术有限公司 | Namenode switching method and system of distributed system |
| CN103944763A (en) * | 2014-04-25 | 2014-07-23 | 国家电网公司 | Network-assistant management system and method of electrical power system |
| US20170279886A1 (en) * | 2016-03-25 | 2017-09-28 | Lsis Co., Ltd. | Method of Synchronizing User Setting Data Among Multiple Centers |
| CN106372221A (en) * | 2016-09-07 | 2017-02-01 | 华为技术有限公司 | File synchronization method, equipment and system |
| CN106503158A (en) * | 2016-10-31 | 2017-03-15 | 深圳中兴网信科技有限公司 | Method of data synchronization and device |
Also Published As
| Publication number | Publication date |
|---|---|
| CN108234602B (en) | 2021-02-09 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US10255146B2 (en) | Cluster-wide service agents | |
| CN105550229B (en) | The method and apparatus of distributed memory system data reparation | |
| CN108574720B (en) | Service online method and device | |
| EP2648114A1 (en) | Method, system, token conreoller and memory database for implementing distribute-type main memory database system | |
| US20080115008A1 (en) | Systems and methods for recovering from configuration data mismatches in a clustered environment | |
| CN103777897A (en) | Method and system for copying data between primary and secondary storage locations | |
| CN107016087A (en) | Hierarchical database high-availability system based on sentry's model | |
| JP2008035456A (en) | Operation management method, operation management program, operation management system, and operation management device | |
| CN102194009A (en) | Database hosting method and database hosting platform system | |
| CN105635311A (en) | Method for synchronizing resource pool information in cloud management platform | |
| US7596083B2 (en) | Network element recovery process | |
| CN101079896A (en) | A multi-availability mechanism coexistence framework of concurrent storage system | |
| CN107085546A (en) | Data managing method and device based on failure field technique | |
| US20170097941A1 (en) | Highly available network filer super cluster | |
| CN106911802A (en) | The dispositions method and device of the management platform of distributed block storage system | |
| CN101572724A (en) | Software version management system | |
| CN104023246A (en) | Private video data cloud-storage system and method | |
| CN106503158A (en) | Method of data synchronization and device | |
| CN106775472A (en) | A kind of controller cache method, storage control and storage system with multiple controllers | |
| CN108989476A (en) | A kind of address distribution method and device | |
| CN103945016A (en) | DHCP server active-standby redundancy method and system | |
| CN107357522A (en) | Data processing method and device | |
| CN111240901B (en) | Node dynamic expansion system, method and equipment of distributed block storage system | |
| CN110351139B (en) | Multi-machine main-standby implementation method of power quality management system | |
| CN108234602A (en) | The multi-layer data synchronous method of MySQL |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |