CN107330065A - A kind of MySQL database clone method based on ISER agreements - Google Patents
A kind of MySQL database clone method based on ISER agreements Download PDFInfo
- Publication number
- CN107330065A CN107330065A CN201710518089.4A CN201710518089A CN107330065A CN 107330065 A CN107330065 A CN 107330065A CN 201710518089 A CN201710518089 A CN 201710518089A CN 107330065 A CN107330065 A CN 107330065A
- Authority
- CN
- China
- Prior art keywords
- main frame
- standby host
- binlog
- disk
- host
- 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; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F11/00—Error detection; Error correction; Monitoring
- G06F11/07—Responding to the occurrence of a fault, e.g. fault tolerance
- G06F11/14—Error detection or correction of the data by redundancy in operation
- G06F11/1402—Saving, restoring, recovering or retrying
- G06F11/1446—Point-in-time backing up or restoration of persistent data
- G06F11/1458—Management of the backup or restore process
- G06F11/1464—Management of the backup or restore process for networked environments
-
- 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
- G06F16/275—Synchronous replication
-
- 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
-
- H—ELECTRICITY
- H04—ELECTRIC COMMUNICATION TECHNIQUE
- H04L—TRANSMISSION OF DIGITAL INFORMATION, e.g. TELEGRAPHIC COMMUNICATION
- H04L69/00—Network arrangements, protocols or services independent of the application payload and not provided for in the other groups of this subclass
- H04L69/26—Special purpose or proprietary protocols or architectures
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Computer Networks & Wireless Communication (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Databases & Information Systems (AREA)
- Computing Systems (AREA)
- Signal Processing (AREA)
- Data Mining & Analysis (AREA)
- Computer Security & Cryptography (AREA)
- Quality & Reliability (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention discloses a kind of MySQL database clone method based on ISER agreements; disaster tolerance protection is carried out by leader follower replication technology between main frame and standby host; ISER agreements are used simultaneously, the Disk Mapping of standby host are used to main frame, the binlog of itself is stored on standby host Map Disk by main frame；If main frame breaks down, main frame transmission binlog and bit mapping disk binlog carries out displacement comparison on system contrast standby host, if deviation, then can re-execute the binlog for having more part on Disk Mapping.The present invention is able to ensure that standby host data and main frame are consistent after disaster occurs for main frame.There is provided a set of MySQL High Availabitities solution in the field being had higher requirements for finance etc. to data consistency.
Description
Technical field
The invention belongs to database technology, more particularly to a kind of improved method of MySQL database leader follower replication technology.
Background technology
Current MySQL is widely used in the middle-size and small-size website on Internet.Because its small volume, speed are fast,
The total cost of ownership is low, especially this feature of open source code, and many middle-size and small-size websites are in order to reduce the website total cost of ownership
And MySQL is have selected as site databases.Even in telecommunications industry, due to performance superior MySQL, stability, perfect
Function, be also more and more used in telecommunication product.
The MYSQL leader follower replication schemes of existing main flow are by the way that Mysql host node data operating records are copied into standby section
Point (slaves), and re-execute data operating record to realize.Host node writes data operating record in reproduction process
Binary log file, and maintenance documentation index circulated with trace log, while log recording be sent to from node carry out data
Update.
The major advantage of existing replication theme is：(1) build flexibly, conveniently realize read and write abruption.(2) to main frame almost without
Performance impact.(3) safeguard relatively easy.
But there is also following major defect：Data transfer uses asynchronous system between main frame and standby host, if main frame is sent out
Raw disaster, there is a possibility that loss of data.
The content of the invention
The present invention's is directed to during existing MySQL leader follower replications, standby if disaster (such as main frame delay machine) occurs for main frame
The data of machine may be asynchronous with main frame, so as to cause the defect of loss of data to realize data there is provided one kind is improved
The zero MySQL database clone method lost.
To achieve these goals, the present invention uses following technical scheme：A kind of MySQL database based on ISER agreements
Clone method, carries out disaster tolerance protection, while using ISER agreements, by standby host between main frame and standby host by leader follower replication technology
Disk Mapping is used to main frame, and the binlog of itself is stored on standby host Map Disk by main frame；If main frame breaks down, it is
Main frame transmission binlog and bit mapping disk binlog carries out displacement comparison on system contrast standby host, then can be again if deviation
Perform the binlog for having more part on Disk Mapping.
Further, transmission uses InfiniBand technologies between main frame and standby host.
Further, it the described method comprises the following steps：
(1) MySQL database is respectively mounted on two computers；
(2) leader follower replication is built between two computers；
(3) shared memory systems, host-initiated iscsid are set up respectively on the one piece of disk or subregion of main frame and standby host
Service, standby host starts tgtd services, and sets up the connection of the ISER agreements between main frame and standby host, is set up on main frame described in standby host
The mapping of disk or subregion；
(4) disk or subregion described in main frame and standby host is realized data redundancy using soft raid technologies, form new disk
Or subregion；
(5) some mount point will be articulated to after the new disk or partition format of formation；
(6) main frame MySQL binlog amended records are set to the mount point of step 5；
(7) main frame MySQL sync_binlog is set to 1, while innodb_flush_log_at_trx_commit is set
For 1.Further, the leader follower replication of the step 2, is concretely comprised the following steps：
(1) mysql of host server is logged in, master state is inquired about；
(2) standby host server slave is configured, master_host main frames are set to, port is host service port, user name
Password, which is configured to main frame, has user and the password of full storehouse access limit, and binlog document locations are the record log files of step 1
Position；
(3) start server replicates function, and check server replicates functional status until principal and subordinate's server configures completion.
The present invention improves MySQL leader follower replication, is not influenceing original by introducing the technologies such as InfiniBand and ISER
While having performance, it is possible to achieve data zero are lost.
The technical principle of the present invention：Disaster tolerance protection is still carried out by main flow leader follower replication technology between main frame and standby host,
ISER agreements are used simultaneously, and the Disk Mapping of standby host is used to main frame.The binlog of itself is stored in standby host mapping by main frame
On disk (in Fig. 1 shown in red circle).When main frame breaks down, system can contrast main frame transmission binlog and bit mapping on standby host
Disk binlog carries out displacement comparison, if deviation, then can re-execute the binlog for having more part on Disk Mapping, from
And reach the loss of data zero.
As above it is related to target to reach, this programme introduces following two technologies：
A, InfiniBand technology：These years, constantly moving to maturity with InfiniBand network technologies,
Infiniband architecture have height handle up low delay the characteristics of, and InfiniBand network stabilizations greatly add over nearest 3 years
By force.By the introducing of InfiniBand technologies, try hard to solve quick transmission MYSQL binlog infrastructure environment.
B, ISER agreement：There are very a variety of agreements to support order and data in transmission over networks, this method is employed closely
The ISER agreements of gradual perfection, are combined using the distinctive support binlog data engines of ISER agreements and MySQL, reached over year
The effect that peculiar data syn-chronization is replicated.
The present invention main frame delay machine when, the binlog of itself is stored in this block on standby host Map Disk by main frame, works as master
When machine breaks down, this block standby host disk remains whole binlog, as long as system correctly applies these binlog files,
It just necessarily ensure that data are not lost.
The present invention is able to ensure that standby host data and main frame are consistent after disaster occurs for main frame.It is finance etc. to data
There is provided a set of MySQL High Availabitities solution in the field that uniformity has higher requirements.
Brief description of the drawings
Fig. 1 is MySQL database clone method schematic diagram of the present invention.
Fig. 2 is the flow chart that MySQL database of the present invention is replicated.
Fig. 3 is process chart when disaster occurs for main frame of the present invention.
Embodiment
With reference to specific embodiments and the drawings, the present invention is described further.
Fig. 1 is MySQL database clone method schematic diagram.Fig. 2 is the flow chart that MySQL database is replicated.Fig. 3 is main frame
Occurs process chart during disaster.
Hardware environment：
(1) two X86 PC server is connected by common Ethernet LAN,
(2) disk of every at least more than two pieces of X86 PC server,
It is connected (each one piece of PC server needs by an InfiniBand between (3) two X86 PC servers
HCA cards).
Implementation steps：
Step 1, two PC server normal mounting MySQL databases (process summary).
Step 2, first build leader follower replication.
Step 2.1 step logs in the mysql of master server, inquires about master state, and records；
Step 2.2 is configured from server slave, is set to master_host main frames, and port is host service port, user
Name password, which is configured to main frame, has user and the password of full storehouse access limit, and binlog document locations are the record log of step 2.1
Document location；
Step 2.3, startup server replicates function；
Step 2.4, check from server replicates functional status：
Slave_IO and Slave_SQL processes must normally be run, i.e. YES states, be otherwise all mistake state (such as：
One of NO belongs to mistake).
Above operating process, principal and subordinate's server configuration is completed.
Step 3, standby host installation and deployment
Step 3.1 prepares one piece of disk (or subregion), it is assumed that for/dev/sdb；
Step 3.2 installs scsi-target-utils system software packages；
Step 3.3 starts tgtd services；
Step 3.4 defines an id and iqn address；
Step 3.5 editor's configuration file sets relevant parameter definition formulation to trust host device using iser agreements, right
Outer export is above-mentioned/dev/sdb；
Step 3.6 service of restarting allows configuration file to come into force.
Step 4, main frame installation and deployment
Step 4.1 installs iscsi-initiator-utils software kits；
Step 4.2 starts iscsid services；
Step 4.3 finds to specify the Map Disk of standby host, and formulates and connected using iser agreements；
Step 4.4 logs in this disk unit using iqn defined above；
Step 4.5 uses and checks that standby host reflects this incoming block disk；
Step should have more one piece of disk using fdisk-l and be assumed to be/dev/sdc；
Step 5 uses soft raid technologies, host side with locally /dev/sdb and newly-generated/dev/sdc is done above
Into raid1, new disk is formed, it is assumed that entitled/dev/dm126；
Step 6, general/dev/dm126 are formatted into certain file system, such as xfs or ext4.
Step 7, general/dev/dm126mount are to some mount point.
Step 8, the mount point that main frame MySQL binlog amended records are set to step 7.
Step 9, main frame MySQL sync_binlog is set to 1, while innodb_flush_log_at_trx_
Commit is set to 1.
Deployment is completed.
Main frame delay machine when, the binlog of itself is stored in this block on standby host Map Disk by main frame, when main frame occur therefore
During barrier, this block standby host disk remains whole binlog, as long as system correctly applies these binlog files, with regard to one surely
Enough ensure that data are not lost.
It is described above, only it is the preferred embodiments of the present invention, any type of limitation is not done to the present invention.It is every according to
Any simple modification, equivalent variations and the modification substantially made according to the technology and method of the present invention to above example, still fall within
In the range of the technology and method scheme of the present invention.
Claims (4)
1. a kind of MySQL database clone method based on ISER agreements, it is characterised in that：Pass through principal and subordinate between main frame and standby host
Reproduction technology carries out disaster tolerance protection, while using ISER agreements, the Disk Mapping of standby host is used to main frame, main frame by itself
Binlog is stored on standby host Map Disk；If main frame breaks down, main frame transmission binlog and position on system contrast standby host
Map Disk binlog carries out displacement comparison, if deviation, then can re-execute the part that has more on Disk Mapping
binlog。
2. database copy method according to claim 1, it is characterised in that：Transmit and use between the main frame and standby host
InfiniBand technologies.
3. database copy method according to claim 1 or 2, it is characterised in that：It the described method comprises the following steps：
(1) MySQL database is respectively mounted on two computers；
(2) leader follower replication is built between two computers；
(3) shared memory systems are set up respectively on the one piece of disk or subregion of main frame and standby host, host-initiated iscsid is serviced,
Standby host starts tgtd services, and sets up the connection of the ISER agreements between main frame and standby host, and disk described in standby host is set up on main frame
Or the mapping of subregion；
(4) disk or subregion described in main frame and standby host is realized data redundancy using soft raid technologies, formed new disk or point
Area；
(5) some mount point will be articulated to after the new disk or partition format of formation；
(6) main frame MySQL binlog amended records are set to the mount point of step 5；
(7) main frame MySQL sync_binlog is set to 1, while innodb_flush_log_at_trx_commit is set to 1.
4. database copy method according to claim 3, it is characterised in that：The leader follower replication of the step 2, specific step
Suddenly it is：
(1) mysql of host server is logged in, master state is inquired about；
(2) standby host server slave is configured, master_host main frames are set to, port is host service port, user name password
Being configured to main frame has user and the password of full storehouse access limit, and binlog document locations are the record log files position of step 1
Put；
(3) start server replicates function, and check server replicates functional status until principal and subordinate's server configures completion.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201710518089.4A CN107330065A (en) | 2017-06-29 | 2017-06-29 | A kind of MySQL database clone method based on ISER agreements |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201710518089.4A CN107330065A (en) | 2017-06-29 | 2017-06-29 | A kind of MySQL database clone method based on ISER agreements |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN107330065A true CN107330065A (en) | 2017-11-07 |
Family
ID=60197321
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201710518089.4A Pending CN107330065A (en) | 2017-06-29 | 2017-06-29 | A kind of MySQL database clone method based on ISER agreements |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN107330065A (en) |
Cited By (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN109358817A (en) * | 2018-10-26 | 2019-02-19 | 北京百度网讯科技有限公司 | Methods, devices and systems for replicate data |
| CN113849351A (en) * | 2021-09-30 | 2021-12-28 | 紫光云技术有限公司 | MySQL high-availability implementation method |
| CN116155920A (en) * | 2023-02-16 | 2023-05-23 | 北京万里开源软件有限公司 | Remote transmission method and system for MySQL protocol database data |
Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20080133963A1 (en) * | 2006-12-04 | 2008-06-05 | Katano Shingo | Method and computer system for failover |
| CN102629906A (en) * | 2012-03-30 | 2012-08-08 | 浪潮电子信息产业股份有限公司 | Design method for improving cluster business availability by using cluster management node as two computers |
| CN102855163A (en) * | 2011-06-27 | 2013-01-02 | 华为软件技术有限公司 | Memory database hot-standby method and host |
| CN103226502A (en) * | 2013-05-21 | 2013-07-31 | 中国工商银行股份有限公司 | Data disaster backup control system and data recovery method |
| CN105095245A (en) * | 2014-05-04 | 2015-11-25 | 阿里巴巴集团控股有限公司 | Filing log synchronizing method and system based on relevance database |
-
2017
- 2017-06-29 CN CN201710518089.4A patent/CN107330065A/en active Pending
Patent Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20080133963A1 (en) * | 2006-12-04 | 2008-06-05 | Katano Shingo | Method and computer system for failover |
| CN102855163A (en) * | 2011-06-27 | 2013-01-02 | 华为软件技术有限公司 | Memory database hot-standby method and host |
| CN102629906A (en) * | 2012-03-30 | 2012-08-08 | 浪潮电子信息产业股份有限公司 | Design method for improving cluster business availability by using cluster management node as two computers |
| CN103226502A (en) * | 2013-05-21 | 2013-07-31 | 中国工商银行股份有限公司 | Data disaster backup control system and data recovery method |
| CN105095245A (en) * | 2014-05-04 | 2015-11-25 | 阿里巴巴集团控股有限公司 | Filing log synchronizing method and system based on relevance database |
Non-Patent Citations (1)
| Title |
|---|
| 管文琦: "《金融行业 MySQL 数据库高可用方案的思考》", 《中国金融电脑》 * |
Cited By (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN109358817A (en) * | 2018-10-26 | 2019-02-19 | 北京百度网讯科技有限公司 | Methods, devices and systems for replicate data |
| CN109358817B (en) * | 2018-10-26 | 2022-02-18 | 北京百度网讯科技有限公司 | Method, device and system for copying data |
| CN113849351A (en) * | 2021-09-30 | 2021-12-28 | 紫光云技术有限公司 | MySQL high-availability implementation method |
| CN116155920A (en) * | 2023-02-16 | 2023-05-23 | 北京万里开源软件有限公司 | Remote transmission method and system for MySQL protocol database data |
| CN116155920B (en) * | 2023-02-16 | 2023-10-03 | 北京万里开源软件有限公司 | Remote transmission method and system for MySQL protocol database data |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US10282262B2 (en) | Non-disruptive controller replacement in a cross-cluster redundancy configuration | |
| US7529816B2 (en) | System for providing multi-path input/output in a clustered data storage network | |
| US8819383B1 (en) | Non-disruptive realignment of virtual data | |
| CN106815218B (en) | Database access method and device and database system | |
| US8341119B1 (en) | Flexible copies having different sub-types | |
| CA2896865A1 (en) | Method and system for using a recursive event listener on a node in hierarchical data structure | |
| US11494130B2 (en) | Operation data accessing device and accessing method thereof | |
| EP3648405B1 (en) | System and method to create a highly available quorum for clustered solutions | |
| CN107330065A (en) | A kind of MySQL database clone method based on ISER agreements | |
| CN105407117A (en) | Distributed data backup method, device and system | |
| US20230409227A1 (en) | Resilient implementation of client file operations and replication | |
| CN102077183A (en) | Maintenance of exo-file system metadata on removable storage device | |
| WO2021213281A1 (en) | Data reading method and system | |
| CN109407975B (en) | Data writing method, computing node and distributed storage system | |
| US10241699B2 (en) | Method, apparatus or software for transferring a storage replication system between storage systems | |
| CA2981469A1 (en) | Parallel asynchronous data replication | |
| US8117493B1 (en) | Fast recovery in data mirroring techniques | |
| US11144407B1 (en) | Synchronous database geo-mirroring using delayed visibility write operations | |
| WO2021109777A1 (en) | Data file import method and device | |
| CN112073212B (en) | Parameter configuration method, device, terminal equipment and storage medium | |
| US7680839B1 (en) | System and method for resynchronizing mirrored volumes | |
| US10678617B2 (en) | Redundant software stack | |
| US10498815B2 (en) | Data recovery objective modeling | |
| CN107944010B (en) | Method and device for reading and writing files in distributed file system | |
| CN110851411B (en) | DNS dynamic change system and method based on file synchronization |
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 | ||
| RJ01 | Rejection of invention patent application after publication | ||
| RJ01 | Rejection of invention patent application after publication |
Application publication date: 20171107 |