Link: mp.weixin.qq.com/s/3mOdxTteXnqdmW-f3JhWsA author: xin-yu pan

Java Universe (Javagogo)

The read service architecture scheme based on Binlog complete data synchronization full cache can realize the average performance of less than 100 milliseconds high availability scheme. It can not only meet the real-time requirements of cache synchronization, but also reduce the complexity of synchronization and solve the problem of distributed transactions.

How to send Binlog?

Take MySQL as an example. The MySQL Binlog is divided into three data formats: Statement, row, and mixed.

Create table demo_TABLE {id bigINT not NULL AUTO_increment COMMENT '主键', message varchar(100) not null comment '主键', Status tinyint not NULL comment 'status ', created datetime not null' created ', modified datetime not null 'created ', primary key ('id') using btree }Copy the code

1. The statement format

The statement format records each SQL statement in a Binlog file. During the primary/secondary replication, the system plays back the SQL statements in the Binlog.

For example, if the following SQL is executed successfully:

Update demo_table set status=' invalid 'where ID =1Copy the code

This particular SQL is recorded in Binlog.

The advantage of using SQL as a Binlog is that it has too little content and is fast to transfer. However, there is a problem. In the case of binlog-based data synchronization, it is necessary to parse the above SQL to obtain the changed fields, and there is a certain development cost.

2. The row format

The Binlog format of row records the contents of the database row before and after it was hit by the next SQL execution. For example, if the SQL statement is executed in row format, the following data is generated:

{"before":{"id":1, "message":" text ", "Status", "effective", "created" : "XXXX - xx - xx", "modified" : "XXXX - xx - xx"}, "after" : {" id ": 1, the" message ":" text ", "Status" : "invalid", "created" : "XXXX - xx - xx", "modified" : "XXXX - xx - xx"}, "change_fields:" (" status ")}Copy the code

The Binlog data for the above case log is very comprehensive, containing changed and unchanged data for all fields in the DEMO_table and marking specific fields that have changed. During data synchronization, it can be used exclusively.

The implementation code for data synchronization based on the above format is very simple, but the disadvantage is that the amount of data generated is large.

  1. Mixed format

Mixed is a dynamic combination of the above two modes. Mixed binlogs dynamically determine whether the record is in row or statement format based on the SQL executed.

For example, some DDL statements, such as SQL with newly added fields, do not need to be recorded in row format. Instead, they can be recorded as statements because they do not involve data changes.

In practice, row or mixed is recommended.

  • Reason one: The full amount of data in both formats allows you to do more logic. As business requirements evolve, synchronization logic will have a lot of personalized requirements, and the more information you have, the easier it will be to write code.

  • Cause two: The ROW format does not need to parse SQL, and the implementation complexity is very low. Parsing SQL recorded in statement format requires a lot of development effort. The more complex the parsing, the more likely it is to cause bugs. Therefore, simple ROW format is recommended.

Link: mp.weixin.qq.com/s/3mOdxTteXnqdmW-f3JhWsA author: xin-yu pan

Java Universe (Javagogo)