Make writing a habit together! This is the 13th day of my participation in the “Gold Digging Day New Plan · April More Text Challenge”. Click here for more details.

Mysql binlog is known to be used for archiving and for primary/secondary synchronization. But do you know why the backup database can be consistent with the primary database after executing a binlog?

A, binlog

Binlog is Mysql’s own log, while Redolog is Innodb’s own log. The search engine is integrated into Mysql as a plug-in.

1. Write process

Binlog During the execution of a transaction, the binlog_cache is first written to the binlog log file, and then the binlog log file is written to the binlog log file once the transaction is committed.

Because binlogs are logical logs that record the execution logic one by one (SQL statements or row records changed from XX to XX), the binlogs in a transaction, no matter how big, must be grouped together.

Each thread will have a binlog_cache. If a transaction is too large for the binlog_cache, it will be stored in a temporary file. When the transaction is committed, the binlog from the cache or temporary file will be written to the binlog log file. Finally, empty binlog_cache or temporary files.

If the production repository deletes a large amount of data using DELETE, then this is a large transaction. When you turn binlog on (which it usually does), you take up a lot of disk space and create a lot of temporary files. When a transaction commits, the disk usage is x2 because it is deleted after the write is completed. Easy to fill the disk.

2. Binlog Write time

Mysql is in user space, so when binlog_cache is actually written to a file, it will pass through the OS cache and then flush into the file (fsync).

Binlog takes the sync_binlog argument, which sets the binlog write time:

0: every time a transaction is committed, it will only be written to the system cache. The system controls the time when the disk (file) is flushed, and mysql does not care.

1: Every time a transaction commits, it is written to the system cache and flushed to disk by calling fsync

N (N>1) : Each committed transaction will be written to the system cache. After accumulating N transactions, fsync will be called to flush to disk.

You don’t usually set it to zero, it’s uncontrollable. We know that writing to the disk is a time-consuming process. Therefore, in the SCENARIO of I/O bottleneck, you can set N to a larger value to improve performance. However, once the system is down, the logs of the latest N transactions will be lost.

Two, the basic principle of active and standby

Assume that there are two nodes A and B. A is the active node and B is the backup node. User A has the read and write permission, and user B only has the read permission. Note that the read and write permissions are for ordinary users of our business, while mysql does not limit reads and writes.

1. Active/standby synchronization process

A long connection will be maintained between the two databases AB, and A thread will be dedicated to serving this long connection in the main library A

  1. B has A thread io_thread in the standby library and sends the binlog location to A
  2. After receiving the request, USER A reads the local binlog and sends it to user B
  3. B sends the io_thread to the database and records the IO_thread in the transfer log
  4. B will also have a SQL_thread read the relay log and parse it for execution.

Sql_thread can have multiple threads

2. Binlog format problem

Binlog has three formats:

  • Statement: Records executed statements.

​

  • Row: Records a data change from XX to XX.

​

  • Mixed: a mixture of the above 2

The statement:

In this format, if a delete statement is executed by the primary library, but the WHERE statement does not condition the primary key, the synchronization between the secondary library and the primary library may result in different results.

row:

This is a record of the change of a certain piece of data, so there is no problem with statement. However, more log space will be used. For example, for delete statements, only one deletion is recorded. For row, each deletion is recorded.

mixed:

Therefore, with mixed, mysql determines for itself whether the SQL statement is safe and selects a different format for the record.

Show global variables like ‘binlog_format’

3. Function problems

If we use binlog in statement format and execute the following statement:

insert into test values(5,now())
Copy the code

Will it cause the master and slave to acquire different times?

The answer is no. The statement also records the current fetch time when it executes the statement.

4. Problem of circular replication

If A and B are in active/standby mode, A generates A binlog and synchronizes it with B. B gets A new binlog and synchronizes it with A new binlog and synchronizes it with A new binlog and creates an infinite loop.

Mysql generates a ServiceID for each node. Two nodes with the same ServiceID cannot back up each other.

  1. When node A records the binlog, it records the serviceID that generates the binlog
  2. After B obtains the binlog, the generated binlog records the serviceID of node A
  3. Node A does not execute the same binlog as its serviceID after obtaining the binlog of node B