Database automatic deployment method, system, device and medium

Abstract

The present invention discloses a database automatic deployment method, system, device and medium, which relate to the technical field of application deployment, wherein the database automatic deployment method includes comparing the difference in data structure between a source database and a target database to obtain a first mapping relationship, including: comparing the differences between the column names, field attributes, and field annotations of the source database and the target database data tables respectively to obtain a first comparison result; comparing the differences between the attributes, character sets, and remarks of the source database and the target database data tables respectively to obtain a second comparison result; comparing the differences between the indexes of the source database and the target database data tables respectively to obtain a third comparison result; generating a corresponding SQL work order based on the first mapping relationship; deploying the source database according to the SQL work order, thereby realizing the automatic deployment of various types of cloud native databases (RDS, Polardb, OceanBase) in multiple operating environments, and improving applicability.

Description

Technical Field

[0001] The present disclosure relates to the technical field of application deployment, and in particular to a database automatic deployment method, system, device and medium. Background Art

[0002] The automated deployment of applications requires multiple steps, including deploying databases, importing data, setting environment variables, deploying middleware, publishing applications, setting data sources, and starting applications. In the actual application deployment process, the tediousness and high error rate of manual operations highlight the necessity of automated configuration. Driven by cloud computing technology, cloud-native databases such as PolarDB and OceanBase have become a new trend in data storage and processing. Automated deployment is the key to achieving efficient operation and maintenance of cloud-native databases. Existing deployment methods cannot solve the problem that cloud-native databases (Polardb, Oceanbase) cannot be automatically deployed due to incompatibility of SQL statements and mismatch of data field types caused by different data structures, and their applicability is relatively weak. Summary of the invention

[0003] In order to solve the defects of the prior art, the embodiments of the present invention provide a method, system, device and medium for automatic deployment of a database. The method, system, device and medium for automatic deployment of a database provided by the embodiments of the present invention adopt the following technical solutions:

[0004] In a first aspect, the database automatic deployment method provided by an embodiment of the present invention comprises the following steps:

[0005] Compare the differences in data structure between the source database and the target database to obtain a first mapping relationship, including:

[0006] Compare the differences between the column names, field attributes, and field comments of the data tables of the source database and the target database respectively to obtain a first comparison result;

[0007] Compare the differences between the attributes, character sets, and remarks of the data tables of the source database and the target database respectively to obtain a second comparison result;

[0008] Comparing the differences between the indexes of the source database and the target database data tables respectively to obtain a third comparison result;

[0009] Based on the first mapping relationship, generate a corresponding SQL work order;

[0010] Deploy the source database according to the SQL work order.

[0011] In some examples, deploying the source database according to the SQL work order includes:

[0012] Determine whether there is a virtual column in the data table of the source database, and if not, create a virtual column;

[0013] If it exists, determine whether the data format of the column in the data table of the source database is in JSON format;

[0014] If it is determined that the data format of the column in the data table of the source database is in JSON format, convert the extra field of the column in the data table of the source database into an empty string and determine whether the default value of the column in the data table of the source database is utf8mb4;

[0015] If it is determined that the default value of the column in the data table of the source database is utf8mb4, then the system default escape character is deleted.

[0016] In some examples, deploying the source database according to the SQL work order includes:

[0017] Determine whether there is a partition data table in the source database data table, and if so, obtain the partition key, partition type, and number of partitions of the partition data table.

[0018] In some examples, before comparing the differences in data structures between the source database and the target database, the method further includes:

[0019] A source database is created and multiple operating environments are configured for the source database. In different operating environments, connection relationships between the source database and the target database are respectively established to obtain a second mapping relationship.

[0020] In some examples, after establishing a connection relationship between the source database and the target database, the method further includes:

[0021] According to the type of the source database, an operating environment matching the source database is selected from the multiple operating environments.

[0022] In some examples, deploying the source database according to the SQL work order further includes:

[0023] Parse the SQL work order, and determine whether the library name of the source database is the same as the library name of the target database based on the parsing result. If they are the same, determine whether the table name of the data table of the source database is exactly the same as the table name of the data table of the target database. If not, create a data table with the table name of the corresponding data table for the source database.

