MySQL binlog - 簡書

今天想到binlog的一個問題,binlog是何時生成的呢?
正常情況下應(yīng)該是每次執(zhí)行更改數(shù)據(jù)的SQL都會生成對應(yīng)的binlog event,
但是如果平時注意的話,就會意識到binlog里不會記錄rollback的事務(wù),只有提交的事務(wù),
那就推測binlog是在提交時生成的.
但是用戶提交時再生成binlog的話,信息在哪保存的呢?
redo里只有物理頁的修改,不可能是根據(jù)redo生成
想到binlog有個參數(shù)
binlog_cache_size
官方文檔上說: The size of the cache to hold changes to the binary log during a transaction.
也就是緩存一個事務(wù)過程中的數(shù)據(jù)變化
而且這個是每個session都會分配那么大的緩存
那如果一個大事務(wù),修改的數(shù)據(jù)超過了緩存大小呢?

mysql> show status like 'binlog%';
+----------------------------+------------------+
| Variable_name              | Value            |
+----------------------------+------------------+
| Binlog_snapshot_file       | mysql-bin.000011 |
| Binlog_snapshot_position   | 23554531         |
| Binlog_cache_disk_use      | 0                |
| Binlog_cache_use           | 1013712          |
| Binlog_stmt_cache_disk_use | 0                |
| Binlog_stmt_cache_use      | 15               |
+----------------------------+------------------+

查看官網(wǎng)
Binlog_cache_disk_use
The number of transactions that used the temporary binary log cache but that exceeded the value of binlog_cache_size and used a temporary file to store statements from the transaction.
可見超過binlog_cache_size的數(shù)據(jù),就直接緩存到臨時文件中了

那到底何時落盤呢?
查看參數(shù)
sync_binlog
sync_binlog=N
where N is a value other than 0 or 1: The binary log is synchronized to disk after N binary log commit groups have been collected.
可見binlog刷盤是按照事務(wù)來做的

由此可見,binlog在提交前都是先緩存,提交后才會落盤

查看源碼確認(rèn)

MySQL Version: 8.0.16

查看binlog相關(guān)函數(shù) commit (之前版本為 binlog_commit)

/*
 提交事務(wù)后處理當(dāng)前session的binlog以及存儲引擎層的提交
主要有三部分工作:
1. 完善binlog cache,補(bǔ)充必要的cache
2. 執(zhí)行 execute an ordered flush and commit
3. 判斷處理異常錯誤
*/
TC_LOG::enum_result MYSQL_BIN_LOG::commit(THD *thd, bool all) {
  .........
    // 重點(diǎn)關(guān)注提交部分
    if (ordered_commit(thd, all, skip_commit)) DBUG_RETURN(RESULT_INCONSISTENT);
  .........
}

ordered_commit 函數(shù)

/*
處理組提交部分邏輯
流程如下:
  1. Queue ourselves for flushing.
  2. Grab the log lock, which might result is blocking if the mutex is
     already held by another thread.
  3. If we were not committed while waiting for the lock
     1. Fetch the queue
     2. For each thread in the queue:
        a. Attach to it
        b. Flush the caches, saving any error code
     3. Flush and sync (depending on the value of sync_binlog).
     4. Signal that the binary log was updated
  4. Release the log lock
  5. Grab the commit lock
     1. For each thread in the queue:
        a. If there were no error when flushing and the transaction shall be
  committed:
           - Commit the transaction, saving the result of executing the commit.
  6. Release the commit lock
  7. Call purge, if any of the committed thread requested a purge.
  8. Return with the saved error code
*/

int MYSQL_BIN_LOG::ordered_commit(THD *thd, bool all, bool skip_commit) {
//     Stage #1: flushing transactions to binary log
  if (has_commit_order_manager(thd)) {
    Slave_worker *worker = dynamic_cast<Slave_worker *>(thd->rli_slave);
    Commit_order_manager *mngr = worker->get_commit_order_manager();

    if (mngr->wait_for_its_turn(worker, all)) {
      thd->commit_error = THD::CE_COMMIT_ERROR;
      DBUG_RETURN(thd->commit_error);
    }

    if (change_stage(thd, Stage_manager::FLUSH_STAGE, thd, NULL, &LOCK_log))
      DBUG_RETURN(finish_commit(thd));
  } else if (change_stage(thd, Stage_manager::FLUSH_STAGE, thd, NULL,
                          &LOCK_log)) {
    DBUG_PRINT("return", ("Thread ID: %u, commit_error: %d", thd->thread_id(),
                          thd->commit_error));
    DBUG_RETURN(finish_commit(thd));
  }
.......
  /*
    Stage #2: Syncing binary log file to disk
  */

  if (change_stage(thd, Stage_manager::SYNC_STAGE, wait_queue, &LOCK_log,
                   &LOCK_sync)) {
    DBUG_PRINT("return", ("Thread ID: %u, commit_error: %d", thd->thread_id(),
                          thd->commit_error));
    DBUG_RETURN(finish_commit(thd));
  }

  /*
    Shall introduce a delay only if it is going to do sync
    in this ongoing SYNC stage. The "+1" used below in the
    if condition is to count the ongoing sync stage.
    When sync_binlog=0 (where we never do sync in BGC group),
    it is considered as a special case and delay will be executed
    for every group just like how it is done when sync_binlog= 1.
  */
  if (!flush_error && (sync_counter + 1 >= get_sync_period()))
    stage_manager.wait_count_or_timeout(
        opt_binlog_group_commit_sync_no_delay_count,
        opt_binlog_group_commit_sync_delay, Stage_manager::SYNC_STAGE);

  final_queue = stage_manager.fetch_queue_for(Stage_manager::SYNC_STAGE);

  if (flush_error == 0 && total_bytes > 0) {
    DEBUG_SYNC(thd, "before_sync_binlog_file");
   // !!!! 調(diào)用系統(tǒng)調(diào)用 Call fsync() to sync the file to disk.
    std::pair<bool, bool> result = sync_binlog_file(false);
    sync_error = result.first;
  }

//     Stage #3: Commit all transactions in order.

}

證實(shí)確實(shí)是操作緩存到cache,提交時再做羅盤