CN114153858B

CN114153858B - Method and device for automatically updating tenant under MySQL table space

Info

Publication number: CN114153858B
Application number: CN202111422989.1A
Authority: CN
Inventors: 史宗耀; 汤泽; 景娜; 韩沙日拉
Original assignee: Taikang Insurance Group Co Ltd; Taikang Pension Insurance Co Ltd
Current assignee: Taikang Insurance Group Co Ltd; Taikang Pension Insurance Co Ltd
Priority date: 2021-11-26
Filing date: 2021-11-26
Publication date: 2024-07-12
Anticipated expiration: 2041-11-26
Also published as: CN114153858A

Abstract

The present invention provides a method and a device for automatically updating tenant under MySQL table space, comprising: determining the maximum update sequence number of MySQL script of each service; for each service, scanning a tenant table space associated with the service in a MySQL table space pool, and executing MySQL script update on the tenant table space associated with the service; judging whether the current execution sequence number of the MySQL script of each service is the same as the maximum update sequence number; if the MySQL script is the same, the MySQL script upgrading of the service is confirmed to be completed; the method and the device realize unified upgrading of the MySQL script of the service by determining the same service of all tenant tablespaces in the MySQL tablespace pool, solve the problem that all services associated with each tenant are required to be independently upgraded under the MySQL tablespace pool due to tablespace data isolation, reduce the development, operation and maintenance cost of the MySQL tablespace pool and improve the reliability of multi-tenant upgrading.

Description

Method and device for automatically updating tenant under MySQL table space

Technical Field

The invention relates to the technical field of big data, and can be applied to the field of insurance, in particular to a method and a device for automatically updating tenants under a MySQL table space.

Background

An enterprise service portal is a manager system that provides company-side business data for different enterprise clients, with the primary business involving supplemental medical funds, employee welfare, health management data, and enterprise annuity data, among others.

In the system layer, a customizable service is provided for different enterprises by a single system instance to form a multi-tenant operation environment, each enterprise is equivalent to one tenant, each tenant can carry out personalized configuration on a rented system or a table space according to requirements, or unified configuration provided by an operator can be used, the technology can realize sharing of the system instance among a plurality of tenants, personalized customization of the tenant system instance can be realized, sharing of common parts of the system is guaranteed, and individual parts are isolated independently.

In the storage layer, the commonly used underlying library is MySQL, which can realize isolation between tenant tablespaces, and ensure the security and privacy of user information, because in the tablespace pool, a plurality of tablespaces corresponding to tenants are operated, each tablespace is associated with a plurality of services, wherein if one service needs to be updated, the tablespace associated with the service needs to be acquired, and each tablespace associated with the service is updated independently, and the problem is that when the tenants in the tablespace pool are too many, the time cost and the labor cost for updating the service are high.

Disclosure of Invention

Aiming at the problems in the prior art, the aim of the present invention is to provide a method and a device for automatically updating a tenant under a MySQL tablespace, so as to solve the technical problem that in the prior art, when updating a service due to MySQL tablespace data isolation, each service or a tablespace related to the service needs to be updated independently, and the specific technical scheme of the present invention is as follows:

in one aspect, provided herein is a method for tenant automatic update under MySQL tablespace, comprising:

determining the maximum update sequence number of MySQL script of each service;

for each service, scanning a tenant table space associated with the service in a MySQL table space pool, and executing MySQL script update on the tenant table space associated with the service;

judging whether the current execution sequence number of the MySQL script of each service is the same as the maximum update sequence number;

If the MySQL script is the same, the update of the MySQL script of the service is confirmed to be completed;

and the current execution sequence number is determined according to the MySQL script upgrading times of executing the service in the MySQL table space pool.

As one embodiment of the present disclosure, the determining whether the current execution sequence number of the MySQL script of each service is the same as the maximum update sequence number further includes:

If not, acquiring the tenant table space sequence number of the service related between the current execution sequence number and the maximum update sequence number;

and sequentially executing MySQL script updating on the tenant table space corresponding to the tenant table space sequence number.

As one embodiment herein, before determining the maximum update sequence number of MySQL script for each service, the method further includes:

regularly monitoring the quantity of free tablespaces in the MySQL tablespace pool;

When the number of the free table spaces is smaller than the free threshold value, starting a space creating thread to create the free table spaces;

and if the number of the free table spaces is larger than the free threshold value, acquiring the latest MySQL script.

As one embodiment herein, the executing MySQL script update on the tenant tablespace associated with the service further comprises:

When the MySQL script of the tenant table space associated with the service is successfully updated, changing an index corresponding to the MySQL script after the tenant table space is updated in a transaction file;

When the MySQL script updating of the tenant table space associated with the service fails, rolling back the transaction file to a more previous state, and executing MySQL script updating on the next tenant table space corresponding to the tenant table space;

