Developers must see! The migration path you want to know is here

Abstract: database migration is for business migration, and whether the business can be successfully switched depends on the migration ability of the database and the accuracy after the migration. From the perspective of the business side, at least the following three correctness can be met. Do business switching.

This article is shared from the HUAWEI CLOUD community " HUAWEI CLOUD GaussDB (for openGauss) special live broadcast No. 4: With the right migration tool, migration can also be very simple ", the original author: Scheming Fat.

1. Background introduction

With the continuous development of the GaussDB (for openGauss) database, more and more customers are choosing to use GaussDB (for openGauss). A large part of the customers are replacing their existing systems with GaussDB (for openGauss). The customers currently use GaussDB (for openGauss). There are many types of databases, such as Oracle, MySQL, PostgreSQL, etc. So how to solve the problem of migrating customers' current database to GaussDB (for openGauss) is an urgent need. The GDS data migration tool that comes with GaussDB (for openGauss) realizes efficient data migration between GaussDB (for openGauss), but it cannot solve heterogeneous synchronization and real-time synchronization scenarios. Huawei Cloud Database Migration Tool DRS provides GaussDB (for openGauss) with heterogeneous migration and real-time synchronization capabilities with an easy-to-use, stable, and efficient cloud service, helping customers to easily migrate databases to GaussDB (for openGauss).

2. Overall solution for database migration

The purpose of database migration is for business migration, and whether the business can be successfully switched depends on the migration capability of the database and the accuracy after migration. From the perspective of the business side, at least the following three correctnesses must be met before the business switching .

• Object migration is correct

All database objects such as stored procedures, functions, triggers, table structures, indexes, etc. of the database can be completely migrated to the target database, and the operating logic of the objects can be guaranteed to be consistent with the source database.

• Data migration is correct

Migrate the full amount of data from the source database to the target database. When the business requires a downtime window, consider full + incremental online migration to ensure uninterrupted business. At the same time, it is necessary to be able to verify the synchronized data to ensure the accuracy of the migrated data.

• migration, the business operation is correct

When the objects and data are migrated to the target database, there are still two risk points in business switching. One is whether the business results in the target database are correct, and the other is whether the performance of the target database can support the load of the business like the source database. . Because the differences between heterogeneous databases are still very large, there are many differences in design concepts and implementation methods, which will result in two objects that look similar, and their operating results or efficiency are completely different. Therefore, there must be tools to verify this difference and ensure the correctness of the business operation after the migration.

In order to achieve the above conditions that need to be met for business switching, HUAWEI CLOUD provides an overall solution for database migration, through four tool products including syntax migration (UGO), DRS-data migration, DRS-data verification and DRS-traffic playback. The closed loop of the migration process.

• Syntax Migration (UGO)

The ability to migrate oracle database objects to GaussDB (for openGauss) is realized, and a complete migration evaluation report can be given, which objects can be fully compatible for migration, which objects need to be converted for migration, and which objects need business cooperation transformation.

• DRS-Data Migration

Realize the ability of real-time migration of database data such as Oracle, MySQL, and PostgreSQL to GaussDB (for openGauss).

• DRS-Data Check

It has realized the consistency check after data migration, and has the ability of row-level comparison, content-level comparison and real-time incremental data comparison.

• DRS-Traffic playback

Realize the ability to capture the business traffic of the Oracle database, convert the traffic SQL, and then replay it in GaussDB (for openGauss).

3. DRS data migration to the cloud

DRS provides a simple and easy-to-use operation interface, and adopts a process-based configuration method. Customers can build a synchronization link by following the prompt steps step by step. In addition to supporting data migration from Oracle to GaussDB (for openGauss), DRS also supports data synchronization between other databases. The following is a list of the source and target database types supported by the current DRS.

In the process of data migration, DRS uses many methods and technologies to reduce possible risks and ensure the stability of the migration process and the consistency of the final data.

• online migration

