Detailed explanation of the solution for real-time synchronization from MySQL to Oracle

1 Requirements Overview

The data of multiple tables in the MySQL5.6 production database are synchronized in real time to the Oracle11g data warehouse. The MySQL historical data is 700G, and an average of about 50G of log files are generated every day. The MySQL log space is 50G, and the log files are rolled out after exceeding the limit. The entire synchronization process must not affect MySQL business operations.

2 Technical Principles

The LingBee data integration software BeeDI is used to synchronize MySQL data to Oracle in real time, synchronize historical data in full through ETL, and synchronize incremental data in real time through log analysis.

Due to the limitation of log space, if all historical data are synchronized at one time, it will take more than one day. The logs generated during the full synchronization process will be deleted, resulting in missing data for the real-time log parsing task. Therefore, historical data needs to be synchronized in batches. The basic operations are as follows:

a Fully synchronize partial tables.

b. Add the fully synchronized table to the real-time log parsing task and start the task. When the corresponding table data on MySQL and Oracle are consistent, stop the log parsing task.

Repeat steps a and b above until all tables are added to the real-time log parsing task.

3 MySQL environment configuration

3.1 Create a User

Create a synchronization user in the main database. Taking user sync as an example, the statements for creating the user and assigning permissions are as follows:

CREATE USER 'sync'@'%' IDENTIFIED BY 'xxxxx';

GRANT SELECT,PROCESS,SUPER,REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO 'sync'@'%';

3.2 Enable BinLog

Query the main database log opening status and log record format:

show variables like 'log_bin';

show variables like "%binlog_format%";

If the log status is OFF or the log format is not ROW, edit the my.ini file and set the following items:

log-bin="xxxxxx" --Open log

binlog_format="ROW" --log record format

4 BeeDI Synchronous Operation

4.1 Configuring a full synchronization task

Create a new ETL job in BeeDI, and set LOB Load to Bound Storage in Mapping Properties in the ETL Template Properties dialog box.

[LOB Load] is used to specify the Oracle LOB data loading method. Positioning storage updates the LOB content by positioning the LOB pointer, and binding storage updates the LOB content by parameter binding.

To optimize data extraction performance, it is recommended to set the [Asynchronous] extraction mode in the [Options] dialog box of the extraction component.

To optimize data loading performance, it is recommended to select Batch loading mode in the Options dialog box of the loading component.

The full synchronization task configuration is completed as follows

According to the above method, create multiple ETL jobs, each of which corresponds to a synchronization table.

4.2 Configuring real-time log parsing tasks

The real-time analysis task uses a script job in which all tables to be synchronized are specified. The script code is as follows

4.3 Execute full synchronization task

Select the full synchronization job to be executed and click the [Start] button on the toolbar. You can start multiple full synchronization jobs at the same time as long as the production library resources and the machine resources where BeeDI is located are sufficient.

4.4 Execute real-time log parsing tasks

When all full synchronization jobs are finished, edit the real-time task script and specify the tables that have been fully synchronized in the variable cdc_tables; set the log capture starting point in the inquireLog function parameter. The start time of the first full synchronization job can be specified as the log capture starting point. The log capture point only needs to be set once when the task is first executed. The log capture point will be automatically managed when the task is run later.

In the Scheduling Window, click Add from the shortcut menu to load the real-time log parsing job to be executed. Select the real-time job in the Scheduling Window, click the Timing button on the toolbar, set the timing in seconds, and set the running cycle to 1 second.

After the real-time task runs, the MySQL database log parsing information is output in the log window, including the log records read every minute and the most recent log parsing time point.

4.5 Add more synchronization tables

When the job status corresponding to the real-time task frequently displays the timing icon, it indicates that the task has entered the real-time state. At this time, the data of the MySQL source table and the Oracle target table are consistent. Stop the real-time log parsing task and configure the full synchronization task of other tables (refer to 4.1 and 4.3). When the full task is completed, edit the real-time log parsing task, add other tables that have been fully synchronized, and start the real-time task (refer to 4.2 and 4.4).

5. Verify synchronization data consistency

Execute select count(*) from [table] in the source database and the target database in turn to compare whether the number of table records is equal.

Execute select sum([numeric column]) from [table] in the source database and target database respectively to compare whether the arithmetic sum of the specified fields is equal.

This is the end of this article about the detailed explanation of the MySQL real-time synchronization to Oracle solution. For more relevant MySQL real-time synchronization to Oracle content, please search for previous articles on 123WORDPRESS.COM or continue to browse the related articles below. I hope everyone will support 123WORDPRESS.COM in the future!