wherein the transaction file is created according to the service type of the service.

As an embodiment herein, the method further comprises:

When MySQL script updating of different services is executed on a single tenant table space, single thread execution is used;

When MySQL script updating of different services is executed for a plurality of tenant table spaces, multithreading parallel execution is used;

Wherein the number of threads is less than the number of tenant tablespaces.

As one embodiment herein, when the MySQL script updating of different services is performed on multiple tenant tablespaces, the method further includes:

sorting tenants in the MySQL table space pool by using a sorting algorithm;

And executing multithreaded MySQL script updating on the tenant table space in the MySQL table space pool according to the sequencing result.

As an embodiment herein, the method further comprises:

If one service is a general service, acquiring the number of tenant tablespaces in the MySQL tablespace pool;

And executing MySQL script updating on the tenant table spaces in the MySQL table space pool in sequence according to the tenant table space quantity.

In another aspect, the present disclosure further provides an apparatus for automatically updating a tenant under MySQL tablespace, including:

the serial number determining unit is used for determining the maximum update serial number of the MySQL script of each service;

the script execution unit is used for scanning the tenant table space associated with the service in the MySQL table space pool for each service and executing MySQL script update on the tenant table space associated with the service;

The serial number judging unit is used for judging whether the current execution serial number of the MySQL script of each service is the same as the maximum update serial number;

an instruction sending unit, configured to send an update completion instruction of the MySQL script of the service when the current execution sequence number is the same as the maximum update sequence number;

In another aspect, there is provided a computer device including a memory, a processor, and a computer program stored on the memory and executable on the processor, the processor implementing the method for automatically updating a tenant under MySQL tablespace of any one of the above when executing the computer program.

In another aspect, there is provided a computer readable storage medium storing a computer program which, when executed by a processor, implements the method for automatically updating tenants under MySQL tablespace of any one of the above.

By adopting the technical scheme, the MySQL scripts of the service are uniformly upgraded by determining the same service of all tenant tablespaces in the MySQL tablespace pool, so that the problem that all services associated with each tenant are required to be independently upgraded under the MySQL tablespace pool due to tablespace data isolation is solved, the development and operation cost of the MySQL tablespace pool is reduced, and the reliability and the effectiveness of multi-tenant upgrading are obviously improved.

The foregoing and other objects, features and advantages will be apparent from the following more particular description of preferred embodiments, as illustrated in the accompanying drawings.

Drawings

In order to more clearly illustrate the embodiments herein or the technical solutions in the prior art, the drawings that are required in the embodiments or the description of the prior art will be briefly described below, it being obvious that the drawings in the following description are only some embodiments herein and that other drawings may be obtained according to these drawings without inventive effort to a person skilled in the art.

FIG. 1 illustrates an overall system diagram of tenant automatic updates under MySQL tablespaces of embodiments herein;

FIG. 2 is a schematic diagram illustrating steps of a method for tenant automatic update under MySQL tablespace according to an embodiment herein;

Fig. 3 illustrates a breakpoint resume schematic of a method for tenant automatic update under MySQL tablespace according to an embodiment herein;

FIG. 4 illustrates a schematic diagram of an apparatus for tenant automatic update under MySQL tablespace in accordance with an embodiment herein;

FIG. 5 is a detailed schematic diagram of an apparatus for tenant automatic update under MySQL tablespace according to an embodiment herein;

FIG. 6 is a schematic diagram of an algorithm unit of an apparatus for tenant automatic update under MySQL tablespace according to an embodiment herein;

FIG. 7 illustrates a data flow diagram of an overall system for tenant automatic update under MySQL tablespace of embodiments herein;

fig. 8 shows a schematic diagram of a computer device according to embodiments herein.

Description of the drawings:

101. A control terminal;

102. updating an engine;

103. a database;

401. a sequence number determining unit;

402. A script execution unit;

403. a serial number judgment unit;

404. An instruction transmitting unit;

501. A monitoring unit;

502. A thread starting unit;

503. A script acquisition unit;

601. an algorithm unit;

802. A computer device;

804. A processor;

806. A memory;

808. A driving mechanism;

810. an input/output module;

812. An input device;

814. an output device;

816. a presentation device;

818. A graphical user interface;

820. a network interface;

822. a communication link;

824. a communication bus.

Detailed Description

The following description of the embodiments of the present disclosure will be made clearly and fully with reference to the accompanying drawings, in which it is evident that the embodiments described are only some, but not all embodiments of the disclosure. All other embodiments, based on the embodiments herein, which a person of ordinary skill in the art would obtain without undue burden, are within the scope of protection herein.

