1. binlog(二進(jìn)制日志)

1.1 binlog介紹

binlog記錄了對數(shù)據(jù)庫執(zhí)行更改的所有操作(不包括查詢)，還包括了執(zhí)行數(shù)據(jù)庫更改操作的時(shí)間和執(zhí)行時(shí)間等信息。binlog主要有兩個作用：恢復(fù)(recovery)。可以通過binlog進(jìn)行point-in-time的恢復(fù)。

復(fù)制(replication)。可以用于搭建MySQL主從集群。

默認(rèn)binlog是沒有開啟的，可以通過show variables like 'log_bin';查看。通過在my.cnf配置log-bin[=name]可以啟動binlog，如果設(shè)置name，則默認(rèn)binlog文件名為主機(jī)名，后綴名為binlog的序列號，所在路徑為數(shù)據(jù)庫所在目錄(datadir)，如：# 配置log-bin，啟動bin log

# cat /etc/mysql/my.cnf

...

[mysqld]

log-bin

server-id=1mysql> show variables like 'datadir';

+---------------+-----------------+

| Variable_name | Value |

+---------------+-----------------+

| datadir | /var/lib/mysql/ |

+---------------+-----------------+

1 row in set (0.00 sec)

mysql> system ls -lh /var/lib/mysql/;

total 249M

-rw-r----- 1 mysql mysql 154 8月 13 19:35 admin-node-bin.000001

-rw-r----- 1 mysql mysql 24 8月 13 19:35 admin-node-bin.index

-rw-r----- 1 mysql mysql 3.8K 7月 30 00:06 admin-node-slow.log

-rw-r----- 1 mysql mysql 56 6月 1 20:08 auto.cnf

-rw------- 1 mysql mysql 1.7K 6月 1 20:08 ca-key.pem

-rw-r--r-- 1 mysql mysql 1.1K 6月 1 20:08 ca.pem

上面的admin-node-bin.000001就是binlog文件，我們在配置文件中沒有指定名稱，所有默認(rèn)用了主機(jī)名(admin-node)做了文件名。admin-node-bin.index為binlog的索引文件，用來存儲過往產(chǎn)生的binlog序號，通常情況下，不建議手工修改這個文件。

1.2 二進(jìn)制日志格式

MySQL 5.1開始引入了binlog格式，即binlog_format參數(shù)，該參數(shù)可設(shè)置的值有STATEMENT、ROT、MIXED。使用select @@binlog_format;或者show variables like 'binlog_format';可以查看當(dāng)前的binlog_format參數(shù)設(shè)置，如下：mysql> select @@binlog_format;

+-----------------+

| @@binlog_format |

+-----------------+

| ROW |

+-----------------+

1 row in set (0.00 sec)

mysql> show variables like 'binlog_format';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| binlog_format | ROW |

+---------------+-------+

1 row in set (0.01 sec)STATEMENT格式下，binlog文件記錄的是日志的邏輯SQL語句。

在ROW格式下，binlog記錄的不再是簡單的SQL語句，而是記錄表的行更改情況。

MIXED格式下，MySQL會動態(tài)自動選擇STATEMENT格式或ROW格式。

如下，以STATEMTNT格式為例，看下binlog文件內(nèi)容。我們先將binlog_format參數(shù)設(shè)置為STATMENT，然后執(zhí)行修改語句，查看binlog內(nèi)容。binlog存儲的是二進(jìn)制，不能直接查看，需要借助命令mysqlbinlog。# 將binlog_format參數(shù)設(shè)置為STATEMENT

mysql> set @@session.binlog_format='STATEMENT';

Query OK, 0 rows affected (0.00 sec)

mysql> select @@binlog_format;

+-----------------+

| @@binlog_format |

+-----------------+

| STATEMENT |

+-----------------+

1 row in set (0.00 sec)

# 執(zhí)行插入語句

mysql> insert into t1 select 4, 'dfd';

Query OK, 1 row affected (0.13 sec)

Records: 1 Duplicates: 0 Warnings: 0

查看binlog內(nèi)容，可以看到記錄了剛剛執(zhí)行的插入語句/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#200814 0:07:28 server id 1 end_log_pos 123 CRC32 0x5a8577db Start: binlog v 4, server v 5.7.31-0ubuntu0.18.04.1-log created 200814 0:07:28

