Ⅰ毡鉴、binlog定義和作用
1.1 定義
記錄每次數(shù)據(jù)庫的邏輯操作(包括表結構變更和表數(shù)據(jù)修改)
包含：binlog文件和index文件
1.2 作用
- 復制：從庫讀取主庫binlog，本地回放實現(xiàn)復制
- 備份恢復：最近邏輯備份數(shù)據(jù)+binlog實現(xiàn)最大可能恢復
- innodb恢復：開啟binlog的情況下，innodb事務提交是二階段提交晰搀，發(fā)生crash的時候蕉拢，innodb中事務有兩種狀態(tài)，一種是commit刻两，一種是prepared增蹭，對于prepared狀態(tài)的事務需要根據(jù)binlog來判斷是提交還是回滾，以此來保證主從數(shù)據(jù)一致性
Ⅱ磅摹、不同類型binlog對比
| - | statement | row | mixed |
|---|---|---|---|
| 說明 | 記錄操作的SQL語句 | 記錄每一行數(shù)據(jù)的變更 | 混合模式 |
| 優(yōu)點 | 易于理解 | 數(shù)據(jù)一致性高滋迈、可flashback | 綜合上述兩種模式 |
| 缺點 | 不支持不確定SQL語句 | 每張表一定要有主鍵 | 之前版本bug比較多 |
| 線上使用 | 不推薦 | 推薦 | 不推薦 |
再說一遍row
- 優(yōu)點：記錄每一行記錄變化，確保證主從數(shù)據(jù)嚴格一致性
- 缺點：全表update户誓，delete全表時binlog文件大饼灿，所以不建議用MySQL做類似操作
調(diào)成statement看，會發(fā)現(xiàn)記錄的是sql語句帝美，不說太多碍彭，線上基本上不會用
寫入數(shù)據(jù)量很大時，ROW格式下悼潭，commit會比較耗時間庇忌，因為他還要寫binlog（ binlog在提交時才寫入 ）
假設更新一張幾百萬的表，產(chǎn)生的binlog可能會有幾百兆舰褪，當commit時漆枚，寫入的數(shù)據(jù)量就是幾百兆，所以會有“阻塞”等待的效果抵知。但其實是在寫binlog到磁盤
Ⅲ墙基、相關參數(shù)及使用命令
log_bin=bin 默認不打開软族，和oracle一樣，不管事務大小残制，提交速度都一樣)
log_bin_basename 設置binlog名立砸，不設置默認為機器名，直接用上面的log_bin=bin也表示二進制文件以bin開頭
binlog_format 之前為statement初茶，5.6有幾個小版本用的mixed颗祝，5.7開始默認row了
max_binlog_size 限定單個binlog文件大小，默認1G
binlog_do_db
binlog_ignore_db binlog過濾
sync_binlog 默認是0恼布，binlog文件每次寫入內(nèi)容不會立刻持久化到磁盤螺戳，具體持久化是交給操作系統(tǒng)做，固系統(tǒng)崩潰會導致binlog的丟失和不一致折汞，建議設置為1倔幼，事務寫入到binlog后立即fsync到磁盤
flush binary logs; 新生成一個binlog
show master status; 查看當前的binlog
tips:
①bin.999999滿了之后怎么辦？ 前面加1位
②binlog文件可能大于max_binlog_size,原因是一個事務產(chǎn)生的所有事件必須記錄在同一個binlog中
Ⅳ爽待、binlog內(nèi)容
4.1 index文件
有序地記錄了當前MySQL服務所使用的所用binlog文件
MySQL運行過程中千萬不要騷操作修改index文件损同，避免出問題
4.2 binlog文件
執(zhí)行show binlog events in 'xxx'；
查看binlog文件內(nèi)容鸟款，不指定文件默認看第一個binlog文件
(root@localhost) [test]> show binlog events;
+------------+------+----------------+-----------+-------------+--------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------+------+----------------+-----------+-------------+--------------------------------------------------+
| bin.000001 | 4 | Format_desc | 3 | 123 | Server ver: 5.7.18-log, Binlog ver: 4 |
| bin.000001 | 123 | Previous_gtids | 3 | 154 | |
| bin.000001 | 154 | Anonymous_Gtid | 3 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| bin.000001 | 219 | Query | 3 | 313 | create database test |
| bin.000001 | 313 | Anonymous_Gtid | 3 | 378 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| bin.000001 | 378 | Query | 3 | 474 | use `test`; create table a (a int) |
| bin.000001 | 474 | Anonymous_Gtid | 3 | 539 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| bin.000001 | 539 | Query | 3 | 649 | use `test`; create table b (b int) engine=myisam |
| bin.000001 | 649 | Anonymous_Gtid | 3 | 714 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| bin.000001 | 714 | Query | 3 | 786 | BEGIN |
| bin.000001 | 786 | Table_map | 3 | 830 | table_id: 219 (test.a) |
| bin.000001 | 830 | Write_rows | 3 | 870 | table_id: 219 flags: STMT_END_F |
| bin.000001 | 870 | Xid | 3 | 901 | COMMIT /* xid=18 */ |
| bin.000001 | 901 | Anonymous_Gtid | 3 | 966 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| bin.000001 | 966 | Query | 3 | 1038 | BEGIN |
| bin.000001 | 1038 | Table_map | 3 | 1082 | table_id: 219 (test.a) |
| bin.000001 | 1082 | Update_rows | 3 | 1128 | table_id: 219 flags: STMT_END_F |
| bin.000001 | 1128 | Xid | 3 | 1159 | COMMIT /* xid=21 */ |
| bin.000001 | 1159 | Anonymous_Gtid | 3 | 1224 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| bin.000001 | 1224 | Query | 3 | 1296 | BEGIN |
| bin.000001 | 1296 | Table_map | 3 | 1340 | table_id: 219 (test.a) |
| bin.000001 | 1340 | Delete_rows | 3 | 1380 | table_id: 219 flags: STMT_END_F |
| bin.000001 | 1380 | Xid | 3 | 1411 | COMMIT /* xid=22 */ |
| bin.000001 | 1411 | Rotate | 3 | 1452 | bin.000002;pos=4 |
+------------+------+----------------+-----------+-------------+--------------------------------------------------+
24 rows in set (0.00 sec)
由此可見膏燃，binlog是由各類event組成，下面分析下event相關內(nèi)容
| field | 含義 |
|---|---|
| (Log_name,Pos) | 一個event開始的位置信息 |
| End_log_pos | 一個event結束的位置信息 |
| Event_type | event類型 |
①End_log_pos - Pos = 每個event占用的字節(jié)數(shù)
②show master status; Position就代表binlog寫到這個偏移量的地方何什，也就是寫了這么多字節(jié)组哩，即當前binlog文件的大小
③每個binlog前四個字節(jié)保留，不寫數(shù)據(jù)
4.3 Event類型分析
| Event_type | 含義 |
|---|---|
| Format_desc | 一個binlog文件開始处渣，記錄server的版本號和二進制日志的版本號伶贰，5.7版本固定占119個字節(jié) |
| Previous_gtids/Anonymous_Gtid | 5.7版本加進來的gtid |
| Query | 開始一個sql語句 |
| Table_map | 操作的哪個庫表 |
| Write_rows | 插入某條記錄，具體看不到 |
| Delete_rows | 刪除某條記錄 |
| Update_rows | 更新某條記錄 |
| Xid | 事務提交霍比，可以看到事務號 |
| Rotate | 一個binlog文件結束,指向下一個event的起始位置（bin.xxx;pos=4） |
再強調(diào)幕袱，row記錄的是每條記錄的情況(每次操作的每個記錄記下來)，而不是sql語句
Ⅴ悠瞬、mysqlbinlog工具的使用
5.1 解析binlog
1们豌、[root@VM_0_5_centos src]# mysqlbinlog bin.000001
截取一段：
# at 1224
#171107 10:17:31 server id 3 end_log_pos 1296 CRC32 0xd4d80fa6 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1510021051/*!*/;
BEGIN
/*!*/;
# at 1296
#171107 10:17:31 server id 3 end_log_pos 1340 CRC32 0x73b187fa Table_map: `test`.`a` mapped to number 219
# at 1340
#171107 10:17:31 server id 3 end_log_pos 1380 CRC32 0x2e637fcd Delete_rows: table id 219 flags: STMT_END_F
BINLOG '
uxcBWhMDAAAALAAAADwFAAAAANsAAAAAAAEABHRlc3QAAWEAAQMAAfqHsXM=
uxcBWiADAAAAKAAAAGQFAAAAANsAAAAAAAEAAgAB//4CAAAAzX9jLg==
'/*!*/;
# at 1380
#171107 10:17:31 server id 3 end_log_pos 1411 CRC32 0x2a6353fd Xid = 22
COMMIT/*!*/;
這個解析出來at xxx什么的可以跟前面直接show binlog events對應起來，但是dml的內(nèi)容有點小難懂浅妆，原因是為了方便傳輸解析出來的每行記錄的內(nèi)容被base64轉(zhuǎn)換了
tips:
mysqlbinlog --base64-output=never xxx 非row格式下只看ddl望迎，加密的dml不顯示
2、[root@VM_0_5_centos src]# mysqlbinlog --base64-output=decode-rows -v bin.000001
row格式下可以將密文轉(zhuǎn)為偽sql
同樣截取一段
# at 966
#171107 10:17:23 server id 3 end_log_pos 1038 CRC32 0x00be64e0 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1510021043/*!*/;
BEGIN
/*!*/;
# at 1038
#171107 10:17:23 server id 3 end_log_pos 1082 CRC32 0x5286fd55 Table_map: `test`.`a` mapped to number 219
# at 1082
#171107 10:17:23 server id 3 end_log_pos 1128 CRC32 0x1ed2714c Update_rows: table id 219 flags: STMT_END_F
### UPDATE `test`.`a`
### WHERE
### @1=1
### SET
### @1=2
# at 1128
#171107 10:17:23 server id 3 end_log_pos 1159 CRC32 0xa254d40a Xid = 21
COMMIT/*!*/;
# at 1159
#171107 10:17:31 server id 3 end_log_pos 1224 CRC32 0x76a7413c Anonymous_GTID last_committed=5 sequence_number=6
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
看到的是每行記錄的內(nèi)容凌外，@n表示第幾列
切記這搞出來的絕對不是sql語句哈辩尊，他只管你一行記錄的內(nèi)容，不管你的sql
tips:
-vv 可以看到更詳細內(nèi)容康辑，比如每個列的類型和屬性摄欲，通常一個v夠看
insert和delete記錄一整行記錄
update記錄前項和后項轿亮。全表更新會導致二進制日志特別大
問題：
binlog_format設為row，只知道變化胸墙，不知道sql語句我注，這咋辦？
解決：
設置參數(shù)binlog_rows_query_log_events=1 建議打開
再去看binlog的events迟隅，會多一個叫Rows_query的event但骨，它會記錄下改變行內(nèi)容的sql
5.2 常用參數(shù)
- 根據(jù)時間點解析
--start-datetime='xxx-xx-xx xx:xx:xx'
--stop-datetime='xxx-xx-xx xx:xx:xx'
- 根據(jù)二進制偏移量解析
--start-position=xxx
tips:
這是從xxx來解析，那從xxx+1開始呢智袭？會報error奔缠，從這邊開始讀出來不是一個完整的event，xxx-1開始也是報錯吼野，讀的時候校哎，每個evnet都有個header，如果不是標準位置就會報錯
ERROR: Error in Log_event::read_log_event(): 'read error', data_len: 16640, event_type: 90
ERROR: Could not read entry at offset 1158: Error in log format or read error.
--stop--position=xxx
到xxx結束箫锤，并不包含xxx這個點
特殊情況：如果指向了一個Table_map的events,會拋出了一個warning
WARNING: The range of printed events ends with a row event or a table map event that does not have the STMT_END_F flag set. This might be because the last statement was not fully written to the log, or because you are using a --stop-position or --stop-datetime that refers to an event in the middle of a statement. The event(s) from the partial statement have not been written to output.
通常通過datetime找position贬蛙，再來進行恢復
Ⅵ雨女、通過mysqlbinlog恢復數(shù)據(jù)
mysqlbinlog binlog.00003 |mysql -S /tmp/mysql.sock -f
-f強制跳過錯誤
只恢復某一段谚攒，就加上--start-position或者--start-datetime等
官方文檔：
如果存在多個二進制日志，并不建議一個一個恢復氛堕，而是用下面這個方法
mysqlbinlog binlog.[0-9]* |mysql -u root -p
一個一個恢復會報danger
說明：
如果分兩次操作馏臭，會被認為在兩個session中操作，如果剛好用到一個臨時表讼稚，一個session退出了括儒，另一個session上去就出錯了
另一種方法：
mysqlbinlog binlog.000001 > /tmp/statements.sql
mysqlbinlog binlog.000002 >> /tmp/statements.sql
mysql -u root -p -e "source /tmp/statements.sql"
Ⅶ、清理binlog
這里介紹三種清理binlog的方法：
法1：purge
purge binary logs to 'xxx';
清理xxxbinlog文件之前的內(nèi)容
purge binary logs before 'xxx'
清理xxx日期之前的內(nèi)容
法2：rm
step1：MySQL停止服務
step2：按順序rm掉binlog文件
step3：編輯index文件锐想，將rm掉的binlog文件從index中去掉
法3：配自動清理參數(shù)
[mysqld]
expire_logs_days=N
表示只保存N天的binlog帮寻，默認值是0，表示不刪除
實現(xiàn)原理：
當binlog文件切換或者mysql服務啟動時赠摇，遍歷index文件固逗，找到第一個"最后修改時間在N天之內(nèi)的文件"，然后將該文件之前的所有binlog全部刪除
Ⅷ藕帜、其他相關問題
8.1 增量備份怎么做
通常MySQL不做增量備份烫罩，除非單點，因為MySQL復制本身就是實時在做增量洽故，從庫開binlog贝攒，在從庫上備份binlog即可(flush binary logs;產(chǎn)生新日志，把之前的存下來)
Oracle增量備份還是有用的时甚，萬一page發(fā)生crash隘弊，需要把所有日志重做一遍
8.2 row格式的binlog回放
一個sql插了3條記錄哈踱，其實插了3次，對應3個write_rows,解析這個東西梨熙，變相執(zhí)行3個sql
一個sql刪了3條記錄嚣鄙，對應的單個delete_rows，回放的時候先根據(jù)主鍵回放串结，沒有主鍵就找一個索引來回放哑子，如果一個索引沒有珠闰，會scan全表
如果表中有10w條記錄炭序，一個索引沒有，你去刪全表的話侦香，每條記錄刪的時候都會掃10w次把敞，復雜度是O(10w^2)次弥奸，但因為記錄越來越少，最后會掃描10w + （10w * 10w-1）/2 次奋早，所以為什么每張表必須要有主鍵盛霎，這里又是一種體現(xiàn)，有主鍵回放速度會快很多耽装，特別是delete和update
注意：沒主鍵是不要扯什么row_id愤炸，binlog是server層的東西，和row_id沒關系
tips:
MySQL5.6推出下面這個參數(shù)來指定scan算法可以部分解決無主鍵表導致的復制延遲問題掉奄，其基本思路是對于在一個ROWS EVENT中的所有前鏡像收集起來规个，然后在一次掃描全表時，判斷HASH中的每一條記錄進行更新
slave_rows_search_algorithms姓建，默認值是table_scan诞仓，index_scan，另一個hash_scan可配速兔，默認沒開墅拭，也不建議用，因為創(chuàng)建hash表消耗比較大
8.3 flash back
二進制日志能實現(xiàn)一個非常好的功能涣狗，用來挽救數(shù)據(jù)谍婉，實現(xiàn)flash back，oracle中還要用到undo
對于insert的event屑柔，如果要flash back屡萤，就搞成delete，delete搞成insert掸宛，update交換前后項即可
聽說8.0會支持這個工具死陆，但是現(xiàn)在每家互聯(lián)網(wǎng)公司都開源自己的工具，實現(xiàn)flashback，但是一定要用row格式的binlog_format
8.4 binlog_cache
binlog默認寫入binlog_cache中
| 步驟 | 操作 |
|---|---|
| 1 | binlog被write到各session對應的文件句柄緩存中措译，也就是標準io緩存 |
| 2 | binlog從每個session私有的緩存中flush到公共緩存中别凤，即操作系統(tǒng)緩存中 |
| 3 | binlog從內(nèi)存中sync到文件系統(tǒng)，持久化 |
binlog生成的過程
| 步驟 | 操作 |
|---|---|
| 1 | binlog被write到各session對應的文件句柄緩存中领虹，也就是標準io緩存 |
| 2 | binlog從每個session私有的緩存中flush到公共緩存中规哪，即操作系統(tǒng)緩存中 |
| 3 | binlog從內(nèi)存中sync到文件系統(tǒng)，持久化 |
第一步session之間互相看不到
第二步每個session之間互相可以看到
在這之前只要機器發(fā)生crash塌衰，則日志就相對應的丟失了
特殊情況：遇到大事務時诉稍，binlog很大，cache放不下就會落盤
(root@172.16.0.10) [(none)]> show global status like 'binlog_cache%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Binlog_cache_disk_use | 0 | -- 記錄使用臨時文件記錄binlog日志的次數(shù)(監(jiān)控項)
| Binlog_cache_use | 1 | -- 記錄使用緩沖寫binlog日志的次數(shù)
+-----------------------+-------+
2 rows in set (0.01 sec)
(root@172.16.0.10) [(none)]> show variables like 'binlog_cache_size';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| binlog_cache_size | 32768 |
+-------------------+-------+
1 row in set (0.00 sec)
默認為32K最疆，sessioin級的內(nèi)存變量杯巨，勿設置太大
- 生產(chǎn)環(huán)境中，我們一般把sync_binlog設為1努酸，讓binlog繞過緩存直接落盤服爷，以此來保證數(shù)據(jù)完整性，所以上面這塊binlog_cache內(nèi)容了解即可
- cache寫不下落盤获诈，然后再寫binlog仍源，就是兩次寫磁盤，這樣會變慢舔涎。若如果參數(shù)Binlog_cache_disk_use次數(shù)很多笼踩，須考慮調(diào)大binlog_cache_size,或者檢查業(yè)務中是否存在大事務(oltp場景盡量大事務拆小事務)