It should be noted that the terms "first," "second," and the like in the description and claims herein and in the foregoing figures are used for distinguishing between similar objects and not necessarily for describing a particular sequential or chronological order. It is to be understood that the data so used may be interchanged where appropriate such that the embodiments described herein may be capable of operation in sequences other than those illustrated or described herein. Furthermore, the terms "comprises," "comprising," and "having," and any variations thereof, are intended to cover a non-exclusive inclusion, such that a process, method, apparatus, article, or device that comprises a list of steps or elements is not necessarily limited to those steps or elements expressly listed or inherent to such process, method, article, or device.

The data (including, but not limited to, data for analysis, data stored, data displayed, etc.) related to the present application are information and data authorized by the user or sufficiently authorized by each party.

An enterprise service portal is taken as one of enterprise service systems, and mainly relates to providing B-end service for enterprise tenants, and main services provided for the tenants comprise supplementary medical funds, employee benefit information, employee health management information, enterprise annual fee and the like.

Because the financial insurance application involves the requirements of high risk and high security, when storing the data of each tenant, an independent table space needs to be created in the database to perform data isolation, because one tenant may purchase several kinds of services, when a certain class of service is upgraded, an operation and maintenance personnel is required to query the background data of the enterprise service portal, query the tenant who purchases the service out, and determine the table space corresponding to the tenant who purchases the service in the database, each tenant who purchases the service generates an association between the tenant table space in the database and the purchased service, then, the table space portal associated with the service is updated independently, when the tenant in the enterprise service portal is fewer, the update can be performed in a way of maintaining and upgrading alone, but when the tenant in the enterprise service portal is more, the way of maintaining and updating alone is obviously inefficient, and consumes manpower and material resources.

Through the analysis, when the enterprise service portal is operated and maintained, when each tenant table space is independently maintained, the enterprise service portal product is purchased for a small number of tenants, and the operation is easy.

However, when a large number of tenants purchase enterprise service portal products, it is very difficult and inefficient to conduct tenant table space maintenance alone when the business is updated.

Therefore, a method is needed to obtain the information of all the businesses in the MySQL tablespace pool corresponding to the enterprise service portal, and perform the update operation on all the tenant tablespaces associated with the businesses according to the information.

It should be noted that, the updating mode adopted in this document is to use MySQL script to execute updating of a certain service, replace the old version of MySQL script with the new version of MySQL script at a corresponding position in the path of the service, in the same way, also write the restful interface or Java class into the MySQL script, or cooperate with the MySQL script, execute upgrading, updating or downgrading of the service in association, for example, use the MySQL script and the restful interface to realize invoking data outside the enterprise service portal, redirect the data into the enterprise service portal, and import the data into the tenant table space to realize updating of the service.

An overall system diagram for tenant automatic update under MySQL tablespace as shown in fig. 1, comprising: a control terminal 101, an update engine 102, and a database 103;

It should be noted that MySQL table space is a virtual space, which is equivalent to a container, and when a tenant purchases an enterprise service portal product of this document, the tenant can establish a corresponding relationship with MySQL table space, and when an operation and maintenance person operates and maintains a product or service purchased by the tenant, it is equivalent to operating and maintaining a service in the MySQL table space purchased by the tenant, and for convenience of description, the MySQL table space purchased by the tenant is simply referred to as a tenant table space.

The control terminal 101 may implement generation of a new tenant, for example, when an enterprise purchases a service of an enterprise service portal, the control terminal 101 may input information related to the enterprise, and after the input is completed, the control terminal 101 sends an instruction to the database 103, the database 103 associates the enterprise information with an idle table space, and invokes a plurality of MySQL scripts to the update engine 102, in this way, the idle table space is started, and the tenant is serviced.

The control terminal 101 may also implement MySQL script generation, for example, when an operator of an enterprise service portal needs to update a certain service, after the MySQL script is written by the control terminal 101 according to an update requirement, the MySQL script is temporarily stored in the update engine 102, because the operator may update several services in one day, several MySQL scripts may be temporarily stored in the update engine 102, when reaching a preset time, the update engine 102 releases all MySQL scripts and sends the MySQL scripts to a MySQL tablespace pool corresponding to the enterprise service portal, that is, the database 103, and it should be noted that the MySQL tablespace pool may be created by several groups of servers, or may be created by one server, and the actual carrier of the MySQL tablespace pool is not limited herein.

In the enterprise service portal, there are some services that require payment from a tenant, and there are some necessary general services, that is, the tenant can enjoy without payment, and these basic items affect the information security of the tenant, or when there are some essential basic files, when there is a need to update the general service, it is necessary to update all tenants in the enterprise service portal, whether the client knows or not, and it is necessary to traverse in the background.

Through the whole system, mySQL scripts can be generated, new user creation of an enterprise service portal can be realized, mySQL table spaces of all tenants of the enterprise service portal can be updated when general service is updated, mySQL table spaces related to certain service can be updated uniformly when service of certain type is updated, operation and maintenance efficiency is greatly improved, and labor cost is reduced.

