前言:binlog 用於記錄資料庫執行寫入性操作的日誌資訊,以二進位制的形式保留在磁碟中。它是由 Server 層進行記錄的,使用任何儲存引擎都會產生 binlog。
實驗準備
我們通過 Docker 來對 MySQL binlog 進行一系列的研究。需要進行一些準備工作:
- 拉取 mysql 5.7 版本映象
$ docker pull mysql:5.7
- 準備 mysql 容器掛載的目錄,以及配置檔案資訊
$ mkdir -p infra/mysql
$ cd infra/mysql
# 掛載目錄
$ mkdir data
# 配置檔案
$ mkdir mysql.conf.d && cd mysql.conf.d
$ echo "[mysqld]
# 設定binlog的格式
binlog_format = statement
# 設定日誌儲存路徑
log-bin=/var/lib/mysql/mysql-bin
server-id=12345" > mysqld.cnf
- 啟動 mysql 容器,檢視 bin_log 是否開啟
# 啟動容器
$ docker run -p3306:3306 -d --name mysql57 \
-v /Users/zioyi/infra/mysql/mysql.conf.d:/etc/mysql/conf.d \
-v /Users/zioyi/infra/mysql/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=root --restart=always mysql:5.6
# 進入容器
$ docker exec -it mysql57 sh
$ [mysql57] mysql -uroot -proot
mysql > show variable like "%log_bin%";
+---------------------------------+--------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/mysql-bin |
| log_bin_index | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+--------------------------------+
6 rows in set (0.00 sec)
- 建立資料庫和表
mysql > create database mall character set utf8mb4 collate utf8mb4_unicode_ci;
Query OK, 1 row affected (0.01 sec)
mysql > use mall;
Database changed
mysql > CREATE TABLE `item` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(100) DEFAULT '' COMMENT '商品名稱',
`price` int(10) unsigned DEFAULT '0' COMMENT '價格',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品清單';
binlog 的三種格式
STATMENT
基於 SQL 語句的複製(statment-based replication,SBR),每一條會修改資料的 SQL 語句都會複製到 binlog 中。
我們將 bin_log 的模式設定成 STATMENT,之後重啟容器
$ echo "[mysqld]
# 設定binlog的格式
binlog_format = statement
# 設定日誌儲存路徑
log-bin=/var/lib/mysql/mysql-bin
server-id=12345" > mysqld.cnf
在資料庫中插入一條資料
mysql> insert into item(name, price) values("book", 10);
Query OK, 1 row affected (0.01 sec)
# 知道最新的 binlog 檔案進行檢視
show binlog events in 'mysql-bin.000004';
+------------------+-----+-------------+-----------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------+-----------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mysql-bin.000004 | 4 | Format_desc | 12345 | 120 | Server ver: 5.6.51-log, Binlog ver: 4 |
| mysql-bin.000004 | 120 | Query | 12345 | 263 | create database mall character set utf8mb4 collate utf8mb4_unicode_ci |
| mysql-bin.000004 | 263 | Query | 12345 | 446 | create database blog_service default character set utf8mb4 default collate utf8mb4_general_ci |
| mysql-bin.000004 | 446 | Query | 12345 | 758 | use `mall`; CREATE TABLE `item` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(100) DEFAULT '' COMMENT '',
`price` int(10) unsigned DEFAULT '0' COMMENT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='' |
| mysql-bin.000004 | 758 | Query | 12345 | 837 | BEGIN |
| mysql-bin.000004 | 837 | Intvar | 12345 | 869 | INSERT_ID=1 |
| mysql-bin.000004 | 869 | Query | 12345 | 991 | use `mall`; insert into item(name, price) values("book", 10) |
| mysql-bin.000004 | 991 | Xid | 12345 | 1022 | COMMIT /* xid=15 */ |
+------------------+-----+-------------+-----------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
可以看到,我們的插入已經被記錄,我們再往表中多插入幾條資料:
mysql > insert into item(name, price) values("fruit", 12);
mysql > insert into item(name, price) values("rice", 2);
# 向表中加入要給新欄位
mysql > alter table item add column type smallint default 0;
mysql > update item set type = 1 where type = 0;
# 在看一下binlog中
mysql > show binlog events in 'mysql-bin.000004';
| mysql-bin.000004 | 1022 | Query | 12345 | 1101 | BEGIN |
| mysql-bin.000004 | 1101 | Intvar | 12345 | 1133 | INSERT_ID=2 |
| mysql-bin.000004 | 1133 | Query | 12345 | 1256 | use `mall`; insert into item(name, price) values("fruit", 12) |
| mysql-bin.000004 | 1256 | Xid | 12345 | 1287 | COMMIT /* xid=23 */ |
| mysql-bin.000004 | 1287 | Query | 12345 | 1366 | BEGIN |
| mysql-bin.000004 | 1366 | Intvar | 12345 | 1398 | INSERT_ID=3 |
| mysql-bin.000004 | 1398 | Query | 12345 | 1519 | use `mall`; insert into item(name, price) values("rice", 2) |
| mysql-bin.000004 | 1519 | Xid | 12345 | 1550 | COMMIT /* xid=25 */ |
| mysql-bin.000004 | 1550 | Query | 12345 | 1675 | use `mall`; alter table item add column type smallint default 0 |
| mysql-bin.000004 | 1675 | Query | 12345 | 1754 | BEGIN |
| mysql-bin.000004 | 1754 | Query | 12345 | 1867 | use `mall`; update item set type = 1 where type = 0 |
| mysql-bin.000004 | 1867 | Xid | 12345 | 1898 | COMMIT /* xid=31 */ |
可以看到,insert
、alert
、update
等 SQL 語句都會被記錄在 binlog 中,並且都和執行時的語句一模一樣。
總結一下 STATEMENT 這種方式的優缺點:
- 優點:如果是 alter、update 這種會影響修改多行記錄的變更,只需要記錄一行 SQL 就行(相比於 ROW 模式),這樣減少了 binlog 的日誌量,節約了 IO,從而提高了效能。
- q
ROW
基於行的複製(row-based repication,RBR),不記錄每天 SQL 語句的上下文資訊,僅需記錄哪條語句被修改了。
更改 binlog 的模式為 ROW:
mysql > set global binlog_format = ROW;
mysql > select @@global.binlog_format;
+------------------------+
| @@global.binlog_format |
+------------------------+
| ROW |
+------------------------+
1 row in set (0.00 sec)
# 更新
mysql > update item set type = 1 where type = 2;
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3 Changed: 3 Warnings: 0
對於 STATEMENT 格式的 binlog ,所有的 DML 操作都記錄在 QUERY_EVENT 中,所以我們直接通過
show binlog events in 'mysql-bin.000004';
就可以看到;而對於 ROW 格式的 binlog ,所有的 DML 操作都記錄在 ROWS_EVENT 中,ROWS_EVENT 分為三種:WRITE_ROWS_EVENT,UPDATE_ROWS_EVENT,DELETE_ROWS_EVENT,分別對應 insert,update 和 delete 操作。我們需要通過 mysqlbinlog 工具來檢視
來檢視一下 ROW 內容:
$ mysqlbinlog mysql-bin.000004 -vv --base64-output=decode-rows
#211219 15:07:12 server id 12345 end_log_pos 2162 CRC32 0xb0fc9915 Update_rows: table id 73 flags: STMT_END_F
### UPDATE `mall`.`item`
### WHERE
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='book' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
### @3=10 /* INT meta=0 nullable=1 is_null=0 */
### @4=1 /* SHORTINT meta=0 nullable=1 is_null=0 */
### SET
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='book' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
### @3=10 /* INT meta=0 nullable=1 is_null=0 */
### @4=2 /* SHORTINT meta=0 nullable=1 is_null=0 */
### UPDATE `mall`.`item`
### WHERE
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='fruit' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
### @3=12 /* INT meta=0 nullable=1 is_null=0 */
### @4=1 /* SHORTINT meta=0 nullable=1 is_null=0 */
### SET
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='fruit' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
### @3=12 /* INT meta=0 nullable=1 is_null=0 */
### @4=2 /* SHORTINT meta=0 nullable=1 is_null=0 */
### UPDATE `mall`.`item`
### WHERE
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2='rice' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
### @3=2 /* INT meta=0 nullable=1 is_null=0 */
### @4=1 /* SHORTINT meta=0 nullable=1 is_null=0 */
### SET
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2='rice' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
### @3=2 /* INT meta=0 nullable=1 is_null=0 */
### @4=2 /* SHORTINT meta=0 nullable=1 is_null=0 */
# at 2162
#211219 15:07:12 server id 12345 end_log_pos 2193 CRC32 0x44ee46b2 Xid = 44
COMMIT/*!*/;
DELIMITER ;
在 ROW 模式中,將update item set type = 1 where type = 2;
語句記錄成了對 3 條記錄的變更操作,分別是對於每條滿足 where 條件的記錄的獨立 update 語句。
delete 語句也是一樣
mysql> update item set type = 1 where id = 3;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from item ;
+----+-------+-------+------+
| id | name | price | type |
+----+-------+-------+------+
| 1 | book | 10 | 2 |
| 2 | fruit | 12 | 2 |
| 3 | rice | 2 | 1 |
+----+-------+-------+------+
3 rows in set (0.00 sec)
mysql> delete from item where type = 2;
Query OK, 2 rows affected (0.01 sec)
$ mysqlbinlog mysql-bin.000004 -vv --base64-output=decode-rows
### DELETE FROM `mall`.`item`
### WHERE
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='book' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
### @3=10 /* INT meta=0 nullable=1 is_null=0 */
### @4=2 /* SHORTINT meta=0 nullable=1 is_null=0 */
### DELETE FROM `mall`.`item`
### WHERE
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='fruit' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
### @3=12 /* INT meta=0 nullable=1 is_null=0 */
### @4=2 /* SHORTINT meta=0 nullable=1 is_null=0 */
# at 2612
#211220 1:18:03 server id 12345 end_log_pos 2643 CRC32 0x435b7a4b Xid = 55
COMMIT/*!*/;
再看一下 alter 語句
mysql> alter table item add column updated datetime default CURRENT_TIMESTAMP;
mysqlbinlog mysql-bin.000004 -vv --base64-output=decode-rows
# at 2612
#211220 1:18:03 server id 12345 end_log_pos 2643 CRC32 0x435b7a4b Xid = 55
COMMIT/*!*/;
# at 2643
#211220 1:24:43 server id 12345 end_log_pos 2795 CRC32 0x68a196c8 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1639963483/*!*/;
SET @@session.time_zone='SYSTEM'/*!*/;
alter table item add column updated datetime default CURRENT_TIMESTAMP
/*!*/;
binlog 中記錄了當前的時間戳 1639963483。
再看一下使用系統函式的語句
mysql> select sysdate();
+---------------------+
| sysdate() |
+---------------------+
| 2021-12-20 01:31:26 |
+---------------------+
1 row in set (0.00 sec)
mysql> update item set updated = sysdate() where id = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from item;
+----+------+-------+------+---------------------+
| id | name | price | type | updated |
+----+------+-------+------+---------------------+
| 3 | rice | 2 | 1 | 2021-12-20 01:32:15 |
+----+------+-------+------+---------------------+
1 row in set (0.00 sec)
$ mysqlbinlog mysql-bin.000004 -vv --base64-output=decode-rows
# at 2875
#211220 1:32:15 server id 12345 end_log_pos 2929 CRC32 0x1f777374 Table_map: `mall`.`item` mapped to number 74
# at 2929
#211220 1:32:15 server id 12345 end_log_pos 3009 CRC32 0x312870a3 Update_rows: table id 74 flags: STMT_END_F
### UPDATE `mall`.`item`
### WHERE
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2='rice' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
### @3=2 /* INT meta=0 nullable=1 is_null=0 */
### @4=1 /* SHORTINT meta=0 nullable=1 is_null=0 */
### @5='2021-12-20 01:24:43' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
### SET
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2='rice' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
### @3=2 /* INT meta=0 nullable=1 is_null=0 */
### @4=1 /* SHORTINT meta=0 nullable=1 is_null=0 */
### @5='2021-12-20 01:32:15' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
# at 3009
#211220 1:32:15 server id 12345 end_log_pos 3040 CRC32 0xb49295bc Xid = 59
COMMIT/*!*/;
在 binlog 中,會直接把 sysdate() 當時返回的值記錄在 update 語句中。
MIXED
基於 STATMENT 和 ROW 兩種模式的混合複製(mixed-based replication,MBR),一般的複製使用 STATMENT 模式儲存 binlog,對於 STATEMENT 模式無法複製的操作使用 ROW 模式儲存 binlog。
mysql > set global binlog_format = MIXED;
mysql > select @@global.binlog_format;
+------------------------+
| @@global.binlog_format |
+------------------------+
| ROW |
+------------------------+
1 row in set (0.00 sec)
# 更新
mysql > update item set type = 1 where type = 2;
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3 Changed: 3 Warnings: 0
插入語句1
mysql> insert into item(name, price, type) values("pencil", 1, 3);
Query OK, 1 row affected (0.00 sec)
$ mysqlbinlog mysql-bin.000004 -vv --base64-output=decode-rows
#211220 1:39:45 server id 12345 end_log_pos 3233 CRC32 0xfcb23be0 Write_rows: table id 74 flags: STMT_END_F
### INSERT INTO `mall`.`item`
### SET
### @1=4 /* INT meta=0 nullable=0 is_null=0 */
### @2='pencil' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
### @3=1 /* INT meta=0 nullable=1 is_null=0 */
### @4=3 /* SHORTINT meta=0 nullable=1 is_null=0 */
### @5='2021-12-20 01:39:45' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
# at 3233
#211220 1:39:45 server id 12345 end_log_pos 3264 CRC32 0x1ee1856c Xid = 62
COMMIT/*!*/;
通過 ROW 模式記錄,因為 updated 欄位是通過 current_timestamp 來得到的
update 語句1
mysql> update item set type = 4;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
$ mysqlbinlog mysql-bin.000004 -vv --base64-output=decode-rows
### UPDATE `mall`.`item`
### WHERE
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2='rice' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
### @3=2 /* INT meta=0 nullable=1 is_null=0 */
### @4=1 /* SHORTINT meta=0 nullable=1 is_null=0 */
### @5='2021-12-20 01:32:15' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
### SET
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2='rice' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
### @3=2 /* INT meta=0 nullable=1 is_null=0 */
### @4=4 /* SHORTINT meta=0 nullable=1 is_null=0 */
### @5='2021-12-20 01:32:15' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
### UPDATE `mall`.`item`
### WHERE
### @1=4 /* INT meta=0 nullable=0 is_null=0 */
### @2='pencil' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
### @3=1 /* INT meta=0 nullable=1 is_null=0 */
### @4=3 /* SHORTINT meta=0 nullable=1 is_null=0 */
### @5='2021-12-20 01:39:45' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
### SET
### @1=4 /* INT meta=0 nullable=0 is_null=0 */
### @2='pencil' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
### @3=1 /* INT meta=0 nullable=1 is_null=0 */
### @4=4 /* SHORTINT meta=0 nullable=1 is_null=0 */
### @5='2021-12-20 01:39:45' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
### UPDATE `mall`.`item`
### WHERE
### @1=5 /* INT meta=0 nullable=0 is_null=0 */
### @2='pencil' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
### @3=1 /* INT meta=0 nullable=1 is_null=0 */
### @4=3 /* SHORTINT meta=0 nullable=1 is_null=0 */
### @5='2021-12-20 10:00:00' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
### SET
### @1=5 /* INT meta=0 nullable=0 is_null=0 */
### @2='pencil' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
### @3=1 /* INT meta=0 nullable=1 is_null=0 */
### @4=4 /* SHORTINT meta=0 nullable=1 is_null=0 */
### @5='2021-12-20 10:00:00' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
# at 3782
還是使用 ROW 模式,看來只要表中有default current_timestamp
欄位,都是這樣的。
我們把 updated 欄位刪掉,再來更新試試:
mysql> alter table item drop column updated;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from item;
+----+--------+-------+------+
| id | name | price | type |
+----+--------+-------+------+
| 3 | rice | 2 | 4 |
| 4 | pencil | 1 | 4 |
| 5 | pencil | 1 | 4 |
+----+--------+-------+------+
3 rows in set (0.00 sec)
mysql> update item set type = 5;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> show binlog events in 'mysql-bin.000004';
| mysql-bin.000004 | 3813 | Query | 12345 | 3923 | use `mall`; alter table item drop column updated |
| mysql-bin.000004 | 3923 | Query | 12345 | 4002 | BEGIN |
| mysql-bin.000004 | 4002 | Query | 12345 | 4100 | use `mall`; update item set type = 5 |
| mysql-bin.000004 | 4100 | Xid | 12345 | 4131 | COMMIT /* xid=82 */ |
此時就是使用 STATMENT 模式來儲存 binlog了。
如果使用系統函式呢
mysql> update item set type = ceil(rand() * 10);
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> select * from item;
+----+--------+-------+------+
| id | name | price | type |
+----+--------+-------+------+
| 3 | rice | 2 | 4 |
| 4 | pencil | 1 | 10 |
| 5 | pencil | 1 | 8 |
+----+--------+-------+------+
3 rows in set (0.00 sec)
每行的 type 都不一樣,看起來這隻能通過 ROW 模式來就記錄了吧?來看看 binlog
$ mysqlbinlog mysql-bin.000004 -vv --base64-output=decode-rows
#211220 1:57:57 server id 12345 end_log_pos 4401 CRC32 0x95bf290e Update_rows: table id 75 flags: STMT_END_F
### UPDATE `mall`.`item`
### WHERE
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2='rice' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
### @3=2 /* INT meta=0 nullable=1 is_null=0 */
### @4=5 /* SHORTINT meta=0 nullable=1 is_null=0 */
### SET
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2='rice' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
### @3=2 /* INT meta=0 nullable=1 is_null=0 */
### @4=4 /* SHORTINT meta=0 nullable=1 is_null=0 */
### UPDATE `mall`.`item`
### WHERE
### @1=4 /* INT meta=0 nullable=0 is_null=0 */
### @2='pencil' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
### @3=1 /* INT meta=0 nullable=1 is_null=0 */
### @4=5 /* SHORTINT meta=0 nullable=1 is_null=0 */
### SET
### @1=4 /* INT meta=0 nullable=0 is_null=0 */
### @2='pencil' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
### @3=1 /* INT meta=0 nullable=1 is_null=0 */
### @4=10 /* SHORTINT meta=0 nullable=1 is_null=0 */
### UPDATE `mall`.`item`
### WHERE
### @1=5 /* INT meta=0 nullable=0 is_null=0 */
### @2='pencil' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
### @3=1 /* INT meta=0 nullable=1 is_null=0 */
### @4=5 /* SHORTINT meta=0 nullable=1 is_null=0 */
### SET
### @1=5 /* INT meta=0 nullable=0 is_null=0 */
### @2='pencil' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
### @3=1 /* INT meta=0 nullable=1 is_null=0 */
### @4=8 /* SHORTINT meta=0 nullable=1 is_null=0 */
# at 4401
果真如此。
對比
模式 | 優點 | 缺點 |
---|---|---|
STATMENT | 如果是 alter、update 這種會影響修改多行記錄的變更,只需要記錄一行 SQL 就行這樣減少了 binlog 的日誌量,節約了 IO,從而提高了效能。 | 在某些情況下,無法還原當時的資料,比如:使用系統函式 sysdate()、rand()等 |
ROW | 不會出現某些特定的儲存過程、function、trigger的調研和觸發無法被正確複製的問題 | 會產生大量的日誌,比如 update 影響函式較多時。 |
MIXED | 綜合了 STATMENT 和 ROW 的優缺點 | - |
在MySQL 5.7.7之前,預設的格式是STATEMENT,MySQL 5.7.7之後,預設值是ROW。
binlog 刷盤時機
對於 InnoDB 儲存引擎而言,只有在事務提交時才會記錄 binlog,此時記錄還在記憶體中,那麼 binlog 是什麼時候刷到磁碟中的呢?MySQL 通過 sync_binlog 引數控制 binlog 的刷盤時機,取值範圍是0-N:
- 0:不去強制要求,由系統自行判斷何時寫入磁碟;
- 1:每次 commit 的時候都要將 binlog 寫入磁碟;
- N:每 N 個事務,才會將binlog寫入磁碟。
從上面可以看出,sync_binlog 最安全的是設定是 1,這也是 MySQL 5.7.7 之後版本的預設值。但是設定一個大一些的值可以提升資料庫效能,因此實際情況下也可以將值適當調大,犧牲一定的一致性來獲取更好的效能。
binlog 的使用場景
在實際應用中,binlog 的主要使用場景有:
- 資料恢復:通過使用 mysqlbinlog 工具來恢復資料。
- 主從複製:在 Master 端開啟 binlog,然後將 binlog 傳送到各個 Slave 端,Slave 端重放 binlog 從而達到主從資料一致。
- 資料同步:與主從複製類似,當我們想同步 MySQL 的資料到適合做資料分析的其他資料庫中時,也是通過消費 binlog 來實現的。