DRS migrates the existing data in the customer database to GaussDB (for openGauss) through full migration, analyzes the source database logs in real time through incremental synchronization, and synchronizes the customer's real-time change data to Gauss (for openGauss) through full and incremental The seamless connection to ensure that customers can completely migrate all data to GaussDB (for openGauss) without interrupting their business.

• pre-check

Before the start of the DRS migration task, in order to discover possible risks or errors after the migration is started, DRS introduces a pre-verification link, which can verify the configuration information, database compatibility information, connectivity information, etc. in advance, and at the same time, Some conditions that can be successfully migrated but may have an impact on the business will be alerted, so that customers can find out in time and deal with them in advance.

• Breakpoint mechanism

In order to ensure the consistency of data migration, DRS has a breakpoint mechanism in each component, which can ensure the accuracy of data synchronization regardless of normal start and stop, abnormal restart or failover scenarios, and will not cause data Lost.

4. DRS technology realization principle

DRS is mainly divided into two large modules in terms of technical implementation, one is full data synchronization, and the other is incremental data synchronization. Full synchronization solves the migration of static data, and incremental synchronization solves the migration of real-time changing data.

Fully synchronized technical architecture

The overall logic of full synchronization is relatively simple, that is, to query the data from the source library through select, and then write the data to the target library, but there will be some key technical points in the specific code implementation.

• Data fragmentation

Generally, the synchronization granularity of full synchronization products can reach table-level concurrency, that is, multiple threads can synchronize multiple tables at the same time, but there are often cases where the amount of data in a single table is particularly large in the customer's system, such as a table For billions or even tens of billions of data, the synchronization time of this table becomes the time of the entire synchronization. So how to further improve the synchronization efficiency of a single table? We can further split a single table, split it into multiple shards according to the primary key, and multi-threads will synchronize in parallel with the shard as the unit.

The current DRS fragments the table according to the following strategy:

• No primary key table is not fragmented
• The partition table is synchronized by partition, and each partition is no longer fragmented
• Tables with a primary key are sharded by the primary key (first column)

• Data is not placed

In order to reduce the disk occupation during the full synchronization process, the data exported by DRS is not cached on disk, but is directly passed to the import thread through the memory. When the export and import rates are equivalent, the efficiency of the full synchronization can be maximized. .

• Breakpoint control

Halfway interruption of full synchronization is a very tricky problem. A table of 200 million data may be synchronized to 180 million due to the network or source database snapshots that cause the synchronization to fail. If there is no good breakpoint Control mechanism, the previous efforts may be wasted, and you have to re-synchronize again. DRS saves records by using fragments as breakpoints. For the above example, even if the synchronization is interrupted, it can be pulled up again, and after pulling up, the successfully synchronized fragments will no longer be synchronized, and there is no synchronization yet. Will continue to sync.

• flow control

The customer’s business often has peak and low peak periods. During the peak period, the resource consumption of the database is the highest. We should try to avoid doing full synchronization during the peak period of the business, because full synchronization affects the cpu, memory, and memory of the source database. The network resource occupancy is very large. DRS uses a flow control mechanism to reduce the resource occupation of the source database during peak business periods. It is mainly by controlling network traffic. Customers can set the time period for flow control. DRS will calculate the synchronization in real time during the full synchronization process. The amount of traffic. After running to this period, when the traffic exceeds the set threshold, it will slow down the speed of data acquisition. After running for this period, all data synchronization speed will be restored.

• Fully + incremental seamless connection

In the business cut database scenario, there are generally two options for the data migration process. One is to migrate the data from the source database to the target database at one time, but a business downtime window is required. The size of this window depends on the full data migration. time. When the amount of data is large, the entire migration process may take several days, and this kind of business downtime is unacceptable. So another solution is a data migration solution without business interruption. Its realization principle is based on the seamless connection of full migration and incremental synchronization. For the migration of Oracle->GaussDB (for openGauss), Oracle database provides designated scn for migration. Snapshot export function. Based on this feature, DRS specifies scn for export when doing full synchronization, so that the entire fully synchronized data is the snapshot data before this scn point, and then incremental synchronization uses this scn point as the synchronization demarcation point. Only incremental transactions larger than this scn will be synchronized to the target database. In this way, the seamless connection between full and incremental is realized. The synchronization process does not require business downtime. When the full data synchronization is completed and the incremental synchronization catches up to the current time point, business switching can be performed, and the business interruption window can be controlled at the second level .