When the service in the MySQL table space pool is updated, the MySQL table space pool is influenced by the fact that data isolation is needed by taking the MySQL table space as the underlying logic, and when the service is updated, each tenant table space related to the service is required to be updated independently, so that the service updating efficiency is greatly influenced, and the operation is labor-consuming and easy to make mistakes.

In order to solve the above problem, embodiments herein provide a method for automatically updating tenants under MySQL tablespaces, which can uniformly update all tenant tablespaces associated with a service, thereby improving the updating efficiency and being not prone to error.

Fig. 2 is a schematic diagram of steps of a method for tenant automatic updating under MySQL tablespace provided in the embodiments herein, which provides the method operation steps described in the examples or flowcharts, but may include more or fewer operation steps based on conventional or non-creative labor. The order of steps recited in the embodiments is merely one way of performing the order of steps and does not represent a unique order of execution. When a system or apparatus product in practice is executed, it may be executed sequentially or in parallel according to the method shown in the embodiments or the drawings. As shown in fig. 2, the method may include:

Step 201, determining the maximum update sequence number of MySQL script of each service.

Step 202, for each service, scanning the tenant tablespace associated with the service in the MySQL tablespace pool, and executing MySQL script update on the tenant tablespace associated with the service.

And 203, judging whether the current execution sequence number of the MySQL script of each service is the same as the maximum update sequence number.

And 204, if the service is the same, confirming that the MySQL script of the service is updated.

By the method, all tenant table spaces associated with a certain service in the MySQL table space pool are obtained according to the maximum update sequence number, and MySQL script update is traversed and executed according to the comparison relation of the maximum update sequence number and the maximum execution sequence number, so that service upgrading efficiency in the MySQL table space pool is improved.

It should be noted that, in this document, the maximum update sequence number is obtained by querying MySQL table spaces associated with the services to be updated in the control terminal 101 by the operation and maintenance personnel in generating some fields of the MySQL script where the MySQL script has been input by the operation and maintenance personnel, and the maximum update sequence numbers of different types of services may be different, and it may be known how many tenant table spaces need to be updated by the MySQL script through the maximum update sequence number.

Scanning the tenant tablespace associated with a certain service can be completed by the update engine 102, after the update engine 102 completes scanning, the scan information is sent to the control terminal 101, so that a target tenant tablespace is obtained, and then MySQL script updating is performed on all tenant tablespaces obtained by scanning.

When the MySQL scripts of all the target tenant tablespaces are updated, in order to ensure that all the target tenant tablespaces can be updated and are not heavy and heavy, whether the MySQL scripts of the service are executed is required to be determined, the front execution sequence number is determined according to the MySQL script updating times of executing the service by the MySQL tablespace pool, when the tenant tablespaces of the MySQL tablespace pool execute the MySQL script updating once, the current execution sequence number is automatically increased to 1, when the MySQL scripts of the second tenant tablespace are executed to be updated, the current execution sequence number is automatically increased to 2, and the current execution sequence number can be continuously and automatically increased by continuously executing the MySQL script updating.

When the current execution sequence number is the same as the maximum update sequence number, the service update is completed, if the current execution sequence number is larger than the maximum update sequence number, it is indicated that the service version of the tenant is behind, and service use abnormality may occur, and serious consequences may be caused, because the MySQL script is the latest version, so that the operation of the service is not affected.

As one embodiment herein, step 203 determines whether the current execution sequence number of MySQL script of each service is the same as the maximum update sequence number, and further includes:

if the service is different, acquiring the tenant table space sequence number of the service related between the current execution sequence number and the maximum update sequence number.

In this step, in order to solve the problem that there is a lag in the version of the service in the tenant table space, which may cause significant economic loss or security abnormality, a solution is proposed.

The update of the MySQL script of the tenant table space is regular, when the current execution sequence number is smaller than the maximum update sequence number, the execution data of the current MySQL script update or the field data of the MySQL script update is obtained, the difference value between the current tenant table space sequence number and the maximum execution sequence number is calculated, for example, the maximum update sequence number is 100, the current execution sequence number is 70, it is obvious that the tenant table space which does not execute the MySQL script update exists between 70 and 100, for example, the tenant table spaces with the tenant table space sequence numbers 88 and 99 are also associated with the service, and then the MySQL script update is sequentially executed on the tenant table spaces with the tenant table space sequence numbers 88, 99 and 100 from small to large.

Step 201, as one embodiment herein, further comprises, before determining the maximum update sequence number of MySQL script for each service:

The amount of free tablespace in the MySQL tablespace pool is periodically monitored.

When the number of free tablespaces is less than the free threshold, the start space creation thread creates free tablespaces.

And if the number of the idle table spaces is greater than the idle threshold, acquiring the latest MySQL script.