# Warning: this binlog is either in use or was not closed properly.

BINLOG '

QGU1Xw8BAAAAdwAAAHsAAAABAAQANS43LjMxLTB1YnVudHUwLjE4LjA0LjEtbG9nAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA

Adt3hVo=

'/*!*/;

# at 123

#200814 0:07:28 server id 1 end_log_pos 154 CRC32 0x58f58a7a Previous-GTIDs

# [empty]

# at 154

#200814 10:25:14 server id 1 end_log_pos 219 CRC32 0x7e873442 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no

SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

# at 219

#200814 10:25:14 server id 1 end_log_pos 302 CRC32 0x5772418a Query thread_id=5 exec_time=0 error_code=0

SET TIMESTAMP=1597371914/*!*/;

SET @@session.pseudo_thread_id=5/*!*/;

SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;

SET @@session.sql_mode=1436549152/*!*/;

SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;

/*!\C utf8 *//*!*/;

SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;

SET @@session.lc_time_names=0/*!*/;

SET @@session.collation_database=DEFAULT/*!*/;

BEGIN

/*!*/;

# at 302

#200814 10:25:14 server id 1 end_log_pos 410 CRC32 0xd9e9a7b8 Query thread_id=5 exec_time=0 error_code=0

use `mytest`/*!*/;

SET TIMESTAMP=1597371914/*!*/;

insert into t1 select 4, 'dfd'

/*!*/;

# at 410

#200814 10:25:14 server id 1 end_log_pos 441 CRC32 0x3a7bf86d Xid = 40

COMMIT/*!*/;

SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;

DELIMITER ;

# End of log file

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

2. redo log(重做日志)

2.1 redo log介紹

redo log是InnoDB存儲引擎特有的。redo log對InnoDB存儲引擎至關(guān)重要，它記錄了對于InnoDB存儲引擎的事務(wù)日志。

redo log的主要目的是保證數(shù)據(jù)的完整性。如數(shù)據(jù)庫由于某種原因突然宕機(jī)，InnoDB存儲引擎可以使用redo log恢復(fù)到宕機(jī)之前的時(shí)刻。

每個InnoDB存儲引擎至少有一個redo log文件組(重做日志文件組，MySQL 5.7只能有一個redo log文件組)，每個redo log組下至少有兩個redo log文件。一個redo log文件組下的每個redo log文件大小一致，并以循環(huán)方式使用。InnoDB存儲引擎先寫redolog1，當(dāng)達(dá)到文件的最后時(shí)，會切換至redolog2，當(dāng)redolog2也被寫滿時(shí)，會再切回到redo1。下圖顯示了一個擁有3個redolog文件的redo log組。

redo log的寫入是先寫入到日志緩沖區(qū)，然后按照一定的策略刷入磁盤。如下圖所示：

2.2 redo log重要參數(shù)介紹

有幾個和redo log相關(guān)的比較重要的參數(shù)，這里作下介紹。innodb_log_file_size：指定了重做日志文件的大小。

innodb_log_files_in_group：指定了redo log組中redo log文件的數(shù)量，默認(rèn)為2。

innodb_mirrored_log_groups：指定了日志鏡像文件組的數(shù)量，默認(rèn)為1。MySQL 5.7沒有這個參數(shù)了，只能由一個組。

innodb_log_group_home_dir：指定了redo log文件組所在路徑，默認(rèn)在數(shù)據(jù)庫路徑下。

innodb_flush_log_at_trx_commit：用來控制redo log刷盤的策略，可設(shè)的值有0、1、2，默認(rèn)是1。0代表redo log由mysql主線程定時(shí)刷新到磁盤；1表示在事務(wù)提交時(shí)，將redo log從緩沖區(qū)同步刷新到磁盤；2表示事務(wù)提交后，異步刷新到磁盤。

2.3 redo log在事務(wù)中的作用

