Method, device and storage medium for automatically modifying table structure in database cluster

Abstract

The present invention relates to a method, device and storage medium for automatically modifying a table structure in a database cluster, comprising: configuring database cluster information, checking whether the table structure corresponding to each server in the database cluster is consistent according to preset steps, and if not, modifying all the table structures to standard table structures; when the table structures corresponding to the servers are consistent and need to be modified, creating a backup table, the table structure of the backup table is consistent with the table structure corresponding to each server, copying data in the table structure corresponding to the server, and performing a modification operation on the backup table; when all the modification operations are successfully executed, deleting the table structure corresponding to the server, modifying the backup table to the table structure name corresponding to the original server, and updating the execution result; solving the problem that the prior art can only manually check whether the table structure is consistent, which is inefficient, and that the original table structure and table data need to be backed up in advance, and directly modifying the table structure will lead to data loss.

Description

Technical Field

[0001] The present invention relates to the technical field of database clusters, and in particular to a method, a device and a storage medium for automatically modifying a table structure in a database cluster. Background Art

[0002] In the production environment, with the increase in data volume, it is inevitable to deploy database clusters (master-slave, read-write separation, sharding without sharding tables, sharding without sharding tables, sharding without sharding databases, sharding without sharding tables). This leads to a problem: in the existing technology, when modifying the cluster table structure, you can only manually check whether the table structure is consistent in turn, and then modify the table structure. You also need to back up the original table structure and table data in advance. Otherwise, directly modifying the table structure may cause the loss of indexes, primary keys, field comments and data, which is time-consuming and labor-intensive. Summary of the invention

[0003] In view of this, the purpose of the present invention is to provide a method, device and storage medium for automatically modifying the table structure in a database cluster, so as to solve the problem in the prior art that when modifying the cluster table structure, the table structure can only be manually checked in sequence to see if it is consistent, and then the table structure is modified. It is also necessary to back up the original table structure and table data in advance. Otherwise, directly modifying the table structure may cause the loss of indexes, primary keys, field comments and data.

[0004] According to a first aspect of an embodiment of the present invention, a method for automatically modifying a table structure in a database cluster is provided, the method comprising:

[0005] Configure the database cluster information, and check whether the table structure corresponding to each server in the database cluster is consistent according to the preset steps. If not, modify all the table structures to the standard table structure;

[0006] When the table structures corresponding to each server are consistent and the table structures need to be modified, a backup table is created, the table structure of the backup table is consistent with the table structure corresponding to each server, the data in the table structure corresponding to the server is copied, and the modification operation is performed on the backup table;

[0007] When all the execution and modification operations are successful, the table structure corresponding to the server is deleted, the backup table is modified to the table structure name corresponding to the original server, and the execution result is updated.

[0008] Preferably,

[0009] Modifying the table structure includes: displaying the standard table structure information corresponding to the server on a page through a list, and modifying the standard table structure information on the page;

[0010] The modifying the table structure also includes: inputting a custom SQL in the SQL input box to modify the standard table structure information.

[0011] Preferably,

[0012] The performing of the modification operation on the backup table includes: executing a custom SQL on the backup table and recording the execution status; when the execution of the custom SQL fails, executing the custom SQL again according to a preset configuration number of times; when all the executions of the custom SQL fail within the preset configuration number of times, deleting the backup table, updating the execution result, and displaying the reason for the execution failure.

[0013] Preferably,

[0014] The checking according to the preset steps whether the table structure corresponding to each server in the database cluster is consistent includes:

[0015] Obtain structural information of the table structures corresponding to each server of the database cluster, and compare whether the structural information of the table structures corresponding to each server is consistent. If the structural information of the table structures corresponding to each server is consistent, then the table structures corresponding to each server are consistent; otherwise, the table structures corresponding to each server are inconsistent.

[0016] Preferably,

[0017] When the table structures corresponding to the servers are inconsistent, modifying all table structures to standard table structures includes:

[0018] Counting the number of different table structures corresponding to each server in the database cluster, ranking the different table structures according to their proportions in each server, and selecting the table structure with the highest proportion as the standard table structure;