[0024] In some examples, the establishing a connection relationship between the source database and the target database respectively includes:

[0025] According to the operating environment of the source database, the IP address and port of the host, the user name, the password, and multiple nodes are configured for the source database, and node information is configured for each of the multiple nodes.

[0026] In a second aspect, the database automatic deployment system provided by the embodiment of the present invention includes:

[0027] The comparison module is configured to compare the differences in data structures between the source database and the target database to obtain a first mapping relationship, wherein:

[0028] The comparison module is further configured to compare the differences between the column names, field attributes, and field comments of the data tables of the source database and the target database, respectively, to obtain a first comparison result;

[0029] The comparison module is further configured to compare the differences between the attributes, character sets, and remarks of the data tables of the source database and the target database, respectively, to obtain a second comparison result;

[0030] The comparison module is further configured to compare the differences between the indexes of the source database and the target database data tables, respectively, to obtain a third comparison result;

[0031] A generation module is configured to generate a corresponding SQL work order based on the first mapping relationship;

[0032] A deployment module is configured to deploy the source database according to the SQL work order, wherein the deployment module includes:

[0033] A judging unit is configured to judge whether there is a virtual column in the data table of the source database, and if not, create a virtual column;

[0034] The judging unit is further configured to judge whether the data format of the column in the data table of the source database is in JSON format if it exists;

[0035] a conversion unit configured to convert the extra field of the column in the data table of the source database into an empty string and determine whether the default value of the column in the data table of the source database is utf8mb4;

[0036] The delete unit is configured to delete the system default escape character.

[0037] In a third aspect, an electronic device provided by an embodiment of the present invention includes a processor and a memory storing computer program instructions; when the processor executes the computer program instructions, the database automatic deployment method disclosed in the first aspect is implemented.

[0038] In a fourth aspect, a computer-readable storage medium provided by an embodiment of the present invention stores computer program instructions, and when the computer program instructions are executed by a processor, the database automatic deployment method disclosed in the first aspect is implemented.

[0039] Compared with the prior art, the database automatic deployment method, system, device and medium provided by the embodiments of the present invention have the following beneficial effects:

[0040] According to different types of databases, the database tables, table structures and other contents are compared, file changes are dynamically perceived, and the operating environment is selected for database deployment operations, realizing the comparison of the full SQL of the database. The entire deployment process is completed automatically and each task can be executed in parallel without manual intervention, which improves efficiency. It realizes the automatic deployment of various types of cloud native databases (RDS, Polardb, OceanBase) in multiple operating environments (alpha, dev, prd, pre, demo, qa), improving applicability. BRIEF DESCRIPTION OF THE DRAWINGS

[0041] In order to more clearly illustrate the embodiments of the present disclosure or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present disclosure. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying any creative work.

[0042] Figure 1 A schematic diagram of a method flow for automatic database deployment according to an embodiment of the present invention;

[0043] Figure 2 A schematic diagram of the structure of a database automatic deployment system according to an embodiment of the present invention;

[0044] Figure 3 1 is a principle block diagram of an electronic device serving as a classical computing device according to an embodiment of the present invention. DETAILED DESCRIPTION

[0045] The following is a further detailed description of the embodiments of the present disclosure in conjunction with the accompanying drawings and examples. The detailed description of the following embodiments and the accompanying drawings are used to exemplarily illustrate the principles of the present disclosure, but cannot be used to limit the scope of the present disclosure. The present disclosure can be implemented in many different forms and is not limited to the specific embodiments disclosed herein, but includes all technical solutions that fall within the scope of the claims.

[0046] In the embodiment of the present invention, the cloud native databases RDS and Poardbo are relational databases and distributed relational databases provided by Alibaba Cloud, respectively, and OceanBase is a distributed relational database provided by Huawei Cloud. The operating environment alpha is usually used for internal testing; the dev environment is a development environment, usually used to develop and test applications; the prd environment is a production environment, that is, the application has been launched and is available to users; the pre environment is a pre-production environment, usually used for higher-level testing before the production environment; the demo environment is a demonstration environment, usually used to show the functions and features of the application to customers, users, etc.; the qa environment is a quality assurance environment, used to test and verify the quality and performance of the application; the JSON format is a lightweight data exchange format; utf8mb4 is used to store four-byte unicode characters. Example

