mysqlbinlog相關(guān)知識點(diǎn)有哪些
本篇內(nèi)容主要講解“MySQL binlog相關(guān)知識點(diǎn)有哪些”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實(shí)用性強(qiáng)。下面就讓小編來帶大家學(xué)習(xí)“mysql binlog相關(guān)知識點(diǎn)有哪些”吧!
堅(jiān)守“ 做人真誠 · 做事靠譜 · 口碑至上 · 高效敬業(yè) ”的價值觀，專業(yè)網(wǎng)站建設(shè)服務(wù)10余年為成都會所設(shè)計(jì)小微創(chuàng)業(yè)公司專業(yè)提供企業(yè)網(wǎng)站設(shè)計(jì)營銷網(wǎng)站建設(shè)商城網(wǎng)站建設(shè)手機(jī)網(wǎng)站建設(shè)小程序網(wǎng)站建設(shè)網(wǎng)站改版，從內(nèi)容策劃、視覺設(shè)計(jì)、底層架構(gòu)、網(wǎng)頁布局、功能開發(fā)迭代于一體的高端網(wǎng)站建設(shè)服務(wù)。
1.
- 邏輯日志,記錄的是數(shù)據(jù)庫內(nèi)部的所有變動（sql語句 行的改變）
- server層日志，binlog不僅僅記錄innodb的變動，也記錄myisam存儲引擎的變動。
- innodb redo 是存儲引擎層，和binlog不是一層，redo只記錄innodb的變化。
- binlog不記錄 select show 等查詢語句,記錄dml ddl等數(shù)據(jù)庫變動的語句.
2.binlog的作用
- 可以查看mysql變更
- mysql的復(fù)制架構(gòu)（主從）
- mysql備份恢復(fù)（可以通過binlog進(jìn)行數(shù)據(jù)的補(bǔ)齊或者回滾）
3.binlog的位置
log_bin = /home/mysql3306/mysql3306/mysql-bin
#binlog路徑+binlog前綴名 如：mysql-bin.000001
4.相關(guān)參數(shù)簡介
(1).log-bin
決定了msyql 的binlog的名字，生成的binlog名字為mysql-bin.000001
(2).binlog_format
規(guī)定binlog的格式，binlog有三種格式statement，row以及mixed，默認(rèn)使用默認(rèn)使用statement，建議使用row格式
(3).expire_logs_days
過期時間 建議大家根據(jù)業(yè)務(wù)設(shè)置7D-30D
(4).binlog_do_db
此參數(shù)表示只記錄指定數(shù)據(jù)庫的二進(jìn)制日志
(5).binlog_ignore_db
此參數(shù)表示不記錄指定的數(shù)據(jù)庫的二進(jìn)制日志
(6).sync_binlog(binlog落盤策略)
0---每一秒刷一次磁盤
1---每次事務(wù)提交都刷一次磁盤
n---（100 200 500）每n次提交落盤一次
innodb_flush_log_at_trx_commit =1
sync_binlog=1
雙1配置，數(shù)據(jù)庫的安全性是最高的，不會丟事務(wù)。
(7).binlog_checksum 為 {CRC32|NONE}
寫binlog時，會將內(nèi)容生成校驗(yàn)位，之后存儲在binlog中。
默認(rèn)情況下，服務(wù)器記錄事件的長度以及事件本身，并使用它來驗(yàn)證事件是否正確寫入。 也可以通過設(shè)置 binlog_checksum系統(tǒng)變量來使服務(wù)器為事件寫入校驗(yàn)和。
(8).max_binlog_size
binlog文件的最大值，默認(rèn)和最大是1GB，并不能嚴(yán)格限定二進(jìn)制文件的大小
(9).max_binlog_cache_size
表示的是binlog 能夠使用的最大cache 內(nèi)存大小
當(dāng)我們執(zhí)行多語句事務(wù)的時候 所有session的使用的內(nèi)存超過max_binlog_cache_size的值時
就會報錯：“Multi-statement transaction required more than 'max_binlog_cache_size' bytes ofstorage”
(10).binlog_cache_size = 4M
線程級參數(shù)，不能設(shè)置太大
5.binlog格式
(1).statement
mysql> show variables like '%binlog_format%';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
執(zhí)行語句:
insert into wwj.t1 values(2,'mxt2');
insert into wwj.t1 values(3,'mxt3');
insert into wwj.t1 values(4,'mxt4');
查看binlog
點(diǎn)擊(此處)折疊或打開
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點(diǎn)，寫入binlog的日志的字節(jié)數(shù)，就是pos的大小，在每次新生成binlog的時候，pos點(diǎn)會重置，但是在一個binlog內(nèi)部，這個pos是單調(diào)遞增的。
xid：分布式事務(wù)ID，Innodb支持分布式事務(wù)，Innodb支持的單實(shí)例內(nèi)部的，server層和存儲引擎層的分布式事務(wù)，還有多節(jié)點(diǎn)分布式事務(wù)（ndb）
Server_id：實(shí)例server_id
解析binlog工具
/usr/local/mysql/bin/mysqlbinlog mysql-bin.000001 > /tmp/all.bin
點(diǎn)擊(此處)折疊或打開
# 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
查看當(dāng)前的binlog pos點(diǎn)的位置
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 | | | |
+------------------+----------+--------------+------------------+-------------------+
應(yīng)用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
應(yīng)用binlog需要注意的事項(xiàng)
如果應(yīng)用多個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的行的改變前后的數(shù)據(jù)
比如在stament格式中，update數(shù)據(jù)，記錄的是update語句
而且row格式中，記錄的行的改變的前后，如果update1000數(shù)據(jù)，記錄一千行的數(shù)據(jù)改變
mysqlbinlog -v mysql-bin.000001 > /tmp/all.bin 增加偽sql
mysqlbinlog -vv mysql-bin.000001 > /tmp/all.bin 增加數(shù)據(jù)類型
點(diǎn)擊(此處)折疊或打開
# 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
其他解析方法
解析不帶行數(shù)據(jù)
/usr/local/mysql/bin/mysqlbinlog --base64-output=DECODE-ROWS -vv mysql-bin.000001
如果binlog解析出來需要恢復(fù)到數(shù)據(jù)庫，那么需要注意不要加--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格式優(yōu)點(diǎn)：
-記錄行改變，而不是SQL，能最大限度保證數(shù)據(jù)安全性
-row格式可以用于閃回（恢復(fù)數(shù)據(jù)）
在線上的環(huán)境中，推薦使用row格式的binlog
-row格式update delete會對磁盤 網(wǎng)絡(luò)造成一個比較大的壓力
(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
(不要刪除當(dāng)前正在使用的binlog，也就是編號最大的binlog)
[root@namenode mysql3306]# cat mysql-bin.index 不會自動更新
/home/mysql3306/mysql3306/mysql-bin.000001
/home/mysql3306/mysql3306/mysql-bin.000002
主從復(fù)制如果刪掉了，容易從庫報錯（保證從庫不再同步該binlog的數(shù)據(jù)）
7.binlog和redo的區(qū)別
- redo 物理邏輯日志 物理：數(shù)據(jù)頁 邏輯： 數(shù)據(jù)頁的改變
binlog 是邏輯日志 記錄的SQL或者是行改變
- redo是innodb存儲引擎層
binlog是server層
- redo時時刻刻都在寫入文件
binlog是 提交事務(wù)的寫入（具體寫入策略跟具體參數(shù)有關(guān)）
到此，相信大家對“mysql binlog相關(guān)知識點(diǎn)有哪些”有了更深的了解，不妨來實(shí)際操作一番吧！這里是創(chuàng)新互聯(lián)網(wǎng)站，更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢，關(guān)注我們，繼續(xù)學(xué)習(xí)！
網(wǎng)站標(biāo)題：mysqlbinlog相關(guān)知識點(diǎn)有哪些
當(dāng)前路徑：http://www.chinadenli.net/article34/pejsse.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供定制網(wǎng)站、營銷型網(wǎng)站建設(shè)、ChatGPT、關(guān)鍵詞優(yōu)化、網(wǎng)站設(shè)計(jì)、Google
聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)
- 服務(wù)器托管時怎么判斷是否死機(jī) 2022-10-03
- 網(wǎng)站服務(wù)器托管價格有哪些影響因素？ 2022-10-02
- 服務(wù)器托管與虛擬主機(jī)的區(qū)別—深圳網(wǎng)站設(shè)計(jì) 2022-06-06
- 艾克賽樂服務(wù)器托管怎么樣？ 2021-03-10
- 服務(wù)器環(huán)境配置 2022-05-25
- 服務(wù)器托管注意事項(xiàng) 2021-01-08
- 提供濟(jì)南服務(wù)器托管，創(chuàng)新互聯(lián)靠什么來實(shí)現(xiàn)？ 2021-03-10
- 服務(wù)器托管可以降低運(yùn)行成本 2016-10-28
- 服務(wù)器托管與虛擬主機(jī)有什么不同？ 2022-10-07
- 服務(wù)器托管帶寬選擇有什么依據(jù)？ 2022-10-08
- 為何廣州服務(wù)器托管/租用選擇創(chuàng)新互聯(lián) 2021-03-11
- 租用香港服務(wù)器托管能滿足哪些建站的要求呢 2021-01-26