[0019] Generate a modification SQL according to the difference between the table structure information of the server with non-standard table structure and the standard table structure information in the database cluster, the server with non-standard table structure creates a standard backup table, executes the modification SQL, and records the execution status, when all the execution modification SQL operations are successful, delete the table structure corresponding to the non-standard table server, modify the standard backup table to the table structure name of the non-standard table server, and update the execution result;

[0020] When the execution of the modified SQL fails, the modified SQL is executed again according to the preset configuration times. When the execution of the modified SQL fails completely within the preset configuration times, the standard backup table is deleted, the execution result is updated, and the reason for the execution failure is displayed.

[0021] Preferably,

[0022] When the table structures corresponding to each server are consistent and the table structures need to be modified, multi-threaded asynchronous execution operations are adopted to modify the table structures of each server simultaneously.

[0023] Preferably,

[0024] The structural information of the table structure includes: table fields, types, primary keys, foreign keys and index information.

[0025] Preferably,

[0026] When configuring the database cluster information, if each server of the database cluster is configured with a table partitioning strategy, the logical table name in the SQL is modified to the table structure name in the real database cluster.

[0027] According to a second aspect of an embodiment of the present invention, a device for automatically modifying a table structure in a database cluster is provided, the device comprising:

[0028] Judgment module: configure database cluster information, check whether the table structure corresponding to each server in the database cluster is consistent according to preset steps, and if not, modify all table structures to standard table structures;

[0029] Table structure modification module: when the table structure corresponding to each server is consistent and needs to be modified, create a backup table whose table structure is consistent with the table structure corresponding to each server, copy the data in the table structure corresponding to the server, and perform modification operations on the backup table;

[0030] Update execution module: When all the execution and modification operations are successful, the table structure corresponding to the server is deleted, the backup table is modified to the table structure name corresponding to the original server, and the execution result is updated.

[0031] According to a third aspect of an embodiment of the present invention, a storage medium is provided, wherein the storage medium stores a computer program, and when the computer program is executed by a system, each step in the method for automatically modifying a table structure in a database cluster is implemented.

[0032] The technical solution provided by the embodiments of the present invention may have the following beneficial effects:

[0033] Through preset steps, it is automatically checked whether the table structures corresponding to each server in the database cluster are consistent. Compared with the existing manual method of checking whether the table structure is consistent, the inspection efficiency has been significantly improved. When modifying the table structure, the modification is performed in the form of a backup table. The backup table directly copies the data of the original table structure. In this way, even if the modification operation on the backup table fails, it will not cause the data in the original table structure to be lost, and there is no need to back up the original table structure and table data in advance.

[0034] It is to be understood that the foregoing general description and the following detailed description are exemplary and explanatory only and are not restrictive of the invention. BRIEF DESCRIPTION OF THE DRAWINGS

[0035] The accompanying drawings, which are incorporated in and constitute a part of this specification, illustrate embodiments consistent with the invention and, together with the description, serve to explain the principles of the invention.

[0036] Figure 1 is a flowchart of a method for automatically modifying a table structure in a database cluster according to an exemplary embodiment;

[0037] Figure 2 is a system schematic diagram of an apparatus for automatically modifying a table structure in a database cluster according to another exemplary embodiment;

[0038] In the attached figure: 1-judgment module, 2-table structure modification module, 3-update execution module. DETAILED DESCRIPTION

[0039] Exemplary embodiments will be described in detail herein, examples of which are shown in the accompanying drawings. When the following description refers to the drawings, the same numbers in different drawings represent the same or similar elements unless otherwise indicated. The embodiments described in the following exemplary embodiments do not represent all embodiments consistent with the present invention. Instead, they are merely examples of devices and methods consistent with some aspects of the present invention as detailed in the appended claims.

[0040] Embodiment 1

[0041] Figure 1 is a flow chart of a method for automatically modifying a table structure in a database cluster according to an exemplary embodiment. Figure 1 As shown, the method includes:

[0042] S1, configure database cluster information, check whether the table structure corresponding to each server in the database cluster is consistent according to the preset steps, if not, modify all table structures to standard table structures;

[0043] S2, when the table structure corresponding to each server is consistent and the table structure needs to be modified, create a backup table, the table structure of the backup table is consistent with the table structure corresponding to each server, copy the data in the table structure corresponding to the server, and perform the modification operation on the backup table;

