共計 7370 個字符,預計需要花費 19 分鐘才能閱讀完成。
本篇內容主要講解“mysql binlog 相關知識點有哪些”,感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷,實用性強。下面就讓丸趣 TV 小編來帶大家學習“mysql binlog 相關知識點有哪些”吧!
1.
– 邏輯日志, 記錄的是數據庫內部的所有變動(sql 語句 行的改變)
– server 層日志,binlog 不僅僅記錄 innodb 的變動,也記錄 myisam 存儲引擎的變動。
– innodb redo 是存儲引擎層,和 binlog 不是一層,redo 只記錄 innodb 的變化。
– binlog 不記錄 select show 等查詢語句, 記錄 dml ddl 等數據庫變動的語句.
2.binlog 的作用
– 可以查看 mysql 變更
– mysql 的復制架構(主從)
– mysql 備份恢復(可以通過 binlog 進行數據的補齊或者回滾)
3.binlog 的位置
log_bin = /home/mysql3306/mysql3306/mysql-bin
#binlog 路徑 +binlog 前綴名 如:mysql-bin.000001
4. 相關參數簡介
(1).log-bin
決定了 msyql 的 binlog 的名字,生成的 binlog 名字為 mysql-bin.000001
(2).binlog_format
規定 binlog 的格式,binlog 有三種格式 statement,row 以及 mixed,默認使用默認使用 statement,建議使用 row 格式
(3).expire_logs_days
過期時間 建議大家根據業務設置 7D-30D
(4).binlog_do_db
此參數表示只記錄指定數據庫的二進制日志
(5).binlog_ignore_db
此參數表示不記錄指定的數據庫的二進制日志
(6).sync_binlog(binlog 落盤策略)
0— 每一秒刷一次磁盤
1— 每次事務提交都刷一次磁盤
n—(100 200 500)每 n 次提交落盤一次
innodb_flush_log_at_trx_commit =1
sync_binlog=1
雙 1 配置,數據庫的安全性是最高的,不會丟事務。
(7).binlog_checksum 為 {CRC32|NONE}
寫 binlog 時,會將內容生成校驗位,之后存儲在 binlog 中。
默認情況下,服務器記錄事件的長度以及事件本身,并使用它來驗證事件是否正確寫入。也可以通過設置 binlog_checksum 系統變量來使服務器為事件寫入校驗和。
(8).max_binlog_size
binlog 文件的最大值,默認和最大是 1GB,并不能嚴格限定二進制文件的大小
(9).max_binlog_cache_size
表示的是 binlog 能夠使用的最大 cache 內存大小
當我們執行多語句事務的時候 所有 session 的使用的內存超過 max_binlog_cache_size 的值時
就會報錯:“Multi-statement transaction required more than max_binlog_cache_size bytes ofstorage”
(10).binlog_cache_size = 4M
線程級參數,不能設置太大
5.binlog 格式
(1).statement
mysql show variables like %binlog_format%
+—————+———–+
| Variable_name | Value |
+—————+———–+
| binlog_format | STATEMENT |
+—————+———–+
執行語句:
insert into wwj.t1 values(2, mxt2
insert into wwj.t1 values(3, mxt3
insert into wwj.t1 values(4, mxt4
查看 binlog
點擊 (此處) 折疊或打開
mysql show binlog events;
+——————+—–+—————-+———–+————-+—————————————+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+——————+—–+—————-+———–+————-+—————————————+
| mysql-bin.000001 | 4 | Format_desc | 12023307 | 123 | Server ver: 5.7.21-log, Binlog ver: 4 |
| mysql-bin.000001 | 123 | Previous_gtids | 12023307 | 154 | |
| mysql-bin.000001 | 154 | Anonymous_Gtid | 12023307 | 219 | SET @@SESSION.GTID_NEXT= ANONYMOUS |
| mysql-bin.000001 | 219 | Query | 12023307 | 293 | BEGIN |
| mysql-bin.000001 | 293 | Query | 12023307 | 398 | insert into wwj.t1 values(13, mxt3) |
| mysql-bin.000001 | 398 | Xid | 12023307 | 429 | COMMIT /* xid=275 */ |
| mysql-bin.000001 | 429 | Anonymous_Gtid | 12023307 | 494 | SET @@SESSION.GTID_NEXT= ANONYMOUS |
| mysql-bin.000001 | 494 | Query | 12023307 | 568 | BEGIN |
| mysql-bin.000001 | 568 | Query | 12023307 | 673 | insert into wwj.t1 values(14, mxt4) |
| mysql-bin.000001 | 673 | Xid | 12023307 | 704 | COMMIT /* xid=276 */ |
+——————+—–+—————-+———–+————-+—————————————+
Log_name:binlog 文件名
Pos:binlog 的 position 點,寫入 binlog 的日志的字節數,就是 pos 的大小,在每次新生成 binlog 的時候,pos 點會重置,但是在一個 binlog 內部,這個 pos 是單調遞增的。
xid:分布式事務 ID,Innodb 支持分布式事務,Innodb 支持的單實例內部的,server 層和存儲引擎層的分布式事務,還有多節點分布式事務(ndb)
Server_id:實例 server_id
解析 binlog 工具
/usr/local/mysql/bin/mysqlbinlog mysql-bin.000001 /tmp/all.bin
點擊 (此處) 折疊或打開
# at 293
#180325 4:14:20 server id 12023307 end_log_pos 398 CRC32 0xb9d2f949 Query thread_id=14 exec_time=0 error_code=0
SET TIMESTAMP=1521922460/*!*/;
insert into wwj.t1 values(13, mxt3)
/*!*/;
# at 398
查看當前的 binlog pos 點的位置
mysql show master status;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect…
Connection id: 15
Current database: *** NONE ***
+——————+———-+————–+——————+——————-+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+——————+———-+————–+——————+——————-+
| mysql-bin.000001 | 704 | | | |
+——————+———-+————–+——————+——————-+
應用 binlog
/usr/local/mysql/bin/mysqlbinlog mysql-bin.000001 /tmp/all.bin
mysql -uroot -proot -S /tmp/mysql3306.sock /tmp/all.bin
mysql source /tmp/all.bin
應用 binlog 需要注意的事項
如果應用多個 binlog
錯誤做法
mysqlbinlog mysql-bin.000001 | mysql -S -u -p
mysqlbinlog mysql-bin.000002 | mysql -S -u -p
正確做法
mysqlbinlog mysql-bin.000001 mysql-bin.000002 | mysql -S -u -p
或者
mysqlbinlog mysql-bin.000001 mysql-bin.000002 /tmp/all.bin
mysql -S -u -p /tmp/all.bin
(2).row
mysql show variables like binlog_format
+—————+——-+
| Variable_name | Value |
+—————+——-+
| binlog_format | ROW |
+—————+——-+
row 格式 binlog:row 格式 binlog 記錄的是 mysql 的行的改變前后的數據
比如在 stament 格式中,update 數據,記錄的是 update 語句
而且 row 格式中,記錄的行的改變的前后,如果 update1000 數據,記錄一千行的數據改變
mysqlbinlog -v mysql-bin.000001 /tmp/all.bin 增加偽 sql
mysqlbinlog -vv mysql-bin.000001 /tmp/all.bin 增加數據類型
點擊 (此處) 折疊或打開
# at 334
#180325 5:51:04 server id 12023307 end_log_pos 458 CRC32 0x1be59376 Delete_rows: table id 108 flags: STMT_END_F
BINLOG
SMi2WhMLdrcALwAAAE4BAAAAAGwAAAAAAAEAA3d3agACdDEAAgMPArQAAsKvIYM=
SMi2WiALdrcAfAAAAMoBAAAAAGwAAAAAAAEAAgAC//wBAAAAA3d3avwCAAAABG14dDL8AwAAAARt
eHQz/AQAAAAEbXh0NPwFAAAABG14dDL8BgAAAARteHQz/AcAAAAEbXh0NPwNAAAABG14dDP8DgAA
AARteHQ0dpPlGw==
/*!*/;
### DELETE FROM `wwj`.`t1`
### WHERE
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2= wwj /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
### DELETE FROM `wwj`.`t1`
### WHERE
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2= mxt2 /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
### DELETE FROM `wwj`.`t1`
### WHERE
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2= mxt3 /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
### DELETE FROM `wwj`.`t1`
### WHERE
### @1=4 /* INT meta=0 nullable=0 is_null=0 */
### @2= mxt4 /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
### DELETE FROM `wwj`.`t1`
### WHERE
### @1=5 /* INT meta=0 nullable=0 is_null=0 */
### @2= mxt2 /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
### DELETE FROM `wwj`.`t1`
### WHERE
### @1=6 /* INT meta=0 nullable=0 is_null=0 */
### @2= mxt3 /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
### DELETE FROM `wwj`.`t1`
### WHERE
### @1=7 /* INT meta=0 nullable=0 is_null=0 */
### @2= mxt4 /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
### DELETE FROM `wwj`.`t1`
### WHERE
### @1=13 /* INT meta=0 nullable=0 is_null=0 */
### @2= mxt3 /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
### DELETE FROM `wwj`.`t1`
### WHERE
### @1=14 /* INT meta=0 nullable=0 is_null=0 */
### @2= mxt4 /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
# at 458
其他解析方法
解析不帶行數據
/usr/local/mysql/bin/mysqlbinlog –base64-output=DECODE-ROWS -vv mysql-bin.000001
如果 binlog 解析出來需要恢復到數據庫,那么需要注意不要加 –base64-output=DECODE-ROWS。
/usr/local/mysql/bin/mysqlbinlog
start-datetime 開始時間
stop-datetime 停止時間
/usr/local/mysql/bin/mysqlbinlog –start-datetime= 2017-04-20 9:00:00 –stop-datetime= 2017-07-20 18:00:00 /home/mysql3306/mysql3306/mysql-bin.000001 /tmp/mysql_binlog.sql
start-position 開始時間的 position
stop-position 停止時間的 position
row 格式優點:
- 記錄行改變,而不是 SQL,能最大限度保證數據安全性
-row 格式可以用于閃回(恢復數據)
在線上的環境中,推薦使用 row 格式的 binlog
-row 格式 update delete 會對磁盤 網絡造成一個比較大的壓力
(3).mixed
一般情況下使用 statment 格式,在遇到特殊情況(造成主從不一致)改成使用 row 格式記錄。
mixed 格式在舊版本 bug 比較多
不推薦使用
6. 清理 binlog
reset master; // 刪除 master 的 binlog,線上不要使用
purge master logs before 2012-03-30 17:20:00 // 刪除指定日期以前的日志索引中 binlog 日志文件
purge master logs to binlog.000002 // 刪除指定日志文件的日志索引中 binlog 日志文件
如果想直接刪除 binlog
rm binlog.000002
(不要刪除當前正在使用的 binlog,也就是編號最大的 binlog)
[root@namenode mysql3306]# cat mysql-bin.index 不會自動更新
/home/mysql3306/mysql3306/mysql-bin.000001
/home/mysql3306/mysql3306/mysql-bin.000002
主從復制如果刪掉了,容易從庫報錯(保證從庫不再同步該 binlog 的數據)
7.binlog 和 redo 的區別
– redo 物理邏輯日志 物理:數據頁 邏輯:數據頁的改變
binlog 是邏輯日志 記錄的 SQL 或者是行改變
– redo 是 innodb 存儲引擎層
binlog 是 server 層
– redo 時時刻刻都在寫入文件
binlog 是 提交事務的寫入(具體寫入策略跟具體參數有關)
到此,相信大家對“mysql binlog 相關知識點有哪些”有了更深的了解,不妨來實際操作一番吧!這里是丸趣 TV 網站,更多相關內容可以進入相關頻道進行查詢,關注我們,繼續學習!