CN111400285A - MySQ L data fragment processing method, apparatus, computer device and readable storage medium - Google Patents
MySQ L data fragment processing method, apparatus, computer device and readable storage medium Download PDFInfo
- Publication number
- CN111400285A CN111400285A CN202010216069.3A CN202010216069A CN111400285A CN 111400285 A CN111400285 A CN 111400285A CN 202010216069 A CN202010216069 A CN 202010216069A CN 111400285 A CN111400285 A CN 111400285A
- Authority
- CN
- China
- Prior art keywords
- mysq
- read
- library
- data
- middleware
- 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.)
- Granted
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 application relates to a mySQ L data fragment processing method, a device, computer equipment and a computer readable storage medium, the method realizes flexible storage, load balancing and automatic on-off line of data by combining a database system through a database sub-table middleware, solves the problems that read-write separation is not supported and smooth capacity expansion migration is not supported through a read-write separation middleware, solves the problem that mySQ L high availability is not supported through a main database high availability middleware, and realizes the fragment processing that mySQ L data can be read-write separated, mysql high availability, load balancing, automatic on-off line and seamless capacity expansion migration are supported.
Description
Technical Field
The present application relates to the field of databases, and in particular, to a mySQ L data fragment processing method, apparatus, computer device and computer-readable storage medium.
Background
MySQ L is widely applied to the Internet industry as a mainstream relational database, with the increasingly complex business requirements and the increasingly large data volume, the requirements for mySQ L storage and data access are increasingly high, and a feasible scheme is to perform fragment processing on a mySQ L large table.
However, in the related art, the library and table splitting is realized through the client mode, and the library and table splitting is not provided with read-write separation and high availability functions, and is not provided with load balancing and automatic online and offline functions, while the library and table splitting is realized through the server mode, and the library and table splitting is realized through a middleware agent or a software system supporting mySQ L protocol.
At present, no effective solution is provided for the problems that mySQ L data fragmentation processing in the related technology cannot give consideration to read-write separation, mySQ L high availability, load balancing, automatic online and offline and smooth capacity expansion migration.
Disclosure of Invention
The embodiment of the application provides a mySQ L data fragment processing method, a mySQ L data fragment processing device, computer equipment and a computer readable storage medium, and aims to at least solve the problems of mySQ L data fragment processing read-write separation, mySQ L high availability, load balancing, automatic online and offline and smooth capacity expansion migration in the related art.
According to one aspect of the invention, a mySQ L data slicing processing method is provided, and the method comprises the following steps:
when reading and writing operations are carried out, fragment connection is obtained according to a database-partitioning and table-partitioning strategy, data are written into a mySQ L main database through read-write separation middleware through the fragment connection, or the data are read from a mySQ L slave database through the read-write separation middleware, wherein the database-partitioning and table-partitioning strategy is configured in the database-partitioning and table-partitioning middleware, the fragment connection is provided by a database system, and the fragment connection indicates the read-write separation middleware for the data;
the database system changes the number of available connections in the database system according to the data volume of the read-write operation, wherein the number of the available connections is the number of the fragment connections provided at the same time;
under the condition that a slave library capacity expansion instruction is triggered, mySQ L slave library capacity expansion is carried out through read-write separation middleware, and under the condition that a slave library migration instruction is triggered, mySQ L slave library migration is carried out through the read-write separation middleware;
in the event of detecting a failure of the mySQ L master library during read and write operations, election and migration of the new mySQ L master library are triggered.
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 includes:
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, increasing the available connection number to a preset first threshold value, 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 the data in processing.
In some embodiments, when the library-based sub-table middleware is a sharing-jdbc, the sharing-jdbc receives a data processing instruction, acquires fragment connection from a database system according to a keyword preset in the data processing instruction and a library-based sub-table strategy, and completes read-write operation.
In some embodiments, when the read-write separation middleware is proxysql, a plurality of proxysql services are mounted in the database system, and the proxysql services are on-line according to a connection starting instruction of the database system or off-line according to a connection disconnection instruction of the database system;
under the condition that a slave library capacity expansion instruction is triggered, starting a new proxysql service on a new server, loading the new proxysql service to a database system, under the condition that a slave library migration instruction is triggered, starting the new proxysql service on the new server, loading the new proxysql service to the database system, downloading an old proxysql service from the database system, and downloading the old proxysql service after a data interaction request aiming at the old proxysql service is finished.
In some of these embodiments, triggering elections and migrating a new mySQ L master library in the event of a failure of mySQ L master library during read and write operations includes:
deploying MHANode on the mySQ L master library and the mySQ L slave libraries, starting MHA monitoring service, monitoring the availability of the mySQ L master library by the MHA monitoring service, and triggering election and migrating a new mySQ L master library in the case of detecting the failure of the mySQ L master library.
According to another aspect of the invention, the mySQ L data fragment processing device comprises a database and table dividing module, a load balancing module, a read-write module and a high availability module;
the database-dividing and table-dividing module is used for acquiring fragment connection according to a database-dividing and table-dividing strategy when reading and writing operation is carried out, wherein the fragment connection is provided by a database system, and the fragment connection indicates read-write separation middleware for 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 connections provided at the same time;
the read-write module is used for writing data into a mySQ L master library through the read-write separation middleware through fragmentation connection or reading the data from the mySQ L slave library through the read-write separation middleware, carrying out mySQ L slave library expansion through the read-write separation middleware under the condition that a slave library expansion instruction is triggered, and carrying out mySQ L slave library migration through the read-write separation middleware under the condition that a slave library migration instruction is triggered;
the high availability module is used for triggering election and migrating a new mySQ L master library in the case of detecting the failure of the mySQ L master library in the process of read-write operation.
In some embodiments, 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, where the first number of connections is a number of connections 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 the data in processing.
In some embodiments, the library-splitting middleware in the library-splitting and table-splitting module is sharing-jdbc, the read-write splitting middleware in the read-write module is proxysql, and the High-Availability module includes a 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 the mySQ L data slice processing method as described above when executing the computer program.
According to another aspect of the present invention, the present invention also provides a computer readable storage medium, on which a computer program is stored, which when executed by a processor, implements the mySQ L data slice processing method as described above.
Compared with the related technology, the mySQ L data fragment processing method, the apparatus, the computer device and the computer readable storage medium provided by the application realize flexible storage, load balancing and automatic on-off line of data by combining the sub-library and sub-table middleware with the database system, solve the problems that read-write separation is not supported and smooth capacity expansion migration is not supported by the read-write separation middleware, solve the problem that mySQ L is not supported by the main library high-availability middleware, and realize mySQ L data read-write separation and mySQ L high-availability, support load balancing, automatic on-off line 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 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 embodiment(s) of the application and together with the description serve to explain the application and not to limit the application. In the drawings:
FIG. 1 is a flow diagram of a mySQ L data sharding processing method according to 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 an architecture diagram of a mySQ L data slicing method according to an embodiment of the present application;
FIG. 4 is a block diagram of a mySQ L data slice processing device according to one embodiment of the invention;
FIG. 5 is a schematic diagram of a computer device in accordance with one embodiment of the present invention.
Detailed Description
In order to make the objects, technical solutions and advantages of the present application more apparent, the present application will be described and illustrated below with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative of the present application and are not intended to limit the present application. All other embodiments obtained by a person of ordinary skill in the art based on the embodiments provided in the present application without any inventive step are within the scope of protection of the present application.
It is obvious that the drawings in the following description are only examples or embodiments of the present application, and that it is also possible for a person skilled in the art to apply the present application to other similar contexts on the basis of these drawings without inventive effort. Moreover, it should be appreciated that in the development of any such actual implementation, as in any engineering or design project, numerous implementation-specific decisions must be made to achieve the developers' specific goals, such as compliance with system-related and business-related constraints, which may vary from one implementation to another.
Reference in the specification to "an embodiment" means that a particular feature, structure, or characteristic described in connection with the embodiment can be included in at least one embodiment of the specification. The appearances of the phrase 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. Those of ordinary skill in the art will explicitly and implicitly appreciate that the embodiments described herein may be combined with other embodiments without conflict.
Unless defined otherwise, technical or scientific terms referred to herein shall have the ordinary meaning as understood by those of ordinary skill in the art to which this application belongs. Reference to "a," "an," "the," and similar words throughout this application are not to be construed as limiting in number, and may refer to the singular or the plural. The present application is directed to the use of the terms "including," "comprising," "having," and any variations thereof, which are intended to cover non-exclusive inclusions; for example, a process, method, system, article, or apparatus that comprises a list of steps or modules (elements) is not limited to the listed steps or elements, but may include other steps or elements not expressly listed or inherent to such process, method, article, or apparatus. Reference to "connected," "coupled," and the like in this application is not intended to be limited to physical or mechanical connections, but may include electrical connections, whether direct or indirect. The term "plurality" as referred to herein means two or more. "and/or" describes an association relationship of associated objects, meaning that three relationships may exist, for example, "A and/or B" may mean: a exists alone, A and B exist simultaneously, and B exists alone. The character "/" generally indicates that the former and latter associated objects are in an "or" relationship. Reference herein to the terms "first," "second," "third," and the like, are merely to distinguish similar objects and do not denote a particular ordering for the objects.
The mySQ L is a relational database management system, the relational database stores data in different tables instead of putting all data in a large warehouse, the mySQ L data fragmentation processing method provided by the invention can be used between a mySQ L client and a mySQ 2 storage engine to manage and control the data read-write process and improve the data read-write efficiency.
FIG. 1 is a flow chart of a mySQ L data fragment processing method according to one embodiment of the invention, and as shown in FIG. 1, the flow chart comprises the following steps:
step S110, when reading and writing operation is carried out, fragment connection is obtained according to a sub-library and sub-table strategy, data is written into a mySQ L main library through the fragment connection and read from the mySQ L through the read-write separation middleware, or the data is read from the library through the read-write separation middleware, wherein the sub-library and sub-table strategy is configured in the sub-library and sub-table middleware, the fragment connection is provided by a database system, the fragment connection indicates the read-write separation middleware used for the data, in step S110, the application service carries out the read-write operation according to a user instruction, the read-write operation is completed by the sub-library and sub-table middleware, the database system and the read-write separation middleware in a cooperation mode, the application service obtains the fragment connection corresponding to-operated data according to the sub-library and sub-table strategy in the sub-library and sub-table middleware, the fragment connection is one of available connections provided by the database system, the fragment connection provides the connection between the application service and the read-write-read-write middleware, the read-write middleware automatically realizes the read-write separation according to the sub-library and sub-library, the read-TsQQ L, the read-write operation can be read from the sub-library, the Myqhardlibrary can be read from the Myqcat, the read-write separation middleware, the My.
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 fragment connections provided at the same time; in step S120, the database system adjusts the number of available connections according to the amount of read and write data to be processed and the amount of read and write data being processed, for example, when the amount of data to be processed in the database system is large, an idle connection is enabled, and the idle connection is adjusted to be an available connection, so that the database system plays a role in load balancing and adjusts and controls the number of available fragmented connections.
The method comprises the following steps that step S130, under the condition that a slave library capacity expansion instruction is triggered, mySQ L is expanded from a library through read-write separation middleware, under the condition that a slave library migration instruction is triggered, mySQ L is migrated from the library through the read-write separation middleware, and in step S130, the read-write separation middleware also plays a role in smooth capacity expansion and migration of the slave library.
In the step S140, in the process of reading and writing operations, under the condition that mySQ L Master library faults are detected, Master library high-availability middleware triggers election and migration of a new mySQ L Master library, in the step S140, the high availability of the mySQ L Master library is realized through the Master library high-availability middleware, so that the availability of the database can be recovered as soon as possible under the condition that the database faults such as downtime or accidental interruption occur, the downtime is reduced as much as possible, the service is ensured not to be interrupted due to the faults of the database, meanwhile, the data of a non-Master node with 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 is switched, the database content before and after switching should be consistent, the service cannot be influenced due to data loss or data inconsistency, and under the condition that the number of the databases is large, the Master library high-availability middleware of various databases is preferably adopted to ensure the maintainability of the databases, such as the MMM (Master-manager), the M L A, the agents and the like.
In the steps S110 to S140, by using the combined setting of the sub-library and sub-table middleware, the database system, the read-write separation middleware and the main library highly available middleware, flexible storage, load balancing and automatic on-off and off-line of data are realized by combining the sub-library and sub-table middleware with the database system, the problem that read-write separation is not supported and smooth capacity-expansion migration is not supported is solved by the read-write separation middleware, the problem that mySQ L highly available is not supported is solved by the main library highly available middleware, and the processing of mySQ L data that can be read-write separated, mysql highly available, support load balancing, automatic on-off and off-line and support seamless capacity-expansion migration is realized.
In some embodiments, the Database System (DBS) changing the number of available connections in the Database System according to the data amount of the read operation and the write operation includes:
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, increasing the available connection number to a preset first threshold value, 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 the data in processing.
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 into an inventory connection pool, taking N DB connection objects from the inventory connection pool according to a preset minimum connection number N, adding the N DB connection objects into an active DB connection pool after the N DB connection objects are successfully connected, wherein the number of the connection objects in the active DB pool is the available connection number, when data exist in a DB queue, taking a free DB connection object from the active DB connection pool, transmitting the data to the free DB connection object for processing, calculating a first number, wherein the first number is the number of data to be processed in the queue divided by the processing number preset for each connection, when the current connection number is less than the maximum connection number M and the first number is greater than the current connection number, taking a connection object from the inventory connection pool, adding the free DB connection pool after the connection is successfully connected, realizing the increase of the available connection number, optionally, changing the connection number each time by setting, judging whether to increase the available connection number according to the data volume to be processed in the queue, setting, adding a new connection capacity expansion connection pool, and adding the connection object to the new connection pool, and realizing the change of the connection of a new connection object, such as a new connection capacity expansion service, and adding a new connection object, and realizing a new connection management system, and realizing the change, wherein the number of the new connection of the connection pool is equal DB connection of the.
In this embodiment, a database system is set as a management tool for connection between application services and read-write separation middleware, so as to provide functions of load balancing, automatic online and offline and pool connection.
In some embodiments, when the library-based sub-table middleware is a sharing-jdbc, the sharing-jdbc receives a data processing instruction, acquires fragment connection from a database system according to a keyword preset in the data processing instruction and a library-based sub-table strategy, and completes read-write operation. In this embodiment, sharing-jdbc is selected as a database-partitioning middleware, an application service needs to introduce a sharing-jdbc jar package, and meanwhile, a sharing-jdbc configuration file is set according to a mysql database-partitioning policy, and under the condition that a data processing instruction is received, according to a preset keyword of the data, for example, a user ID or a key field of the data. The Sharding-jdbc supports cross-server, cross-instance and cross-library tables, is suitable for large-scale data storage, can realize light-weight mysql sub-library and sub-table, and is simple and stable to use and low in maintenance cost.
In some embodiments, the read-write separation middleware is proxysql:
on one hand, a plurality of proxysql services are mounted in the database system, and the proxysql services are on-line according to a starting connection instruction of the database system or are off-line according to a disconnection instruction of the database system;
in the Proxysql configuration file, the master library address is the master library address of mySQ L, all mySQ L slave libraries and master library addresses are filled in from the library addresses, and a shunting query request can be set through weight to ensure that the query request is not influenced.
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, then logging in a proxysqsl management port, adding mysql _ servers records, and then 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, then the record of an old slave library in a mysql _ servers table is deleted, and the following steps are executed:
load mysql servers to runtime
save mysql servers to disk
and downloading the old proxysql service from the database system, and downloading the old proxysql service after the data interaction request aiming at the old proxysql service is finished.
In this embodiment, proxysql is used as a read-write separation middleware, almost all configurations can be changed on line without restarting, and proxysql supports a connection pool function, so that a plurality of proxysql services can be mounted at the back end of a DBS more efficiently, load balancing and flow requests can be realized by using the connection pool function, off-line fault proxysql and on-line proxysql recovered from the fault can be automatically performed, proxysql migration can be performed on the basis of DBS, read-write requests are not affected, and high-performance access mysql is better realized; meanwhile, smooth capacity expansion and migration can be better realized by the automatic reconnection and re-execution mechanism of proxysql.
In some embodiments, during read and write operations, upon detecting failure of mySQ L master library, the master library highly available middleware triggers election and migration of new mySQ L master library includes deploying MHA nodes from the master library at mySQ L and mySQ L, initiating MHA monitoring services, which monitors mySQ L master library availability, upon detecting failure of mySQ L master library, triggers election and migration of new mySQ L master library in this embodiment, MHA is used to make high availability of mySQ L, optionally Mysql version uses Mysql 8.0, MHA version uses 0.58. MHA host software package is deployed on the master library and all slave library servers, fig. 2 is a schematic diagram of an embodiment of the MHA architecture according to the present invention, as shown in fig. 2, MHA manager software package is deployed on the MHA monitoring server, MHA manager software package is installed, a management software package is installed on MHA monitoring server, a management software package is used to perform MHA switch, a path configuration information is used to configure mh a map, and configure a slave library, and other map library is used to perform path switching, and other map information, if MHA route switch occurs, and a route switch is needed to monitor mhqae.
The MHA supports one master and one slave and one master and multiple slaves, and can meet various requirements. By using the scheme, mysql master library migration can be easily realized. On one hand, the mysql master library is automatically switched in a failure mode, IP addresses, ports, deployment paths, VIPs and network card identifications of the mysql master library and all slave libraries are configured, and then MHA monitoring service is started. When the MHA monitoring service detects that the mysql master library is unavailable, fault switching is triggered, a master _ ip _ failover script is called, a new master library is selected, mysql master-slave copying is recovered, switching time can be controlled automatically and is generally in the minute level, and therefore high availability of the mysql master library is achieved. On the other hand, online migration of mysql master libraries may be achieved by MHA: and stopping the MHA monitoring service, configuring the information of the mysql master library and all slave libraries, and executing a redisplay switching command, wherein the redisplay switching command can be completed in a second level without causing data loss.
In addition, under the condition that the MHA and the proxysql are jointly enabled, the main library address adopts an MHA VIP address in the proxysql configuration file, so that the mysql main library is guaranteed to be highly available; all slave libraries and MHA VIP addresses are filled in from library addresses, and the shunting inquiry requests can be set through weights, so that the inquiry requests are not influenced.
In the embodiment, high availability of mysql one master and multiple slaves or one master and one slave can be realized through the MHA, and fault automatic switching of the mysql master library is supported, and meanwhile, on-line switching of the master library and 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 an architecture diagram of a mySQ L data fragment processing method according to an embodiment of the present application, and as shown in fig. 3, the architecture of the mySQ L data fragment processing method includes a mapping-jdbc configured application 32, a DBS cluster 34, a proxysql cluster 36, and a mysql MHA master-slave copy cluster 38:
and the application program performs sharing-jdbc configuration according to the mysql database-partitioning strategy, wherein the table-partitioning strategy needs to be configured, and the access address comprises the DBS IP address and the port.
The DBS cluster provides stable proxy service, supports the functions of load balancing, automatic online and offline and pool connection of the backend proxysql service, and accordingly realizes 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 library and slave library information of mysql, including a master library VIP, a slave library IP address and a port, and mysql connection setting, read-write separation rules. Description of the drawings: in order to improve the availability of read-write requests, read rules configure all slave library IP addresses and ports, and master library VIPs and ports, the slave library is set to be heavier in weight, and the master library VIP is set to be smaller in weight; in the write request rule, the VIP and the port of the main library are configured, so that when the main library is switched or all the auxiliary libraries are unavailable, the read-write request is not influenced.
The bottom 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.
By adopting the scheme and adopting a mature and stable technology, the functions of high availability, read-write separation, warehouse division and table division, load balance and automatic online and offline are provided, and the method is more suitable for being applied to large-scale production environments.
It should be understood that, although the steps in the above-described flowcharts are shown in order as indicated by the arrows, the steps are not necessarily performed in order as indicated by the arrows. The steps are not performed in the exact order shown and described, and may be performed in other orders, unless explicitly stated otherwise. Moreover, at least a portion of the steps in the above-described flowcharts may include multiple sub-steps or multiple stages, which are not necessarily performed at the same time, but may be performed at different times, and the order of performing the sub-steps or the stages is not necessarily sequential, but may be performed alternately or alternatingly with other steps or at least a portion of the sub-steps or stages of other steps.
The present embodiment also provides a mySQ L data fragment processing apparatus, which is used for implementing the above embodiments and preferred embodiments, and has been described in detail, as used in the following, the terms "module", "unit", "sub-unit", and the like can implement a combination of software and/or hardware of predetermined functions.
FIG. 4 is a block diagram of the mySQ L data fragment processing device according to one embodiment of the present invention, as shown in FIG. 4, the device includes a sub-base sub-table module 42, a load balancing module 44, a read-write module 46 and a high availability module 48;
the database-dividing and table-dividing module 42 is configured to obtain a fragment connection according to a database-dividing and table-dividing policy when performing read-write operation, where the fragment connection is provided by a database system and indicates read-write separation middleware for data;
the load balancing module 44 is configured to change, by the database system, the number of available connections in the database system according to the data amount of the read-write operation, where the number of connections is the number of fragmented connections provided at the same time;
the read-write module 46 is used for writing data into the mySQ L master library through the read-write separation middleware through fragmentation connection or reading the data from the mySQ L slave library through the read-write separation middleware, carrying out mySQ L slave library expansion through the read-write separation middleware under the condition that a slave library capacity expansion instruction is triggered, and carrying out mySQ L slave library migration through the read-write separation middleware under the condition that a slave library migration instruction is triggered;
the high availability module 48 is used to trigger election and migration of the new mySQ L master library by master library high availability middleware in the event that failure of the mySQ L master library is detected during read and write operations.
In some embodiments, the load balancing module 44 is further configured to increase the number of available connections to a preset first threshold if 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, where the first number of connections is the number of connections required by the 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 the data in processing.
In some embodiments, the library and table middleware in the library and table module 42 is sharing-jdbc, the read-write separation middleware in the read-write module 46 is proxysql, and the Master library high availability middleware 48 is Master highhavailability MHA.
The above modules may be functional modules or program modules, and may be implemented by software or hardware. For a module implemented by hardware, the modules may be located in the same processor; or the modules can be respectively positioned in different processors in any combination.
According to the mySQ L data fragment processing device, the sub-database and sub-table middleware is combined with a database system to achieve flexible storage, load balancing and automatic on-line and off-line of data, 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 mySQ L is not supported to be highly available is solved through the main-database highly available middleware, and fragment transfer processing that mySQ L data can be read-write separated, mySQ L is highly available, load balancing and automatic on-line and off-line are supported, and seamless expansion migration is supported is achieved.
In one embodiment, FIG. 5 is a schematic diagram of a computer device according to one embodiment of the present invention, which provides a computer device, the computer device may be a server, an internal structure diagram of which may be as shown in FIG. 5, 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 used for providing computing and control capabilities.
According to the computer equipment for processing mySQ L data fragments, flexible storage, load balancing and automatic on-line and off-line of data are achieved through the sub-library and sub-table middleware and the database system, 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 mySQ L is not supported to be highly available is solved through the main library highly available middleware, and fragment transfer processing that mySQ L data can be read-write separated, mySQ L is highly available, load balancing and automatic on-line and off-line are supported, and seamless expansion migration is supported is achieved.
In addition, in combination with the mySQ L data fragment processing method in the above embodiment, the embodiment of the application can be implemented by providing a computer readable storage medium, wherein the computer readable storage medium stores computer program instructions, and the computer program instructions, when executed by a processor, implement any one of the mySQ L data fragment processing methods in the above embodiments.
According to the computer equipment for processing mySQ L data fragments, flexible storage, load balancing and automatic on-line and off-line of data are achieved through the sub-library and sub-table middleware and the database system, 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 mySQ L is not supported to be highly available is solved through the main library highly available middleware, and fragment moving processing that mySQ L data can be read-write separated, mysql is highly available, load balancing and automatic on-line and off-line are supported and seamless expansion migration is supported is achieved.
It will be understood by those of ordinary skill in the art that all or a portion of the processes of the methods of the embodiments described above may be implemented by a computer program that may be stored on a non-volatile computer-readable storage medium, which when executed, may include the processes of the embodiments of the methods described above, wherein any reference to memory, storage, database, or other medium used in the embodiments provided herein may include non-volatile and/or volatile memory.
The technical features of the embodiments described above may be arbitrarily combined, and for the sake of brevity, all possible combinations of the technical features in the embodiments described above are not described, but should be considered as being within the scope of the present specification as long as there is no contradiction between the combinations of the technical features.
The above-mentioned embodiments only express several embodiments of the present application, and the description thereof is more specific and detailed, but not construed as limiting the scope of the invention. It should be noted that, for a person skilled in the art, several variations and modifications can be made without departing from the concept of the present application, which falls within the scope of protection of the present application. Therefore, the protection scope of the present patent shall be subject to the appended claims.
Claims (10)
1. A mySQ L data fragment processing method is characterized by comprising the following steps:
when reading and writing operations are carried out, fragment connection is obtained according to a library division and table division strategy, data are written into a mySQ L main library through a read-write separation middleware through the fragment connection, or the data are read from a mySQ L slave library through the read-write separation middleware, wherein the library division and table division strategy is configured in the library division and table division middleware, the fragment connection is provided by a database system, and the fragment connection indicates the read-write separation middleware for the data;
the database system changes the number of available connections in the database system according to the data volume of the read-write operation, wherein the number of the available connections is the number of the fragment connections provided at the same time;
under the condition that a slave library capacity expansion instruction is triggered, mySQ L slave library capacity expansion is carried out through the read-write separation middleware, and under the condition that a slave library migration instruction is triggered, mySQ L slave library migration is carried out through the read-write separation middleware;
during the read-write operation, master library high availability middleware triggers election and migration of a new mySQ L master library in the event that failure of the mySQ L master library is detected.
2. The mySQ L data fragment processing method of claim 1, wherein the database system changing the number of available connections in the database system according to the data volume of the read operation and the write operation comprises:
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, increasing the available connection number to a preset first threshold value, 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 the data in processing.
3. The mySQ L data fragment processing method according to claim 1, wherein, when the library-table-splitting middleware is a sharing-jdbc, the sharing-jdbc receives a data processing instruction, acquires fragment connection from the database system according to a keyword preset in the data processing instruction and the library-table-splitting policy, and completes the read-write operation.
4. The mySQ L data fragment processing method according to claim 1, wherein in the case that the separation middleware is proxysql,
mounting a plurality of proxysql services in the database system, and performing online proxysql services according to a starting connection instruction of the database system or performing offline proxysql services according to a disconnection instruction of the database system;
starting a new proxysql service on a new server and 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 and loading the new proxysql service to the database system under the condition that the slave library migration instruction is triggered, downloading an old proxysql service from the database system, and downloading the old proxysql service after a data interaction request aiming at the old proxysql service is finished.
5. The mySQ L data fragmentation processing method of claim 1, wherein the master library high availability middleware triggering election and migrating a new mySQ L master library in case of detecting failure of the mySQ L master library during the read-write operation comprises:
deploying MHA nodes on the mySQ L master library and the mySQ L slave libraries, starting MHA monitoring service, wherein the MHA monitoring service is used for monitoring the availability of the mySQ L master library, and triggering election and transferring a new mySQ L master library in the case of detecting that the mySQ L master library fails.
6. The mySQ L data fragment processing device is characterized by comprising a database and table dividing module, a load balancing module, a read-write module and a high availability module;
the database-dividing and table-dividing module is used for acquiring fragment connection according to a database-dividing and table-dividing strategy when reading and writing operation is carried out, wherein the fragment connection is provided by a database system, and the fragment connection indicates read-write separation middleware for the data;
the load balancing module is used for the database system to change the number of available connections in the database system according to the data volume of the read-write operation, wherein the number of connections is the number of the fragmented connections provided at the same time;
the read-write module is used for writing data into a mySQ L master library through the read-write separation middleware through the fragment connection or reading the data from the mySQ L slave library through the read-write separation middleware, carrying out mySQ L slave library expansion through the read-write separation middleware under the condition that a slave library expansion instruction is triggered, and carrying out mySQ L slave library migration through the read-write separation middleware under the condition that a slave library migration instruction is triggered;
the high availability module is used for triggering election and migrating a new mySQ L master library by master library high availability middleware in the case of detecting that the mySQ L master library fails in the read-write operation process.
7. The mySQ L data slice 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 if 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 the 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 the data in processing.
8. The mySQ L data fragment processing device of claim 6, wherein the banking and sublist middleware in the banking and sublist module is sharding-jdbc, the read-write separation middleware in the read-write module is proxysql, and the Master High Availability middleware is Master High Availability MHA.
9. A computer apparatus comprising a memory, a processor and a computer program stored on the memory and executable on the processor, wherein the processor when executing the computer program implements the mySQ L data slice processing method of any one of claims 1-5.
10. A computer readable storage medium having stored thereon a computer program, wherein the program, when executed by a processor, implements the mySQ L data slice processing method of 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 true CN111400285A (en) | 2020-07-10 |
| CN111400285B 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) |
Cited By (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN112364104A (en) * | 2021-01-13 | 2021-02-12 | 上海爱可生信息技术股份有限公司 | Distributed database capacity expansion method, distributed database system and computer readable storage medium |
| CN112486718A (en) * | 2020-11-30 | 2021-03-12 | 深圳市移卡科技有限公司 | Database fault automatic switching method and device and computer storage medium |
| CN113535430A (en) * | 2021-07-16 | 2021-10-22 | 深圳华锐金融技术股份有限公司 | Application data read-write separation method and device, computer equipment and storage medium |
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 |
Cited By (3)
| 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 |
Also Published As
| Publication number | Publication date |
|---|---|
| CN111400285B (en) | 2023-06-20 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN109729129B (en) | Configuration modification method of storage cluster system, storage cluster and computer system | |
| US20190394266A1 (en) | Cluster storage system, data management control method, and non-transitory computer readable medium | |
| CN110224871B (en) | High-availability method and device for Redis cluster | |
| US7058731B2 (en) | Failover and data migration using data replication | |
| US8688773B2 (en) | System and method for dynamically enabling an application for business continuity | |
| US9753761B1 (en) | Distributed dynamic federation between multi-connected virtual platform clusters | |
| US7424547B2 (en) | File sharing device and inter-file sharing device data migration method | |
| US8875134B1 (en) | Active/active storage and virtual machine mobility over asynchronous distances | |
| CN103618627B (en) | A kind of manage the method for virtual machine, Apparatus and system | |
| US9454417B1 (en) | Increased distance of virtual machine mobility over asynchronous distances | |
| CN111400285A (en) | MySQ L data fragment processing method, apparatus, computer device and readable storage medium | |
| US20230308507A1 (en) | Commissioning and decommissioning metadata nodes in a running distributed data storage system | |
| US20040243650A1 (en) | Shared nothing virtual cluster | |
| US8726083B1 (en) | Synchronized taking of snapshot memory images of virtual machines and storage snapshots | |
| US20210081287A1 (en) | Data service failover in shared storage clusters | |
| CN109446169B (en) | Double-control disk array shared file system | |
| CN113010496B (en) | Data migration method, device, equipment and storage medium | |
| CN113268472B (en) | Distributed data storage system and method | |
| WO2018171728A1 (en) | Server, storage system and related method | |
| US20040210888A1 (en) | Upgrading software on blade servers | |
| US20200387575A1 (en) | Migrating virtual machines using asynchronous transfer and synchronous acceleration | |
| CN105323271B (en) | Cloud computing system and processing method and device thereof | |
| US9805049B1 (en) | Migrating applications over asynchronous distances using semi-synchronous acceleration | |
| US10884881B2 (en) | Scale-out storage system and configuration information control method for implementing high-availability, high-speed failover | |
| JP5956364B2 (en) | Cluster system |
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 |