[0044] S3, when all the execution and modification operations are successful, the table structure corresponding to the server is deleted, the backup table is modified to the table structure name corresponding to the original server, and the execution result is updated;

[0045] What you need to know is that a database cluster, as the name implies, uses at least two or more database servers to form a virtual single database logical image, which provides transparent data services to clients like a single database system.

[0046] It can be understood that, in this embodiment, after configuring the database cluster information, the table to be operated is selected on the Web page, and the program determines whether the table structure corresponding to each server in the configured database cluster is consistent according to the preset steps. If the table structures of each server are inconsistent, all the table structures are modified to be consistent. Through this application, the user does not need to manually compare whether the table structures of each server are consistent, which saves the user's time and significantly improves the inspection efficiency. When the user needs to modify the table structure, a backup table is created according to the customer's modification content. The table structure of the backup table is the same as the table structure consistent with the above-mentioned servers. At the same time, the data of each table structure is copied to the backup table. According to the user's modification content, the corresponding modification operation is performed in the backup table. After all the modification operations are successfully executed, the original table structure of the server is deleted, and the backup table is modified to the original table structure name of the server, and the execution result is updated. Through this application, when the user modifies the table structure, the modification is performed in the form of a backup table, and the data in the table structure does not need to be backed up in advance. Even if the modification operation fails, it will not affect the data in the original table structure of the server, and will not cause data loss.

[0047] Preferably,

[0048] Modifying the table structure includes: displaying the standard table structure information corresponding to the server on a page through a list, and modifying the standard table structure information on the page;

[0049] The modifying the table structure further comprises: inputting a custom SQL in the SQL input box to modify the standard table structure information;

[0050] It can be understood that in the present application, when the user needs to modify the table structure, the consistent standard table structure information modified by each server in the database table structure is displayed on the page through a list, and the standard table structure information is modified on the page, or a custom SQL is entered in the SQL input box to modify the standard table structure information. Regardless of the method, the modified content can be expressed in the form of SQL.

[0051] Preferably,

[0052] The performing of the modification operation on the backup table includes: executing the custom SQL on the backup table and recording the execution status; when the execution of the custom SQL fails, executing the custom SQL again according to a preset configuration number of times; when the execution of the custom SQL fails within the preset configuration number of times, deleting the backup table, updating the execution result, and displaying the reason for the execution failure;

[0053] It can be understood that after the user makes corresponding modifications to the table structure, the user executes the custom SQL on the backup table. For each modified content, the execution status of each modification operation in the backup table is recorded. When there is an execution failure, the operation is re-executed according to the preset configuration number of times. When the operation still fails after execution 3 times, the backup table is deleted, the execution result is updated, and the reason for the execution failure is displayed. The reason for the execution failure is the function implemented by the underlying code when the system is built. The existing technology is already quite mature and will not be elaborated here.

[0054] Preferably,

[0055] The checking according to the preset steps whether the table structure corresponding to each server in the database cluster is consistent includes:

[0056] Obtaining structural information of table structures corresponding to each server of the database cluster, and comparing whether the structural information of the table structures corresponding to each server is consistent; if the structural information of the table structures corresponding to each server is consistent, then the table structures corresponding to each server are consistent; otherwise, the table structures corresponding to each server are inconsistent;

[0057] It can be understood that the above-mentioned automatic determination of whether the structures of the servers in the database cluster are consistent through preset steps includes: obtaining the structural information of the table structure of each server, and determining whether the table structure is consistent by comparing the structural information. If the table structure information of all servers is consistent, it is determined that the table structures of all servers are consistent. If the table structure information of a server is different from the table structure information of other servers, it is determined that the table structures of all servers are inconsistent. The system or software can automatically run the identification of this process without manual participation.

[0058] Preferably,

[0059] When the table structures corresponding to the servers are inconsistent, modifying all table structures to standard table structures includes:

[0060] Counting the number of different table structures corresponding to each server in the database cluster, ranking the different table structures according to their proportions in each server, and selecting the table structure with the highest proportion as the standard table structure;