In order to ensure the normal operation of the enterprise service portal, a new tenant can be admitted, or when the tenant table space of the tenant is changed, or under the condition of data redundancy backup, the MySQL table space pool corresponding to the tenant table space cannot crash due to the fact that the sudden load exceeds the operation load, herein, a plurality of idle table spaces are reserved according to the quantity of the tenant table space, and it is required to be noted that the idle table space is only a virtual space which is not written with data, or is a virtual space which can establish the corresponding relation between enterprise information and the table space, is not inoperable, or is a virtual space which has been operated with general service but has not purchased for payment service when receiving an instruction, and is also a virtual space which has not been operated with general service and payment service.

Therefore, when the service is updated, the normal operation of the MySQL table space pool needs to be ensured preferentially, when the free quantity in the MySQL table space pool cannot reach a preset free threshold, the current update request is suspended, the MySQL script is put aside, a special space creation thread in the MySQL table space pool is started, the free table space is created, and after the free table space is created, the free table space quantity is larger than the free threshold, the latest MySQL script can be sent to the MySQL table space pool.

It should be noted that the idle threshold is set by the user, may be a fixed value, for example, 10 idle thresholds may be set based on the number of MySQL tablespaces in the MySQL tablespace pool, for example, the number of MySQL tablespaces is 1000, and the idle threshold is 10% or 100 of the number of MySQL tablespaces, which is not limited in the preset manner, and may be set by a person skilled in the art according to the need.

It should be noted that, when the MySQL table space pool is operated on the database 103, the database 103 may be used as a server, and when the space creation thread creates the free table space, the space creation thread may send an instruction to the database 103, and power up the hard disk that is not powered up, so as to activate the hard disk to become the free table space, or may implement the creation of the free table space by logically dividing the operation space of some data under the MySQL table space pool.

As an embodiment herein, as shown in fig. 3, a breakpoint continuous transmission schematic of a method for automatically updating tenants under MySQL tablespace, step 202 executes MySQL script update on a tenant tablespace associated with the service, and further includes:

Step 301, when the update of the MySQL script of the tenant table space associated with the service is successful, changing the index corresponding to the updated MySQL script of the target tenant table space in the transaction file.

Step 302, when the update of MySQL script of the tenant table space associated with the service fails, rolling back the transaction file to a previous state, and executing the update of MySQL script on the next tenant table space corresponding to the target tenant table space.

It should be noted that the transaction file is created according to the service type of the service.

In this step, in order to avoid the problem that when MySQL script update is executed, service update fails or update cannot be continued due to an abnormal phenomenon, such as power failure of the database 103 or interruption of MySQL script update caused by abnormality of the update engine 102, a breakpoint continuous transmission method based on MySQL is provided.

As shown in table 1, in a transaction file schematic table of a method for automatically updating a tenant under MySQL table space, for example, when a certain service a is used to update the tenant table space in the MySQL table space pool, if there is a tenant table space with a table space number of 1-10, the tenant table space with a table space number of 1 needs to be updated, when executing to the tenant table space with a table space number of 1, if executing is completed, in a transaction file corresponding to the service a, the index flag bit of the tenant table space with a table space number of 1 is updated to yes, if executing the MySQL script of the tenant table space with a table space number of 2 suddenly occurs, when the abnormal phenomenon is completed, the service file is rolled back from large to small in sequence by the table space number, when the tenant table space with a table space number of 1 is queried, the next tenant table space with a table space number of 1 in the tenant table 1 is queried, the tenant table space with a table space number of 2 in the table 1 is updated by the service a, and the service a is continuously executed by the tenant table space of 2.

TABLE 1

As an embodiment herein, the method for automatically updating the tenant under the MySQL tablespace further includes:

single threaded execution is used when MySQL script updates for different services are executed on a single tenant table space.

When MySQL script updating of different services is executed on a plurality of tenant table spaces, multithreading parallel execution is used.

Wherein the number of threads is less than the number of tenant tablespaces.

In this step, in order not to affect the normal operation of the enterprise service portal, the execution of the MySQL script update process is very limited for the thread call, so that the thread call mode is specified in order to achieve both the MySQL script update efficiency and the normal operation of the enterprise service portal.

Because a plurality of different services may be associated in each tenant table space, in order to balance the utilization of resources, when one tenant upgrades two services at the same time, for convenience of explanation, one thread is recorded as a first thread, the other thread is recorded as a second thread, two processes cannot be called at the same time, when the situation of calling two threads occurs, after the MySQL script of the first thread is ordered to the MySQL script of the second thread, after the MySQL script of the second thread is updated, the subsequent MySQL script is executed.

In different tenant table spaces, a multithreading parallel execution mode can be adopted, but it is to be noted that, for normal operation of an enterprise service portal, the number of threads occupied by a MySQL script update process is far smaller than the number of tenant space tables, for example, the number of tenant space tables is 1000, and the number of threads occupied by the MySQL script update process is only 10, so that an excellent mode is required to improve the efficiency of MySQL script update.