[0047] like Figure 1 As shown, the database automatic deployment method provided by the embodiment of the present invention includes the following steps:

[0048] S1, creating a source database and configuring multiple operating environments for the source database, establishing connection relationships between the source database and the target database in different operating environments, and obtaining a second mapping relationship.

[0049] Specifically, multiple operating environments include alpha, dev, prd, pre, demo, qa and other operating environments to facilitate the subsequent deployment of the source database. The source database is the database that needs to be published and pushed, and the target database is the database that needs to be executed. According to the second mapping relationship, the source database is pushed to the object storage server (such as Alibaba Cloud Object Storage Server OSS), and the data of the source system and the data of the target system are extracted into the intermediate database.

[0050] S2, selecting an operating environment matching the source database from the multiple operating environments configured in step S1 according to the type of the source database.

[0051] Specifically, according to the type of source database (MySQL, Polardb, OceanBase, etc.), match it with the corresponding operating environment. For example, when the source database is MySQL, the operating environments that match the source database are alpha, dev, prd, pre, demo, and qa.

[0052] S3, comparing the difference in data structure between the source database and the target database to obtain a first mapping relationship, including the following steps:

[0053] S31, respectively comparing the differences between the column names, field attributes, and field comments of the data tables of the source database and the target database to obtain a first comparison result.

[0054] Specifically, before the comparison, the data tables are sorted according to their virtual IDs, and the column names, field attributes, and field comments of the source database and the target database are determined respectively.

[0055] S32, respectively comparing the differences between the character sets and the comments of the data tables of the source database and the target database to obtain a second comparison result.

[0056] S33, respectively comparing the differences between the indexes of the source database and the target database data tables to obtain a third comparison result.

[0057] S4: Generate a corresponding SQL work order based on the first mapping relationship.

[0058] S5: Deploy the source database according to the SQL work order.

[0059] Specifically, the embodiments of the present invention compare database tables, table structures and other contents according to different types of databases, dynamically perceive file changes, select the operating environment to perform database deployment operations, and realize the comparison of the full SQL of the database. The entire deployment process is completed automatically and various tasks can be executed in parallel without manual intervention, thereby improving efficiency. It realizes the automatic deployment of various types of cloud native databases (RDS, Polardb, OceanBase) in multiple operating environments (alpha, dev, prd, pre, demo, qa), which has strong applicability.

[0060] In some examples, step S5 specifically includes the following steps:

[0061] S51, determining whether there is a virtual column in the data table of the source database, and if not, creating a virtual column.

[0062] S52: If it exists, determine whether the data format of the column in the data table of the source database is in JSON format.

[0063] Specifically, the MySQL database supports virtual columns and JSON field types. During the virtual column comparison process, virtual column comparison is currently not supported. If there are virtual columns in the source database data table, you need to modify the default value of JSON to achieve compatibility with higher-version databases (Polardb databases).

[0064] S53, if it is determined that the data format of the column in the data table of the source database is in JSON format, convert the extra field of the column in the data table of the source database into an empty string and determine whether the default value of the column in the data table of the source database is utf8mb4.

[0065] Specifically, when the extra field of the data in the column of the data table in JSON format contains a virtual column, it needs to be converted to an empty string.

[0066] S54, if it is determined that the default value of the column in the data table of the source database is utf8mb4, then the system default escape character is deleted to adapt to the characteristics of the json format data.

[0067] In some examples, step S5 specifically further includes the following steps:

[0068] Determine whether there is a partitioned data table in the source database data table. If so, obtain the partition key, partition type, and number of partitions of the partitioned data table.

[0069] Specifically, considering the characteristics of distributed databases (such as OceanBase database), the comparison of partition tables is not supported during the comparison between the source database and the target database. Since partition tables can bring scalability, in order to ensure the integrity of the structure, when the source database data table is determined to be a partition table, the partition key, partition type, and number of partitions contained in the partition table are parsed and applied to the target database.

[0070] In some examples, step S5 specifically further includes the following steps:

