“This is the seventh day of my participation in the First Challenge 2022.

preface

Read/write separation (master/slave synchronization) involves the Binary Log. Read/write separation (master/slave synchronization) involves the Binary Log.

Why Binary Log?

In other words: What does the Binary Log do?

  • If accidentally deleted data how to undertake recovery?
  • If you want to do read/write separation, how do you synchronize data between master and slave?

So binary logging has two important uses:

  • For replication, binary logs are used on the master replication server as a record of statements to be sent to the slave server. Many of the details of binary log formatting and processing are for this purpose. The master server sends events contained in its binary logs to its slave server, which executes these events to make the same data changes as the master server. The slave server stores events received from the master server in its relay log until they can be executed. The format of trunk logs is the same as that of binary logs.

  • Some data recovery operations require binary logging. After the backup file is restored, the events in the binary logs recorded after the backup are re-executed. These events cause the database to update from the backup point.

Now that we know what it does, let’s talk about it.

Introduction to the

The binary log is a set of log files that contain information about data changes to the MySQL server instance.

Binary logging was introduced in MySQL 3.23.14. It contains all the update data statements. It also contains statements that may have updated it (for example, a DELETE that does not match any rows) unless row-based logging is used. Statements are stored as “events” that describe modifications. Binary logs also contain information about how long each statement takes to update data.

Binary logs also contain some additional metadata, including:

  • Information about the server state required to correctly reproduce the statement (such as the SQL model involved)
  • Error code
  • Metadata needed to maintain the binary Llog itself (for example, rotation events)

Binary logs are a track of global state changes that occur during server running. It contains events that describe this change in state. More precisely, binary log events describe actions that can be used to reproduce the same global state changes that occurred on the server.

type

  • Statement-based logging: Events contain SQL statements that make changes to data (insert, update, delete)
  • Row-based logging: Events describe changes to a single row.

Obviously, the statement-based footprint is relatively small, since a single statement may trigger multiple inserts, updates, and deletes. But statement-based replication is less guaranteed than row-based, row-based granularity is smaller and control is more stringent, which will be covered in subsequent master-slave replication without much description here.

Hybrid logging uses statement-based logging by default, but is automatically switched to line-based logging as needed.

Row-based (and hybrid) logging has been available since MySQL 5.1.

use

  • Enable the binary logging service by using the log-bin option.

Add three lines directly to the my.INF main configuration file

Log_bin_basename =/var/lib/mysql/mysql-bin =/var/lib/mysql/mysql-bin Manage all binlog files log_bin_index= /var/lib/mysql-bin.indexCopy the code
  • Set the logging mode

In my.inf

Set the log format to STATEMENT, ROW, and MIXED. binlog_format = mixedCopy the code
  • The mysqlbinlog utility can be used to print binary or relay log content in readable form.
  • Sync_binlog can be set to 1, indicating that the binlog of each transaction is persisted to disk. This ensures that the binlog will not be lost after MySQL restarts abnormally.