As one embodiment herein, when the steps of the MySQL script update for executing different services to multiple tenant tablespaces, the steps further include:

and ordering the tenants in the MySQL table space pool by using an ordering algorithm.

In this step, the available sorting algorithm may include an MRU algorithm, and MRU (Most recently used) most recently commonly used algorithms are used to calculate MySQL tablespace pools, and according to the result obtained by the MRU algorithm, the tenant tablespaces are sorted, for example, the first liveness is the tenant tablespace with the tablespace number of 2, the second liveness is the tenant tablespace with the tablespace number of 10, … …, and the lowest liveness is the tenant tablespace with the tablespace number of 70, if only two threads are used in the multithreading parallel upgrade, mySQL script updating is preferentially performed on the tenant tablespace with the tablespace number of 2 in the first liveness and the tenant tablespace with the tablespace number of 10 in the second liveness, and finally the tenant tablespace with the tablespace number of 70 is updated.

By the method, the enterprise service portal can be enabled to be a server with priority and active users, loss of the users is avoided, and the competitiveness of the product is improved.

As one embodiment herein, the method for automatically updating the MySQL tablespace by the tenant further includes:

And if one service is a general service, acquiring the number of tenant tablespaces in the MySQL tablespace pool.

In this step, for better service tenants, the enterprise service portal herein not only has paid services, but also includes several free services, where the free services also have security services or basic services, so these security services or basic services may be collectively referred to as general services, that is, each tenant table space is associated with a general service, when the general service needs to be updated, it is required to obtain the number of all table spaces in the current MySQL table space pool, and may include free table spaces or may not include free table spaces, and when the obtaining is completed, mySQL script update needs to be performed on the general services in all tenant table spaces.

and the tenant table space with the tenant table space sequence number can be updated according to the MySQL script upgrade of the assigned tenant table space sequence number.

Therefore, the method not only can update MySQL scripts of tenant table spaces related to a certain service in batches, but also can update MySQL scripts of specific tenant table spaces in a targeted manner, and of course, the specific MySQL scripts are customized and created according to the requirements of the tenant table spaces.

By the method, the automatic capacity expansion of the multi-tenant MySQL table empty pool can be realized, the specific update requirement of a specific tenant can be solved, the problem of breakpoint recovery during the update of the tenant table space in a multi-tenant table space environment can be realized, the update efficiency can be improved in the process of executing the MySQL script update in parallel, and the tenant with high use frequency can be updated preferentially according to the MRU algorithm.

FIG. 4 is a schematic diagram of an apparatus for tenant automatic update in MySQL tablespace, comprising:

a serial number determining unit 401, configured to determine a maximum update serial number of MySQL scripts of each service;

A script execution unit 402, configured to scan, for each service, a tenant table space associated with the service in the MySQL table space pool, and perform MySQL script update on the tenant table space associated with the service;

A sequence number judging unit 403, configured to judge whether the current execution sequence number of the MySQL script of each service is the same as the maximum update sequence number;

And the indication sending unit 404 is configured to send an update completion indication of MySQL script of the service when the current execution sequence number is the same as the maximum update sequence number.

By the device, the number of the tenant table spaces associated with a certain service in the MySQL table space pool can be conveniently and rapidly determined and used as the maximum update sequence number, all the related services in a single tenant table space are required to be determined in the past, each tenant table space associated with the service is updated when the service is updated, and the service can be traversed and executed after the fact that the tenant table spaces are associated with the service to be updated is confirmed to be completed by the device until all the tenant table spaces associated with the service are updated.

As shown in fig. 5, a detailed schematic diagram of an apparatus for automatically updating a tenant under MySQL table space, where the apparatus for automatically updating a tenant under MySQL table space further includes:

And the monitoring unit 501 is configured to monitor the amount of free tablespaces in the MySQL tablespace pool at regular time.

A thread start unit 502, configured to start a space creation thread to create a free table space when the number of free table spaces is smaller than a free threshold.

And the script obtaining unit 503 is configured to obtain the latest MySQL script if the number of the free table spaces is greater than the free threshold.

Note that, the listening unit 501 is preset with a timing task, for example, 24:00 as a timed task for the listening unit 501 when 24:00, the device automatically updated wakes up itself to start monitoring the current amount of free table space, the monitoring unit 501 may be set in the update engine 102, and when the monitoring unit 501 monitors the amount of free table space, the monitoring unit 501 sends the amount of free table space to the thread starting unit 502, and sends an enabling signal to the script obtaining unit.

The thread start unit 502 obtains the number of free table spaces, compares the number of free table spaces with a preset free threshold, and if the current number of free table spaces is smaller than the free threshold, starts a space creation thread to create a free table space, the space creation thread can be bound with the thread start unit 502 and can only be started by the thread start unit 502, and can send an enabling signal to the script acquisition unit 503 whenever the number of free table spaces is larger than the free threshold.

