Shulou(Shulou.com)06/01 Report--

Download the latest version of Navicat Premium

Navicat Premium is a management tool that can connect to multiple databases. It allows you to connect to MySQL, Oracle and PostgreSQL databases simultaneously with a single program, making it more convenient to manage different types of databases.

Unlike synchronization, synchronization is an one-time process that synchronizes the schema and data of two databases, and replication is a continuous (automatic) process of replicating data between two databases (although schema updates are also possible). Replication can occur asynchronously so that there is no need to establish a permanent connection between the two databases, or to replicate during off-peak hours (for example, late at night, where there is little traffic on the database server).

The primary role of replication is to create a consolidated repository of all user databases and / or to propagate the same level of information among all users. In both cases, the result is a distributed database where users can access data related to their tasks without interfering with the work of others. Database replication is called normalization in order to eliminate data ambiguity or inconsistency between users.

This article describes how to automate database replication using Navicat Premium's new automation utility.

Merge replication

As the name implies, merge replication involves merging data from two or more databases into one database. As an exercise, we will merge the contents of the sakila and sakla2 databases into a third database called "sakila_merged", which will store the merged dataset.

Required data synchronization job

The merge replication job will require us to create and save two data synchronization profiles: one for each source database. These steps are exactly the same as those in the "Creating a Data Synchronization Job" section above, so we won't repeat them here.

Create a batch job

Batch jobs can be triggered by the source database or target, as we did in the previous section of snapshot replication. However, it is usually easiest to trigger batch jobs from the target database because they will all reside on the same server. We'll do it here, too.

Click the Automation button in the main toolbar.

Then click New Batch Job in the Objects toolbar to open the New Batch Job tab.

Browse for source connections, databases, and / or schemas on the Objects pane. This will cause the saved jobs for the database to appear in the bottom pane of Available Jobs.

In the Available Jobs pane, select the data synchronization job type, and then double-click or drag to move the job from the Available Jobs list to the Selected Jobs list above.

Click the Save button on the Automation toolbar and provide a descriptive name in the Save dialog box. This enables the the Set Task Schedule and Delete Task Schedule buttons.

In the General tab of the Task Schedule dialog box, you can again provide a description of the task and provide several options for performing the task.

In the Triggers tab, you can configure tasks to run on a variety of schedules, including once, daily, weekly, monthly, and any arrangement of each schedule. This time, two jobs will be performed instead of one.

After you have configured the automation job, you can test it by clicking the Start button in the automation toolbar.

Transaction replication

In merge replication, only the merged database contains all the latest data. Each source database contains only baseline data, as well as data inserted since the first population. In transactional replication, the user receives a complete initial copy of the database and then periodically receives updates as the data changes so that all databases use the same dataset. Keeping multiple databases synchronized makes this the most complex type of replication.

Required data synchronization job

When using transactional replication, the number of data synchronization jobs required increases significantly because data must be replicated across all user databases. For example, suppose we have three databases, sakila, sakila2, and sakila3. We can use a total of six data synchronization jobs to merge and propagate complete datasets in all user databases: three for merging user databases and three for updating them with merged datasets.

The following is an automation wizard that contains all six jobs:

In order to allow enough time for the data merge to complete, it is best to divide the job into two parts, the first part merging the data, and the second part updating the user database with the complete dataset after the specified delay.

The following is the appearance of an automated job that propagates the merged dataset to the user database:

Running this job two hours after the first run should provide sufficient time to complete the merge. Therefore, if the first job is scheduled to run at midnight, we will set the job to start at 2 a.m.:

Conclusion

This article describes how to automate database replication using Navicat Premium 12's automation utility. It works in conjunction with its synchronization tool to allow DBA to automate various types of replication to run according to a predefined schedule.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.