記錄了所有對MySQL數據庫的修改事件, 包括DDL和DML操作. 其中binlog僅記錄成功執行的日志, 對于回滾或者Syntax Error而未執行的事件并不記錄.
log_bin為OFF說為未啟用二進制日志, 我們嘗試通過以下指令打開
MariaDB [(none)]> set global log_bin = 'mariadb-bin';ERROR 1238 (HY000): Variable 'log_bin' is a read only variable好吧, 其實仍然是要修改my.cnf配置文件, 在my.cnf或自定義配置中添加[mysqld]組, 設置log_bin屬性的值為mariadb-bin以開啟binlog.
以后我們查看目錄/var/log/mysql/的時候就會發現有如mariadb-bin.000001的一系列日志.待重啟MySQL后執行
現在也可以正常顯示binary的信息
MariaDB [(none)]> show binary logs;+------------------+-----------+| Log_name | File_size |+------------------+-----------+| mysql-bin.000001 | 313 |+------------------+-----------+1 row in set (0.00 sec)NOTE: mysql> flush logs; 用于產生新的二進制日志, 方便調試.
基于段的日志格式 - statement
這是MySQL 5.7之前默認的日志格式, 記錄CUD的SQL語句.
MariaDB [(none)]> show variables like 'binlog_format';+---------------+-----------+| Variable_name | Value |+---------------+-----------+| binlog_format | STATEMENT |+---------------+-----------+1 row in set (0.00 sec)查看日志mysqlbinlog工具可直接用于查看日志
mysqlbinlog mysql-bin.000001優點:記錄每個事件所執行的SQL語句, 故不需要記錄每一行的具體變化, 所以日志記錄量相對較少, 節約磁盤IO與網絡IO(如果只對一條記錄修改或者插入, ROW格式的日志量有可能少于STATEMENT格式).
缺點:為了確保這些SQL語句能在從庫中正確地執行, 所以要記錄上下文信息, 以保證重放時的行為一致. 但如果使用UUID()這類非確定性函數, 可能會造成主從的數據不一致.
基于行的日志格式 - row
在MySQL 5.7后的默認格式, 每當進行CUD操作修改行記錄時都會將數據寫入binlog.
如果我們有一個修改了10k條數據的情況下, 基于段的日志格式僅會記錄該條SQL, 而基于行的日志則會有10k條記錄
查看日志mysqlbinlog -vv mysql-bin.000001優點
使主從復制更加安全, 由于ROW格式記錄了每一行的更改, 當日志被從庫重放時僅需應用該條更改即可, 使得不確定性函數也能安全地使用. 更大程度上減少由于主從數據不一致而造成復制鏈路中斷的情況.基于行的復制比基于段的復制要高效缺點
記錄的日志量較大混合日志格式 - mixed
混合使用STATEMENT和ROW, 根據SQL語句由系統決定使用基于段還是基于行的日志格式, 如非確定性函數則會以ROW格式記錄, 其他的以STATEMENT記錄.
由于ROW格式記錄的日志量巨大, 在MySQL 5.6以后, 官方增加了binlog_row_image參數改善其記錄方式.
FULL為默認值, 意思是記錄一行紀錄里面的所有內容, 無論該列是否被修改.
MINIMAL僅記錄被修改的列, 這樣就可以大大減少記錄量.
NOBLOB與FULL類型, 但是如果沒有修改BLOB或TEXT類型的列, 就不會記錄該大數據類型的列.
假如有表結構如下
CREATE TABLE `t_test` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `title` varchar(32) NOT NULL DEFAULT '', `content` text, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;表中有記錄
id->1, title->'prontera', content->'hey guy'
FULL (全記錄)
我們修改id為1的記錄, 然后查看/var/log/mysql中的binlog
MariaDB [employees]> update t_test set title = 'solar' where id = 1;Query OK, 1 row affected (0.01 sec)Rows matched: 1 Changed: 1 Warnings: 0可以看出, 我們僅僅set title, 但是binlog_row_image為FULL的時候會記錄所有的列
BINLOG 'HPyWWBMBAAAANwAAAM8BAAAAACEAAAAAAAEACWVtcGxveWVlcwAGdF90ZXN0AAMDD/wDYAACBA==HPyWWBgBAAAASQAAABgCAAAAACEAAAAAAAEAA///+AEAAAAIcHJvbnRlcmEHAGhleSBndXn4AQAAAAVzb2xhcgcAaGV5IGd1eQ=='/*!*/;### UPDATE `employees`.`t_test`### WHERE### @1=1 /* INT meta=0 nullable=0 is_null=0 */### @2='prontera' /* VARSTRING(96) meta=96 nullable=0 is_null=0 */### @3='hey guy' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */### SET### @1=1 /* INT meta=0 nullable=0 is_null=0 */### @2='solar' /* VARSTRING(96) meta=96 nullable=0 is_null=0 */### @3='hey guy' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */MINIMAL (按需記錄)
同樣地, 將id為1的記錄的title更新為原先的’prontera’, 對比binlog的確是有效地減少了日志量
BINLOG 'rv2WWBMBAAAANwAAAM8BAAAAACEAAAAAAAEACWVtcGxveWVlcwAGdF90ZXN0AAMDD/wDYAACBA==rv2WWBgBAAAALQAAAPwBAAAAACEAAAAAAAEAAwEC/gEAAAD+CHByb250ZXJh'/*!*/;### UPDATE `employees`.`t_test`### WHERE### @1=1 /* INT meta=0 nullable=0 is_null=0 */### SET### @2='prontera' /* VARSTRING(96) meta=96 nullable=0 is_null=0 */NOBLOB (除去BLOB&TEXT的全紀錄) 更新時不包含text類型的列
MariaDB [employees]> update t_test set title = 'juno' where id = 1;Query OK, 1 row affected (0.01 sec)Rows matched: 1 Changed: 1 Warnings: 0類似于FULL模式, 但是binlog不會記錄沒有被更新的大數據類型(BLOB或TEXT類型)
BINLOG 'hP+WWBMBAAAANwAAAM8BAAAAACEAAAAAAAEACWVtcGxveWVlcwAGdF90ZXN0AAMDD/wDYAACBA==hP+WWBgBAAAAMwAAAAICAAAAACEAAAAAAAEAAwMD/AEAAAAFcGF5b278AQAAAARqdW5v'/*!*/;### UPDATE `employees`.`t_test`### WHERE### @1=1 /* INT meta=0 nullable=0 is_null=0 */### @2='payon' /* VARSTRING(96) meta=96 nullable=0 is_null=0 */### SET### @1=1 /* INT meta=0 nullable=0 is_null=0 */### @2='juno' /* VARSTRING(96) meta=96 nullable=0 is_null=0 */更新時包含text列
MariaDB [employees]> update t_test set title = 'prontera', content = 'google' where id = 1;Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0當有大數據類型的列被更新時, 該列才會被記錄, 一定程度上減少了日志量.
BINLOG 'PQCXWBMBAAAANwAAAM8BAAAAACEAAAAAAAEACWVtcGxveWVlcwAGdF90ZXN0AAMDD/wDYAACBA==PQCXWBgBAAAAPgAAAA0CAAAAACEAAAAAAAEAAwMH/AEAAAAEanVub/gBAAAACHByb250ZXJhBgBnb29nbGU='/*!*/;### UPDATE `employees`.`t_test`### WHERE### @1=1 /* INT meta=0 nullable=0 is_null=0 */### @2='juno' /* VARSTRING(96) meta=96 nullable=0 is_null=0 */### SET### @1=1 /* INT meta=0 nullable=0 is_null=0 */### @2='prontera' /* VARSTRING(96) meta=96 nullable=0 is_null=0 */### @3='google' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */通過實驗, 在基于ROW格式情況下, 即使處于同一IDC, 也建議使用minimal模式以大量減少日志量.
基于Docker Compose構建的MySQL MHA集群
新聞熱點
疑難解答