[0071] Parse the SQL work order and determine whether the library name of the source database is the same as the library name of the target database based on the parsing result. If they are the same, determine whether the table name of the data table of the source database is exactly the same as the table name of the data table of the target database. If not, create a data table with the table name of the corresponding data table for the source database.

[0072] Specifically, a database may contain multiple data tables, each of which has a corresponding table name. When the table name of a data table in the source database is different from the table name of a data table in the target database, the table name of the data table in the source database is modified so that the table names of the data tables in the source database are consistent with the table names of the data tables in the target database.

[0073] The embodiment of the present invention solidifies multiple frequent and fixed-mode operation links that require manual deployment in the prior art into a universal workflow by adopting a preset work order method, replaces manual operations with automated scripts, supports non-blocking change operations on tens of millions of large tables, and realizes automatic review of multi-layer workflows and automated deployment of mainstream databases such as RDS, Polardb, OceanBase, etc., solves the compatibility problems of SQL statements and data field type mismatch problems caused by the automated configuration of multi-environment resources of cloud native databases (Polardb, Oceanbase, etc.) and different data structures, saves labor costs, greatly improves the deployment efficiency of the database, eliminates the risk of manual misoperation, and improves the publishing efficiency by about 80%. Example

[0074] like Figure 2 As shown, the database automatic deployment system provided by the embodiment of the present invention includes:

[0075] The comparison module is configured to compare the differences in data structures between the source database and the target database to obtain a first mapping relationship, wherein:

[0076] The comparison module is further configured to compare the differences between the column names, field attributes, and field comments of the data tables of the source database and the target database, respectively, to obtain a first comparison result.

[0077] The comparison module is further configured to compare the differences between the attributes, character sets, and remarks of the data tables of the source database and the target database, respectively, to obtain a second comparison result.

[0078] The comparison module is further configured to compare the differences between the indexes of the source database and the target database data tables respectively to obtain a third comparison result.

[0079] The generation module is configured to generate a corresponding SQL work order based on the first mapping relationship.

[0080] The deployment module is configured to deploy the source database according to the SQL work order. Example

[0081] The specific application scenarios of the database automatic deployment method provided by the embodiment of the present invention include:

[0082] In the e-commerce industry, in order to meet the functions of marketing, lottery, annual billing, etc., cloud native databases OceanBase and PolarDB are used as source databases. In order to realize the frequent change operations of SQL statements in different operating environments and meet the requirements of multiple availability, the database automation deployment method is used to realize the automatic execution of SQL work orders in different operating environments to improve deployment efficiency. By parsing the parameter data passed by the continuous integration CI end, the corresponding tasks are produced, and the SQL work order begins to execute. After successful execution, the continuous deployment CD end begins to deploy the project. The specific process is as follows:

[0083] (1) Configure the address of the source database's operating environment. For example, add the addresses of multiple source database operating environments (alpha, dev, prd, pre, demo, and qa) to the configuration management.

[0084] (2) Submit the SQL file (mysql_dbname.sql) containing the complete table creation statement (such as create table x);

[0085] (3) Package the marketing project and SQL files through the continuous integration CI end to generate a package file;

[0086] (4) Deliver the packaged file to the designated cloud object storage server (such as Alibaba Cloud Object Storage Service OSS) and the reviewer;

[0087] (5) After verification, the continuous integration CI end calls the continuous deployment service, triggers the comparison operation of the SQL files in each environment, and obtains the environment of the SQL file to be executed. For example, if the operating environment is qa;

[0088] (6) Parse the OSS path. For example, by parsing the OSS file path of qa, the published service name is d1-promotion-api, and the storage path of the SQL file is 2023.1.22;

[0089] (7) Obtain the database mysql_dbname.sql to be released through the passed path, start comparison, and obtain the CREATE TABLE and ALTER TABLE statements that have differences;

[0090] (8) Obtain the CREATE / ALTER statement, create an SQL work order, and push the approval message through Feishu;

[0091] (9) Review the SQL work order and confirm or reject the execution of the SQL work order based on the review results;