在InnoDB存儲引擎中，事務(wù)日志通過redo log文件和InnoDB存儲引擎的日志緩沖(InnoDB Log Buffer，是InnoDB存儲引擎將redo log的緩沖區(qū))來實(shí)現(xiàn)。當(dāng)開始一個事務(wù)時(shí)，會記錄該事務(wù)的一個LSN(Log Sequence Number，日志序列號)，當(dāng)事務(wù)執(zhí)行時(shí)，會往InnoDB存儲引擎的日志緩沖里插入事務(wù)日志，當(dāng)事務(wù)提交時(shí)，必須將InnoDB存儲引擎的日志緩沖寫入磁盤(默認(rèn)的實(shí)現(xiàn)即innodb_flush_log_at_trx_commit=1)。也就是在數(shù)據(jù)落盤之前，需要先寫日志。這種方式成為預(yù)寫日志方式(Write-Ahead Logging， WAL)。

InnoDB存儲引擎通過預(yù)寫日志的方式來保證事務(wù)的完整性。也就是說磁盤上存儲的數(shù)據(jù)頁和內(nèi)存緩沖池中的頁是不同步的，對于內(nèi)存緩沖池中頁的修改，是先寫入redo log文件，再寫入磁盤，是一種異步的方式。可以通過show engine innodb status看著當(dāng)前磁盤數(shù)據(jù)和redo log數(shù)據(jù)的狀態(tài)，其中Log sequence number表示內(nèi)存中當(dāng)前的LSN，Log flushed up to表示刷新到redo log文件的LSN，Last checkpoint at表示最后一次檢查點(diǎn)的LSN，即已經(jīng)刷新到磁盤的LSN。因?yàn)閿?shù)據(jù)是異步寫入redo log和刷新的磁盤的，所以在有數(shù)據(jù)庫有數(shù)據(jù)變化時(shí)上面的三個值可能是不同的。mysql> show engine innodb status\G

*************************** 1. row ***************************

......

---

LOG

---

Log sequence number 937484896

Log flushed up to 937484896

Pages flushed up to 937484896

Last checkpoint at 937484887

0 pending log flushes, 0 pending chkp writes

10 log i/o's done, 0.00 log i/o's/second

----------------------

......

3. bin log和redo log的區(qū)別bin log會記錄所有與MySQL有關(guān)的日志記錄，包括不同存儲引擎的日志。而redo log是InnoDB存儲引擎特有的，只記錄有關(guān)InnoDB本身的事務(wù)日志。

記錄的內(nèi)容不同。bin log記錄的是關(guān)于一個事務(wù)的具體操作內(nèi)容，主要用于人工恢復(fù)數(shù)據(jù)。而redo log記錄的是關(guān)于每個頁(Page)的更改的物理情況，如Spaceid，PageNo，OpCode，Data，它是InnoDB用于保證crash-safe能力地，也就是事務(wù)提交后MySQL崩潰地話，可以保證事務(wù)的持久性，從而保證數(shù)據(jù)的完整性。也就是說，bin log是用作人工恢復(fù)的。redo log是MySQL自己使用的，用于保證在數(shù)據(jù)庫崩潰是的數(shù)據(jù)完整性。

寫入的時(shí)間不同。bin log文件是在事務(wù)提交前記錄的，而在事務(wù)進(jìn)行的過程中，不斷有redo log被寫入日志文件中。

4. undo log

在執(zhí)行事務(wù)的過程中可能會失敗，這個時(shí)候就可以利用undo log將數(shù)據(jù)回滾到修改之前的樣子。與redo不同的是，redo放在文件中，而undo存放在數(shù)據(jù)庫內(nèi)部的一個特殊段中，稱為undo段，undo段位于共享表空間內(nèi)(ibdata1)。

利用undo回滾并不是直接將數(shù)據(jù)庫恢復(fù)到執(zhí)行事務(wù)之前的樣子，因?yàn)槭聞?wù)是支持并發(fā)的，如果直接恢復(fù)到某個事務(wù)執(zhí)行之前的樣子，就可能會影響到其他事務(wù)的結(jié)果。實(shí)際上利用undo回滾采取的是類似補(bǔ)償?shù)姆绞?#xff0c;比如對每個insert，InnoDB存儲引擎會完成一個delete；對每個delete，會完成一個insert；對每個update，會執(zhí)行一個相反的update。也就是說回滾不是物理上的恢復(fù)，而是邏輯上的恢復(fù)，保證回滾的事務(wù)沒有更改數(shù)據(jù)庫數(shù)據(jù)。

總結(jié)

以上是生活随笔為你收集整理的mysql重做日志与binlog日志区别_MySQL日志之binlog、redo log、undo log的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。