CN105956207A - Binlog-based configurable mysql database real-time synchronization method - Google Patents
Binlog-based configurable mysql database real-time synchronization method Download PDFInfo
- Publication number
- CN105956207A CN105956207A CN201610525981.0A CN201610525981A CN105956207A CN 105956207 A CN105956207 A CN 105956207A CN 201610525981 A CN201610525981 A CN 201610525981A CN 105956207 A CN105956207 A CN 105956207A
- Authority
- CN
- China
- Prior art keywords
- server
- data
- binlog
- mysql
- file
- 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
Classifications
-
- G—PHYSICS
- G06—COMPUTING OR CALCULATING; 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
- G06F16/275—Synchronous replication
Landscapes
- Engineering & Computer Science (AREA)
- Databases & Information Systems (AREA)
- Theoretical Computer Science (AREA)
- Computing Systems (AREA)
- Data Mining & Analysis (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention discloses a binlog-based configurable mysql database real-time synchronization method. The method is triggered on the basis of a mysql action event, and interactive action of a user among a database influencing configuration operation of a system, a configuration file and an underlying system on a page can be all executed by a backup server in one mysql action event, namely that a certain action of the user done at a main server can be completely copied to the backup server in real time. Consistent configuration of two servers in hot standby is guaranteed, service can be normally provided after switching, and the reliability and stability of the system are improved.
Description
Technical field
The present invention relates to data syn-chronization field, particularly relate to a kind of configurable mysql based on binlog
Data base's real-time synchronization method.
Background technology
Hot standby is the disaster tolerance mode of a kind of server, is a kind of backup form of server, when master server goes out
Existing problem, backup server is enabled by system automatically, completes the switching of service.So between two-server
Configuration should synchronize, although mysql provides data base's copy function, it is possible to achieve two data bases with
Step, the software of the most a lot of file synchronization can be with synchronous documents.But this mode data base and file
Synchronization be separate, it is possible to cause data base and configuration file inconsistent, and at some and the end
Layer system has in mutual application, and those interactive actions cannot be synchronized, and causes two-server inconsistent,
After hot standby switching, some function cannot be properly functioning.
Summary of the invention
Present invention aims to prior art problem in data synchronization process, it is provided that a kind of based on
Configurable mysql data base's real-time synchronization method of binlog.
The present invention is on the basis of the binlog of mysql, is operated by the sql of mysql and triggers synchronization
Action, controls the table synchronized and the data needing special handling flexibly simultaneously with configuration file.The method bag
Include following steps:
Step 1: server A to be synchronized and server B are all configured binlog, and is each configured to main frame；
Using logfile file to store the filename of current binlog, logpos file stores currently
The position of binlog；In configuration file, configuration needs the table synchronized and needs the table of special handling；
Step 2: initialize between server A and server B, makes two main frames be in identical initial shape
State；
(2.1) server A will need the table, configuration file and the packing of logfile and logpos file that synchronize,
It is sent to server B by rsync；
(2.2) server B covers content and the configuration of current table by the file cladding that server A transmits
The content of file, performs initializtion script；
Step 3: server B is synchronized by the binlog of server A；
(3.1) logfile Yu the logpos file that server B synchronizes to come according to server A determines reading
The position of binlog, reads binlog data of server A, and updates logfile and logpos
File；
(3.2) if the data read need not synchronize, then step 3.1 is jumped to；
(3.3) if the data read are the data needing special handling, the API processing specific function is called, so
After jump to step 3.1；
(3.4) judge that what action type sql statement is, if insert operation, then jump to step
3.5, if update operation, then jump to step 3.6, if delete operation, then jump to
Step 3.7；
(3.5) corresponding mysql data are inserted, then with table name, major key and Major key for parameter call API
Complete the interactive operation after new data inserts；
(3.6) first the mutual behaviour before data update is completed with table name, major key and Major key for parameter call API
Make, then update the data of mysql, recall API and complete the interactive operation after data update；
(3.7) the mutual behaviour before first completing data deletion with table name, major key and Major key for parameter call API
Make, then delete the data of mysql；
(3.8) one data have processed, and jump to step 3.1.
The invention has the beneficial effects as follows: the present invention propose method of data synchronization, due to the method based on
The action event of mysql triggers, the data base that the configuration operation of system is affected by user at the page, configuration literary composition
Part and the interactive action with first floor system can be held at backup server in a mysql action event
OK, say, that certain action that user does at master server can copy to backup server with real-time full.
Ensure hot standby two-server configuration consistency, service can be normally provided after handover, improve the reliable of system
Property and stability.
Accompanying drawing explanation
Fig. 1 is the schematic flow sheet of data syn-chronization.
Detailed description of the invention
With specific embodiment, the present invention is described in further detail below in conjunction with the accompanying drawings.
As it is shown in figure 1, the present invention is on the basis of the binlog of mysql, grasped by the sql of mysql
Make to trigger synchronization action, control the table synchronized and the number needing special handling simultaneously with configuration file flexibly
According to.Concrete implementation step is as follows:
Step 1: server A to be synchronized and server B are all configured binlog, and is each configured to main frame；
Using logfile file to store the filename of current binlog, logpos file stores currently
The position of binlog.In configuration file, configuration needs the table synchronized and needs the table of special handling.
Step 2: initialize between server A and server B, makes two main frames be in identical initial shape
State；
(2.1) server A will need the table, configuration file and the packing of logfile and logpos file that synchronize,
It is sent to server B by rsync；
(2.2) server B covers content and the configuration of current table by the file cladding that server A transmits
The content of file, performs initializtion script；
Step 3: server B is synchronized by the binlog of server A；
(3.1) logfile Yu the logpos file that server B synchronizes to come according to server A determines reading
The position of binlog, reads binlog data of server A, and updates logfile and logpos
File；
(3.2) if the data read need not synchronize, then step 3.1 is jumped to；
(3.3) if the data read are the data needing special handling, such as certain table stores host name,
Gateway and DNS, wherein host name can not be synchronized, and other field is required for synchronizing, and the most just may be used
With call process this table specific API to complete this synchronize, then branch to step 3.1；
(3.4) judge that what action type sql statement is, if insert operation, then jump to step
3.5, if update operation, then jump to step 3.6, if delete operation, then jump to
Step 3.7；
(3.5) corresponding mysql data are inserted, then with table name, major key and Major key for parameter call API
Complete the interactive operation after new data inserts.Such as Add User operation, and underlying operating system is also required to create
This user, and the when that the password of user's the exterior and the interior storage being encryption.It is accomplished by first calling API clear crytpographic key,
Then corresponding user is created according to the password decrypted；
(3.6) first the mutual behaviour before data update is completed with table name, major key and Major key for parameter call API
Make, then update the data of mysql, recall API and complete the interactive operation after data update.It is such as
System has routing configuration function, needs first to be deleted by original routing rule after amendment routing configuration, then
Add the routing rule after amendment.So needing to call deletion route respectively before and after mysql more new data
The API and the API of interpolation routing rule of rule；
(3.7) the mutual behaviour before first completing data deletion with table name, major key and Major key for parameter call API
Make, then delete the data of mysql.Such as delete user operation, first the user of first floor system is deleted again
Delete mysql data；
(3.8) one data have processed, and jump to step 3.1.
Claims (4)
1. configurable mysql data base's real-time synchronization method based on binlog, it is characterised in that should
Method comprises the following steps:
Step 1: server A to be synchronized and server B are all configured binlog, and is each configured to main frame；
Using logfile file to store the filename of current binlog, logpos file stores currently
The position of binlog；In configuration file, configuration needs the table synchronized and needs the table of special handling；
Step 2: initialize between server A and server B, makes two main frames be in identical initial shape
State；
(2.1) server A will need the table, configuration file and the packing of logfile and logpos file that synchronize,
It is sent to server B by rsync；
(2.2) server B covers content and the configuration of current table by the file cladding that server A transmits
The content of file, performs initializtion script；
Step 3: server B is synchronized by the binlog of server A；
(3.1) logfile Yu the logpos file that server B synchronizes to come according to server A determines reading
The position of binlog, reads binlog data of server A, and updates logfile and logpos
File；
(3.2) if the data read need not synchronize, then step 3.1 is jumped to；
(3.3) if the data read are the data needing special handling, the API processing specific function is called, so
After jump to step 3.1；
(3.4) judge that what action type sql statement is, if insert operation, then jump to step
3.5, if update operation, then jump to step 3.6, if delete operation, then jump to
Step 3.7；
(3.5) corresponding mysql data are inserted, then with table name, major key and Major key for parameter call API
Complete the interactive operation after new data inserts；
(3.6) first the mutual behaviour before data update is completed with table name, major key and Major key for parameter call API
Make, then update the data of mysql, recall API and complete the interactive operation after data update；
(3.7) the mutual behaviour before first completing data deletion with table name, major key and Major key for parameter call API
Make, then delete the data of mysql；
(3.8) one data have processed, and jump to step 3.1.
A kind of configurable mysql data base's real-time synchronization based on binlog the most according to claim 1
Method, it is characterised in that in described step 3.5, for the operation that Adds User, underlying operating system needs
Create this user, and time the password of user's the exterior and the interior storage is encryption, first call API clear crytpographic key, then
Corresponding user is created according to the password decrypted.
A kind of configurable mysql data base's real-time synchronization based on binlog the most according to claim 1
Method, it is characterised in that in described step 3.6, when having routing configuration function in system, in amendment route
First original routing rule is deleted after configuration, then add the routing rule after amendment, so
The API deleting routing rule and the API adding routing rule is called respectively before and after mysql more new data.
A kind of configurable mysql data base's real-time synchronization based on binlog the most according to claim 1
Method, it is characterised in that in described step 3.7, when deleting user operation, first by the user of first floor system
Delete and delete mysql data again.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201610525981.0A CN105956207A (en) | 2016-07-01 | 2016-07-01 | Binlog-based configurable mysql database real-time synchronization method |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201610525981.0A CN105956207A (en) | 2016-07-01 | 2016-07-01 | Binlog-based configurable mysql database real-time synchronization method |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN105956207A true CN105956207A (en) | 2016-09-21 |
Family
ID=56903376
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201610525981.0A Pending CN105956207A (en) | 2016-07-01 | 2016-07-01 | Binlog-based configurable mysql database real-time synchronization method |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN105956207A (en) |
Cited By (6)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN106485465A (en) * | 2016-09-22 | 2017-03-08 | 济南浪潮高新科技投资发展有限公司 | A kind of configurable timing data synchronous method |
| CN107291926A (en) * | 2017-06-29 | 2017-10-24 | 搜易贷（北京）金融信息服务有限公司 | A kind of binlog analysis methods |
| CN108958972A (en) * | 2018-06-25 | 2018-12-07 | 郑州云海信息技术有限公司 | A kind of method of mysql DB Backup |
| CN113741908A (en) * | 2021-08-25 | 2021-12-03 | 杭州安恒信息技术股份有限公司 | High-availability deployment method, device, electronic device and storage medium |
| CN114281606A (en) * | 2021-12-17 | 2022-04-05 | 杭州海康威视系统技术有限公司 | Dual-computer hot standby optimization method, system, equipment and computer readable storage medium |
| CN117785568A (en) * | 2024-02-27 | 2024-03-29 | 广东保伦电子股份有限公司 | Dual-master dual-machine hot standby method and device |
Citations (6)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20030037020A1 (en) * | 2000-02-22 | 2003-02-20 | Lars Novak | Method and apparatus for synchronizing databases of portable devices without change logs |
| US20130318044A1 (en) * | 2010-07-27 | 2013-11-28 | Oracle International Corporation | Mysql database heterogeneous log based replication |
| CN104601548A (en) * | 2014-12-24 | 2015-05-06 | 深圳市大成天下信息技术有限公司 | Generation method and device of encrypted file and computing equipment |
| CN104657382A (en) * | 2013-11-21 | 2015-05-27 | 阿里巴巴集团控股有限公司 | Method and device for detecting consistency of data of MySQL master and slave servers |
| CN104951474A (en) * | 2014-03-31 | 2015-09-30 | 阿里巴巴集团控股有限公司 | Method and device for acquiring MySQL binlog incremental logs |
| CN105512284A (en) * | 2015-12-07 | 2016-04-20 | 上海爱数信息技术股份有限公司 | MySQL data protection method based on affair form data and binlog file |
-
2016
- 2016-07-01 CN CN201610525981.0A patent/CN105956207A/en active Pending
Patent Citations (6)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20030037020A1 (en) * | 2000-02-22 | 2003-02-20 | Lars Novak | Method and apparatus for synchronizing databases of portable devices without change logs |
| US20130318044A1 (en) * | 2010-07-27 | 2013-11-28 | Oracle International Corporation | Mysql database heterogeneous log based replication |
| CN104657382A (en) * | 2013-11-21 | 2015-05-27 | 阿里巴巴集团控股有限公司 | Method and device for detecting consistency of data of MySQL master and slave servers |
| CN104951474A (en) * | 2014-03-31 | 2015-09-30 | 阿里巴巴集团控股有限公司 | Method and device for acquiring MySQL binlog incremental logs |
| CN104601548A (en) * | 2014-12-24 | 2015-05-06 | 深圳市大成天下信息技术有限公司 | Generation method and device of encrypted file and computing equipment |
| CN105512284A (en) * | 2015-12-07 | 2016-04-20 | 上海爱数信息技术股份有限公司 | MySQL data protection method based on affair form data and binlog file |
Cited By (7)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN106485465A (en) * | 2016-09-22 | 2017-03-08 | 济南浪潮高新科技投资发展有限公司 | A kind of configurable timing data synchronous method |
| CN107291926A (en) * | 2017-06-29 | 2017-10-24 | 搜易贷（北京）金融信息服务有限公司 | A kind of binlog analysis methods |
| CN107291926B (en) * | 2017-06-29 | 2020-08-18 | 搜易贷（北京）金融信息服务有限公司 | Binlog analysis method |
| CN108958972A (en) * | 2018-06-25 | 2018-12-07 | 郑州云海信息技术有限公司 | A kind of method of mysql DB Backup |
| CN113741908A (en) * | 2021-08-25 | 2021-12-03 | 杭州安恒信息技术股份有限公司 | High-availability deployment method, device, electronic device and storage medium |
| CN114281606A (en) * | 2021-12-17 | 2022-04-05 | 杭州海康威视系统技术有限公司 | Dual-computer hot standby optimization method, system, equipment and computer readable storage medium |
| CN117785568A (en) * | 2024-02-27 | 2024-03-29 | 广东保伦电子股份有限公司 | Dual-master dual-machine hot standby method and device |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN105956207A (en) | Binlog-based configurable mysql database real-time synchronization method | |
| CN106250270B (en) | A kind of data back up method under cloud computing platform | |
| CN106685691B (en) | The efficient update configuration method of terminal device in Internet of things system | |
| US9648059B2 (en) | System and methods for multi-user CAx editing conflict management | |
| US9934242B2 (en) | Replication of data between mirrored data sites | |
| CN103220358B (en) | The online file synchronisation method in a kind of multiple terminals, system, server and terminal unit | |
| CN102968486A (en) | High-reliability file synchronization method based on log change | |
| CN106919465B (en) | Method and apparatus for multiple data protection in a storage system | |
| CN104993940A (en) | Method and device for reducing data loss in main/standby node fault switching process | |
| US8612799B2 (en) | Method and apparatus of backing up subversion repository | |
| US20230108138A1 (en) | Techniques for preserving clone relationships between files | |
| US20200341861A1 (en) | Method, device, and computer-readable storage medium for managing storage system | |
| CN111125001A (en) | Snap copy method, electronic device, and computer program product | |
| CN107621994A (en) | A method and device for creating a data snapshot | |
| CN108881452B (en) | Data synchronization method, device and storage medium | |
| CN111522688B (en) | Data backup method and device for distributed system | |
| CN113051342B (en) | A method and system for lightweight dual-machine switching | |
| CN108984346A (en) | A kind of method, system and the storage medium of creation data disaster tolerance | |
| CN105512116A (en) | Data synchronization method and device | |
| WO2021208401A1 (en) | Continuous data protection system and method for modern applications | |
| CN107239505B (en) | Cluster mirror synchronization method and system | |
| JP4715777B2 (en) | Replication method, storage apparatus, and program | |
| CN101711064A (en) | Modified information managing method and terminal | |
| EP4124938A1 (en) | Synchronizing file system items having a data and metadata portion between a source storage system and a destination storage system | |
| JP5673234B2 (en) | Database replication method |
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: 20160921 |
|
| RJ01 | Rejection of invention patent application after publication |