[0092] (10) After the SQL work order is confirmed to be executed, it will start to execute. After successful execution, the CD-side deployment project will be continuously deployed to complete the database automation deployment.

[0093] Figure 3 The hardware structure principle block diagram of an embodiment of the electronic device provided by the present invention is shown. The electronic device includes a processor 601 and a memory 602 storing computer program instructions. When the processor executes the computer program instructions, the database automatic deployment method disclosed in the above embodiment of the present invention is implemented.

[0094] Specifically, the processor 601 may include a central processing unit (CPU), or an application specific integrated circuit (ASIC), or may be configured to implement one or more integrated circuits of the embodiment of the present invention.

[0095] The memory 602 may include a large capacity memory for data or instructions. By way of example and not limitation, the memory 602 may include a hard disk drive (HDD), a floppy disk drive, a flash memory, an optical disk, a magneto-optical disk, a magnetic tape, or a universal serial bus (USB) drive or a combination of two or more of these. In appropriate cases, the memory 602 may include a removable or non-removable (or fixed) medium. In appropriate cases, the memory 602 may be inside or outside the integrated gateway disaster recovery device. In a specific embodiment, the memory 602 is a non-volatile solid-state memory.

[0096] The memory may include read-only memory (ROM), random access memory (RAM), magnetic disk storage media devices, optical storage media devices, flash memory devices, electrical, optical or other physical / tangible memory storage devices. Thus, typically, the memory includes one or more tangible (non-transitory) computer-readable storage media (e.g., memory devices) encoded with software including computer-executable instructions, and when the software is executed (e.g., by one or more processors), it is operable to perform the operations described with reference to the method according to an aspect of the present disclosure.

[0097] In some examples, such as Figure 3 As shown, the electronic device may further include a communication interface 603 and a bus 610. Figure 3 As shown, the processor 601, the memory 602, and the communication interface 603 are connected and communicate with each other via a bus 610. The electronic device in the embodiment of the present invention may be a server or other computing device, or may be a cloud server.

[0098] The communication interface 603 is mainly used to implement communication between various modules, devices, units and / or equipment in the embodiment of the present invention.

[0099] Bus 610 includes hardware, software or both, and couples the components of online data traffic billing equipment to each other. For example, but not limitation, the bus may include accelerated graphics port (AGP) or other graphics bus, enhanced industrial standard architecture (EISA) bus, front-side bus (FSB), hypertransport (HT) interconnection, industrial standard architecture (ISA) bus, infinite bandwidth interconnection, low pin count (LPC) bus, memory bus, micro channel architecture (MCA) bus, peripheral component interconnect (PCI) bus, PCI-Express (PCI-X) bus, serial advanced technology attachment (SATA) bus, video electronics standard association local (VLB) bus or other suitable bus or two or more of these combinations. Where appropriate, bus 610 may include one or more buses. Although the embodiment of the present invention describes and shows a specific bus, the present invention considers any suitable bus or interconnection.

[0100] The present invention also provides a computer program product, which includes computer program instructions, and when the computer program instructions are executed by a processor, the database automatic deployment method provided by an embodiment of the present invention is implemented. The computer program product is, for example, a software installation package, a plug-in compatible with a related software system, etc.

[0101] It should be clear that the present invention is not limited to the specific configuration and processing described above and shown in the figures. For the sake of simplicity, a detailed description of the known method is omitted here. In the above embodiments, several specific steps are described and shown as examples. However, the method process of the present invention is not limited to the specific steps described and shown, and those skilled in the art can make various changes, modifications and additions, or change the order between the steps after understanding the spirit of the present invention.

[0102] In addition, the words “first”, “second” and the like used in the present disclosure do not indicate any order, quantity or importance, but are only used to distinguish different parts.

[0103] The above is only a specific implementation of the present invention. Those skilled in the art can clearly understand that for the convenience and simplicity of description, the specific working process of the system, module and unit described above can refer to the corresponding process in the aforementioned method embodiment, and will not be repeated here. It should be understood that the protection scope of the present invention is not limited to this. Any technician familiar with the technical field can easily think of various equivalent modifications or replacements within the technical scope disclosed by the present invention, and these modifications or replacements should be covered within the protection scope of the present invention.

Claims