[0061] Generate a modification SQL according to the difference between the table structure information of the server with non-standard table structure and the standard table structure information in the database cluster, the server with non-standard table structure creates a standard backup table, executes the modification SQL, and records the execution status, when all the execution modification SQL operations are successful, delete the table structure corresponding to the non-standard table server, modify the standard backup table to the table structure name of the non-standard table server, and update the execution result;

[0062] When the execution of the modified SQL fails, the modified SQL is executed again according to the preset configuration times. When the execution of the modified SQL fails completely within the preset configuration times, the standard backup table is deleted, the execution result is updated, and the reason for the execution failure is displayed;

[0063] It can be understood that when the table structures of various servers in the database cluster are inconsistent, the table structures are ranked according to their proportions in the entire database cluster, and the table structure with the highest proportion is selected as the standard table structure. In addition to the system default table structure with the highest proportion, users can also set the standard table structure according to their own needs, that is, the most common type of table structure corresponding to each server, compare other table structure information that is not a standard table structure with the standard table structure information, modify different places, generate modification SQL, create a standard backup table, copy the table structure data content of other non-standard table structures in the standard backup table, perform the modification operation in the standard backup table according to the modification SQL, and record the execution status. When the modification SQL operation is successfully executed, the table structure corresponding to the non-standard table server is deleted, the standard backup table is modified to the non-standard table server table structure name, and the execution result is updated. When the modification SQL fails to execute, the modification SQL is executed again according to the preset configuration number of times. The preset configuration number here can be set independently according to the needs of the user. Within the preset configuration number of times, when the modification SQL fails to execute, the standard backup table is deleted, the execution result is updated, and the reason for the execution failure is displayed.

[0064] Preferably,

[0065] When the table structures corresponding to each server are consistent and the table structures need to be modified, multi-threaded asynchronous execution operations are adopted to modify the table structures of each server simultaneously;

[0066] It is understandable that when modifying the table structure, multi-threaded asynchronous execution is used, that is, the table structures of all servers are modified synchronously, which speeds up the modification process and improves the modification efficiency.

[0067] Preferably,

[0068] The structural information of the table structure includes: table fields, types, primary keys, foreign keys and index information;

[0069] It is understandable that the table structure information includes the table's fields, types, primary keys, foreign keys, and index information. When determining whether the table structures are consistent, the table's fields, types, primary keys, foreign keys, and index information are compared and determined.

[0070] Preferably,

[0071] When configuring the database cluster information, if each server of the database cluster is configured with a table partitioning strategy, the logical table name in the SQL is modified to the table structure name in the real database cluster;

[0072] It is understandable that for the table sharding scenario, if the user of the table has configured a table sharding strategy, the program will automatically change the logical table name in the execution statement to the real table name.

[0073] Embodiment 2:

[0074] Figure 2 is a system schematic diagram of an apparatus for automatically modifying a table structure in a database cluster according to an exemplary embodiment. Figure 2 As shown, the device comprises:

[0075] Judgment module 1: configure database cluster information, and check whether the table structure corresponding to each server in the database cluster is consistent according to preset steps. If not, modify all table structures to standard table structures;

[0076] Table structure modification module 2: when the table structure corresponding to each server is consistent and the table structure needs to be modified, create a backup table whose table structure is consistent with the table structure corresponding to each server, copy the data in the table structure corresponding to the server, and perform modification operations on the backup table;

[0077] Update execution module 3: when all the execution and modification operations are successful, delete the table structure corresponding to the server, modify the backup table to the table structure name corresponding to the original server, and update the execution result;

[0078] It can be understood that the present embodiment also provides a device for automatically modifying the table structure in the database cluster, the device comprising a judgment module 1, a table structure modification module 2 and an update execution module 3. The table to be operated is selected on the Web page, and the judgment module 1 judges whether the table structure corresponding to each server in the configured database cluster is consistent according to the preset steps. If the table structures of the servers are inconsistent, all the table structures are modified to be consistent. Through the present application, the user does not need to manually compare whether the table structures of the servers are consistent, which saves the user's time and significantly improves the inspection efficiency. When the user needs to modify the table structure, the table structure Modification module 2 creates a backup table, the table structure of the backup table is the same as the table structure of the above-mentioned servers, and the data of each table structure is copied in the backup table. According to the user's modification content, the corresponding modification operation is performed in the backup table. After all modification operations are successfully executed, the update execution module 3 deletes the original table structure of the server, modifies the backup table to the original table structure name of the server, and updates the execution result. Through this application, when the user modifies the table structure, the modification is performed in the form of a backup table. There is no need to back up the data in the table structure in advance. Even if the modification operation fails, it will not affect the data in the original table structure of the server, and will not cause data loss.

