The method comprises the steps that an Online DDL client designed without a trigger is connected with a main instance of a database, an original table receives a DDL request, the DDL client creates a shadow table ghost _ table according to the requirement of a DDL modification table, and DML operation on the original table old _ table continuously exists on a MySQL main instance.

the online DDL client registers the client as a Slave instance, acquires incremental binlog information of the MySQL main instance after logging, requires a login slot and a replay client right for a login account, prevents DML operation on an original table old _ table after user trigger switching, and copies the total data and the incremental data of the original table old _ table into a shadow table ghost _ table, wherein the total data and the incremental data are from the binlog.

S3, verifying the copy data in the shadow table with the data in the original table;

the copy data in the shadow table is checked against the data in the original table by the crc32 algorithm.

S4, when the verification result of the shadow table is consistent with that of the original table, the shadow table is renamed to the name of the original table and replaces the original table.

And waiting for the consistency of the data of the shadow table ghost _ table and the data of the original table old _ table, renaming the shadow table to be the original table name, and replacing the original table.

Fig. 4 shows a system logic operation process of the method for modifying a table structure by online DDL, which is implemented according to the operation processes 1-8 shown in fig. 4, and does not affect the DML operation of the current service system, wherein step 1 is that an original table old _ table receives a DDL request sent by Master;step 2 is: the online DDL client is connected with a Master instance of a Master database; step 3 is: the DDL client creates a shadow table ghost _ table;step 4 is: acquiring incremental binlog information of the MySQL main instance;step 5 is: the Master sends the information of the total and incremental data in the copy table old _ table to the shadow table ghost _ table; step 6 is: the Master sends user trigger switching information; and thesteps 7 and 8 are as follows: the shadow table ghost _ table and the original table old _ table are consistent in data, and the shadow table is switched to be the original table. The Client conducts DML operation on the Master, and the Reliction operation is conducted between the Master and the Slave.

According to the scheme, the table structure is modified through online DDL, when the table structure is modified, the original table is not locked, the operation of a current business system is not influenced, namely the original table also allows DML operations such as INSERT, UPDATE and DELETE, the integrity of business data in the process of modifying the table structure is guaranteed, after the table structure is modified, the original table is locked, the data of the original table is copied to an shadow table and verified, the consistency of the copied table data is guaranteed, the shadow table copying the data of the original table is renamed and replaces the original table, the normal operation of the business on the line is not influenced in the whole process of modifying the DDL table structure, the efficient processing of a large number of threads in the process of modifying the table structure is guaranteed, the normal operation of the business system can be maintained, and the writing operation of a front-end application to the target is not influenced.

Preferably, in any of the above embodiments, S2 specifically includes: and obtaining incremental binlog information of the main example of the relational database, and copying the full data and the incremental data of the original table in the binlog information to the shadow table.

The binary log stream is used to capture data changes of the original table, i.e., to obtain binlog information and to asynchronously copy the binlog information to the shadow table. Wherein the full data represents the data of the original table before the DDL operation and the incremental data represents the incremental content of the DML or the like after the DDL operation.

The method comprises the steps of capturing table change in a binlog binary log stream mode to obtain incremental data of an original table during table structure modification and full data before modification, copying the incremental data of the original table and the full data of the original table to a shadow table in a small data copying process, and guaranteeing integrity of the shadow table data in a DDL table structure modification process.

Preferably, in any of the above embodiments, before the copying at S2, further comprising: and S21, the user triggers switching to prevent the DML operation on the original table.

After the DDL table structure modification operation is completed, the original table is locked after the user triggers the switching, the incremental data and the full data of the original table are copied into the shadow table, the DDL operation and the service data operation of the original table are continuously performed before the user triggers, and the original table is locked after the user triggers, the operations of copying, renaming and replacing are performed.

Preferably, in any of the above embodiments, S3 specifically includes: the copy data in the shadow table is checked against the data of the original table by the crc32 algorithm.