1. A database automation deployment method, It is characterized in that include: Compare the differences in data structure between the source database and the target database to obtain a first mapping relationship, including: Compare the differences between the column names, field attributes, and field comments of the data tables of the source database and the target database respectively to obtain a first comparison result; Compare the differences between the attributes, character sets, and remarks of the data tables of the source database and the target database respectively to obtain a second comparison result; Comparing the differences between the indexes of the source database and the target database data tables respectively to obtain a third comparison result; Based on the first mapping relationship, generate a corresponding SQL work order; Deploy the source database according to the SQL work order, including: Determine whether there is a virtual column in the data table of the source database, and if not, create a virtual column; If it exists, determine whether the data format of the virtual column in the data table of the source database is in JSON format; If it is determined that the data format of the virtual column in the data table of the source database is in JSON format, convert the extra field of the virtual column in the data table of the source database into an empty string and determine whether the default value of the virtual column in the data table of the source database is utf8mb4; If it is determined that the default value of the virtual column in the data table of the source database is utf8mb4, the system default escape character is deleted.

2. The database automation deployment method according to claim 1, It is characterized in that According to the SQL work order, deploying the source database further includes: Determine whether there is a partition data table in the source database data table, and if so, obtain the partition key, partition type, and number of partitions of the partition data table.

3. The database automation deployment method according to claim 1, It is characterized in that Before comparing the differences in data structures between the source database and the target database, the method further includes: A source database is created and multiple operating environments are configured for the source database. In different operating environments, connection relationships between the source database and the target database are respectively established to obtain a second mapping relationship.

4. The database automation deployment method according to claim 3, It is characterized in that After establishing a connection relationship between the source database and the target database respectively, the method includes: According to the type of the source database, an operating environment matching the source database is selected from the multiple operating environments.

5. The database automation deployment method according to claim 1, It is characterized in that The deploying the source database according to the SQL work order further includes: Parse the SQL work order, and determine whether the library name of the source database is the same as the library name of the target database based on the parsing result. If they are the same, determine whether the table name of the data table of the source database is exactly the same as the table name of the data table of the target database. If not, create a data table with the table name of the corresponding data table for the source database.

6. The database automation deployment method according to claim 3, It is characterized in that The establishing of a connection relationship between the source database and the target database comprises: According to the operating environment of the source database, the IP address and port of the host, the user name, the password, and multiple nodes are configured for the source database, and node information is configured for each of the multiple nodes.

7. A database automation deployment system, It is characterized in that include: The comparison module is configured to compare the differences in data structures between the source database and the target database to obtain a first mapping relationship, wherein: The comparison module is further configured to compare the differences between the column names, field attributes, and field comments of the data tables of the source database and the target database, respectively, to obtain a first comparison result; The comparison module is further configured to compare the differences between the attributes, character sets, and remarks of the data tables of the source database and the target database, respectively, to obtain a second comparison result; The comparison module is further configured to compare the differences between the indexes of the source database and the target database data tables, respectively, to obtain a third comparison result; A generation module is configured to generate a corresponding SQL work order based on the first mapping relationship; A deployment module is configured to deploy the source database according to the SQL work order, wherein the deployment module includes: A judging unit is configured to judge whether there is a virtual column in the data table of the source database, and if not, create a virtual column; The judging unit is further configured to judge whether the data format of the virtual column in the data table of the source database is in JSON format if it exists; a conversion unit configured to, if it is determined that the data format of the virtual column in the data table of the source database is in JSON format, convert the extra field of the virtual column in the data table of the source database into an empty string and determine whether the default value of the virtual column in the data table of the source database is utf8mb4; The deletion unit is configured to delete the system default escape character if it is determined that the default value of the virtual column in the data table of the source database is utf8mb4.

8. An electronic device, comprising a processor and a memory storing computer program instructions; when the processor executes the computer program instructions, the database automatic deployment method according to any one of claims 1 to 6 is implemented.

9. A computer-readable storage medium, It is characterized in that The computer-readable storage medium stores computer program instructions, and when the computer program instructions are executed by a processor, the database automatic deployment method according to any one of claims 1 to 6 is implemented.

Images