[0079] Embodiment three:

[0080] This embodiment provides a storage medium, wherein the storage medium stores a computer program, and when the computer program is executed by the system, each step in the method for automatically modifying the table structure in the database cluster is implemented;

[0081] It is understandable that the storage medium mentioned above can be a read-only memory, a magnetic disk or an optical disk, etc.

[0082] It can be understood that the same or similar parts of the above embodiments can be referenced to each other, and the contents not described in detail in some embodiments can refer to the same or similar contents in other embodiments.

[0083] It should be noted that, in the description of the present invention, the terms "first", "second", etc. are only used for descriptive purposes and cannot be understood as indicating or implying relative importance. In addition, in the description of the present invention, unless otherwise specified, the meaning of "plurality" refers to at least two.

[0084] Any process or method description in a flowchart or otherwise described herein may be understood to represent a module, segment or portion of code that includes one or more executable instructions for implementing the steps of a specific logical function or process, and the scope of the preferred embodiments of the present invention includes alternative implementations in which functions may not be performed in the order shown or discussed, including performing functions in a substantially simultaneous manner or in the reverse order depending on the functions involved, which should be understood by those skilled in the art to which the embodiments of the present invention belong.

[0085] It should be understood that the various parts of the present invention can be implemented by hardware, software, firmware or a combination thereof. In the above-mentioned embodiments, a plurality of steps or methods can be implemented by software or firmware stored in a memory and executed by a suitable instruction execution system. For example, if implemented by hardware, as in another embodiment, it can be implemented by any one of the following technologies known in the art or their combination: a discrete logic circuit having a logic gate circuit for implementing a logic function for a data signal, a dedicated integrated circuit having a suitable combination of logic gate circuits, a programmable gate array (PGA), a field programmable gate array (FPGA), etc.

[0086] A person skilled in the art may understand that all or part of the steps in the method for implementing the above-mentioned embodiment may be completed by instructing related hardware through a program, and the program may be stored in a computer-readable storage medium, which, when executed, includes one or a combination of the steps of the method embodiment.

[0087] In addition, each functional unit in each embodiment of the present invention may be integrated into a processing module, or each unit may exist physically separately, or two or more units may be integrated into one module. The above-mentioned integrated module may be implemented in the form of hardware or in the form of a software functional module. If the integrated module is implemented in the form of a software functional module and sold or used as an independent product, it may also be stored in a computer-readable storage medium.

[0088] The storage medium mentioned above can be a read-only memory, a magnetic disk or an optical disk, etc.

[0089] In the description of this specification, the description with reference to the terms "one embodiment", "some embodiments", "examples", "specific examples", or "some examples" means that the specific features, structures, materials or characteristics described in conjunction with the embodiment or example are included in at least one embodiment or example of the present invention. In this specification, the schematic representation of the above terms does not necessarily refer to the same embodiment or example. Moreover, the specific features, structures, materials or characteristics described may be combined in any one or more embodiments or examples in a suitable manner.

[0090] Although the embodiments of the present invention have been shown and described above, it is to be understood that the above embodiments are exemplary and are not to be construed as limitations of the present invention. A person skilled in the art may change, modify, replace and vary the above embodiments within the scope of the present invention.

Claims