Technical architecture of incremental synchronization

The incremental synchronization architecture of DRS is mainly divided into three parts, namely data capture, file placement and data playback.

• Data scraping

Data capture obtains the change data of the source database in real time through the analysis of the source database log. The internal implementation mainly includes log extraction, log analysis, transaction integration, and data placement.

• Log pull

DRS uses Oracle's Logminer interface to obtain real-time redo logs. After redo is archived, DRS will read the archived log files. In order to prevent the archive logs of the source library from being deleted indefinitely, DRS will start the log pulling thread (multi-threaded concurrent) to pull the logs to the local, and then perform subsequent analysis.

• Log analysis

The data obtained by the Oracle Logminer interface needs to be further analyzed to obtain the actual change content. The DRS log analysis thread performs filtering, splicing, metadata mapping, conversion and other operations on the returned data to form a complete change record object.

• Transaction integration

Log parsing is parsed according to the order of the source database change data. After parsing, the transactions of each record are cross-mixed, and each record must be integrated according to the transaction id to form a complete transaction. On the other hand, for the Oracle RAC scenario, it is also necessary to sort the transactions of different nodes to avoid the occurrence of out-of-order transactions.

• Placement file

After transaction integration, a sequence is formed in accordance with the order of the source database business submission, and DRS will write the data to the disk file in this order. The placed data contains all the information of each piece of changed data in the source database, including table information, column information, transaction information, data information and other additional information (such as timestamp, rowid, etc.). According to these information, the following components can be used Each piece of changed data is restored to the SQL of the object.

• Data playback

Data playback is the process of executing the data captured by the data in the target database, but it is decoupled from the capturing of the data. It reads the DRS placement file, parses out each changed data, reconstructs the corresponding SQL statement according to the metadata information recorded in the file, and executes it in the target database.

Before data playback, DRS provides filtering and conversion functions. It can filter the synchronized data. Filter conditions can be configured. For example, only the data with id <10000 can be synchronized, and the table name, schema name or column name of the synchronized data can also be synchronized. Perform mapping, etc.

Exception handling and playback performance are two important considerations. DRS handles abnormal data during playback by configuring data conflict strategies, and improves loading performance through a concurrency mechanism.

• Conflict strategy

The so-called conflict refers to data-type errors (such as primary key conflict, update and delete cannot find records, etc.) during data playback. The first version of these errors is caused by inconsistent data on both sides. DRS adopts three processing strategies for this type of error, namely covering, ignoring and waiting.

coverage: when a conflict occurs, overwrite the data of the target library with the captured data

ignore: After data conflict, skip the error record and continue execution

waiting: After data conflict, wait for manual processing

• Concurrency mechanism

The DRS concurrency mechanism uses record-level concurrency to maximize the performance of data loading.

First, read the incremental data from the DRS placement file and put them in a queue in order. The parallel analysis engine will get each piece of data from the queue and judge whether there is a data conflict based on its primary key information. For data that does not conflict, Explaining that it can be executed in parallel, the data is distributed to multiple thread queues. When the amount of data in the thread queue reaches the set threshold, this batch of data will be executed as a transaction in the target database. For conflicting data, put this data in the conflict queue, wait for the thread to execute the previous batch of data, and then enter the parallel analysis engine again to determine whether there is a conflict.

Ps: This content is compiled according to the live broadcast of the "GaussDB (for openGauss) Data Migration DRS" technology, missed the live broadcast, please click here to review the wonderful content~

Click to follow and learn about Huawei Cloud's fresh technology for the first time~