The script acquisition unit 503 has the function equivalent to a firewall, that is, when the enabling signal of the monitoring unit 501 and the enabling signal of the thread starting unit 502 are received simultaneously, the script acquisition unit 503 can acquire the external MySQL script, and by adopting the method, illegal personnel are prevented from tampering with various service data in the MySQL table space, and a large amount of financial loss is avoided.

Fig. 6 is a schematic diagram of an algorithm unit of a device for automatically updating tenants under MySQL tablespace, which includes:

an algorithm unit 601, configured to sort tenants in the MySQL table space pool using an algorithm.

It should be noted that the algorithm may be an MRU algorithm, which performs liveness ranking on tenants in the MySQL table space pool, so that the dependence of the tenants on products may be increased, and may also be an LRU algorithm ((LEAST RECENTLY used) least recently used algorithm, which performs liveness ranking on tenants in the MySQL table space pool, and preferentially upgrades the use of the least recently used algorithm, so that the use frequency is least, and the separation of users may be avoided, and users may be attracted to increase services.

After the algorithm unit 601 finishes sorting the tenants in the MySQL tablespace pool, the sorting result is sent to the thread starting unit 502, and the thread starting unit 502 executes multithreaded MySQL script update on the tenant tablespace in the MySQL tablespace pool.

FIG. 7 is a data flow diagram of an overall system for tenant automatic update under MySQL tablespace, comprising:

step 701, writing a plurality of MySQL scripts through the control terminal 101, and sending the MySQL scripts to the update engine 102.

Step 702, when the timing task is triggered, the update engine 102 determines whether the free table space satisfies the free threshold, if not, step 703 is executed, and if so, step 704 is executed.

Step 703, starting a space creation thread, creating a free table space, and executing step 704 after the creation is completed.

Step 704, receiving all MySQL scripts and determining which service the scripts correspond to.

Step 705, the update engine 102 scans all the service table spaces associated with the service in the database 103 to determine the maximum update sequence number.

Step 706, the update engine 102 executes MySQL script update on all tenant tablespaces associated with the service according to the maximum update sequence number.

Through the steps, the separation of writing and executing MySQL scripts is realized, and when the MySQL scripts are executed, the update can be executed in a traversing manner according to the service which the MySQL scripts want to update, so that compared with the existing independent update of the tenant table space, the update efficiency is greatly improved.

The problem that the manual operation and maintenance tenant table space is high in complexity and easy to make mistakes in a multi-tenant complex service scene is solved through the technical means, and the final consistency of tenant table space updating is ensured.

As shown in fig. 8, for one computer device provided by embodiments herein, the computer device 802 may include one or more processors 804, such as one or more Central Processing Units (CPUs), each of which may implement one or more hardware threads. The computer device 802 may also include any memory 806 for storing any kind of information, such as code, settings, data, etc. For example, and without limitation, memory 806 may include any one or more of the following combinations: any type of RAM, any type of ROM, flash memory devices, hard disks, optical disks, etc. More generally, any memory may store information using any technique. Further, any memory may provide volatile or non-volatile retention of information. Further, any memory may represent fixed or removable components of computer device 802. In one case, the computer device 802 may perform any of the operations of the associated instructions when the processor 804 executes the associated instructions stored in any memory or combination of memories. The computer device 802 also includes one or more drive mechanisms 808, such as a hard disk drive mechanism, an optical disk drive mechanism, and the like, for interacting with any memory.

The computer device 802 may also include an input/output module 810 (I/O) for receiving various inputs (via input device 812) and for providing various outputs (via output device 814)). One particular output mechanism may include a presentation device 816 and an associated Graphical User Interface (GUI) 818. In other embodiments, input/output module 810 (I/O), input device 812, and output device 814 may not be included, but merely as a computer device in a network. The computer device 802 may also include one or more network interfaces 820 for exchanging data with other devices via one or more communication links 822. One or more communications buses 824 couple the above-described components together.

The communication link 822 may be implemented in any manner, such as, for example, through a local area network, a wide area network (e.g., the internet), a point-to-point connection, etc., or any combination thereof. Communication link 822 may include any combination of hardwired links, wireless links, routers, gateway functions, name servers, etc., governed by any protocol or combination of protocols.

Corresponding to the method in fig. 2-3, embodiments herein also provide a computer readable storage medium having stored thereon a computer program which, when executed by a processor, performs the steps of the above method.

Embodiments herein also provide a computer readable instruction wherein the program therein causes the processor to perform the method as shown in fig. 2-3 when the processor executes the instruction.

It should be understood that, in the various embodiments herein, the sequence number of each process described above does not mean the sequence of execution, and the execution sequence of each process should be determined by its functions and internal logic, and should not constitute any limitation on the implementation process of the embodiments herein.