1. A method for automatically modifying a table structure in a database cluster, characterized in that: The method comprises: Configure the database cluster information, and check whether the table structure corresponding to each server in the database cluster is consistent according to the preset steps. If not, modify all the table structures to the standard table structure; The modification of all table structures to standard table structures includes: Counting the number of different table structures corresponding to each server in the database cluster, ranking the different table structures according to their proportions in each server, and selecting the table structure with the highest proportion as the standard table structure; Generate a modification SQL according to the difference between the table structure information of the server with non-standard table structure and the standard table structure information in the database cluster, the server with non-standard table structure creates a standard backup table, executes the modification SQL, and records the execution status, when all the execution modification SQL operations are successful, delete the table structure corresponding to the non-standard table server, modify the standard backup table to the table structure name of the non-standard table server, and update the execution result; When the execution of the modified SQL fails, the modified SQL is executed again according to the preset configuration times. When the execution of the modified SQL fails completely within the preset configuration times, the standard backup table is deleted, the execution result is updated, and the reason for the execution failure is displayed; When the table structures corresponding to each server are consistent and need to be modified, multi-threaded asynchronous execution is used to modify the table structures of each server at the same time; When the table structures corresponding to each server are consistent and the table structures need to be modified, a backup table is created, the table structure of the backup table is consistent with the table structure corresponding to each server, data in the table structure corresponding to the server is copied, and a modification operation is performed on the backup table; When all the execution and modification operations are successful, the table structure corresponding to the server is deleted, the backup table is modified to the table structure name corresponding to the original server, and the execution result is updated.

2. The method according to claim 1, characterized in that Modifying the table structure includes: displaying the standard table structure information corresponding to the server on a page through a list, and modifying the standard table structure information on the page; The modifying the table structure also includes: inputting a custom SQL in the SQL input box to modify the standard table structure information.

3. The method according to claim 2, characterized in that The performing of the modification operation on the backup table includes: executing a custom SQL on the backup table and recording the execution status; when the execution of the custom SQL fails, executing the custom SQL again according to a preset configuration number of times; when all the executions of the custom SQL fail within the preset configuration number of times, deleting the backup table, updating the execution result, and displaying the reason for the execution failure.

4. The method according to claim 3, characterized in that Also includes: The checking according to the preset steps whether the table structure corresponding to each server in the database cluster is consistent includes: Obtain structural information of the table structures corresponding to each server of the database cluster, and compare whether the structural information of the table structures corresponding to each server is consistent. If the structural information of the table structures corresponding to each server is consistent, then the table structures corresponding to each server are consistent; otherwise, the table structures corresponding to each server are inconsistent.

5. The method according to claim 4, characterized in that The structural information of the table structure includes: table fields, types, primary keys, foreign keys and index information.

6. The method according to claim 1, characterized in that When configuring the database cluster information, if each server of the database cluster is configured with a table partitioning strategy, the logical table name in the SQL is modified to the table structure name in the real database cluster.

7. A device for automatically modifying a table structure in a database cluster, characterized in that: The device comprises: Judgment module: configure database cluster information, check whether the table structure corresponding to each server in the database cluster is consistent according to preset steps, and if not, modify all table structures to standard table structures; The modification of all table structures to standard table structures includes: Counting the number of different table structures corresponding to each server in the database cluster, ranking the different table structures according to their proportions in each server, and selecting the table structure with the highest proportion as the standard table structure; Generate a modification SQL according to the difference between the table structure information of the server with non-standard table structure and the standard table structure information in the database cluster, the server with non-standard table structure creates a standard backup table, executes the modification SQL, and records the execution status, when all the execution modification SQL operations are successful, delete the table structure corresponding to the non-standard table server, modify the standard backup table to the table structure name of the non-standard table server, and update the execution result; When the execution of the modified SQL fails, the modified SQL is executed again according to the preset configuration times. When the execution of the modified SQL fails completely within the preset configuration times, the standard backup table is deleted, the execution result is updated, and the reason for the execution failure is displayed; Table structure modification module: when the table structure corresponding to each server is consistent and needs to be modified, create a backup table whose table structure is consistent with the table structure corresponding to each server, copy the data in the table structure corresponding to the server, and perform modification operations on the backup table; When the table structures corresponding to each server are consistent and the table structures need to be modified, multi-threaded asynchronous execution operations are adopted to modify the table structures of each server simultaneously; Update execution module: When all the execution and modification operations are successful, the table structure corresponding to the server is deleted, the backup table is modified to the table structure name corresponding to the original server, and the execution result is updated.

8. A storage medium, characterized in that: The storage medium stores a computer program, and when the computer program is executed by the system, each step of the method for automatically modifying the table structure in a database cluster as described in any one of claims 1 to 6 is implemented.

Images