CN112437166A - Automatic switching method for MySQL database domain names - Google Patents
Automatic switching method for MySQL database domain names Download PDFInfo
- Publication number
- CN112437166A CN112437166A CN202011191292.3A CN202011191292A CN112437166A CN 112437166 A CN112437166 A CN 112437166A CN 202011191292 A CN202011191292 A CN 202011191292A CN 112437166 A CN112437166 A CN 112437166A
- Authority
- CN
- China
- Prior art keywords
- database
- domain name
- slave
- master
- node
- 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
- 238000000034 method Methods 0.000 title claims abstract description 9
- 230000004075 alteration Effects 0.000 description 1
- 230000005540 biological transmission Effects 0.000 description 1
- 230000000737 periodic effect Effects 0.000 description 1
- 238000006467 substitution reaction Methods 0.000 description 1
Images
Classifications
-
- H—ELECTRICITY
- H04—ELECTRIC COMMUNICATION TECHNIQUE
- H04L—TRANSMISSION OF DIGITAL INFORMATION, e.g. TELEGRAPHIC COMMUNICATION
- H04L61/00—Network arrangements, protocols or services for addressing or naming
- H04L61/45—Network directories; Name-to-address mapping
- H04L61/4505—Network directories; Name-to-address mapping using standardised directories; using standardised directory access protocols
- H04L61/4511—Network directories; Name-to-address mapping using standardised directories; using standardised directory access protocols using domain name system [DNS]
-
- 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/21—Design, administration or maintenance of databases
-
- 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
-
- 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
-
- 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/14—Session management
- H04L67/148—Migration or transfer of sessions
Landscapes
- Engineering & Computer Science (AREA)
- Databases & Information Systems (AREA)
- Theoretical Computer Science (AREA)
- Computer Networks & Wireless Communication (AREA)
- Signal Processing (AREA)
- Data Mining & Analysis (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Computing Systems (AREA)
- Data Exchanges In Wide-Area Networks (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention discloses an automatic switching method for MySQL database domain names, which comprises the following steps: A. on a DNS server, defining a master/slave two database domain names for each database master-slave cluster; B. defining a pool for each database domain name, and hanging a node IP corresponding to the database cluster under each pool; the DNS server periodically uses the IP address of the node of the database cluster to establish database connection with the node of the database cluster, and uses a command to check the state of 'read only' of the currently connected database, and the DNS server provides the IP address corresponding to the domain name resolution of the master/slave database in real time according to the 'read only' state of the database node. When the database is switched between master and slave, the application end does not need to modify the configuration file and does not need to manually modify the A/AAAA record corresponding to the domain name of the database. After switching, when the application establishes a new database connection, the corresponding database node IP can be directly obtained through the master library domain name/the slave library domain name, so that the application is connected to the correct database node.
Description
Technical Field
The invention relates to the technical field of databases, in particular to an automatic domain name switching method for a MySQL database.
Background
A database (database) is a repository that stores data. To facilitate storage and management of data, it stores data on a disk according to a particular rule. Data stored in a database can be efficiently organized and managed by a database management system. The MySQL database may be referred to as the SQL language database that operates at the highest speed at present, and the MySQL database may be referred to as the SQL language database that operates at the highest speed at present.
Domain Name (Domain Name), which is the Name of a computer or group of computers on the Internet composed of a string of characters separated by dots, is used to identify the electronic position of the computer (sometimes also referred to as geographical location, a local area with administrative autonomy) during data transmission. The domain name is a "mask" on an IP address. The purpose of a domain name is to facilitate the address of a set of servers (web site, email, FTP, etc.) that are remembered and communicated.
After the MySQL database is switched between a master database and a slave database, the application needs to modify the database domain name in the configuration file, and the original master database domain name is modified into the slave database domain name; or modifying the A/AAAA record corresponding to the database domain name in the DNS server by a network administrator, and analyzing the old database to the new database IP node.
Disclosure of Invention
In order to solve the above problems, the present invention provides an automatic domain name switching method for a MySQL database, comprising:
A. on a DNS server, defining two database domain names for each database master-slave cluster, namely a master database domain name and a slave database domain name respectively;
B. defining a pool for each database domain name, and hanging a node IP corresponding to the database cluster under each pool;
the DNS server periodically uses the IP address of the database cluster to establish database connection with the nodes of the database cluster, and uses a command to check the state of the 'read only' of the currently connected database
And D, providing the IP address corresponding to the domain name resolution of the database by the DNS server in real time according to the read only state of the database node.
Further, step D includes:
after the master-slave state of the database is changed, the 'read only' states of the master library and the slave library are also changed synchronously, and when the DNS server is found to be changed when the DNS server is periodically connected with the database, the resolved IP address of the corresponding domain name is immediately updated;
for the domain name of the master library, the domain name of the master library corresponds to the node IP with the state of 'read only' being 'OFF', and because only one node belongs to the master library, the domain name of the master library only has one resolved IP address;
for the slave library domain name, the slave library domain name corresponds to the node IP with the "read only" state being "ON", and since there may be a plurality of nodes belonging to the slave library, there may be a plurality of resolved IP addresses for the slave library domain name.
According to the invention, the DNS server acquires the current 'read _ only' state through periodic connection data, and provides real-time database domain name resolution service for other clients who want to access according to the real-time state of the 'read _ only'.
When the database is switched between master and slave, the application end does not need to modify the configuration file and does not need to manually modify the A/AAAA record corresponding to the domain name of the database. After switching, when the application establishes a new database connection, the corresponding database node IP can be directly obtained through the master library domain name/the slave library domain name, so that the application is connected to the correct database node.
Drawings
Fig. 1 is a flowchart of an automatic domain name switching method of a MySQL database according to the present invention.
Detailed Description
The present invention will be described in further detail with reference to the following examples. This should not be understood as limiting the scope of the above-described subject matter of the present invention to the following examples. Various substitutions and alterations according to the general knowledge and conventional practice in the art are intended to be included within the scope of the present invention without departing from the technical spirit of the present invention as described above.
As shown in fig. 1, a method for automatically switching domain names of MySQL databases includes:
1. defining database master/slave domain names for a single database
On a DNS server, defining two database domain names for each database master-slave cluster, namely a master database domain name and a slave database domain name respectively, wherein the master database domain name has read-write permission and starts with mym; the slave library domain name is read-only, beginning with mys.
2. Defining one Pool for each database master/slave domain name
Defining a pool for each database domain name, and hanging an IP node of a corresponding database cluster under each pool.
3. Periodically checking read only status
The DNS server periodically uses the IP address of the database cluster to establish database connection with the nodes of the database cluster, and uses a command SHOW VARIABLE LIKE '% read _ only%' to check the 'read _ only' state of the currently connected database; and judging the current master-slave state of the database according to the read only state, and finally influencing the resolution result of the master library domain name or the slave library domain name.
4. Providing IP addresses based on read only status
The DNS server provides IP addresses corresponding to domain name resolution of the database in real time according to the 'read only' state of a database node, the 'read only' states of a master library and a slave library of the database are synchronously changed after the master-slave state of the database is changed, and the DNS server immediately updates the resolution IP addresses of corresponding domain names after the 'read only' state of the DNS server is found to be changed when the DNS server is periodically connected with the database;
for the domain name of the master library, the domain name of the master library corresponds to the node IP with the state of 'read only' being 'OFF', and because only one node belongs to the master library, the domain name of the master library only has one resolved IP address;
for the slave library domain name, the slave library domain name corresponds to the node IP with the "read only" state being "ON", and since there may be a plurality of nodes belonging to the slave library, there may be a plurality of resolved IP addresses for the slave library domain name.
Example 1.
Step 1: on the DNS server, two database domain names are defined for each database master-slave cluster, which are a master library domain name (having read-write permission, beginning with mym) and a slave library domain name (read-only permission, beginning with mys), as shown in table 1:
table 1:
step 2: defining each database domain name as one Pool, and hanging an IP node of a corresponding database cluster under each Pool, as shown in table 2:
table 2:
and step 3: periodically, the DNS server establishes a database connection with a node of the database cluster using the IP address of the database cluster, and checks the "read _ only" status of the currently connected database using the command SHOW variabilis LIKE "% read _ only%", as shown in table 3:
table 3:
and 4, step 4: the DNS server provides an IP address corresponding to the domain name resolution of the database by using the record A according to the 'read _ only' state of the database node in real time; after the database master-slave status changes, the "read _ only" status of its master/slave library changes synchronously. After the DNS server is periodically connected with a database and the state of the read _ only is found to be changed, the A record of the corresponding domain name is immediately modified, and the resolution IP address of the corresponding domain name is updated. The resolved IP address before handover is shown in table 4; table 5 shows the resolved IP addresses after the handover.
Table 4:
table 5:
Claims (2)
1. a MySQL database domain name automatic switching method is characterized by comprising the following steps:
A. on a DNS server, defining two database domain names for each database master-slave cluster, namely a master database domain name and a slave database domain name respectively;
B. defining a pool for each database domain name, and hanging a node IP corresponding to the database cluster under each pool;
the DNS server periodically uses the IP address of the database cluster to establish database connection with the nodes of the database cluster, and uses a command to check the state of the 'read only' of the currently connected database
And D, providing the IP address corresponding to the domain name resolution of the database by the DNS server in real time according to the read only state of the database node.
2. The MySQL database domain name automatic switching method of claim 1, wherein the step D comprises the following steps:
after the master-slave state of the database is changed, the 'read only' states of the master library and the slave library are also changed synchronously, and when the DNS server is found to be changed when the DNS server is periodically connected with the database, the resolved IP address of the corresponding domain name is immediately updated;
for the domain name of the master library, the domain name of the master library corresponds to the node IP with the state of 'read only' being 'OFF', and because only one node belongs to the master library, the domain name of the master library only has one resolved IP address;
for the slave library domain name, the slave library domain name corresponds to the node IP with the "read only" state being "ON", and since there may be a plurality of nodes belonging to the slave library, there may be a plurality of resolved IP addresses for the slave library domain name.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202011191292.3A CN112437166A (en) | 2020-10-30 | 2020-10-30 | Automatic switching method for MySQL database domain names |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202011191292.3A CN112437166A (en) | 2020-10-30 | 2020-10-30 | Automatic switching method for MySQL database domain names |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN112437166A true CN112437166A (en) | 2021-03-02 |
Family
ID=74694938
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202011191292.3A Pending CN112437166A (en) | 2020-10-30 | 2020-10-30 | Automatic switching method for MySQL database domain names |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN112437166A (en) |
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN113190374A (en) * | 2021-06-30 | 2021-07-30 | 四川新网银行股份有限公司 | Application fault transfer system and method based on mysql MGR |
Citations (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN108599996A (en) * | 2018-04-03 | 2018-09-28 | 武汉斗鱼网络科技有限公司 | Fault handling method, device and the terminal of data-base cluster |
| CN108900572A (en) * | 2018-05-31 | 2018-11-27 | 康键信息技术（深圳）有限公司 | Primary database domain name method for handover control, computer equipment and storage medium |
| CN111581284A (en) * | 2020-04-29 | 2020-08-25 | 上海中通吉网络技术有限公司 | High-availability method, device and system for database and storage medium |
-
2020
- 2020-10-30 CN CN202011191292.3A patent/CN112437166A/en active Pending
Patent Citations (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN108599996A (en) * | 2018-04-03 | 2018-09-28 | 武汉斗鱼网络科技有限公司 | Fault handling method, device and the terminal of data-base cluster |
| CN108900572A (en) * | 2018-05-31 | 2018-11-27 | 康键信息技术（深圳）有限公司 | Primary database domain name method for handover control, computer equipment and storage medium |
| CN111581284A (en) * | 2020-04-29 | 2020-08-25 | 上海中通吉网络技术有限公司 | High-availability method, device and system for database and storage medium |
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN113190374A (en) * | 2021-06-30 | 2021-07-30 | 四川新网银行股份有限公司 | Application fault transfer system and method based on mysql MGR |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US6553368B2 (en) | Network directory access mechanism | |
| DE69838739T2 (en) | Method and apparatus for presenting and using network topology data | |
| KR101153137B1 (en) | Method and system for transitioning between synchronous and asynchronous communication models | |
| JP5514834B2 (en) | Selective database replication | |
| EP2469444B1 (en) | Multi-tenant system | |
| US6816896B2 (en) | Method and apparatus for detecting changes to network elements | |
| US7801848B2 (en) | Redistributing a distributed database | |
| US7181436B1 (en) | Automatically generating replication topology information for use by a directory service | |
| US6643670B2 (en) | Efficient replication of an expanded partial database | |
| US10243919B1 (en) | Rule-based automation of DNS service discovery | |
| US20070112812A1 (en) | System and method for writing data to a directory | |
| US6721758B1 (en) | System and method for using schema attributes as meta-data in a directory service | |
| CN106202492A (en) | The Metadata Service optimization method of a kind of NFS and system | |
| US9143559B2 (en) | Directory server replication | |
| CN112437166A (en) | Automatic switching method for MySQL database domain names | |
| CN108809838B (en) | Service discovery processing method and device based on tree structure | |
| JPH06266600A (en) | Distributed file system | |
| CA2547324A1 (en) | Distributed knowledge management system | |
| CN111274004B (en) | Process instance management method and device and computer storage medium | |
| CN101883082A (en) | Method, equipment and system for acquiring modeling file information of network configuration protocol server | |
| CN112422706B (en) | Method and system for analyzing name space of digital object | |
| CN109491615A (en) | A kind of arbitration system based on cluster storage system | |
| CN111614750B (en) | Data updating method, system, equipment and storage medium | |
| US9219706B2 (en) | Just-in-time wrapper synchronization | |
| KR100462283B1 (en) | Policy based network management system and method using extensible markup language |
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 |
Application publication date: 20210302 |
|
| RJ01 | Rejection of invention patent application after publication |