MySQL Logging Mechanism

During transaction execution, logs are first written into the binlog cache.
Upon transaction commit, the contents of the binlog cache are written to the binlog file.
Each thread has its own binlog cache, but all share a single binlog file.

Parameters:
- binlog_cache_size: Controls the size of the binlog cache for each thread. Exceeding this size will temporarily store the data on the disk.
- sync_binlog: Controls the write and fsync timing.
  1. sync_binlog=0: Every transaction commit does a write but no fsync.
  2. sync_binlog=1: Every transaction commit executes fsync.
  3. sync_binlog=N (N>1): Every transaction commit does a write, but only does fsync after every N commits.

During transaction execution, the generated redo logs are first written into redo log buffer.

Redo log potential states:
1. Resides in the redo log buffer (physically in the MySQL process memory).
2. Written to disk (write) but not persisted (fsync); Physically in the file system’s page cache.
3. Persisted to disk (hard disk).
Parameters:
- innodb_flush_log_at_trx_commit: Controls the write strategy for the redo log.
  1. =0: Redo logs remain in the buffer upon transaction commit.
  2. =1: Redo logs are directly persisted to disk upon transaction commit.
  3. =2: Redo logs are written to the page cache upon transaction commit.
A background thread in InnoDB writes logs from redo log buffer to the file system’s page cache every second and then persists it to the disk.
Uncommitted transaction’s redo logs can also be persisted to the disk.

Set binlog_group_commit_sync_delay and binlog_group_commit_sync_no_delay_count parameters to reduce binlog disk write frequency.
Set sync_binlog to a value greater than 1 (typically between 100-1000). This might result in binlog loss during power failures.
Set innodb_flush_log_at_trx_commit to 2. This might result in data loss during power failures.