CN114579530A - Table space migration method and device, electronic equipment and computer readable storage medium - Google Patents
Table space migration method and device, electronic equipment and computer readable storage medium Download PDFInfo
- Publication number
- CN114579530A CN114579530A CN202011382623.1A CN202011382623A CN114579530A CN 114579530 A CN114579530 A CN 114579530A CN 202011382623 A CN202011382623 A CN 202011382623A CN 114579530 A CN114579530 A CN 114579530A
- Authority
- CN
- China
- Prior art keywords
- ddl
- data
- source
- source table
- row log
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/10—File systems; File servers
- G06F16/18—File system types
- G06F16/1805—Append-only file systems, e.g. using logs or journals to store data
- G06F16/1815—Journaling file systems
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/21—Design, administration or maintenance of databases
- G06F16/214—Database migration support
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/23—Updating
- G06F16/2379—Updates performed during online database operations; commit processing
Landscapes
- Engineering & Computer Science (AREA)
- Databases & Information Systems (AREA)
- Theoretical Computer Science (AREA)
- Data Mining & Analysis (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The embodiment of the application provides a table space migration method and device, electronic equipment and a computer readable storage medium, and relates to the field of databases. The method comprises the following steps: creating a target table with the same table structure as the source table in a target table space, then generating a Data Definition Language (DDL) row log of the source table through a DDL row log function of the source table, wherein the DDL row log is used for recording a DML operation of a data operation language aiming at the source table, further storing first table data in the source table to the target table based on a snapshot of the source table, storing second table data in the source table to the target table based on the DDL row log, and determining a table name of the target table based on the source table. The embodiment of the application realizes online table space migration in the database, not only ensures the consistency and integrity of the migration table data, but also does not influence the normal use of the application program.
Description
Technical Field
The present application relates to the field of database technologies, and in particular, to a table space migration method and apparatus, an electronic device, and a computer-readable storage medium.
Background
The advent of the big data age has enabled databases to play an increasingly important role in the computer field, and during the use of databases, it is often necessary to migrate a data table in one tablespace to another tablespace for various reasons, which is the migration of tablespaces.
The existing tablespace migration is generally realized through two modes, one mode is to lock a table of related data tables before migrating the table data and then directly copy the table data to a target table, and the other mode is to create a trigger in the data table needing to be migrated and then synchronize new data through the trigger to realize complete tablespace migration.
In practical application, if a first mode is adopted to lock a table for a data table, normal operation of application can be affected, if the database does not support a data table migration command, the database needs to be offline first, and then table space migration is completed in a mode of creating a new table, which is an offline table space migration mode, and the offline table space migration mode can greatly affect an online database and has low efficiency; however, if the second method is adopted, there are two main limitations, namely that a primary key must exist in a data table to be migrated and at least three new triggers must be created, which is too restrictive for the data table to implement table space migration in a large range.
Disclosure of Invention
The application provides a table space migration identification method, a table space migration identification device, electronic equipment and a computer readable storage medium, which are used for solving the technical problem that in the existing table space migration process, a table must be locked or a trigger is used so that limitation is excessive.
In a first aspect, a method for migrating a tablespace is provided, where the method includes:
creating a target table with the same table structure as the source table in the target table space;
generating a DDL row log of a source table through a data definition language DDL row log function of the source table, wherein the DDL row log is used for recording data operation language DML operation aiming at the source table;
storing first table data in a source table to a target table based on the snapshot of the source table;
storing second table data in the source table to a target table based on the DDL row log;
a table name for the target table is determined based on the source table.
Preferably, before storing the first table data in the source table to the target table based on the snapshot of the source table, the method further includes:
and improving the isolation level of the source table to be the time of repeated reading, and acquiring the snapshot of the source table.
Preferably, after storing the first table data in the source table to the target table based on the snapshot of the source table, the method further includes:
an index of the target table is created based on the index of the source table.
Preferably, the method further comprises:
the source table, the index of the source table, and the DDL row log of the source table are deleted.
Preferably, storing the second table data in the source table to the target table based on the DDL row log includes:
step A, scanning the current DDL row log to obtain second table data in a source table;
step B, storing the second table data to a target table, and deleting the second table data in the DDL row log;
step C, when detecting that the DDL row log is updated, acquiring the updated DDL row log;
and D, taking the updated DDL row log as the current DDL row log, and repeatedly executing the steps A to D until the current DDL row log is not updated.
In a second aspect, an apparatus for migrating a tablespace is provided, the apparatus comprising:
the creating module is used for creating a target table with the same table structure as that of the source table in the target table space;
the system comprises a first starting module, a second starting module and a third starting module, wherein the first starting module is used for generating a DDL (data definition language) row log of a source table through a DDL row log function of the source table, and the DDL row log is used for recording a DML (data operation language) operation aiming at the data of the source table;
the first table data module is used for storing first table data in the source table to the target table based on the snapshot of the source table;
the second table data module is used for storing second table data in the source table to the target table based on the DDL row log;
and the determining module is used for determining the table name of the target table based on the source table.
Preferably, before the first table data module, the method further comprises: a second starting module;
and the second starting module is used for acquiring the snapshot of the source table when the isolation level of the source table is promoted to be repeatable reading.
Preferably, after the first table data module, the method further comprises: an indexing module;
an index module to create an index of the target table based on the index of the source table.
Preferably, the apparatus further comprises: a deletion module;
and the deleting module is used for deleting the source table, the index of the source table and the DDL row log of the source table.
Preferably, the second table data module comprises:
the first processing submodule is used for scanning the current DDL row log to obtain second table data in the source table;
the second processing submodule is used for storing the second table data into the target table and deleting the second table data in the DDL row log;
the third processing submodule is used for acquiring the updated DDL row log when the DDL row log is detected to be updated;
and taking the updated DDL row log as the current DDL row log, and repeatedly calling the first processing submodule, the second processing submodule and the third processing submodule until the current DDL row log is not updated.
In a third aspect, an electronic device is provided, which includes:
one or more processors;
a memory;
one or more applications, wherein the one or more applications are stored in the memory and configured to be executed by the one or more processors, the one or more programs configured to: the migration method of the table space shown in the first aspect of the present application is executed.
In a fourth aspect, a computer-readable storage medium is provided, on which a computer program is stored, which when executed by a processor, implements the migration method of a table space shown in the first aspect of the present application.
By applying the table space migration method provided by the embodiment of the application, a target table with the same table structure as that of a source table is created in a target table space, a Data Definition Language (DDL) row log function of the source table is used for generating a DDL row log of the source table, the DDL row log is used for recording data operation language (DML) operation aiming at the source table, first table data in the source table is stored to the target table based on a snapshot of the source table, second table data in the source table is stored to the target table based on the DDL row log, and a table name of the target table is determined based on the source table.
The method for automatically recording the changed data of the source table in the table space migration process through the DDL row log and migrating the original data and the changed data of the source table to the target table on line by combining the snapshot of the source table overcomes the technical problems that the table space migration is limited too much and the efficiency is low due to the fact that the table needs to be locked or a trigger is relied on in the existing table space migration, and therefore the technical effect that the accuracy and the integrity of the transferred table data are guaranteed while the table space migration is automatically on line in the database is achieved.
Drawings
In order to more clearly illustrate the technical solutions in the embodiments of the present application, the drawings used in the description of the embodiments of the present application will be briefly described below.
Fig. 1 is a schematic flowchart of a table space migration method according to an embodiment of the present application;
FIG. 2 is a flowchart illustrating a method for migrating a tablespace according to another embodiment of the present application;
FIG. 3 is a flowchart illustrating a method for migrating a tablespace according to another embodiment of the present application;
fig. 4 is a schematic structural diagram of a table space migration apparatus according to an embodiment of the present application;
fig. 5 is a schematic structural diagram of a migration electronic device of a tablespace according to an embodiment of the present application.
Detailed Description
Reference will now be made in detail to embodiments of the present application, examples of which are illustrated in the accompanying drawings, wherein like or similar reference numerals refer to the same or similar elements or elements having the same or similar function throughout. The embodiments described below with reference to the drawings are exemplary only for the purpose of explaining the present application and are not to be construed as limiting the present invention.
As used herein, the singular forms "a", "an", "the" and "the" are intended to include the plural forms as well, unless the context clearly indicates otherwise. It will be further understood that the terms "comprises" and/or "comprising," when used in this specification, specify the presence of stated features, integers, steps, operations, elements, and/or components, but do not preclude the presence or addition of one or more other features, integers, steps, operations, elements, components, and/or groups thereof. It will be understood that when an element is referred to as being "connected" or "coupled" to another element, it can be directly connected or coupled to the other element or intervening elements may also be present. Further, "connected" or "coupled" as used herein may include wirelessly connected or wirelessly coupled. As used herein, the term "and/or" includes all or any element and all combinations of one or more of the associated listed items.
To make the objects, technical solutions and advantages of the present application more clear, embodiments of the present application will be described in further detail below with reference to the accompanying drawings.
The terms referred to in this application will first be introduced and explained:
the data Definition language ddl (data Definition language) is a component of a database language, and mainly includes functions of creating a database, creating a database table, modifying the database table, deleting the content of the database table, and the like. The DDL log is a temporary system table, and five fields are added on the basis of the table structure of a source table and are respectively used for recording a sequence number, an operation type, data before and after operation, a changed field recorded in a bitmap form and a transaction number corresponding to the operation.
The data Manipulation language dml (data Manipulation language) is a component of a database language, and mainly includes operations of adding, deleting, modifying and searching a database table.
The tablespace is a logical partition of the database, a plurality of operating system files can form one tablespace, the tablespace manages data files in the space in a unified mode, one tablespace only belongs to one database, one database space is composed of a plurality of tablespaces, and all database objects are stored in the designated tablespace.
The snapshot is a fast reading technology for a memory based on a hardware programming technology, is often applied to backup and recovery of online data, and can perform fast data recovery when an application failure or file damage occurs to a storage device to recover the data to a state at an available time point. The database snapshot is a view of the database at a certain time point, and when the database is restored by using the snapshot, only the changed pages are restored to the source database, which is undoubtedly higher than the restoration mode of backup.
Repeatable read isolation rr (repeatable read) is one of four isolation levels defined by database standards, i.e. queries within the same transaction are all consistent at the beginning of the transaction.
Snapshot isolation si (snapshot isolation) is a level of isolation of the database, and under snapshot isolation, three kinds of read exceptions, i.e., dirty read, unrepeatable read, and magic read, do not occur, and read operations are not blocked.
In an existing application scenario, table space migration is to perform table locking on a source table, migrate data of the source table to a target table by using a command for migrating a database table, or to perform offline database locking, complete data migration by offline import and export operations, and change a table name. In such a scenario, the database table space migration cannot be completed online, the operation of the application program needs to be stopped in the migration process, the efficiency is low, and the use experience of the user is affected.
In another existing application scenario, table space migration is realized in a trigger mode, after a new table is created in a target table space, at least three triggers are created in a source table, data are copied from the source table to the target table, a new DML operation is updated to the target table through the created triggers in the copying process, and then operations of changing table names and cleaning up the data of the source table are performed. In such a scenario, a primary key must exist in the source table, multiple triggers need to be created, the limitation condition of table space migration is too many, and the table space migration operation cannot be performed in this manner to a large extent.
The application provides a table space migration method, a table space migration device, an electronic device and a computer-readable storage medium, which aim to solve the above technical problems in the prior art.
The following describes the technical solutions of the present application and how to solve the above technical problems with specific embodiments. The following several specific embodiments may be combined with each other, and details of the same or similar concepts or processes may not be repeated in some embodiments. Embodiments of the present application will be described below with reference to the accompanying drawings.
An embodiment of the present application provides a method for migrating a table space, and as shown in fig. 1, the method includes:
in step S101, a target table having the same table structure as the source table is created in the target table space.
And creating a data table with the same table structure as that of the source table needing data migration in the target table space, wherein the data table is the target table. Specifically, the table structure of the target table is the same as that of the source table, and the table name of the target table may be a special table name, for example, the table name of the target table is named by using a special prefix or suffix, so as to be conveniently matched with the source table in the database and distinguished from other data tables in the database. At this point, the target table does not build an index.
Step S102, generating a DDL row log of the source table through a data definition language DDL row log function of the source table, wherein the DDL row log is used for recording data operation language DML operation aiming at the source table.
And starting a Data Definition Language (DDL) row log function of the source table, generating a DDL row log of the source table and starting to record data operation language (DML) operation of the source table from the starting time. The DDL row log is a system temporary table, and five fields such as SEQNO $, DMLTYPE $, OLD _ FLAG $, CHANGE _ MASK $, and XID $areadded on the basis of the table structure of a source table and are respectively used for recording a sequence number, a DML operation type, data before and after operation, a changed field recorded in a bitmap form, and a transaction number corresponding to the operation. And after the normal service row operation is finished, if the data table is judged to start the DDL row log function, calling a corresponding DDL row log operation interface to record related data in the corresponding row log. Specifically, the DDL line log operation interface may include three general entry interfaces DDL _ rlog _ insert, DDL _ rlog _ update, and DDL _ rlog _ delete, and records the relevant data of insertion, update, and deletion of the data table into the line log through a call interface. It should be noted that the names of the above fields and interfaces are only examples and are not strictly limited.
Step S103, storing the first table data in the source table to the target table based on the snapshot of the source table.
The method comprises the steps of obtaining snapshot data of a source table, copying first table data obtained based on the obtained snapshot data to a target table, wherein in the process, DML operation aiming at the source table is not affected and is carried out normally, relevant DML operation is recorded in a DDL row log of the source table, and a physical log is not recorded in the copying process. The first table data refers to original table data of the source table, that is, table data which is not recorded in the DDL row log, and the first table data in the source table can be indirectly acquired without affecting the source table through a snapshot mode.
Step S104, storing the second table data in the source table to the target table based on the DDL row log.
Scanning a DDL row log of a source table to obtain related DML operation data recorded in the DDL row log after the source table starts a DDL row log function, obtaining second table data in the source table according to the DDL row log which is obtained by scanning and stores the DML operation data, executing the DDL row log to copy the second table data in the source table to a target table, and deleting the table data which is copied to the target table in the DDL row log of the source table after the execution is finished. The second table data refers to updated data of the source table, is distinguished from original data of the source table obtained through snapshot, executes a DDL row log in which the DML operation is recorded in the source table, and stores the second table data of the source table to the target table.
And detecting whether the DDL row log is updated or not, when the DDL row log is detected to be updated, continuing to scan the current DDL row log, repeating the process, and copying the second table data to the target table until the DDL row log of the source table is not updated and the table data of the source table is completely migrated to the target table.
In other words, in this embodiment, the operation of scanning and executing the DDL row log of the source table is performed at least once until the DDL row log of the source table is completely executed, that is, the operation is stopped when it is detected that there is no update in the DDL row log.
Step S105, determining the table name of the target table based on the source table.
And immediately locking the source table and the target table after the DDL row logs of the source table are completely processed, wherein the table data is migrated, and the table locking operation is used for finishing the subsequent operation of table space migration and comprises the step of determining the table name of the target table based on the table name of the source table. Specifically, the table name of the source table is changed into a standard table name except the table names of the source table and the target table, and then the special table name of the target table is replaced by the table name of the source table before table space migration.
By applying the table space migration method provided by the embodiment of the application, a target table with the same table structure as that of a source table is created in a target table space, a Data Definition Language (DDL) row log function of the source table is used for generating a DDL row log of the source table, the DDL row log is used for recording data operation language (DML) operation aiming at the source table, first table data in the source table is stored to the target table based on a snapshot of the source table, second table data in the source table is stored to the target table based on the DDL row log, and a table name of the target table is determined based on the source table.
The method for automatically recording the changed data of the source table in the table space migration process through the DDL row log and migrating the original data and the changed data of the source table to the target table on line by combining the snapshot of the source table overcomes the technical problems that the table space migration is limited too much and the efficiency is low due to the fact that the table needs to be locked or a trigger is relied on in the existing table space migration, and therefore the technical effect that the accuracy and the integrity of the transferred table data are guaranteed while the table space migration is automatically on line in the database is achieved.
Another possible implementation manner is provided in the embodiment of the present application, as shown in fig. 2, including:
in step S201, a target table having the same table structure as the source table is created in the target table space.
And creating a data table with the same table structure as that of the source table needing data migration in the target table space, wherein the data table is the target table. Specifically, the table structure of the target table is the same as that of the source table, and the table name of the target table may be a special table name, for example, the table name of the target table is named by using a special prefix or suffix, so as to be conveniently matched with the source table in the database and distinguished from other data tables in the database. At this time, the target table does not establish an index, thereby reducing the system load and accelerating the execution process.
Step S202, generating a DDL row log of the source table through a data definition language DDL row log function of the source table, wherein the DDL row log is used for recording data operation language DML operation aiming at the source table.
And starting a Data Definition Language (DDL) row log function of the source table, generating a DDL row log of the source table and starting to record data operation language (DML) operation of the source table from the starting time. The DDL row log of the data definition language is a system temporary table, and five fields such as SEQNO $, DMLTYPE $, OLD _ FLAG $, CHANGE _ MASK $andXID $areadded on the basis of the table structure of a source table and are respectively used for recording a sequence number, a DML operation type, data before and after operation, a field for recording CHANGE in a bitmap form and a transaction number corresponding to the operation. And after the normal service row operation is finished, if the data table is judged to start the DDL row log function, calling a corresponding DDL row log operation interface to record related data in the corresponding row log. Specifically, the DDL line log operation interface may include three general entry interfaces DDL _ rlog _ insert, DDL _ rlog _ update, and DDL _ rlog _ delete, and records the relevant data of insertion, update, and deletion of the data table into the line log through a call interface. It should be noted that the names of the above fields and interfaces are only examples and are not strictly limited.
Step S203, the isolation level of the source table is promoted to be repeatable reading, and a snapshot of the source table is obtained.
The repeatable read isolation level is started by the source table, and based on the repeatable read isolation level, the original data of the source table can be distinguished from the incremental data after the DDL row log is started, so that the correctness of concurrent data reading is effectively guaranteed. And when the isolation level of the source table is increased to the repeatable read isolation level, acquiring the snapshot of the source table. The repeatable read isolation level is one of four isolation levels defined by the database standard, namely, the queries in the same transaction are consistent at the beginning time of the transaction, so that the snapshot of the source table is not influenced by the DML operation of the source table.
Step S204, storing the first table data in the source table to the target table based on the snapshot of the source table.
The method comprises the steps of obtaining snapshot data of a source table, copying first table data obtained based on the obtained snapshot data to a target table, wherein in the process, DML operation aiming at the source table is not affected and is carried out normally, relevant DML operation is recorded in a DDL row log of the source table, and a physical log is not recorded in the copying process. The first table data refers to original table data of the source table, that is, table data which is not recorded in the DDL row log, and the first table data in the source table can be indirectly acquired without affecting the source table through a snapshot mode.
Step S205, creating an index of the target table based on the index of the source table.
An index of the target table is created from the index definition of the source table. The database index is an internal implementation technology of a relational database management system, belongs to the category of internal modes, can quickly locate contents to be inquired, and can establish one or more indexes on a basic table to provide multiple access ways and accelerate the search speed. In this embodiment, after the snapshot data of the source table is copied, the index and the table constraint definition of the target table are created according to the index definition of the source table, and this process does not record a physical log, thereby reducing the system load.
Step S206, storing the second table data in the source table to the target table based on the DDL row log.
Scanning a DDL row log of a source table to obtain related DML operation data recorded in the DDL row log after the source table starts a DDL row log function, obtaining second table data in the source table according to the DDL row log which is obtained by scanning and stores the DML operation data, executing the DDL row log to copy the second table data in the source table to a target table, and deleting the table data which is copied to the target table in the DDL row log of the source table after the execution is finished. The second table data refers to updated data of the source table, is distinguished from original data of the source table obtained through snapshot, executes a DDL row log in which the DML operation is recorded in the source table, and stores the second table data of the source table to the target table.
And detecting whether the DDL row log is updated or not, when the DDL row log is detected to be updated, continuing to scan the current DDL row log, repeating the process, and copying the second table data to the target table until the DDL row log of the source table is not updated and the table data of the source table is completely migrated to the target table.
In other words, in this embodiment, the operation of scanning and executing the DDL row log of the source table is performed at least once until the DDL row log of the source table is completely executed, that is, the operation is stopped when it is detected that there is no update in the DDL row log.
Step S207, determining the table name of the target table based on the source table.
And immediately locking the source table and the target table after the DDL row logs of the source table are completely processed, wherein the table data is migrated, and the table locking operation is used for finishing the subsequent operation of table space migration and comprises the step of determining the table name of the target table based on the table name of the source table. Specifically, the table name of the source table is changed into a standard table name except the table names of the source table and the target table, and then the special table name of the target table is replaced by the table name of the source table before table space migration.
And step S208, deleting the source table, the index of the source table and the DDL row log of the source table.
And after the table space migration is completed, deleting the source table, the index of the source table, the DDL row log of the source table and data information related to the source table in the source table space, and releasing the table space occupied by the source table.
By applying the table space migration method provided by the embodiment of the application, a target table with the same table structure as that of a source table is created in a target table space, a Data Definition Language (DDL) row log function of the source table is used for generating a DDL row log of the source table, the DDL row log is used for recording data operation language (DML) operation aiming at the source table, the isolation level of the source table is promoted to be repeatable reading, a snapshot of the source table is obtained, first table data in the source table is stored to the target table based on the snapshot of the source table, an index of the target table is created based on the index of the source table, second table data in the source table is stored to the target table based on the DDL row log, a table name of the target table is determined based on the source table, and the source table, the index of the source table and the DDL row log of the source table are deleted.
The method for automatically recording the changed data of the source table in the table space migration process through the DDL row log and migrating the original data and the changed data of the source table to the target table on line by combining the snapshot of the source table overcomes the technical problems that the table space migration is limited too much and the efficiency is low due to the fact that the table needs to be locked or a trigger is relied on in the existing table space migration, and therefore the technical effect that the accuracy and the integrity of the transferred table data are guaranteed while the table space migration is automatically on line in the database is achieved.
The embodiment of the present application provides a possible implementation manner for storing the second table data in the source table to the target table based on the DDL row log, as shown in fig. 3, including:
step A, scanning the current DDL row log to obtain second table data in the source table.
And B, storing the second table data to the target table, and deleting the second table data in the DDL row log.
And step C, when detecting that the DDL row log is updated, acquiring the updated DDL row log.
And D, taking the updated DDL row log as the current DDL row log, and repeatedly executing the steps A to D until the current DDL row log is not updated.
Scanning a DDL row log of a source table to obtain related DML operation data recorded in the DDL row log after the source table starts a DDL row log function, obtaining second table data in the source table according to the DDL row log which is obtained by scanning and stores the DML operation data, executing the DDL row log to copy the second table data in the source table to a target table, and deleting the table data which is copied to the target table in the DDL row log of the source table after the execution is finished. The second table data refers to updated data of the source table, is distinguished from original data of the source table obtained through snapshot, executes a DDL row log in which the DML operation is recorded in the source table, and stores the second table data of the source table to the target table.
And detecting whether the DDL row logs are updated or not, when the DDL row logs are detected to be updated, continuing to scan the current DDL row logs, repeating the process, copying the second table data to the target table until the DDL row logs of the source table are not updated, and completely migrating the table data of the source table to the target table.
In other words, in this embodiment, the operation of scanning and executing the DDL row log of the source table is performed at least once until the DDL row log of the source table is completely executed, that is, the operation is stopped when it is detected that there is no update in the DDL row log.
By applying the table space migration method provided by the embodiment of the application, a target table with the same table structure as that of a source table is created in a target table space, a Data Definition Language (DDL) row log function of the source table is used for generating a DDL row log of the source table, the DDL row log is used for recording data operation language (DML) operation aiming at the source table, the isolation level of the source table is promoted to be repeatable reading, a snapshot of the source table is obtained, first table data in the source table is stored to the target table based on the snapshot of the source table, an index of the target table is created based on the index of the source table, second table data in the source table is stored to the target table based on the DDL row log, a table name of the target table is determined based on the source table, and the source table, the index of the source table and the DDL row log of the source table are deleted.
The method for automatically recording the changed data of the source table in the table space migration process through the DDL row log and migrating the original data and the changed data of the source table to the target table on line by combining the snapshot of the source table overcomes the technical problems that the table space migration is limited too much and the efficiency is low due to the fact that the table needs to be locked or a trigger is relied on in the existing table space migration, and therefore the technical effect that the accuracy and the integrity of the transferred table data are guaranteed while the table space migration is automatically on line in the database is achieved.
An embodiment of the present application provides a migration apparatus of a table space, as shown in fig. 4, the migration apparatus of a table space includes:
a creating module 401, configured to create a target table in the target table space, where the target table has the same table structure as the source table.
A first starting module 402, configured to generate a DDL row log of the source table through a data definition language DDL row log function of the source table, where the DDL row log is used to record a data operation language DML operation for the source table.
A first table data module 403, configured to store first table data in the source table to the target table based on the snapshot of the source table.
A second table data module 404, configured to store second table data in the source table to the target table based on the DDL row log.
A determining module 405, configured to determine a table name of the target table based on the source table.
In a preferred embodiment of the present application, before the first table data module, the method further includes:
and the second starting module is used for obtaining the snapshot of the source table when the isolation level of the source table is promoted to be repeatable reading.
In a preferred embodiment of the present application, after the first table data module, the method further includes:
an index module to create an index of the target table based on the index definition of the source table.
In a preferred embodiment of the present application, the apparatus further comprises:
and the deleting module is used for deleting the source table, the index of the source table and the DDL row log of the source table.
In a preferred embodiment of the present application, the second table data module includes:
the first processing submodule is used for scanning the current DDL row log to obtain second table data in the source table;
the second processing submodule is used for storing the second table data to the target table and deleting the second table data in the DDL row log;
the third processing submodule is used for acquiring the updated DDL row log when the fact that the DDL row log is updated is detected;
and taking the updated DDL row log as the current DDL row log, and repeatedly calling the first processing submodule, the second processing submodule and the third processing submodule until the current DDL row log is not updated.
By applying the table space migration device provided by the embodiment of the application, a target table with the same table structure as that of a source table is created in a target table space, a DDL row log of the source table is generated through a Data Definition Language (DDL) row log function of the source table, the DDL row log is used for recording data operation language (DML) operation aiming at the source table, the isolation level of the source table is promoted to be repeatable reading, a snapshot of the source table is obtained, first table data in the source table is stored to the target table based on the snapshot of the source table, an index of the target table is created based on the index of the source table, second table data in the source table is stored to the target table based on the DDL row log, a table name of the target table is determined based on the source table, and the source table, the index of the source table and the DDL row log of the source table are deleted.
The method for automatically recording the changed data of the source table in the table space migration process through the DDL row log and migrating the original data and the changed data of the source table to the target table on line by combining the snapshot of the source table overcomes the technical problems that the table space migration is limited too much and the efficiency is low due to the fact that the table needs to be locked or a trigger is relied on in the existing table space migration, and therefore the technical effect that the accuracy and the integrity of the transferred table data are guaranteed while the table space migration is automatically on line in the database is achieved.
An embodiment of the present application provides an electronic device, including: a memory and a processor; at least one program stored in the memory for execution by the processor, which when executed by the processor, implements: the method for automatically recording the changed data of the source table in the table space migration process through the DDL row log and migrating the original data and the changed data of the source table to the target table on line by combining the snapshot of the source table overcomes the technical problems that the table space migration is limited too much and the efficiency is low due to the fact that the table needs to be locked or a trigger is relied on in the existing table space migration, and therefore the technical effect that the accuracy and the integrity of the transferred table data are guaranteed while the table space migration is automatically on line in the database is achieved.
In an alternative embodiment, an electronic device is provided, as shown in fig. 5, the electronic device 5000 shown in fig. 5 includes: a processor 5001 and a memory 5003. The processor 5001 and the memory 5003 are coupled, such as via a bus 5002. Optionally, the electronic device 5000 may further include a transceiver 5004, and the transceiver 5004 may be used for data interaction between the electronic device and other electronic devices, such as transmission of data and/or reception of data. It should be noted that the transceiver 5004 is not limited to one in practical application, and the structure of the electronic device 5000 is not limited to the embodiment of the present application.
The Processor 5001 may be a CPU (Central Processing Unit), a general-purpose Processor, a DSP (Digital Signal Processor), an ASIC (Application Specific Integrated Circuit), an FPGA (Field Programmable Gate Array) or other Programmable logic device, a transistor logic device, a hardware component, or any combination thereof. Which may implement or perform the various illustrative logical blocks, modules, and circuits described in connection with the disclosure. The processor 5001 may also be a combination of processors implementing computing functionality, e.g., a combination comprising one or more microprocessors, a combination of DSPs and microprocessors, or the like.
The Memory 5003 may be a ROM (Read Only Memory) or other type of static storage device that can store static information and instructions, a RAM (Random Access Memory) or other type of dynamic storage device that can store information and instructions, an EEPROM (Electrically Erasable Programmable Read Only Memory), a CD-ROM (Compact Disc Read Only Memory) or other optical Disc storage, optical Disc storage (including Compact Disc, laser Disc, optical Disc, digital versatile Disc, blu-ray Disc, etc.), a magnetic Disc storage medium or other magnetic storage device, or any other medium that can be used to carry or store desired program code in the form of instructions or data structures and that can be accessed by a computer, but is not limited to these.
The memory 5003 is used for storing application program codes for executing the present solution, and the execution is controlled by the processor 5001. The processor 5001 is configured to execute application program code stored in the memory 5003 to implement the contents shown in the foregoing method embodiments.
The present application provides a computer-readable storage medium, on which a computer program is stored, which, when running on a computer, enables the computer to execute the corresponding content in the foregoing method embodiments. Compared with the prior art, the method for automatically recording the changed data of the source table in the table space migration process through the DDL row log and migrating the original data and the changed data of the source table to the target table on line by combining the snapshot of the source table overcomes the technical problems of excessive table space migration limitation and low efficiency caused by table locking or dependence on a trigger in the existing table space migration, thereby realizing the technical effect of ensuring the accuracy and the integrity of the migrated table data while automatically migrating the table space in the database on line.
It should be understood that, although the steps in the flowcharts of the figures are shown in order as indicated by the arrows, the steps are not necessarily performed in order as indicated by the arrows. The steps are not performed in the exact order shown and may be performed in other orders unless explicitly stated herein. Moreover, at least a portion of the steps in the flow chart of the figure may include multiple sub-steps or multiple stages, which are not necessarily performed at the same time, but may be performed at different times, which are not necessarily performed in sequence, but may be performed alternately or alternately with other steps or at least a portion of the sub-steps or stages of other steps.
The foregoing is only a partial embodiment of the present invention, and it should be noted that, for those skilled in the art, various modifications and decorations can be made without departing from the principle of the present invention, and these modifications and decorations should also be regarded as the protection scope of the present invention.
Claims (12)
1. A method for migrating a tablespace, comprising:
creating a target table with the same table structure as the source table in the target table space;
generating a DDL row log of the source table through a Data Definition Language (DDL) row log function of the source table, wherein the DDL row log is used for recording data operation language (DML) operation aiming at the source table;
storing first table data in the source table to the target table based on the snapshot of the source table;
storing second table data in the source table to the target table based on the DDL row log;
determining a table name of the target table based on the source table.
2. The method for migrating tablespace of claim 1, prior to the storing the first table data in the source table to the target table based on the snapshot of the source table, further comprising:
and the isolation level of the source table is promoted to be repeatable reading, and a snapshot of the source table is obtained.
3. The method for migrating tablespace of claim 1, after the storing the first table data in the source table to the target table based on the snapshot of the source table, further comprising:
an index of the target table is created based on the index of the source table.
4. The method for migrating tablespaces according to claim 1, further comprising:
and deleting the source table, the index of the source table and the DDL row log of the source table.
5. The method for migrating tablespace of claim 1, wherein the storing the second table data in the source table to the target table based on the DDL row log comprises:
step A, scanning a current DDL row log to obtain second table data in the source table;
step B, storing the second table data to the target table, and deleting the second table data in the DDL row log;
step C, when detecting that the DDL row log is updated, acquiring the updated DDL row log;
and D, taking the updated DDL row log as the current DDL row log, and repeatedly executing the steps A to D until the current DDL row log is not updated.
6. A migration apparatus of a tablespace, comprising:
the creating module is used for creating a target table with the same table structure as the source table in the target table space;
the first starting module is used for generating a DDL row log of the source table through a Data Definition Language (DDL) row log function of the source table, wherein the DDL row log is used for recording data operation language (DML) operation aiming at the source table;
a first table data module, configured to store first table data in the source table to the target table based on the snapshot of the source table;
a second table data module, configured to store second table data in the source table to the target table based on the DDL row log;
and the determining module is used for determining the table name of the target table based on the source table.
7. The tablespace migration apparatus according to claim 6, further comprising, before said first tablespace data module: a second starting module;
the second starting module is configured to obtain a snapshot of the source table when the isolation level of the source table is raised to be repeatable read.
8. The tablespace migration apparatus according to claim 6, further comprising, after the first tablespace data module: an indexing module;
the index module is used for creating the index of the target table based on the index definition of the source table.
9. The tablespace migration apparatus according to claim 6, further comprising: a deletion module;
and the deleting module is used for deleting the source table, the index of the source table and the DDL row log of the source table.
10. The tablespace migration apparatus according to claim 6, wherein said second tablespace data module comprises:
the first processing submodule is used for scanning the current DDL row log to obtain second table data in the source table;
the second processing submodule is used for storing the second table data to the target table and deleting the second table data in the DDL row log;
the third processing submodule is used for acquiring the updated DDL row log when the fact that the DDL row log is updated is detected;
and taking the updated DDL row log as the current DDL row log, and repeatedly calling the first processing submodule, the second processing submodule and the third processing submodule until the current DDL row log is not updated.
11. An electronic device, characterized in that the electronic device comprises:
one or more processors;
a memory;
one or more applications, wherein the one or more applications are stored in the memory and configured to be executed by the one or more processors, the one or more programs configured to: performing the method of migrating a tablespace according to any of claims 1-5.
12. A computer-readable storage medium, on which a computer program is stored, which, when being executed by a processor, carries out a method of migrating a tablespace according to any one of claims 1 to 5.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202011382623.1A CN114579530A (en) | 2020-11-30 | 2020-11-30 | Table space migration method and device, electronic equipment and computer readable storage medium |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202011382623.1A CN114579530A (en) | 2020-11-30 | 2020-11-30 | Table space migration method and device, electronic equipment and computer readable storage medium |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN114579530A true CN114579530A (en) | 2022-06-03 |
Family
ID=81767594
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202011382623.1A Pending CN114579530A (en) | 2020-11-30 | 2020-11-30 | Table space migration method and device, electronic equipment and computer readable storage medium |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN114579530A (en) |
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| WO2024087777A1 (en) * | 2022-10-26 | 2024-05-02 | 北京奥星贝斯科技有限公司 | Data reorganization method and apparatus for database table, medium, and computer device |
-
2020
- 2020-11-30 CN CN202011382623.1A patent/CN114579530A/en active Pending
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| WO2024087777A1 (en) * | 2022-10-26 | 2024-05-02 | 北京奥星贝斯科技有限公司 | Data reorganization method and apparatus for database table, medium, and computer device |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US10754875B2 (en) | Copying data changes to a target database | |
| CN107391628B (en) | Data synchronization method and device | |
| US10990628B2 (en) | Systems and methods for performing a range query on a skiplist data structure | |
| US7769719B2 (en) | File system dump/restore by node numbering | |
| JP2020502626A (en) | Formation and operation of test data in a database system | |
| CN108205464B (en) | Database deadlock processing method and device and database system | |
| US20080005183A1 (en) | Method And Apparatus for Propagating Tables While Preserving Cyclic Foreign Key Relationships | |
| CN108376156B (en) | Method, device, server and storage medium for creating database index | |
| CN107665219B (en) | Log management method and device | |
| CN109033278B (en) | Data processing method and device, electronic equipment and computer storage medium | |
| CN109213432B (en) | Storage device for writing data using log structured merge tree and method thereof | |
| CN114600094A (en) | Generating hash trees for database architectures | |
| CN115757629A (en) | Multi-source heterogeneous data increment synchronization method and system, storage medium and electronic equipment | |
| CN111324620A (en) | Data processing method, device and storage medium | |
| US11061889B2 (en) | Systems and methods of managing manifest refresh in a database | |
| CN114579530A (en) | Table space migration method and device, electronic equipment and computer readable storage medium | |
| CN115687359A (en) | Data table partitioning method and device, storage medium and computer equipment | |
| CN115658391A (en) | Backup recovery method of WAL mechanism based on QianBase MPP database | |
| CN114385657A (en) | Data storage method, device and storage medium | |
| CN110413617B (en) | Method for dynamically adjusting hash table group according to size of data volume | |
| CN109165208B (en) | Method and system for loading data into database | |
| CN109241027B (en) | Data migration method, device, electronic equipment and computer readable storage medium | |
| CN112965939A (en) | File merging method, device and equipment | |
| CN115114258A (en) | Data copying method and device, electronic equipment and computer storage medium | |
| CN114328500A (en) | Data access method, device, equipment and computer readable storage medium |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination |