CN109492000A - A kind of clone method and system of database table - Google Patents

Abstract

The present application discloses a method and system for replicating a database table. The method first acquires all table data blocks matching the source table, then copies all table data blocks and stores them in the storage area of the target table, and finally according to the target table The storage address and target table name are updated, and the block header and table directory of all table data blocks are updated, thereby completing the replication of the database table. The system includes a table data block acquisition module, a replication module and an update module. The application adopts the method of data block replication to realize table data replication, which avoids the traditional migration process of reading table records in data blocks and then inserting table records into data blocks, which can greatly improve the replication efficiency of database tables, and because there are no table records Read and insert operations are also beneficial to save CPU resources.

Description

Method and system for copying database table

technical field

The present application relates to the technical field of databases, and in particular, to a method and system for replicating database tables.

Background technique

In the process of using the database, it is often encountered that the data of one table is completely copied to another table, that is, the replication of the database table.

The current method of copying database tables is usually the method of querying records from tables and inserting them. Specifically, the database provides a method similar to the create table XX as select*from YY statement for creating tables and copying data. After executing the statement, first use select to query the table records from the initial table, read the table records, and then Insert table records one by one into the new table until the entire table is copied.

However, in the current database table replication method, since each table record needs to be queried in the initial table and inserted into the new table one by one, the larger the initial table is, the slower the query speed will be, and the larger the source table, the slower the replication process will be for the system I. The more /O is occupied, which will put pressure on the system I/O. Long-term large table queries will also occupy more CPU resources, resulting in CPU resource consumption. Therefore, the current method of copying tables is not efficient enough and occupies a lot of CPU resources.

SUMMARY OF THE INVENTION

The present application provides a method and system for replicating a database table, so as to solve the problems in the prior art that the replication efficiency of the database table is low and occupies more CPU resources.

In order to solve the above technical problems, the embodiments of the present application disclose the following technical solutions:

A method for replicating a database table, the method comprising:

S1: Get all table data blocks that match the source table;

S2: copy all the table data blocks, and store the all table data blocks in the storage area of the target table;

S3: Update the block headers and table directories of all table data blocks according to the storage address of the target table and the target table name.

Optionally, step S2 includes:

S21: Copy all the table data blocks at one time, and store all the table data blocks in the storage area of the target table; or,

S22: Copy all the table data blocks by using the incremental synchronous replication method, and store all the table data blocks in the storage area of the target table.

Optionally, step S21 includes:

S211: Obtain the source table structure according to the obtained table copy command create table XX copy from YY, and create the target table;

S212: Read and copy the table data blocks in the source table storage area in turn according to the number of table data blocks set by the first parameter, where the first parameter is used to set the number of table data blocks to be read and copied at one time .

Optionally, the number of table data blocks read at one time set by the first parameter is 64.

Optionally, step S22 includes:

S221: Obtain the source table structure according to the obtained table copy command create table XX integrate copy from YY, and create the target table;

S222: Read and copy the table data blocks in the source table storage area in turn according to the number of table data blocks set by the first parameter;

S223: When the table record in the read table data block changes, record the changed table data block in the table data block change record file according to a second parameter, where the second parameter is used to set the table The path and file name of the data block change record file;

S224: According to the obtained table incremental synchronization command start integrate incr copy, sequentially read the table data blocks in the table data block change record file in chronological order, and convert the table data blocks in the table data block change record file The blocks are synchronized to the target table in turn;

S225: Delete the read table data block in the table data block change record file.

S226: Return to step S223 until the table data block in the target table is completely consistent with the table data block in the source table.

Optionally, the storage content of the table data block change record file includes: the changed table data block, the time stamp of the change in the table data block, and the user and table name to which the changed table data block belongs, and the storage content is according to are stored in chronological order.

A system for replicating a database table, the system comprising:

The table data block obtaining module is used to obtain all table data blocks matching the source table;

a copying module for copying all the table data blocks and storing the all table data blocks in the storage area of the target table;

An update module, configured to update the block headers and table directories of all the table data blocks according to the storage address of the target table and the target table name.

Optionally, the replication module includes: a first replication unit or a second replication unit;

the first copying unit, configured to copy all the table data blocks at one time, and store the all table data blocks in the storage area of the target table;

The second copying unit is configured to copy all the table data blocks by adopting the method of incremental synchronous replication, and store all the table data blocks in the storage area of the target table.

Optionally, the first replication unit includes:

The first target table creation subunit is used to obtain the source table structure according to the obtained table copy command create table XX copyfrom YY, and create the target table;

The first reading and copying subunit is used to sequentially read and copy the table data blocks in the source table storage area according to the number of table data blocks set by the first parameter, and the first parameter is used to set once Read the number of replicated table data blocks.

Optionally, the second replication unit includes:

The second target table creation subunit is used to obtain the source table structure according to the obtained table copy command create table XXintegrate copy from YY, and create the target table;

The second reading and copying subunit is used for sequentially reading and copying the table data blocks in the source table storage area according to the number of table data blocks set by the first parameter;

The change record subunit is used to record the changed table data block in the table data block change record file according to the second parameter when the table record in the read table data block changes, the second parameter Used to set the path and file name of the table data block change record file;

The incremental synchronization subunit is used to sequentially read the table data blocks in the table data block change record file according to the obtained table incremental synchronization command start integrate incrcopy, and record the table data block changes The table data blocks in the file are synchronized to the target table;

Clearing the subunit for deleting the table data block read in the table data block change record file;

The loop subunit is used to execute the table copy command create table XX integrate copy from YY, the table records in the source table data block change again, and during the execution of the table incremental synchronization command start integrateincr copy, the table data block that has been synchronized changes again. When a change occurs, restart the change record subunit, incremental synchronization subunit, and clear subunit until the table data blocks in the target table and the table data blocks in the source table are exactly the same.

The technical solutions provided by the embodiments of the present application may include the following beneficial effects:

The present application provides a method for copying a database table. The method first obtains all table data blocks matching the source table, then directly copies all table data blocks, and stores all table data blocks in the storage area of the target table, and finally according to The storage address and target table name of the target table are updated, and the block header and table directory of all table data blocks are updated, thereby completing the replication of the database table. The present application adopts the method of data block replication to realize table data replication, which avoids the process of reading table records in data blocks and inserting table records into data blocks. Therefore, the replication efficiency of database tables can be greatly improved, and since there is no table record read Fetch and insert operations are also beneficial to save CPU resources. In addition, when copying data blocks in this application, two copying methods are provided: one is to copy all table data blocks at one time, and store all table data blocks in the storage area of the target table. This copying method is suitable for static tables. Replication can greatly improve the replication efficiency; the other is to use incremental synchronous replication. During the full table replication process, when a table data block is read, if it changes again, the changed table data block will be recorded. To the table data block change record file, the table data blocks in the file will be synchronized to the target table when the table is incrementally synchronized. This method can further improve the consistency of the source table data and the target table data, thereby improving the accuracy of replication. sex.

The present application also provides a database table replication system, which mainly includes a table data block acquisition module, a replication module and an update module. First, all table data blocks matching the source table are obtained through the table data block acquisition module, then all table data blocks are copied through the copy module and stored in the storage area of the target table, and finally the update module is based on the storage address of the target table and Target table name, update the block header and table directory of all table data blocks. In the present application, the data block copy method is adopted to copy the table data from the source table to the target table, which saves the time for reading the table records in the data block and inserting the table records into the data block, and greatly shortens the data copy time. Therefore, it can effectively improve the The replication efficiency of database tables, and because there is no table in the system to record read and insert operations, is also conducive to saving CPU resources.

It is to be understood that both the foregoing general description and the following detailed description are exemplary and explanatory only and are not limiting of the present application.

Description of drawings

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

In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, the following briefly introduces the accompanying drawings that need to be used in the description of the embodiments or the prior art. In other words, other drawings can also be obtained based on these drawings without creative labor.

1 is a schematic flowchart of a method for replicating a database table according to an embodiment of the present application;

2 is a schematic diagram of the format of a data block in an embodiment of the present application;

FIG. 3 is a schematic structural diagram of a system for replicating a database table according to an embodiment of the present application.

Detailed ways

In order to make those skilled in the art better understand the technical solutions in the present application, the technical solutions in the embodiments of the present application will be described clearly and completely below with reference to the accompanying drawings in the embodiments of the present application. Obviously, the described The embodiments are only a part of the embodiments of the present application, but not all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by those of ordinary skill in the art without creative work shall fall within the scope of protection of the present application.

For a better understanding of the present application, the embodiments of the present application are explained in detail below with reference to the accompanying drawings.

Referring to FIG. 1 , FIG. 1 is a schematic flowchart of a method for copying a database table according to an embodiment of the present application. As can be seen from Figure 1, the method for replicating the database table in this embodiment mainly includes the following steps:

S1: Get all table data blocks that match the source table.

Database table replication refers to copying the data from the source table to the target table, and all records of the database table are stored in data blocks. Therefore, it is necessary to obtain all table data blocks of the source table first.

A database table contains multiple data blocks, a data block can only store records of a certain database table, and will not store records of different database tables at the same time, and a data block can store multiple records of a database table, even up to Hundreds of thousands of records.

A data block is usually 8K in size, and the format of the data block can be seen in Figure 2. As can be seen from Figure 2, in a data block, according to the different functions of each part, the data block storage area can be divided into five areas: block header, table directory, row directory, row data and free space, and each area has its own function. The block header is used to store basic information of the data block, such as the physical address of the data block, the type of the segment to which the data block belongs, and the type of the segment to which the data block belongs includes the data segment and the index segment. The table directory is used to store table information, that is, if the data of some tables is stored in this data block, the related information of these tables will be stored in the "table directory". The row directory is used to record row information, that is, if row data exists in the data block, the row information will be recorded in the row directory, where the row information includes row addresses and the like. Row data is used to store table data and index data, and is the space for storing table records. Free space is used to store row records, and when records are inserted into database tables, free space is reduced.

After acquiring all the table data blocks matched by the source table, step S2 is performed: copying all the table data blocks, and storing all the table data blocks in the storage area of the target table.

In this embodiment, there are two methods for replicating table data blocks: one is to perform replication only once, and the table data changed during the replication process will not be replicated again; the other is an incremental synchronous replication method. Specifically, step S2 includes the following two situations:

S21: Copy all table data blocks at one time, and store all table data blocks in the storage area of the target table.

When the source table is a static table, after the method in step S21 is used for replication, the data of the source table and the target table are completely consistent. Since the data block is copied directly at one time, the process of reading the table record and inserting the table record into the data block is avoided. Therefore, the replication efficiency of the database table can be greatly improved, and since there is no table record read and insert operations, a lot of CPU resources can be saved.

Specifically, step S21 includes the following processes:

S211: Obtain the source table structure according to the obtained table copy command create table XX copy from YY, and create a target table.

That is: obtain the source table structure according to the obtained table copy command create table XX copy from YY, and then create the target table according to the table copy command and the source table structure. The source table structure can be obtained through commands, thereby avoiding manual obtaining and improving the efficiency of data replication.

S212: Read and copy the table data blocks in the source table storage area in turn according to the number of table data blocks set by the first parameter, where the first parameter is used to set the number of table data blocks read at one time during table copying .

It can be seen from the above steps S211 and S212 that the method for directly copying data blocks at one time in this embodiment adopts the table copy command create table XX copy from YY. After executing the command, first create the target table according to the structure of the source table, then start from the first table data block stored in the table, read and copy a batch of table data blocks according to the number of table data blocks specified by the first parameter, read After the fetching and copying is completed, read and copy the next batch of table data blocks according to the number of table data blocks specified by the first parameter, and return to the command line after all table data blocks are copied.

In this embodiment, the first parameter may use the parameter copy_table_block_count. The number of table data blocks read at one time set by the first parameter can be adjusted according to the storage performance of the server. If this parameter is set too small, storage reads will be performed frequently, thus increasing the number of storage reads and affecting efficiency. ; If this parameter is set too large, it will take up too much memory. In this embodiment, the number of table data blocks read at one time is preferably 64, that is, the parameter setting of 64 table data blocks read and copied at one time can not only improve the efficiency of data synchronization, but also not cause pressure on the system or cause too much performance influences.

When the method of step S21 is adopted, the data blocks in the table are read and copied only once, which is applicable to the replication of static tables.

When the replicated database table is a dynamic table, this embodiment also provides another method for table replication, that is, step S22 : replicate all table data blocks by means of incremental synchronous replication, and store all table data blocks to the target The storage area for the table.

Specifically, step S22 includes the following processes:

S221: Obtain the structure of the source table according to the obtained table copy command create table XX integrate copy from YY, and create the target table.

That is: obtain the source table structure according to the obtained table copy command create table XX integrate copy from YY, and then create the target table according to the table copy command create table XX integrate copy from YY and the source table structure.

S222: Read and copy the table data blocks in the source table storage area in sequence according to the number of table data blocks set by the first parameter.

S223: when the table record in the read table data block changes, record the changed table data block in the table data block change record file according to the second parameter, and the second parameter is used to set the table data block Change the path and file name of the log file.

In this embodiment, the second parameter may use the parameter copy_table_block_change_trace. From the start of executing the table copy command create table XX integrate copy from YY to perform a complete copy of the entire table, to the completion of the execution of the table copy command, when the table data block in the source table changes after being read, the changed table data will be Blocks are logged to the table data block change log file. If during the table data block copying process, the copied table data block has changed many times, the table data block after multiple changes will also be recorded in the table data block change record file.

The content stored in the table data block change record file includes: the changed table data block, the time stamp of the change of the table data block, and the user and table name to which the changed table data block belongs, and the stored content is stored in chronological order. In this embodiment, different database tables can be replicated at the same time. When full table full replication of different database tables is performed at the same time, the changed table data blocks of different database tables are all recorded in the table data block change record file in chronological order.

S224: According to the obtained table incremental synchronization command start integrate incr copy, sequentially read the table data blocks in the table data block change record file in chronological order, and sequentially synchronize the table data blocks in the table data block change record file to target table.

After executing the table incremental synchronization command start integrate incr copy, the change table data blocks recorded in the table data block change record file will be synchronized to the corresponding target table in time sequence. If the incremental synchronization command has not been completed and the synchronized table data block has changed again, the changed table data block is added to the table data block change record file again, and the synchronization is performed again.

S225: Delete the read table data block in the table data block change record file.

Read the table data blocks in the table data block change record file, and synchronize the table data blocks in the table data block change record file to the target table in turn, which is equivalent to updating the table data blocks of the target table. After the data update is completed, Clears the read table data blocks in the table data block change record file in time, thereby improving the efficiency of database table replication.

S226: Return to step S223 until the table data block in the target table is completely consistent with the table data block in the source table.

During the execution of the table copy command create table XX integrate copy from YY, when the table records in the table data block of the source table change again, and during the execution of the table incremental synchronization command start integrate incr copy, the synchronized table data block changes again , return to step S223, that is, execute steps S223, S224 and S225 again, until the table data block in the target table is completely consistent with the table data block in the source table.

It can be seen from the above steps S221-S226 that, in the method for incremental synchronous replication in this embodiment, the table replication command create table XX integrate copy from YY is adopted. After the command is executed, the target table will be created according to the structure of the source table, and then, starting from the first table data block stored in the table, the table data blocks will be read and copied in sequence according to the number of table data blocks specified by the first parameter. If the table records in the table data block are changed after being read, the table data block whose table records are changed will be recorded in the file specified by the second parameter. In this embodiment, the table data block specified by the second parameter The file is the table data block change record file. Obviously, after the database table is copied, multiple table data blocks may be recorded in the file specified by the second parameter. In order to synchronize the changed table data blocks to the target table, this embodiment adopts the table incremental step command start integrate incr copy. After the command is executed, the table data block change record file specified by the copy_table_block_change_trace parameter will be read in chronological order, and the The table data blocks changed in the table data block change record file are synchronized to the target table. Finally, the read table data blocks will be deleted from the table data block change record file until the last table data in the table data block change record file is deleted. After the block is processed, return to the command line, and the synchronization of the change table data block is completed.

In this implementation, the changed table data blocks are recorded in the table data block change record file, and the table data blocks in the file are synchronized to the target table when the table is incrementally synchronized, which can further improve the relationship between the source table data and the target table data. consistency, thereby improving the accuracy of replication. In addition, the cyclic mode is set in this embodiment, and multiple incremental synchronous replication can be performed, thereby improving the replication accuracy and replication efficiency.

S3: Update the block headers and table directories of all table data blocks according to the storage address of the target table and the target table name.

The storage address of the target table in this embodiment refers to the actual storage address of the table data block in the target table. After all the copied table data blocks are stored in the storage area of the target table, write the new table data block storage address into the table data block header of the target table, and change the table name in the table data block table directory of the target table to Target table name. By executing step S3, the new data block is turned into a data block of the target table, and corresponding row records are read from these new data blocks when querying the data of the target table.

Referring to FIG. 3 on the basis of the embodiments shown in FIG. 1 and FIG. 2 , FIG. 3 is a schematic structural diagram of a database table replication system provided by an embodiment of the present application. It can be seen from FIG. 3 that the replication system of the database table in this application mainly includes three parts: a table data block acquisition module, a replication module and an update module. Among them, the table data block acquisition module is used to obtain all table data blocks that match the source table; the copy module is used to copy all table data blocks and store all table data blocks in the storage area of the target table; the update module is used to The storage address and target table name of the target table, update the block header and table directory of all table data blocks.

The replication module further includes a first replication unit or a second replication unit, the first replication unit is used for static table replication, and the second replication unit is used for dynamic table replication. Specifically, the first copying unit is used for copying all table data blocks at one time, and storing all table data blocks in the storage area of the target table; the second copying unit is used for copying all table data blocks by adopting the method of incremental synchronous copying , and store all table data blocks to the storage area of the target table.

Further, the first copying unit includes: a first target table creation subunit and a first reading and copying subunit. The first target table creation subunit is used for obtaining the source table structure according to the obtained table copy command create table XX copy fromYY, and creates the target table; the first reading and copying subunit is used for setting according to the first parameter The number of table data blocks in the source table is read and copied in turn. The first parameter is used to set the number of table data blocks to be read and copied at one time.

The second copying unit includes: a second target table creation subunit, a second reading and copying subunit, a change recording subunit, an incremental synchronization subunit, a clearing subunit, and a looping subunit. Wherein, the second target table creation subunit is used to obtain the source table structure according to the obtained table copy command create table XX integrate copy from YY, and create the target table; the second read and copy subunit is used to The set number of table data blocks, read and copy the table data blocks in the source table storage area in turn; the change record subunit is used for when the table records in the read table data block change, according to the second The parameter records the changed table data block to the table data block change record file, and the second parameter is used to set the path and file name where the table data block change record file is located; the incremental synchronization subunit is used to The incremental synchronization command start integrate incr copy reads the table data blocks in the table data block change record file in chronological order, and synchronizes the table data blocks in the table data block change record file to the target table; Delete the read table data block in the table data block change record file; the loop subunit is used to execute the table copy command create table XX integrate copy from YY, the table record in the table data block of the source table changes again, and the execution table During the incremental synchronization command start integrate incr copy, when the synchronized table data block changes again, restart the change record subunit, incremental synchronization subunit, and clear subunit until the table data block in the target table and the source table The table data blocks are completely consistent.

The working principle and working method of the database table replication system have been described in detail in the database table replication method shown in FIG. 1 and FIG. 2 , and will not be repeated here.

The replication system of the present application replicates the table data from the source table to the target table by means of data block replication, which can save the time for reading the table records in the data block and inserting the table records into the data block, and greatly shortens the data replication time. Therefore, It can effectively improve the replication efficiency of database tables, and because there is no table record read and insert operations in the system, it is also beneficial to save CPU resources.

The above descriptions are only specific embodiments of the present application, so that those skilled in the art can understand or implement the present application. Various modifications to these embodiments will be readily apparent to those skilled in the art, and the generic principles defined herein may be implemented in other embodiments without departing from the spirit or scope of the present application. Therefore, this application is not intended to be limited to the embodiments shown herein, but is to be accorded the widest scope consistent with the principles and novel features disclosed herein.

Claims (10)

1. a replication method of database table, is characterized in that, described method comprises: S1: Get all table data blocks that match the source table; S2: copy all the table data blocks, and store the all table data blocks in the storage area of the target table; S3: Update the block headers and table directories of all table data blocks according to the storage address of the target table and the target table name. 2. The method for replicating a database table according to claim 1, wherein step S2 comprises: S21: Copy all the table data blocks at one time, and store all the table data blocks in the storage area of the target table; or, S22: Copy all the table data blocks by using the incremental synchronous replication method, and store all the table data blocks in the storage area of the target table. 3. The method for replicating a database table according to claim 2, wherein step S21 comprises: S211: Obtain the source table structure according to the obtained table copy command create table XX copy from YY, and create the target table; S212: Read and copy the table data blocks in the source table storage area in turn according to the number of table data blocks set by the first parameter, where the first parameter is used to set the number of table data blocks to be read and copied at one time . 4 . The method for replicating a database table according to claim 3 , wherein the number of table data blocks read at one time set by the first parameter is 64. 5 . 5. The method for replicating a database table according to claim 2, wherein step S22 comprises: S221: Obtain the source table structure according to the obtained table copy command create table XX integrate copy from YY, and create the target table; S222: Read and copy the table data blocks in the source table storage area in turn according to the number of table data blocks set by the first parameter; S223: When the table record in the read table data block changes, record the changed table data block in the table data block change record file according to a second parameter, where the second parameter is used to set the table The path and file name of the data block change record file; S224: According to the obtained table incremental synchronization command start integrate incr copy, sequentially read the table data blocks in the table data block change record file in chronological order, and convert the table data blocks in the table data block change record file The blocks are synchronized to the target table in turn; S225: delete the read table data block in the table data block change record file; S226: Return to step S223 until the table data block in the target table is completely consistent with the table data block in the source table. 6. The method for replicating a database table according to claim 5, wherein the storage content of the table data block change record file comprises: the changed table data block, the time stamp of the change in the table data block, and the change The user and table name to which the table data block belongs, and the storage content is stored in chronological order. 7. A system for replicating a database table, wherein the system comprises: The table data block obtaining module is used to obtain all table data blocks matching the source table; a copying module for copying all the table data blocks and storing the all table data blocks in the storage area of the target table; An update module, configured to update the block headers and table directories of all the table data blocks according to the storage address of the target table and the target table name. 8. The system for copying a database table according to claim 7, wherein the copying module comprises: a first copying unit or a second copying unit; the first copying unit, configured to copy all the table data blocks at one time, and store the all table data blocks in the storage area of the target table; The second copying unit is configured to copy all the table data blocks by adopting the method of incremental synchronous replication, and store all the table data blocks in the storage area of the target table. 9. The replication system of a database table according to claim 8, wherein the first replication unit comprises: The first target table creation subunit is used to obtain the source table structure according to the obtained table copy command create table XX copy fromYY, and create the target table; The first reading and copying subunit is used to sequentially read and copy the table data blocks in the source table storage area according to the number of table data blocks set by the first parameter, and the first parameter is used to set once Read the number of replicated table data blocks. 10. The system for replicating a database table according to claim 8, wherein the second replication unit comprises: The second target table creation subunit is used to obtain the source table structure according to the obtained table copy command create table XX integratecopy from YY, and create the target table; The second reading and copying subunit is used for sequentially reading and copying the table data blocks in the source table storage area according to the number of table data blocks set by the first parameter; The change record subunit is used to record the changed table data block in the table data block change record file according to the second parameter when the table record in the read table data block changes, the second parameter Used to set the path and file name of the table data block change record file; The incremental synchronization subunit is used to sequentially read the table data blocks in the table data block change record file according to the obtained table incremental synchronization command start integrate incr copy, and change the table data blocks The table data blocks in the record file are synchronized to the target table; Clearing the subunit for deleting the table data block read in the table data block change record file; The loop subunit is used to execute the table copy command create table XX integrate copy from YY, the table records in the source table data block change again, and during the execution of the table incremental synchronization command start integrate incrcopy, the already synchronized table data block again When a change occurs, restart the change record subunit, incremental synchronization subunit, and clear subunit until the table data blocks in the target table and the table data blocks in the source table are exactly the same.