Reference since the problem: www.zhihu.com/question/42… The answer is personally original

Set innodb_flush_log_at_trx_COMMIT to 1 and sync_binlog to 1. MySQL’s default configuration is double 1.

Innodb_flush_log_at_trx_commit is the configuration of innoDB engine and sync_binlog is the upper configuration of MySQL engine. Both control disk write policies.

MySQL innoDB engine after transaction commit:

  1. Binlog write memory
  2. Redo log Write memory
  3. The two configurations determine whether the two logs are flushed or not (callfsync)
  4. Commit to complete

Innodb_flush_log_at_trx_commit: Flush policy of the redo log. The default value is 1

  • ifinnodb_flush_log_at_trx_commitSet to 0:log bufferWill write once per secondlog file, andlog filetheflush(Brush to disk) operation at the same time. In this mode, when a transaction commits,Write to disk is not actively triggeredThe operation;
  • ifinnodb_flush_log_at_trx_commitSet to 1: each time a transaction commitsMySQLWill thelog bufferWrite data tolog fileAnd,flush(swipe to a disk)
  • ifinnodb_flush_log_at_trx_commitSet to 2: each time a transaction commitsMySQLWill write data to the log bufferlog file, butflushThe (swipe to disk) operation is not simultaneous. In this mode,MySQL willExecute once per secondflush(Brush to disk) operation.

Sync_binlog: indicates the disk flushing policy of binlog. The default value is 0

  • If it is 0, MySQL does not sync to disk like the operating system does with other files, but depends on the operating system to flushbinary log.
  • sync_binlog = N (N>0)MySQL inEvery N timeBinary logbinary log, will be usedfdatasync()The function writes its binary logbinary logSynchronize to disk.