binlog

Binary files are used for backup, restoration, and primary/secondary environments.

Binlog configuration

(Note: 5.7 must add server_id)

server_id = 6

log_bin=/data/mysql/mysql-bin

binlog_format = row

kdir -p /data/mysql

chown -R mysql.mysql /data/mysql

Restarting the database takes Effect

MySQL provides a sync_binlog parameter to control the binlog flush of the database. The default is sync_binlog = 0, which means that MySQL does not control the refresh of binlog. The file system itself controls the refresh of its cache. Best performance but most risk. You are advised to set sync_binlog to 1, which indicates that the binlog of each transaction will be persisted to the disk. It’s the safest way.

What does the binlog record

Binlog is a function of the SQL layer. Changes to SQL statements are logged.

  • DDL (CREATE, DROP, ALTER) : Records the current DDL unchanged.
  • DCL (grant, rollback, etc.) : Record the current DCL intact.
  • DML: (INSERT, UPDATE, DELETE) : Records only committed transaction DML.

Binlog Three recording modes (bytes)

According to a byte guru, yes

  • Statement (5.6 Default) : Statement mode records the current DML intact
  • Row (5.7 default) : Records row changes
  • Mixed: mixed mode, usually not used

Statement and ROW mode comparison:

Statement: Readable and low log volume, but not rigorous enough. Such as:

id name datetime

Insert into t1 values(1,’zs’,now());

The now() function here is the one that’s not rigorous. If I log on 1 and 10 finds that the table is broken, then when we run this statement we will find that datetime is 10, which is obviously not correct.

Row: very low readability, large log volume, enough rigor.

The reason for low readability is that users cannot understand the recorded data and need to use tools to analyze it.

conclusion

Today I’m going to introduce you to binlog. It’s not a lot of stuff. It’s how to configure binlog and the three formats of binlog. Not difficult, are very important, if the subsequent discovery of new good content will be added.