CN111400285B - mySQL data fragment processing method, device, computer equipment and readable storage medium - Google Patents
mySQL data fragment processing method, device, computer equipment and readable storage medium Download PDFInfo
- Publication number
- CN111400285B CN111400285B CN202010216069.3A CN202010216069A CN111400285B CN 111400285 B CN111400285 B CN 111400285B CN 202010216069 A CN202010216069 A CN 202010216069A CN 111400285 B CN111400285 B CN 111400285B
- Authority
- CN
- China
- Prior art keywords
- mysql
- read
- library
- middleware
- data
- 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
-
- 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
- G06F16/214—Database migration support
-
- 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/1479—Generic software techniques for error detection or fault masking
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F11/00—Error detection; Error correction; Monitoring
- G06F11/30—Monitoring
- G06F11/3003—Monitoring arrangements specially adapted to the computing system or computing system component being monitored
- G06F11/302—Monitoring arrangements specially adapted to the computing system or computing system component being monitored where the computing system component is a software system
-
- 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
-
- Y—GENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y02—TECHNOLOGIES OR APPLICATIONS FOR MITIGATION OR ADAPTATION AGAINST CLIMATE CHANGE
- Y02D—CLIMATE CHANGE MITIGATION TECHNOLOGIES IN INFORMATION AND COMMUNICATION TECHNOLOGIES [ICT], I.E. INFORMATION AND COMMUNICATION TECHNOLOGIES AIMING AT THE REDUCTION OF THEIR OWN ENERGY USE
- Y02D10/00—Energy efficient computing, e.g. low power processors, power management or thermal management
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Physics & Mathematics (AREA)
- Databases & Information Systems (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Computing Systems (AREA)
- Quality & Reliability (AREA)
- Data Mining & Analysis (AREA)
- Mathematical Physics (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The method realizes flexible storage, load balancing and automatic online and offline of data by combining a database system through a database splitting middleware, solves the problems of unsupported read-write separation and unsupported smooth capacity expansion migration through a read-write separation middleware, solves the problem of unsupported mySQL high availability through a main database high availability middleware, and realizes the mobile fragmentation processing of mySQL data capable of achieving read-write separation and mySQL high availability, supporting load balancing and automatic online and offline and supporting seamless capacity expansion.
Description
Technical Field
The present application relates to the field of databases, and in particular, to mySQL data fragment processing methods, apparatuses, computer devices, and computer readable storage media.
Background
MySQL is used as a mainstream relational database, is widely applied in the internet industry, and as service requirements become more complex and data volume becomes larger, requirements on MySQL storage and data access become higher, and a more feasible scheme is to perform fragmentation processing on MySQL large tables.
However, in the related art, the library and table separation is realized through a client mode, and the functions of read-write separation and high availability are not provided, and the functions of load balancing and automatic online and offline are not provided; the database splitting and table splitting are realized through a server mode, and the database splitting and table splitting also needs to be realized through a middleware agent or a set of software system which needs to support mySQL protocol, and the method is suitable for a small number of offline services due to the fact that the structure is complex, the performance is low, the maintenance difficulty is high, and the application scene is limited.
At present, effective solutions are not proposed for solving the problems that read-write separation, mySQL high availability, load balancing, automatic online and offline and smooth capacity expansion migration cannot be considered in mySQL data slicing processing in the related technology.
Disclosure of Invention
The embodiment of the application provides a mySQL data slicing processing method, a mySQL data slicing processing device, a mySQL data slicing processing computer device and a mySQL data slicing processing computer readable storage medium, and aims to at least solve the problems of read-write separation, mySQL high availability, load balancing, automatic online and offline and smooth capacity expansion migration in the related technologies.
According to one aspect of the invention, there is provided a mySQL data fragment processing method, the method comprising:
when performing read-write operation, obtaining a slicing connection according to a slicing connection, and writing data into a mySQL main library through a read-write separation middleware or reading data from a mySQL slave library through the read-write separation middleware, wherein the slicing connection is provided by a database system, and the slicing connection indicates the read-write separation middleware for the data;
the database system changes the available connection number in the database system according to the data volume of the read-write operation, wherein the available connection number is the number of the slicing connection provided at the same time;
under the condition that the slave library capacity expansion instruction is triggered, mySQL slave library capacity expansion is carried out through the read-write separation middleware, and under the condition that the slave library migration instruction is triggered, mySQL slave library migration is carried out through the read-write separation middleware;
in the read-write operation process, triggering election and migrating a new mySQL main library under the condition that the mySQL main library is detected to be faulty.
In some of these embodiments, the database system changing the number of available connections in the database system based on the amount of data for the read operation and the write operation comprises:
increasing the available connection number to a preset first threshold value under the condition that the available connection number is smaller than a preset maximum connection number and the first connection number in the database system is larger than the available connection number, wherein the first connection number is the connection number required by data to be processed in the database system;
and reducing the number of available connections to a preset second threshold value under the condition that the second number of connections is smaller than the number of available connections and is larger than a preset minimum number of connections, wherein the second number of connections is the number of connections required by the data in process.
In some embodiments, in the case that the database splitting and table splitting middleware is a shaping-jdbc, the shaping-jdbc receives a data processing instruction, and acquires the fragment connection from the database system according to a preset keyword in the data processing instruction and a database splitting and table splitting strategy, so as to complete the read-write operation.
In some embodiments, in the case that the read-write separation middleware is proxysql, a plurality of proxysql services are mounted in the database system, and the proxysql services are online according to a start connection instruction of the database system or the proxysql services are offline according to a disconnection instruction of the database system;
in the case that the slave-bank expansion instruction is triggered, a new proxysql service is started on the new server, the new proxysql service is loaded to the database system, in the case that the slave-bank migration instruction is triggered, the new proxysql service is started on the new server, the new proxysql service is loaded to the database system, the old proxysql service is disconnected from the database system, and after the data interaction request for the old proxysql service is ended, the old proxysql service is disconnected.
In some embodiments, in the case that a failure of the mySQL master library is detected during the read-write operation, triggering the election and migrating the new mySQL master library includes:
and deploying MHAnode on the mySQL master library and the mySQL slave library, starting MHA monitoring service, wherein the MHA monitoring service is used for monitoring the availability of the mySQL master library, and triggering election and migration of a new mySQL master library under the condition that the mySQL master library is detected to be faulty.
According to another aspect of the present invention, there is provided a mySQL data fragment processing apparatus, the apparatus comprising: the system comprises a database and table dividing module, a load balancing module, a read-write module and a high-availability module;
the database splitting and table splitting module is used for acquiring the split connection according to a database splitting and table splitting strategy when performing read-write operation, wherein the split connection is provided by a database system, and the split connection indication is used for reading and writing separation middleware of data;
the load balancing module is used for changing the available connection number in the database system according to the data volume of the read-write operation, wherein the connection number is the number of the fragment connection provided at the same time;
the read-write module is used for writing data into the mySQL main library through the read-write separation middleware through the fragment connection or reading data from the mySQL slave library through the read-write separation middleware; under the condition that the slave library capacity expansion instruction is triggered, mySQL slave library capacity expansion is carried out through the read-write separation middleware, and under the condition that the slave library migration instruction is triggered, mySQL slave library migration is carried out through the read-write separation middleware;
the high-availability module is used for triggering election and migrating a new mySQL main library under the condition that the mySQL main library is detected to be faulty in the read-write operation process.
In some embodiments, the load balancing module is further configured to increase the available connection number to a preset first threshold value when the available connection number is smaller than a preset maximum connection number and the first connection number in the database system is larger than the available connection number, where the first connection number is a number of connections required for data to be processed in the database system; and reducing the number of available connections to a preset second threshold value under the condition that the second number of connections is smaller than the number of available connections and is larger than a preset minimum number of connections, wherein the second number of connections is the number of connections required by the data in process.
In some embodiments, the middle part of the sub-database sub-table in the sub-database sub-table module is a recording-jdbc, the middle part of the read-write separation in the read-write module is a proxysql, and the high-availability module comprises Master High Availability MHA
According to another aspect of the present invention, there is also provided a computer device comprising a memory, a processor and a computer program stored on the memory and executable on the processor, the processor implementing a mySQL data fragmentation processing method as described above when executing the computer program.
According to another aspect of the present invention, there is also provided a computer readable storage medium having stored thereon a computer program which when executed by a processor implements a mySQL data-slicing processing method as described above.
Compared with the related art, the mySQL data fragment processing method, device, computer equipment and computer readable storage medium provided by the application realize flexible storage, load balancing and automatic online and offline of data by combining the database system through the database splitting middleware, solve the problems that read-write separation is not supported and smooth capacity expansion migration is not supported through the read-write separation middleware, solve the problem that mySQL is not supported to be high in availability through the main database high availability middleware, and realize the fragment processing that mySQL data can be read-write separation and mySQL is high in availability, support load balancing and automatic online and offline and support seamless capacity expansion migration.
The details of one or more embodiments of the application are set forth in the accompanying drawings and the description below to provide a more thorough understanding of the other features, objects, and advantages of the application.
Drawings
The accompanying drawings, which are included to provide a further understanding of the application and are incorporated in and constitute a part of this application, illustrate embodiments of the application and together with the description serve to explain the application and do not constitute an undue limitation to the application. In the drawings:
FIG. 1 is a flow chart of a mySQL data sharding processing method in accordance with one embodiment of the invention;
FIG. 2 is a schematic diagram of an MHA architecture in accordance with one embodiment of the present invention;
FIG. 3 is a block diagram of a mySQL data sharding approach in accordance with one embodiment of the present application;
FIG. 4 is a block diagram of a mySQL data-slicing processing device according to one embodiment of the invention;
FIG. 5 is a schematic diagram of a computer device according to one embodiment of the invention.
Detailed Description
In order to make the objects, technical solutions and advantages of the present application more apparent, the present application is described and illustrated below with reference to the accompanying drawings and examples. It should be understood that the specific embodiments described herein are for purposes of illustration only and are not intended to limit the present application. All other embodiments, which can be made by one of ordinary skill in the art without undue burden on the person of ordinary skill in the art based on the embodiments provided herein, are intended to be within the scope of the present application.
It is apparent that the drawings in the following description are only some examples or embodiments of the present application, and it is possible for those of ordinary skill in the art to apply the present application to other similar situations according to these drawings without inventive effort. Moreover, it should be appreciated that while such a development effort might be complex and lengthy, it would nevertheless be a routine undertaking of design, fabrication, or manufacture for those of ordinary skill having the benefit of this disclosure, and thus should not be construed as having the benefit of this disclosure.
Reference in the specification to "an embodiment" means that a particular feature, structure, or characteristic described in connection with the embodiment may be included in at least one embodiment of the application. The appearances of such phrases in various places in the specification are not necessarily all referring to the same embodiment, nor are separate or alternative embodiments mutually exclusive of other embodiments. It is to be expressly and implicitly understood by those of ordinary skill in the art that the embodiments described herein can be combined with other embodiments without conflict.
Unless defined otherwise, technical or scientific terms used herein should be given the ordinary meaning as understood by one of ordinary skill in the art to which this application belongs. Reference to "a," "an," "the," and similar terms herein do not denote a limitation of quantity, but rather denote the singular or plural. The terms "comprising," "including," "having," and any variations thereof, are intended to cover a non-exclusive inclusion; for example, a process, method, system, article, or apparatus that comprises a list of steps or modules (elements) is not limited to only those steps or elements but may include other steps or elements not expressly listed or inherent to such process, method, article, or apparatus. The terms "connected," "coupled," and the like in this application are not limited to physical or mechanical connections, but may include electrical connections, whether direct or indirect. The term "plurality" as used herein refers to two or more. "and/or" describes an association relationship of an association object, meaning that there may be three relationships, e.g., "a and/or B" may mean: a exists alone, A and B exist together, and B exists alone. The character "/" generally indicates that the context-dependent object is an "or" relationship. The terms "first," "second," "third," and the like, as used herein, are merely distinguishing between similar objects and not representing a particular ordering of objects.
The various techniques described in this application may be used in the process of data storage and reading and writing in mySQL databases. mySQL is a relational database management system that keeps data in different tables, rather than placing all the data in one large repository. The mySQL data fragment processing method provided by the invention can be used between the mySQL client and the mySQL storage engine, and can be used for managing and controlling the data read-write process, so that the data read-write efficiency is improved.
According to one aspect of the invention, a mySQL data fragment processing method is provided. FIG. 1 is a flow chart of a mySQL data sharding processing method according to one embodiment of the invention, as shown in FIG. 1, the flow comprising the steps of:
step S110, when performing read-write operation, obtaining a split connection according to a split table strategy, and writing data into a mySQL main library through a split connection through a read-write separation middleware or reading data from a mySQL slave library through the read-write separation middleware, wherein the split table strategy is configured in the split table middleware, the split connection is provided by a database system, and the split connection indicates the read-write separation middleware for the data; in step S110, the application service performs a read-write operation according to a user instruction, where the read-write operation is completed by collaboration of the database and table separating middleware, the database system and the read-write separating middleware: the application service obtains the slicing connection corresponding to the data to be operated according to the database splitting table strategy in the database splitting table middleware, wherein the slicing connection is one of available connections provided by the database system. The slicing connection provides connection between the application service and the read-write middleware, and the read-write middleware automatically realizes mySQL read-write separation according to the request type: data is written to the mySQL master library when a write operation is performed, and is read from the mySQL slave library when a read operation is performed. The library and table middleware can be MyCat, tsharding, shrarding-jdbc and the like, and the reading and writing separation middleware can be Atlas, myCAT, proxysql and the like.
Step S120, the database system changes the available connection number in the database system according to the data volume of the read-write operation, wherein the connection number is the number of the slicing connection provided at the same time; in step S120, the database system adjusts the number of available connections according to the amount of read-write data to be processed and being processed, for example, in the case that the amount of data to be processed in the database system is large, an idle connection is started, and the idle connection is adjusted to be an available connection, so that the database system plays a role in load balancing, and the number of available fragmented connections is adjusted and controlled.
Step S130, mySQL slave library expansion is carried out through the read-write separation middleware under the condition that the slave library expansion instruction is triggered, and mySQL slave library migration is carried out through the read-write separation middleware under the condition that the slave library migration instruction is triggered; in step S130, the above-mentioned read-write separation middleware also plays a role in smoothly expanding and migrating from the library. When the slave library expansion instruction is triggered, the slave library expansion can be realized by adding a new slave library address in the read-write separation middleware, when the slave library migration instruction is triggered, the old slave library can be stopped and is taken off line when no query request is detected on the old slave library by adding the new slave library address in the read-write separation middleware.
Step S140, in the read-write operation process, under the condition that the mySQL main library is detected to be faulty, the main library high-availability middleware triggers election and migrates a new mySQL main library. In step S140, the high availability of mySQL main library is realized through the high availability middleware of the main library, so that the availability of the database can be recovered as soon as possible under the condition that the database is down or accidentally interrupted and other faults occur, the downtime is reduced as much as possible, and the service is ensured not to be interrupted due to the faults of the database; meanwhile, the data of the non-master node serving as the functions of backup, read-only copy and the like should be consistent with the data of the master node in real time or finally; in addition, when the database switching occurs to the service, the contents of the database before and after switching should be consistent, and the service is not affected due to data missing or data inconsistency. In the case of a large number of databases, it is preferable that the Master of each type of database has a high availability of middleware to ensure maintainability of the database, such as MMM (Master-Master replication manager for MySQL), MHA or agents of various versions, etc.
The steps S110 to S140 utilize the joint setting of the database splitting and table splitting middleware, the database system, the read-write separation middleware and the main database high availability middleware, realize flexible storage, load balancing and automatic online and offline of data by combining the database splitting and table splitting middleware with the database system, solve the problems of not supporting read-write separation and not supporting smooth capacity expansion migration by the read-write separation middleware, solve the problem of not supporting mySQL high availability by the main database high availability middleware, and realize the migration and fragment processing of mySQL data capable of realizing read-write separation and mySQL high availability, supporting load balancing and automatic online and offline and supporting seamless capacity expansion.
In some of these embodiments, changing the number of connections available in the Database System (DBS) according to the amount of data for the read operation and the write operation includes:
increasing the available connection number to a preset first threshold value under the condition that the available connection number is smaller than a preset maximum connection number and the first connection number in the database system is larger than the available connection number, wherein the first connection number is the connection number required by data to be processed in the database system;
and reducing the number of available connections to a preset second threshold value under the condition that the second number of connections is smaller than the number of available connections and is larger than a preset minimum number of connections, wherein the second number of connections is the number of connections required by the data in process.
Preferably, when the database system is started, creating M Database (DB) connection objects according to a preset maximum connection number M, adding the M Database (DB) connection objects to the inventory connection pool, and taking N DB connection objects from the inventory connection pool according to a preset minimum connection number N, and adding the N DB connection objects to the active DB connection pool after the N DB connection objects are successfully connected, where the number of connection objects in the active DB pool is the available connection number. When there is data in the DB queue, a free DB connection object is fetched from the active DB connection pool, and the data is transferred to the free DB connection object for processing. In addition, a first number is calculated, the first number is the data amount to be processed in the queue divided by the processing number preset for each connection, the current connection number is smaller than the maximum connection number M, and the first number is larger than the current connection number, one connection object is taken out of the stock connection pool, the connection object is added into the active DB connection pool after successful connection, the increase of the available connection number is realized, alternatively, the connection number increased each time can be changed through setting, and the frequency of the judging process for judging whether to increase the available connection number according to the data amount to be processed in the queue can also be changed through setting, for example, the judgment is carried out every ten seconds or the judgment is carried out every 30 seconds. On the other hand, the second connection number is calculated as the average data in the queue per second divided by the configured number of processing per connection, when the second connection number is smaller than the current connection number and larger than the configured minimum connection number N, an idle DB connection object is taken from the active DB connection pool, connection is disconnected, threads are stopped, and the DBG connection object is added into the stock connection pool. The connection pool function can control the connection number of the application program accessing mySQL, and can improve mySQL request performance. For example, in the case that the read-write separation middleware is proxysql, according to the existing proxysql configuration, starting a new proxysql service on a new server, and then loading the new proxysql service to a DBS to realize capacity expansion of the proxysql service; . And adding a proxysql service according to the capacity-expansion proxysql service method, then downloading old proxysql from DBS, and stopping the proxysql service after the proxysql request is ended, so that the proxysql service migration can be realized.
In the embodiment, the database system is set as a management tool for connection between application service and read-write separation middleware, so that the functions of load balancing, automatic online and offline and connection pool are provided, and in addition, the DBS cluster is relatively low in maintenance cost, mature and stable, and large-scale back-end service is supported.
In some embodiments, in the case that the database splitting and table splitting middleware is a shaping-jdbc, the shaping-jdbc receives a data processing instruction, and acquires the fragment connection from the database system according to a preset keyword in the data processing instruction and a database splitting and table splitting strategy, so as to complete the read-write operation. In this embodiment, a storing-jdbc is selected as a repository-splitting and table-splitting middleware, and an application service needs to introduce a storing-jdbc jar package, and meanwhile, a storing-jdbc configuration file is set according to a mysql repository-splitting and table-splitting policy, and under the condition that a data processing instruction is received, a preset keyword of the data, for example, a user ID or a key field of the data is used. The supporting of the shoving-jdbc is cross-server, cross-instance and cross-library table, is suitable for large-scale data storage, can realize the splitting and the table splitting of the lightweight mysql, and is simple and stable to use and low in maintenance cost.
In some of these embodiments, the read-write separation middleware is proxysql:
on the one hand, a plurality of proxysql services are mounted in a database system, and the proxysql services are on line according to a starting connection instruction of the database system or off line according to a disconnection instruction of the database system;
on the other hand, proxysql is used as a middleware, and the main function is to support mysql read-write separation and support one master-slave and one master-multi-slave. In the proxysql configuration file, the master library address is mySQL master library address, all mySQL slave libraries and master library addresses are filled in the slave library addresses, and the split query request can be set through weights, so that the query request is ensured not to be affected. Moreover, proxysql supports online capacity expansion slave libraries and migration slave libraries, and does not affect application service read-write requests. In order to avoid the problem of proxysql single point, at least two proxysql services need to be deployed, the proxysql services are deployed on different servers respectively, meanwhile, the proxysql IP address and port are added into the DBS configuration for load balancing, and a plurality of proxysqls share the traffic evenly.
Under the condition that a slave library capacity expansion instruction is triggered, deploying a new slave library on a new server according to service requirements, logging in a proxysqsl management port, adding a mysql_servers record, and executing the following commands:
load mysql servers to runtime
save mysql servers to disk
under the condition that a slave library migration instruction is triggered, a new proxysql service is started on a new server, the new proxysql service is loaded to a database system, the record of an old slave library in a mysql_servers table is deleted, and the execution is performed:
load mysql servers to runtime
save mysql servers to disk
the old proxysql service is off-line from the database system, and after the data interaction request for the old proxysql service is ended, the old proxysql service is off-line.
In the embodiment, proxysql is adopted as a read-write separation middleware, almost all configurations can be changed online without restarting, the proxysql supports a connection pool function, load balancing can be realized by mounting a plurality of proxysql services at the rear end of a DBS (distributed service system), flow requests are shared by utilizing the connection pool function, and a offline fault proxysql and a proxysql recovered from the online fault can be automatically realized, proxysql migration can be performed based on the DBS, the read-write request is not influenced, and high-performance access mysql is better realized; meanwhile, the automatic reconnection and re-execution mechanism of the proxysql can realize smooth capacity expansion and migration better.
In some embodiments, in the case that a failure of the mySQL main library is detected during the read-write operation, the main library high-availability middleware triggers election and migration of the new mySQL main library comprises: and deploying MHA nodes on the mySQL master library and the mySQL slave library, starting an MHA monitoring service, wherein the MHA monitoring service is used for monitoring the availability of the mySQL master library, and triggering election and migration of a new mySQL master library under the condition that the mySQL master library is detected to be faulty. In this embodiment, the MHA is employed to achieve a high availability of mySQL, optionally mySQL version uses mySQL 8.0 and MHA version uses 0.58. mhAnode software packages are deployed on mysql master and all slave library servers, FIG. 2 is a schematic diagram of the MHA architecture in one embodiment according to the invention, as shown in FIG. 2, with MHA manager software packages deployed on servers where MHA monitoring services reside. Installing the MHA manager package generates a management tool for the MHA to switch the mysql master library. In the MHA configuration file, information of the mysql master library and all slave libraries including IP addresses and ports, mysql file path information, and the like need to be configured, while information of VIP and mysql file paths, and the like, is configured by a master_ip_failover and master_ip_online_change. Executing master_check_reply-conf=app1. Cnf can take effect of VIP, then start MHA monitoring service for monitoring whether mysql master library is available, if mysql master library fails, MHA automatically triggers a switch, elects a new master library, and resumes other slave libraries.
MHA supports a master-slave and a master-multi-slave, which can meet a variety of requirements. With the adoption of the scheme, mysql master library migration can be easily realized. In one aspect, the mysql master library fails to switch automatically, configures the IP addresses, ports, deployment paths, VIP and network card identifiers of the mysql master library and all slave libraries, and then starts the MHA monitoring service. When the MHA monitoring service detects that the mysql master library is unavailable, the fault switching is triggered, a master_ip_failover script is called, a new master library is selected, mysql master-slave replication is restored, the switching time can be controlled automatically, and the level is generally in minutes, so that the mysql master library is high in availability. On the other hand, online migration of mysql master library may be achieved by MHA: stopping MHA monitoring service, configuring mysql master library and all slave library information, executing redisplay switching command, and completing in second level without data loss.
In addition, under the condition of jointly starting the MHA and the proxysql, in the proxysql configuration file, the main library address adopts the MHA VIP address, so that the mysql main library can be ensured to be high in availability; filling all slave libraries and MHA VIP addresses by the slave library addresses, and setting shunt query requests through weights to ensure that the query requests are not affected.
In the embodiment, mysql one master and multiple slaves or one master and one slave are high in availability through MHA, so that the mysql master library fault automatic switching is supported, and meanwhile, the online switching master library and the fault manual switching are also supported.
The embodiments of the present application are described and illustrated below by means of preferred embodiments.
FIG. 3 is a diagram of an architecture of mySQL data sharding method according to one embodiment of the present application, as shown in FIG. 3, including a shrding-jdbc configured application 32, a DBS cluster 34, a proxysql cluster 36, and a mySQL MHA master-slave replication cluster 38:
the application program carries out the shrning-jdbc configuration according to the mysql database splitting strategy, and needs to configure the splitting strategy and access addresses, wherein the access addresses comprise DBS IP addresses and ports.
The DBS cluster provides stable proxy service, supports the load balance and automatic online and offline of the back-end proxysql service, and has the function of connecting a pool, thereby realizing high-performance and high-availability service. The DBS back end configures IP addresses and ports of a plurality of proxysqsl services
Each proxysql service needs to configure master and slave library information of mysql, including master library VIP, slave library IP address and port, and mysql connection setup, read-write separation rules. Description: in order to improve the usability of the read-write request, the read rule configures all the IP addresses and ports of the slave library, the VIP and ports of the master library, the weight of the slave library is set to be larger, and the weight of the VIP of the master library is set to be smaller; in the rule of writing request, the VIP and ports of the master library are configured, so that when the master library is switched or all slave libraries are not available, the reading and writing request is not influenced.
The bottommost layer is a mysql MHA master-slave replication cluster, which can be a set of mysql master-slave clusters or a plurality of mysql master-slave clusters, and each mysql master-slave cluster is provided with a corresponding master library VIP.
Through the scheme, a mature and stable technology is adopted, so that the system has the advantages of high availability, read-write separation, library and table division, load balancing and automatic online and offline functions, and is relatively suitable for being applied to a large-scale production environment.
It should be understood that, although the steps in the flowcharts described above are shown in order as indicated by the arrows, these steps are not necessarily performed in order as indicated by the arrows. The steps are not strictly limited to the order of execution unless explicitly recited herein, and the steps may be executed in other orders. Moreover, at least some of the steps in the flowcharts described above may include a plurality of sub-steps or stages that are not necessarily performed at the same time, but may be performed at different times, and the order of execution of the sub-steps or stages is not necessarily sequential, but may be performed alternately or alternately with at least a part of the sub-steps or stages of other steps or other steps.
The embodiment also provides a mySQL data fragment processing device, which is used for realizing the above embodiment and the preferred implementation, and is not described in detail. As used below, the terms "module," "unit," "sub-unit," and the like may be a combination of software and/or hardware that implements a predetermined function. While the means described in the following embodiments are preferably implemented in software, implementation in hardware, or a combination of software and hardware, is also possible and contemplated.
FIG. 4 is a block diagram of a mySQL data fragment processing device according to one embodiment of the invention, as shown in FIG. 4, comprising: a database and table dividing module 42, a load balancing module 44, a read-write module 46 and a high availability module 48;
the database splitting module 42 is configured to obtain a split connection according to a database splitting policy when performing a read-write operation, where the split connection is provided by a database system, and the split connection indicates a read-write separation middleware for data;
the load balancing module 44 is configured to change the number of available connections in the database system according to the data size of the read-write operation, where the number of connections is the number of fragment connections provided at the same time;
the read-write module 46 is used for writing data into the mySQL main library through the read-write separation middleware through the slice connection or reading data from the mySQL slave library through the read-write separation middleware; under the condition that the slave library capacity expansion instruction is triggered, mySQL slave library capacity expansion is carried out through the read-write separation middleware, and under the condition that the slave library migration instruction is triggered, mySQL slave library migration is carried out through the read-write separation middleware;
the high availability module 48 is configured to trigger election and migration of a new mySQL master library by a high availability middleware of the master library in the case that a failure of the mySQL master library is detected during a read-write operation.
In some embodiments, the load balancing module 44 is further configured to increase the available connection number to a preset first threshold value when the available connection number is less than a preset maximum connection number and the first connection number in the database system is greater than the available connection number, where the first connection number is a number of connections required for the data to be processed in the database system;
and reducing the number of available connections to a preset second threshold value under the condition that the second number of connections is smaller than the number of available connections and is larger than a preset minimum number of connections, wherein the second number of connections is the number of connections required by the data in process.
In some embodiments, the sub-library and sub-table middleware in the sub-library and sub-table module 42 is a standard-jdbc, the read-write separation middleware of the read-write module 46 is proxysql, and the main library high-availability middleware 48 is Master High Availability MHA.
The above-described respective modules may be functional modules or program modules, and may be implemented by software or hardware. For modules implemented in hardware, the various modules described above may be located in the same processor; or the above modules may be located in different processors in any combination.
According to the mySQL data fragment processing device, flexible storage, load balancing and automatic online and offline of data are realized by combining the database system through the database splitting middleware, the problems that read-write separation is not supported and smooth expansion migration is not supported are solved through the read-write separation middleware, the problem that mySQL is not supported by the high-availability middleware of the main database is solved, and the migration fragment processing of mySQL data which can be read-write separation, mySQL is high-availability, load balancing and automatic online and offline and seamless expansion migration is realized.
In one embodiment, FIG. 5 is a schematic diagram of a computer device, which may be a server, and whose internal structure may be as shown in FIG. 5, according to one embodiment of the present invention. The computer device includes a processor, a memory, a network interface, and a database connected by a system bus. Wherein the processor of the computer device is configured to provide computing and control capabilities. The memory of the computer device includes a non-volatile storage medium and an internal memory. The non-volatile storage medium stores an operating system, computer programs, and a database. The internal memory provides an environment for the operation of the operating system and computer programs in the non-volatile storage media. The database of the computer device is used to store mySQL data. The network interface of the computer device is used for communicating with an external terminal through a network connection. The computer program, when executed by a processor, implements a mySQL data fragment processing method.
According to the computer equipment for mySQL data slicing processing, flexible storage, load balancing and automatic online and offline of data are realized by combining the database system through the database splitting middleware, the problems that read-write separation is not supported and smooth expansion migration is not supported are solved through the read-write separation middleware, the problem that mySQL is not supported by the main database high-availability middleware is solved, and the movable slicing processing of mySQL data which can be read-write separation and mySQL high availability, support load balancing and automatic online and offline and support seamless expansion migration is realized.
In addition, in combination with the mySQL data slicing processing method in the above embodiment, the embodiment of the application may be implemented by providing a computer readable storage medium. The computer readable storage medium has stored thereon computer program instructions; the computer program instructions, when executed by a processor, implement any of the mySQL data sharding methods of the embodiments described above.
According to the computer equipment for mySQL data slicing processing, flexible storage, load balancing and automatic online and offline of data are realized by combining the database system through the database splitting middleware, the problems that read-write separation is not supported and smooth expansion migration is not supported are solved through the read-write separation middleware, the problem that mySQL is not supported by the high-availability middleware of the main database is solved, and the slicing processing of mySQL data which can be read-write separation and mySQL is high in availability, load balancing and automatic online and offline and seamless expansion migration is realized.
Those skilled in the art will appreciate that implementing all or part of the above described methods may be accomplished by way of a computer program stored on a non-transitory computer readable storage medium, which when executed, may comprise the steps of the embodiments of the methods described above. Any reference to memory, storage, database, or other medium used in the various embodiments provided herein may include non-volatile and/or volatile memory. The nonvolatile memory can include Read Only Memory (ROM), programmable ROM (PROM), electrically Programmable ROM (EPROM), electrically Erasable Programmable ROM (EEPROM), or flash memory. Volatile memory can include Random Access Memory (RAM) or external cache memory. By way of illustration and not limitation, RAM is available in a variety of forms such as Static RAM (SRAM), dynamic RAM (DRAM), synchronous DRAM (SDRAM), double Data Rate SDRAM (DDRSDRAM), enhanced SDRAM (ESDRAM), synchronous Link DRAM (SLDRAM), memory bus direct RAM (RDRAM), direct memory bus dynamic RAM (DRDRAM), and memory bus dynamic RAM (RDRAM), among others.
The technical features of the above-described embodiments may be arbitrarily combined, and all possible combinations of the technical features in the above-described embodiments are not described for brevity of description, however, as long as there is no contradiction between the combinations of the technical features, they should be considered as the scope of the description.
The above examples merely represent a few embodiments of the present application, which are described in more detail and are not to be construed as limiting the scope of the invention. It should be noted that it would be apparent to those skilled in the art that various modifications and improvements could be made without departing from the spirit of the present application, which would be within the scope of the present application. Accordingly, the scope of protection of the present application is to be determined by the claims appended hereto.
Claims (10)
1. A mySQL data fragment processing method, the method comprising:
when performing read-write operation, obtaining a slicing connection according to a slicing connection strategy, and writing data into a mySQL main library through a read-write separation middleware or reading data from a mySQL slave library through the read-write separation middleware, wherein the slicing connection is provided by a database system, and the slicing connection indicates the read-write separation middleware for the data;
the database system changes the available connection number in the database system according to the data volume of the read-write operation, wherein the available connection number is the number of the slicing connection provided at the same time;
under the condition that the slave library capacity expansion instruction is triggered, mySQL slave library capacity expansion is carried out through the read-write separation middleware, and under the condition that the slave library migration instruction is triggered, mySQL slave library migration is carried out through the read-write separation middleware;
in the read-write operation process, under the condition that the mySQL main library is detected to be faulty, the main library high-availability middleware triggers election and migrates a new mySQL main library;
performing mySQL slave library capacity expansion through the read-write separation middleware comprises deploying a new slave library, and adding an address of the new slave library in the read-write separation middleware; performing mySQL slave library migration through the read-write separation middleware comprises deploying the new slave library, adding an address of the new slave library in the read-write separation middleware, and downloading the old slave library under the condition that no query request is detected on the old slave library.
2. The mySQL data sharding method of claim 1 wherein the database system changing the number of connections available in the database system according to the amount of data of the read-write operation comprises:
increasing the available connection number to a preset first threshold value under the condition that the available connection number is smaller than a preset maximum connection number and a first connection number in the database system is larger than the available connection number, wherein the first connection number is the connection number required by data to be processed in the database system;
and reducing the available connection number to a preset second threshold value under the condition that the second connection number is smaller than the available connection number and is larger than a preset minimum connection number, wherein the second connection number is the connection number required by data in processing.
3. The mySQL data slicing processing method according to claim 1, wherein in the case that the database splitting and table splitting middleware is a shrarding-jdbc, the shrarding-jdbc receives a data processing instruction, and acquires slicing connection from the database system according to a preset keyword in the data processing instruction and the database splitting and table splitting strategy to complete the read-write operation.
4. The mySQL data fragment processing method according to claim 1, wherein, in the case where the read-write separation middleware is proxysql,
mounting a plurality of proxysql services in the database system, and uploading the proxysql services according to a starting connection instruction of the database system or downloading the proxysql services according to a disconnection instruction of the database system;
and under the condition that the slave library capacity expansion instruction is triggered, starting a new proxysql service on a new server, loading the new proxysql service to the database system, under the condition that the slave library capacity expansion instruction is triggered, starting the new proxysql service on the new server, loading the new proxysql service to the database system, and downloading an old proxysql service from the database system, and after the data interaction request for the old proxysql service is finished, downloading the old proxysql service.
5. The mySQL data sharding method of claim 1, wherein in the case of detecting that the mySQL master library fails during the read-write operation, the master library high availability middleware triggers election and migrates a new mySQL master library comprising:
and deploying MHAnode on the mySQL master library and the mySQL slave library, and starting MHA monitoring service, wherein the MHA monitoring service is used for monitoring the availability of the mySQL master library, and triggering election and migration of a new mySQL master library under the condition that the mySQL master library is detected to be faulty.
6. A mySQL data fragment processing apparatus, the apparatus comprising: the system comprises a database and table dividing module, a load balancing module, a read-write module and a high-availability module;
the database splitting and table splitting module is used for acquiring the split connection according to a database splitting and table splitting strategy when performing read-write operation, wherein the split connection is provided by a database system, and the split connection indicates a read-write separation middleware for the data;
the load balancing module is used for changing the available connection number in the database system according to the data volume of the read-write operation, wherein the available connection number is the number of the fragment connection provided at the same time;
the read-write module is used for writing data into the mySQL main library through the read-write separation middleware through the fragment connection or reading data from the mySQL slave library through the read-write separation middleware; under the condition that the slave library capacity expansion instruction is triggered, mySQL slave library capacity expansion is carried out through the read-write separation middleware, and under the condition that the slave library migration instruction is triggered, mySQL slave library migration is carried out through the read-write separation middleware;
the high-availability module is used for triggering election and migration of a new mySQL main library by a high-availability middleware of the main library under the condition that the mySQL main library is detected to be faulty in the read-write operation process;
performing mySQL slave library capacity expansion through the read-write separation middleware comprises deploying a new slave library, and adding an address of the new slave library in the read-write separation middleware; performing mySQL slave library migration through the read-write separation middleware comprises deploying the new slave library, adding an address of the new slave library in the read-write separation middleware, and downloading the old slave library under the condition that no query request is detected on the old slave library.
7. The mySQL data fragment processing apparatus of claim 6, wherein the load balancing module is further configured to increase the number of available connections to a preset first threshold when the number of available connections is less than a preset maximum number of connections and a first number of connections in the database system is greater than the number of available connections, wherein the first number of connections is a number of connections required for data to be processed in the database system;
and reducing the available connection number to a preset second threshold value under the condition that the second connection number is smaller than the available connection number and is larger than a preset minimum connection number, wherein the second connection number is the connection number required by data in processing.
8. The mySQL data fragment processing device according to claim 6, wherein the database and table middleware in the database and table module is a standard-jdbc, the read-write separation middleware in the read-write module is a proxysql, and the master database high available middleware is a MasterHighAvailabilityMHA.
9. A computer device comprising a memory, a processor, and a computer program stored on the memory and executable on the processor, wherein the processor implements the mySQL data shard processing method of any one of claims 1 to 5 when the computer program is executed by the processor.
10. A computer readable storage medium having stored thereon a computer program, which when executed by a processor implements a mySQL data fragment processing method according to any one of claims 1 to 5.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202010216069.3A CN111400285B (en) | 2020-03-25 | 2020-03-25 | mySQL data fragment processing method, device, computer equipment and readable storage medium |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202010216069.3A CN111400285B (en) | 2020-03-25 | 2020-03-25 | mySQL data fragment processing method, device, computer equipment and readable storage medium |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN111400285A CN111400285A (en) | 2020-07-10 |
| CN111400285B true CN111400285B (en) | 2023-06-20 |
Family
ID=71434548
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202010216069.3A Active CN111400285B (en) | 2020-03-25 | 2020-03-25 | mySQL data fragment processing method, device, computer equipment and readable storage medium |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN111400285B (en) |
Families Citing this family (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN112486718A (en) * | 2020-11-30 | 2021-03-12 | 深圳市移卡科技有限公司 | Database fault automatic switching method and device and computer storage medium |
| CN112364104A (en) * | 2021-01-13 | 2021-02-12 | 上海爱可生信息技术股份有限公司 | Distributed database capacity expansion method, distributed database system and computer readable storage medium |
| CN113535430A (en) * | 2021-07-16 | 2021-10-22 | 深圳华锐金融技术股份有限公司 | Application data read-write separation method and device, computer equipment and storage medium |
| CN117453648A (en) * | 2023-10-08 | 2024-01-26 | 广州新赫信息科技有限公司 | High-performance relational database construction method based on trusted chain |
Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| KR20130101349A (en) * | 2012-03-05 | 2013-09-13 | 네이버비즈니스플랫폼 주식회사 | Data management system and method using database middleware |
| CN108984569A (en) * | 2017-06-05 | 2018-12-11 | 中兴通讯股份有限公司 | Database switching method, system and computer readable storage medium |
| CN109344157A (en) * | 2018-09-20 | 2019-02-15 | 深圳市牛鼎丰科技有限公司 | Read and write abruption method, apparatus, computer equipment and storage medium |
| CN110019444A (en) * | 2017-09-08 | 2019-07-16 | 阿里巴巴集团控股有限公司 | A kind of operation requests processing method, device, equipment and system |
| CN110837506A (en) * | 2019-11-07 | 2020-02-25 | 中电福富信息科技有限公司 | Mycat-based data fragmentation and read-write separation method and system |
-
2020
- 2020-03-25 CN CN202010216069.3A patent/CN111400285B/en active Active
Patent Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| KR20130101349A (en) * | 2012-03-05 | 2013-09-13 | 네이버비즈니스플랫폼 주식회사 | Data management system and method using database middleware |
| CN108984569A (en) * | 2017-06-05 | 2018-12-11 | 中兴通讯股份有限公司 | Database switching method, system and computer readable storage medium |
| CN110019444A (en) * | 2017-09-08 | 2019-07-16 | 阿里巴巴集团控股有限公司 | A kind of operation requests processing method, device, equipment and system |
| CN109344157A (en) * | 2018-09-20 | 2019-02-15 | 深圳市牛鼎丰科技有限公司 | Read and write abruption method, apparatus, computer equipment and storage medium |
| CN110837506A (en) * | 2019-11-07 | 2020-02-25 | 中电福富信息科技有限公司 | Mycat-based data fragmentation and read-write separation method and system |
Also Published As
| Publication number | Publication date |
|---|---|
| CN111400285A (en) | 2020-07-10 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN111400285B (en) | mySQL data fragment processing method, device, computer equipment and readable storage medium | |
| US11360854B2 (en) | Storage cluster configuration change method, storage cluster, and computer system | |
| US9361192B2 (en) | Method and apparatus for restoring an instance of a storage server | |
| CN109842651B (en) | Uninterrupted service load balancing method and system | |
| CN110597910A (en) | Remote data synchronization method, device and system | |
| US9703853B2 (en) | System and method for supporting partition level journaling for synchronizing data in a distributed data grid | |
| CN111131451A (en) | Service processing system and service processing method | |
| CN106603319B (en) | Fault processing method, management server and logic server | |
| US20230385244A1 (en) | Facilitating immediate performance of volume resynchronization with the use of passive cache entries | |
| CN113010496B (en) | Data migration method, device, equipment and storage medium | |
| CN112000635A (en) | Data request method, device and medium | |
| CN112202853B (en) | Data synchronization method, system, computer device and storage medium | |
| CN110069365B (en) | Method for managing database and corresponding device, computer readable storage medium | |
| WO2014177085A1 (en) | Distributed multicopy data storage method and device | |
| CN112052230B (en) | Multi-machine room data synchronization method, computing device and storage medium | |
| CN106325768B (en) | A kind of two-shipper storage system and method | |
| CN111935244B (en) | Service request processing system and super-integration all-in-one machine | |
| CN111240901B (en) | Node dynamic expansion system, method and equipment of distributed block storage system | |
| CN107943615B (en) | Data processing method and system based on distributed cluster | |
| CN105323271B (en) | Cloud computing system and processing method and device thereof | |
| CN116389233B (en) | Container cloud management platform active-standby switching system, method and device and computer equipment | |
| CN114363356B (en) | Data synchronization method, system, device, computer equipment and storage medium | |
| CN111176886B (en) | Database mode switching method and device and electronic equipment | |
| WO2020103627A1 (en) | Service self-healing method and device based on virtual machine disaster recovery, and storage medium | |
| CN117201284A (en) | Gateway management method, system, device and medium |
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 |