CN108234602B - MySQL multi-layer data synchronization method - Google Patents
MySQL multi-layer data synchronization method Download PDFInfo
- Publication number
- CN108234602B CN108234602B CN201711304154.XA CN201711304154A CN108234602B CN 108234602 B CN108234602 B CN 108234602B CN 201711304154 A CN201711304154 A CN 201711304154A CN 108234602 B CN108234602 B CN 108234602B
- Authority
- CN
- China
- Prior art keywords
- data
- node
- mysql
- master
- layer
- 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.)
- Active
Links
Images
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
Landscapes
- Engineering & Computer Science (AREA)
- Databases & Information Systems (AREA)
- Theoretical Computer Science (AREA)
- Data Mining & Analysis (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Computer Networks & Wireless Communication (AREA)
- Signal Processing (AREA)
- Computing Systems (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention discloses a multi-layer data synchronization method of MySQL, and relates to the field of database reading and writing. The method comprises the following steps: establishing ceph for each data writing node, respectively establishing 1 part of data writing directories on DB1 and DB2 of each data writing node, and mounting the data writing directories of DB1 and DB2 to the ceph of the data writing node; under the data writing directories of the DB1 and the DB2 of each data writing node, 1 set of MySQL is installed, after the data writing node of the first layer receives the data, the data is written into a DB master, and the DB masters of the data synchronization nodes of the other layers sequentially synchronize the data. When the method is used for synchronizing the multilayer data, the change of the synchronous offset can be avoided, and the data synchronization precision is ensured.
Description
Technical Field
The invention relates to the field of database reading and writing, in particular to a multi-layer data synchronization method of MySQL (relational database management system).
Background
Due to The rapid development of IPTV (interactive network television) and OTT service (Over The Top, providing various application services to users via The internet), large-scale distributed deployment of EPG (Electronic Program Guide) is required, so that central node EPG and edge node EPG appear. The central node EPG needs to receive data sent by a CMS (Content Management System) and write the data into a database, and the database of the central node EPG and the database of the edge node EPG need to share the data; since the central node EPG and the edge node EPG have no interface to transmit data, it is necessary to synchronize data of databases of the central node EPG and the edge node EPG.
In order to deal with the rapid development of the IPTV and OTT services and enhance the timeliness of user experience, the edge node EPG may reach dozens or even hundreds, and each edge node EPG may have multiple EPGs (the node EPG is arranged in a cluster, for example, shenzhen city is the edge node EPG, and multiple other EPGs belonging to the edge node EPG are available in the shenzhen city). Therefore, when the central node EPG data is synchronized by all edge node EPGs, the data volume is large, and in order to reduce the data synchronization pressure of the central node EPG, 1 layer of cache nodes need to be added between the central node EPG and the edge node EPG, that is, the central node EPG, the edge node EPG and the cache nodes all need to perform data synchronization, which is hereinafter referred to as three-layer synchronization.
The database used by the EPG system is basically MySQL, and data synchronization is carried out through the self mechanism of the MySQL. However, most of MySQL synchronization is two-layer data synchronization, and MySQL is generally carried by 1 main server and 1 standby server.
However, when three-layer synchronization is performed through MySQL, the following defects may exist:
when the primary server of MySQL fails, the primary server and the standby server of MySQL need to be switched to the standby server for data synchronization, and because the primary server and the standby server of MySQL respectively adopt 1 data cache directory, the primary server fails, and the standby server cannot know the synchronization progress through the synchronization directory, so that the synchronization offset changes. As can be seen from this, if the active server of the second layer (cache node) fails, a data synchronization error (i.e., a lower synchronization error) of the third layer (edge node EPG) may occur, and further, an EPG may be erroneous.
Disclosure of Invention
Aiming at the defects in the prior art, the invention solves the technical problems that: how to avoid the change of the synchronous offset when the MySQL carries out multi-layer data synchronization, and ensure the data synchronization precision.
In order to achieve the aim, the invention provides a MySQL multi-layer data synchronization method, which defines the data synchronization node of the lowest layer as a data reading node and defines the data synchronization nodes of other layers except the lowest layer as data writing nodes; the primary data synchronization server of each read data node is called DB1, the standby data synchronization server of each read data node is called DB2, and the data synchronization server in use of each read data node is called DB master; the method comprises the following steps:
s1: building ceph for each data writing node, and turning to S2;
s2: creating 1 part of data writing directories on DB1 and DB2 of each data writing node, mounting the data writing directories of DB1 and DB2 to ceph of the data writing node, and turning to S3;
s3: under the data writing directories of DB1 and DB2 of each data writing node, 1 set of MySQL is respectively installed, and the operation goes to S4;
s4: after the data writing node of the first layer receives the data, the data is written into a DB master, and the DB masters of the data synchronization nodes of the other layers synchronize the data in sequence; the way of synchronizing data in sequence is: the DB master of the lower data synchronization node synchronizes the data in the DB master of the upper data synchronization node; reading a data synchronization server of the data node, and synchronizing data written in a DB master of the data node by upper data;
after S3, the method further includes the following steps:
s3 a: detecting the running state of DB1 and/or DB2 of each data writing node, taking the DB with the running state being used as a DB master, and starting a MySQL process of the DB master; when the operation state of the MySQL process of the DB master is detected to be the stop operation, the operation state of the other DB is modified to be in use.
On the basis of the technical scheme, the data synchronization node at the lowest layer is an edge node EPG at the third layer; the data synchronization nodes of the other layers comprise a central node EPG of a first layer and a cache node of a second layer.
On the basis of the above technical solution, the process of S4 includes: the CMS transmits the data to the central node EPG; the central node EPG writes data into the DB master, the DB master of the cache node synchronizes the data in the DB master of the central node EPG, and the data synchronization server of the edge node EPG synchronizes the data in the DB master of the cache node.
On the basis of the above technical solution, the method further comprises the following steps after S4: when the client requests to acquire the data of the edge node EPG, the edge node EPG returns the data in the data synchronization server to the client.
On the basis of the above technical solution, the method for detecting the operating state of the DB1 and/or DB2 of the data write node in S3a includes: and installing a set of keepalive on the DB1 and the DB2 of the data writing node respectively, and detecting the operation state of the DB1 and/or the DB2 through the notify function of the keepalive.
On the basis of the technical scheme, the MySQL process detection method in S3a comprises the following steps: and adding a monitoring script 'check _ mysql.sh' to the keepalived, and detecting the running state of the MySQL process through the script 'check _ mysql.sh'.
On the basis of the above technical solution, after the DB in which the operation state is in use is regarded as the DB master in S3a, the method further includes the following steps: associate the VIP with the DB master.
On the basis of the above technical solution, the process of S1 includes: and selecting at least 1 server for each data writing node to build ceph.
On the basis of the technical scheme, the number of the servers building the ceph is 3: 1 main server, 1 standby server and 1 data capacity expansion server.
Compared with the prior art, the invention has the advantages that:
referring to the present invention S1 to S4, the present invention mounts the data write directories of DB1 and DB2 of each data write node to ceph of the data write node, so that the data write directories of DB1 and DB2 are shared. On the basis, when the data writing node of the lower layer synchronizes the data of the upper layer, even if the DB1 of the data writing node of the upper layer fails and needs to be switched to the DB2, the DB2 can read the data writing directory of the DB1 through ceph, and further know the synchronization progress of the DB1, so that the change of the synchronization offset is avoided, and the data synchronization precision is ensured.
Drawings
Fig. 1 is a flowchart of a MySQL multi-layer data synchronization method in an embodiment of the present invention.
Detailed Description
The present invention will be described in further detail with reference to the accompanying drawings and examples.
Firstly, defining the data synchronization node at the lowest layer as a read data node, namely an edge node EPG at the third layer in the embodiment; the data synchronization nodes of the other layers except the lowermost layer are defined as data writing nodes, which are the central node EPG of the first layer and the cache node of the second layer in this embodiment. The primary data sync server for each read data node is referred to as DB1, the backup data sync server for each read data node is referred to as DB2, and the active data sync server for each read data node is referred to as DB master.
On this basis, referring to fig. 1, the multi-layer data synchronization method of MySQL in the embodiment of the present invention includes the following steps:
s1: building ceph (distributed file system) for each data writing node, wherein the specific flow is as follows: in the machine room of each data write-in node, at least 1 server is selected to build a ceph, and in this embodiment, 3 servers are selected to build a ceph, where 1 is primary, 1 is standby, and 1 is used for data capacity expansion, and the process goes to S2.
S2: on DB1 and DB2 of each data write node, 1 data write directory (/ mnt/data/mysql) is created, and the data write directories of DB1 and DB2 are mounted on ceph of the data write node, and the process goes to S3.
S3: under the data writing directories of DB1 and DB2 of each data writing node, 1 set of MySQL is installed, and the flow goes to S4.
S4: after receiving the data, the data writing node of the first layer (namely the uppermost layer) writes the data into the DB master, and the DB masters of the data synchronization nodes of the other layers synchronize the data in sequence; the way of synchronizing data in sequence is: the DB master of the lower data writing node synchronizes the data in the DB master of the upper data writing node; and the data synchronization server of the data reading node synchronizes the data written in the DB master of the data writing node by the upper layer.
The specific process of S4 in this embodiment is: the CMS transmits the data to the central node EPG; the center node EPG writes data in the DB master, the DB master of the cache node automatically synchronizes data in the DB master of the center node EPG, and the data synchronization server of the edge node EPG automatically synchronizes data in the DB master of the cache node, go to S5.
S5: when a client (specifically, STB, Set Top Box, digital video converter Box) requests to acquire data of the edge node EPG, the edge node EPG returns the data in the data synchronization server to the client for the client to display.
Referring to S1 to S4, the data write directories of DB1 and DB2 of each data write node are mounted on ceph of the data write node, so that the data write directories of DB1 and DB2 are shared. On the basis, when the data writing node of the lower layer synchronizes the data of the upper layer, even if the DB1 of the data writing node of the upper layer fails and needs to be switched to the DB2, the DB2 can read the data writing directory of the DB1 through ceph, and further know the synchronization progress of the DB1, so that the change of the synchronization offset is avoided, and the data synchronization precision is ensured. In addition, since the read data node at the lowest layer does not need to be synchronized by other nodes, the read data node does not need to build ceph.
After S3 (i.e., in the process of performing S4 to S5), the following steps are further included:
s3 a: detecting the running state of DB1 and/or DB2 of each data writing node:
when the DB1 or DB2 is in use, the DB in use is set as the DB master (DB 1 is generally set as the DB master in the first run), and VIP (Virtual IP Address) is associated with the DB master to start the MySQL procedure of the DB master. When the operation state of the MySQL process of the DB master is detected to be out of operation (namely the current DB is in failure and the operation state of the current DB is modified to be unused), the operation state of the other DB (the current DB is DB1, the other DB is DB2, the current DB is DB2 and the other DB is DB1) is modified to be in use.
When the running state of the DB1 or the DB2 is not in use, the MySQL process of the DB in the running state of not in use is closed.
The method for detecting the operation status of the DB1 and/or the DB2 of the data writing node in S3a of this embodiment is as follows: the method comprises the steps that a set of keepalives (used for detecting the state of a server, if one server is down or works in a fault, the keepalives are detected, the faulty server is removed from a system, meanwhile, other servers are used for replacing the work of the server, the keepalives automatically add the server into a server group after the server works normally, all the works are automatically completed without manual intervention, and only the faulty server needs to be repaired manually), and the running state of DB1 and/or DB2 is detected through the notify function (adding script' notify.
The detection method of the MySQL procedure in S3a of this embodiment is as follows: and adding a monitoring script 'check _ mysql.sh' to the keepalived, and detecting the running state of the MySQL process through the script 'check _ mysql.sh'.
Further, the present invention is not limited to the above-mentioned embodiments, and it will be apparent to those skilled in the art that various modifications and improvements can be made without departing from the principle of the present invention, and these modifications and improvements are also considered to be within the scope of the present invention. Those not described in detail in this specification are within the skill of the art.
Claims (9)
1. A MySQL multi-layer data synchronization method defines the data synchronization node of the lowest layer as a read data node, and defines the data synchronization nodes of other layers except the lowest layer as data write nodes; the primary data synchronization server of each write data node is called DB1, the standby data synchronization server of each write data node is called DB2, and the data synchronization server in use of each read data node is called DB master; the method is characterized by comprising the following steps:
s1: building a distributed file system ceph for each data writing node, and turning to S2;
s2: 1 data writing directory is respectively established on DB1 and DB2 of each data writing node, the data writing directories of DB1 and DB2 are mounted on ceph of the data writing node, sharing of the data writing directories of DB1 and DB2 is achieved, and the process is turned to S3;
s3: under the data writing directories of DB1 and DB2 of each data writing node, 1 set of MySQL is respectively installed, and the operation goes to S4;
s4: after the data writing node of the first layer receives the data, the data is written into a DB master, and the DB masters of the data synchronization nodes of the other layers synchronize the data in sequence; the way of synchronizing data in sequence is: the DB master of the lower data writing node synchronizes the data in the DB master of the upper data writing node; reading a data synchronization server of the data node, and synchronizing data written in a DB master of the data node by upper data;
after S3, the method further includes the following steps:
s3 a: detecting the running state of DB1 and/or DB2 of each data writing node, taking the DB with the running state being used as a DB master, and starting a MySQL process of the DB master; when the operation state of the MySQL process of the DB master is detected to be the stop operation, the operation state of the other DB is modified to be in use.
2. The MySQL, multi-tier data synchronization method of claim 1, wherein: the data synchronization node of the lowest layer is an edge node EPG of a third layer; the data synchronization nodes of the other layers comprise a central node EPG of a first layer and a cache node of a second layer.
3. The MySQL, multi-tier data synchronization method of claim 2, wherein the flow of S4 comprises: the content management system CMS transmits the data to the central node EPG; the central node EPG writes data into the DB master, the DB master of the cache node synchronizes the data in the DB master of the central node EPG, and the data synchronization server of the edge node EPG synchronizes the data in the DB master of the cache node.
4. The MySQL multi-layer data synchronization method of claim 3, further comprising the following steps after S4: when the client requests to acquire the data of the edge node EPG, the edge node EPG returns the data in the data synchronization server to the client.
5. The MySQL, multi-tier data synchronization method of claim 1, wherein: the detection method of the running state of the DB1 and/or DB2 of the data writing node in the S3a comprises the following steps: and installing a set of keepalive on the DB1 and the DB2 of the data writing node respectively, and detecting the operation state of the DB1 and/or the DB2 through the notify function of the keepalive.
6. The MySQL multi-layer data synchronization method of claim 5, wherein the MySQL procedure detection method in S3a is as follows: and adding a monitoring script 'check _ mysql.sh' to the keepalived, and detecting the running state of the MySQL process through the script 'check _ mysql.sh'.
7. The MySQL multi-layer data synchronization method of any claim 1 to 6, wherein: after the DB in which the operation status is in use is taken as the DB master in S3a, the method further includes the following steps: the virtual IP address VIP is associated with the DB master.
8. The MySQL multi-layer data synchronization method of any claim 1 to 6, wherein the flow of S1 includes: and selecting at least 1 server for each data writing node to build ceph.
9. The MySQL, multi-tier data synchronization method of claim 8, wherein: the servers for building the ceph are 3: 1 main server, 1 standby server and 1 data capacity expansion 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 CN108234602A (en) | 2018-06-29 |
| CN108234602B true 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 (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN103944763A (en) * | 2014-04-25 | 2014-07-23 | 国家电网公司 | Network-assistant management system and method of electrical power system |
Family Cites Families (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN103491192B (en) * | 2013-09-30 | 2016-08-17 | 北京搜狐新媒体信息技术有限公司 | The Namenode changing method of a kind of distributed system and system |
| KR20170111146A (en) * | 2016-03-25 | 2017-10-12 | 엘에스산전 주식회사 | User setting data synchronization method between multiple centers |
| CN106372221B (en) * | 2016-09-07 | 2019-08-20 | 华为技术有限公司 | A kind of method of file synchronization, equipment and system |
| CN106503158B (en) * | 2016-10-31 | 2019-12-10 | 深圳中兴网信科技有限公司 | Data synchronization method and device |
-
2017
- 2017-12-11 CN CN201711304154.XA patent/CN108234602B/en active Active
Patent Citations (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN103944763A (en) * | 2014-04-25 | 2014-07-23 | 国家电网公司 | Network-assistant management system and method of electrical power system |
Also Published As
| Publication number | Publication date |
|---|---|
| CN108234602A (en) | 2018-06-29 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN111581284B (en) | Database high availability method, device, system and storage medium | |
| US8595546B2 (en) | Split brain resistant failover in high availability clusters | |
| EP2648114A1 (en) | Method, system, token conreoller and memory database for implementing distribute-type main memory database system | |
| CN102955845B (en) | Data access method, device and distributed data base system | |
| EP2643771B1 (en) | Real time database system | |
| CN106062717A (en) | Distributed storage replication system and method | |
| CN101136728A (en) | Cluster system and method for backing up a replica in a cluster system | |
| US10320905B2 (en) | Highly available network filer super cluster | |
| CN104506625A (en) | Method for improving reliability of metadata nodes of cloud databases | |
| US11386015B2 (en) | Methods for managing storage systems with dualport solid-state disks accessible by multiple hosts and devices thereof | |
| CN102937955A (en) | Main memory database achieving method based on My structured query language (SQL) double storage engines | |
| CN104023246A (en) | Private video data cloud-storage system and method | |
| CN111240901B (en) | Node dynamic expansion system, method and equipment of distributed block storage system | |
| CN106850724B (en) | Data pushing method and device | |
| CN108234602B (en) | MySQL multi-layer data synchronization method | |
| CN117632374A (en) | Container mirror image reading method, medium, device and computing equipment | |
| CN109218386B (en) | High-availability method for managing Hadoop namespace | |
| CN103327105B (en) | Slave node service automatic recovery method in hadoop system | |
| CN115934251A (en) | Method and system for realizing high availability of cloud native NFS | |
| CN113905054B (en) | RDMA (remote direct memory access) -based Kudu cluster data synchronization method, device and system | |
| CN107819619A (en) | A kind of continual method of access for realizing NFS | |
| CN109257403A (en) | Date storage method and equipment, distributed memory system | |
| CN115361269B (en) | Hot backup method for emergency broadcasting equipment | |
| CN112000885A (en) | List service system, list generation method, server and storage medium | |
| US7644306B2 (en) | Method and system for synchronous operation of an application by a purality of processing units |
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 |