It should also be understood that in embodiments herein, the term "and/or" is merely one relationship that describes an associated object, meaning that three relationships may exist. For example, a and/or B may represent: a exists alone, A and B exist together, and B exists alone. In addition, the character "/" herein generally indicates that the front and rear associated objects are an "or" relationship.

Those of ordinary skill in the art will appreciate that the elements and algorithm steps described in connection with the embodiments disclosed herein may be embodied in electronic hardware, in computer software, or in a combination of the two, and that the elements and steps of the examples have been generally described in terms of function in the foregoing description to clearly illustrate the interchangeability of hardware and software. Whether such functionality is implemented as hardware or software depends upon the particular application and design constraints imposed on the solution. Skilled artisans may implement the described functionality in varying ways for each particular application, but such implementation decisions should not be interpreted as causing a departure from the scope of the present disclosure.

It will be clear to those skilled in the art that, for convenience and brevity of description, specific working procedures of the above-described systems, apparatuses and units may refer to corresponding procedures in the foregoing method embodiments, and are not repeated herein.

In the several embodiments provided herein, it should be understood that the disclosed systems, devices, and methods may be implemented in other ways. For example, the apparatus embodiments described above are merely illustrative, e.g., the division of the units is merely a logical function division, and there may be additional divisions when actually implemented, e.g., multiple units or components may be combined or integrated into another system, or some features may be omitted or not performed. In addition, the coupling or direct coupling or communication connection shown or discussed with each other may be an indirect coupling or communication connection via some interfaces, devices, or elements, or may be an electrical, mechanical, or other form of connection.

The units described as separate units may or may not be physically separate, and units shown as units may or may not be physical units, may be located in one place, or may be distributed on a plurality of network units. Some or all of the elements may be selected according to actual needs to achieve the objectives of the embodiments herein.

In addition, each functional unit in the embodiments herein may be integrated in one processing unit, or each unit may exist alone physically, or two or more units may be integrated in one unit. The integrated units may be implemented in hardware or in software functional units.

The integrated units, if implemented in the form of software functional units and sold or used as stand-alone products, may be stored in a computer readable storage medium. Based on such understanding, the technical solutions herein are essentially or portions contributing to the prior art, or all or portions of the technical solutions may be embodied in the form of a software product stored in a storage medium, including several instructions to cause a computer device (which may be a personal computer, a server, or a network device, etc.) to perform all or part of the steps of the methods described in the embodiments herein. And the aforementioned storage medium includes: a usb disk, a removable hard disk, a Read-Only Memory (ROM), a random access Memory (RAM, random Access Memory), a magnetic disk, or an optical disk, or other various media capable of storing program codes.

Specific examples are set forth herein to illustrate the principles and embodiments herein and are merely illustrative of the methods herein and their core ideas; also, as will be apparent to those of ordinary skill in the art in light of the teachings herein, many variations are possible in the specific embodiments and in the scope of use, and nothing in this specification should be construed as a limitation on the invention.

Claims

1. A method for automatically updating tenants under MySQL table space, comprising:

if the number of the free table spaces is larger than the free threshold value, acquiring the latest MySQL script;

determining the maximum update sequence number of MySQL script of each service;

and the current execution sequence number is determined according to the MySQL script updating times of executing the service in the MySQL table space pool.

2. The method for automatically updating MySQL tablespace according to claim 1, wherein the determining whether the current execution sequence number of the MySQL script of each service is the same as the maximum update sequence number further comprises:

3. The method of claim 1, wherein the performing MySQL script updates on the tenant tablespace associated with the service further comprises:

4. The method of MySQL tablespace tenant automatic updating of claim 1, further comprising:

Wherein the number of threads is less than the number of tenant tablespaces.

5. The method for automatically updating a tenant under MySQL tablespace according to claim 4, wherein the executing MySQL script updates for different services to a plurality of tenant tablespaces is performed in parallel using multithreading, further comprising:

sorting tenants in the MySQL table space pool by using a sorting algorithm;

6. The method of MySQL tablespace tenant automatic updating of claim 1, further comprising:

7. An apparatus for automatically updating a tenant under MySQL table space, comprising:

The monitoring unit is used for regularly monitoring the quantity of the free table spaces in the MySQL table space pool;

A thread starting unit, configured to start a space creation thread to create a free table space when the number of free table spaces is smaller than a free threshold;

a script acquisition unit, configured to acquire a latest MySQL script if the number of the free table spaces is greater than the free threshold;

8. 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 method of automatic tenant update under MySQL tablespace of any one of claims 1-6 when the computer program is executed by the processor.

9. A computer readable storage medium, wherein the computer readable storage medium stores a computer program which, when executed by a processor, implements a method for automatically updating a tenant under MySQL tablespace according to any one of claims 1 to 6.