Guarantee of consistency and integrity during Database Synchronization

Database Synchronization Process, the strictest indicator, the first is efficiency, that is, the number of SQL records synchronized per second; the second is consistency, that is, the data generated by the master database, whether the data is consistent after the slave database is synchronized; the third is how to ensure the integrity of Database Synchronization, that is, when there is a problem in each link of synchronization, how to consider error handling. Next we will discuss and analyze the rationality separately.

First, efficiency

The database synchronization process is divided into three phases: extraction, analysis, and loading.

The extraction process mainly reads database logs in real time, which can be performed at intervals of milliseconds. Therefore, there is no efficiency problem and the extraction can be performed in real time;

The analysis process is mainly to analyze SQL statements and convert the source log to the load logs of the slave database. The main time of this part is the file write time, since the write time of a general hard disk is about 50 Mb/s, if the number of logs generated by the primary database array per second is about 300 MB/S for large transaction volumes, in contrast, the analysis component will still have a latency of several seconds, because our synchronization software is generally not installed in the master database and cannot use the write speed of the high-speed array, so the latency of the second level will still be generated, however, as long as the log volume is not generated at a continuous peak, data will be synchronized after a period of time. If you want to extract logs in real time and analyze the logs in real time, we recommend that you configure only one array for the synchronization server;

The loading process mainly loads the SQL statements of the slave database based on the analysis component results. Therefore, concurrent loading is the only way to improve efficiency. However, the configuration of the slave database server is not very high, it is recommended that the number of persistent connections of the database be reasonably configured, because the standby database is sometimes used as the load sharing for query statistics. Therefore, if the resources of the standby database are limited, the effect of the query statistics sharing will be greatly reduced.

2. Consistency

How can we ensure that the data of the slave database is consistent with that of the master database? If this problem is raised at the business level, there is certainly no good solution. A common answer is "view the slave Database ", after the application is connected, check that the synchronized data is correct. However, such spot checks may fail to detect data with special errors, I want to analyze this problem from several mechanisms.

1. Analysis Log)

This is the SQL statement that is about to be loaded to the slave database. This part is the basis for correct loading. If this part of data is correct, it ensures that the data source is correct.

2. Upload Log)

Is whether there is a problem when loading data. Sometimes the definition of the slave database and the master database table is inconsistent, so this log will record errors and check where the problem occurs based on this log.

The above two logs can basically ensure error capture during the synchronization process, coupled with the synchronization Data Check Program (this data requires the master and slave database check process cannot change the data) and the Application

Spot check for connecting to the slave database is a secondary check method. At the same time, the slave database provides services in real time as the query and statistical analysis, which also changes the spot check to the normal state to ensure data consistency.

1 2 Next Page