The crc32 algorithm checking algorithm specifically represents data consistency checking, SQL statements are transmitted to the slave library after the master library is checked, the slave library also obtains its own result after the slave library is executed once, if the two values obtained by the master library and the slave library are the same, the data is consistent, and if the two values are different, the master library and the slave library are inconsistent. The specific executed SQL statements are as follows:

SELECT COUNT(*)AS cnt,

coalesee (power) (CONV (BIT _ XOR (CAST (CRC32(CONCAT _ WS (# ', various column names)) ASUNSIGNED)),10,16)),0) AS CRC FROM ' database ', a "table ' FORCE INDEX (" "PRIMARY") wheel (("id ' > = '1')) AND ((" id < ' > 1000'))

Note: the where condition is the range to be executed calculated from the system busy level.

The verification of the copy data and the original table data in the shadow table is carried out through a crc32 algorithm, and the consistency of the table data after the copying is finished is ensured.

Preferably, in any of the above embodiments, S4 further includes: when the verification results of the shadow table and the original table do not match, the process proceeds to step S2.

And when the verification result of the shadow table data is inconsistent with that of the original table data, the original table data is obtained again for copying, and the integrity and consistency of the copied data are further ensured through the verification of the verification result.

Preferably, in any of the above embodiments, before S1, the method further includes: s11, the Online DDL client connects with the primary instance of the relational database.

In a certain embodiment, as shown in fig. 2, the operation flow of the Online DDL client is that the Online DDL client prepares, the original table receives a DDL request, the DDL client creates a shadow table ghost _ table, obtains incremental binlog information of the MySQL main instance, copies the total amount and the incremental data to the shadow table ghost _ table, checks the data between the ghost _ table and the original table old _ table consistently, and switches the ghost _ table to the original table old _ table.

The Online DDL client designed without the trigger is connected with the main instance of the database, so that the trigger is not used during the copying of the DDL incremental data, the influence of the copying process on the main library is minimized, and if the trigger is used, the trigger causes the problems of extra resource overhead, lock competition caused by concurrent writing, incapability of really pausing and the like during the execution.

Preferably, in any of the above embodiments, before S6, the method further includes: s12, an example architecture of a relational database is deployed.

In one embodiment, as shown in FIG. 3, the relational database may be deployed as follows: deploying business applications, distributed data agents, namely a logic database and a MySQL database, namely a physical database, and specifically, 1. adopting a multi-tenant mode by one or a group of distributed database agents to bear the distributed data storage requirements of a plurality of business applications, and performing data isolation among all the business applications through a vertical database partitioning strategy; 2. each service application is divided into four segments according to the need, namely, the library A shown in fig. 3; or a single bank undivided mode is adopted, such as a B bank shown in FIG. 3; 3. each sub-slice or sub-library is provided with a corresponding standby library, a main library and a standby library are defaulted, the main library and the standby library can be expanded into a main library and a standby library according to needs, and the main library and the standby library adopt a MySQL internal data synchronization replication strategy for data synchronization; for the service with less writing and more reading, a plurality of standby banks can be set according to the requirement, and a read-write separation strategy is set, such as the bank C shown in fig. 3.

Preferably, in any of the above embodiments, the DDL functions include: adding an index, deleting an index, adding a foreign key constraint, deleting a foreign key constraint, changing a column name, adding a column, deleting a column, modifying a column data type, changing a column order, setting a column attribute, adding a primary key, deleting and adding a primary key, and deleting a primary key.

The structure of the diversified online modification data table is realized through the functions of DDL, such as adding indexes, deleting indexes, adding external key constraints, deleting external key constraints, changing column names, adding columns, deleting columns, modifying column data types and the like.

In one embodiment, a storage medium is provided, where instructions are stored, and when the instructions are read by a computer, the computer is caused to execute a method for modifying a table structure of a relational database online DDL according to any one of the above embodiments.

In one embodiment, an apparatus for online DDL modification table structure of a relational database is provided, as shown in the figure, the apparatus includes: a memory 11 for storing a computer program;

the processor 12 is configured to execute a computer program to implement a method for modifying a table structure of a relational database online DDL according to any one of the above embodiments.

The reader should understand that in the description of this specification, reference to the description of the terms "one embodiment," "some embodiments," "an example," "a specific example," or "some examples," etc., means that a particular feature, structure, material, or characteristic described in connection with the embodiment or example is included in at least one embodiment or example of the invention. In this specification, the schematic representations of the terms used above are not necessarily intended to refer to the same embodiment or example. Furthermore, the particular features, structures, materials, or characteristics described may be combined in any suitable manner in any one or more embodiments or examples. Furthermore, various embodiments or examples and features of different embodiments or examples described in this specification can be combined and combined by one skilled in the art without contradiction.

In the several embodiments provided in the present application, it should be understood that the disclosed apparatus and method may be implemented in other ways. For example, the above-described method embodiments are merely illustrative, and for example, the division of steps into only one logical functional division may be implemented in practice in another way, for example, multiple steps may be combined or integrated into another step, or some features may be omitted, or not implemented.

The above method, if implemented in the form of software functional units and sold or used as a stand-alone product, may be stored in a computer readable storage medium. Based on such understanding, the technical solution of the present invention essentially or partially contributes to the prior art, or all or part of the technical solution can be embodied in the form of a software product stored in a storage medium and including instructions for causing a computer device (which may be a personal computer, a server, or a network device) to execute all or part of the steps of the method according to the embodiments of the present invention. And the aforementioned storage medium includes: various media capable of storing program codes, such as a usb disk, a removable hard disk, a Read-only memory (ROM), a Random Access Memory (RAM), a magnetic disk, or an optical disk.

While the invention has been described with reference to specific embodiments, it will be understood by those skilled in the art that various changes in form and details may be made therein without departing from the spirit and scope of the invention as defined by the appended claims. Therefore, the protection scope of the present invention shall be subject to the protection scope of the claims.

Claims

1. A method for modifying a table structure of an online DDL of a relational database is characterized by comprising the following steps:

s1, creating the shadow table according to the DDL function to be modified, and not locking the original table, the service system continuously keeps DML operation to the original table;

2. The method according to claim 1, wherein the S2 specifically includes: obtaining incremental binlog information of a main instance of a relational database, and copying the full data and the incremental data of the original table in the binlog information to the shadow table.

3. The method of claim 2, wherein before the copying of the S2, the method further comprises: and S21, the user triggers switching to prevent the DML operation on the original table.

4. The method for modifying table structure of relational database online DDL according to any of claims 1-3, wherein the S3 specifically comprises: and verifying the copied data in the shadow table with the data of the original table through a crc32 algorithm.

5. The method for online DDL modification of table structure in relational database according to claim 4, wherein said S4 further comprises: if the verification results of the shadow table and the original table are not consistent, the process proceeds to step S2.

6. The method for online DDL modification of table structure in relational database according to claim 5, further comprising, before said S1: s11, the Online DDL client connects with the primary instance of the relational database.

7. The method for online DDL modification of table structure in relational database according to claim 6, further comprising, before said S6: s12, an example architecture of a relational database is deployed.

8. The method of claim 7, wherein the DDL function comprises: adding an index, deleting an index, adding a foreign key constraint, deleting a foreign key constraint, changing a column name, adding a column, deleting a column, modifying a column data type, changing a column order, setting a column attribute, adding a primary key, deleting and adding a primary key, and deleting a primary key.

9. A storage medium having stored therein instructions, which when read by a computer, cause the computer to execute a method of relational database online DDL modification table structure according to any one of claims 1 to 8.

10. An apparatus for online DDL modification of table structure of relational database, comprising:

a memory for storing a computer program;

a processor for executing said computer program to implement the method of relational database online DDL modification table structure according to any one of claims 1 to 8.