關(guān)于MySQL binlog分析

一、區(qū)別redolog和binlog

1、如下表格的一個(gè)簡(jiǎn)單對(duì)比

2、redolog記錄的是對(duì)于每個(gè)頁的修改

數(shù)據(jù)頁地址、行地址、操作類型(I/D)、數(shù)據(jù)

e.g：一個(gè)update修改100行,至少產(chǎn)生200行redo日志,所以DML產(chǎn)生的redo可能很大：

100、2、D

100、2、I、'value'

……

實(shí)際就是update table set ……100(數(shù)據(jù)頁地址) 2(行地址)

3、binlog只是記錄DML、DDL、DCL,不記錄SELECT

通過參數(shù)設(shè)置控制binlog的記錄模式：

1、語句模式

e.g：delete from t1 where id<=1000;

2、行模式

delete from t1 where id=1;

delete from t1 where id=2;

……

delete from t1 where id=1000;

4、圖解redolog、binlog機(jī)制

注意：

　　在oracle里面雖然redolog也是循環(huán)覆蓋的,但是在循環(huán)覆蓋之前,數(shù)據(jù)庫(kù)會(huì)將redo拷貝出來做個(gè)歸檔,所以oracle里可以用redolog做數(shù)據(jù)恢復(fù).

二、開啟binlog及相關(guān)關(guān)注點(diǎn)

1、開啟binlog

修改配置文件/etc/my.cnf,在[mysqld]下添加：

重啟MySQL,即可……binlog的啟動(dòng)大概會(huì)為mysql增加1%的負(fù)載,因此在絕大多數(shù)情況下,binlog都不會(huì)成為mysql的性能瓶頸,所以一般都是會(huì)開啟binlog的.

2、binlog的存放

3、如何手工切換binlog

　　1、重啟數(shù)據(jù)庫(kù),每次重啟都會(huì)新切binlog

　　2、mysql> flush logs;

4、查看binlog日志文件

三、關(guān)注binlog相關(guān)參數(shù)

　　mysql> show variables like '%bin%';

1、binlog_cache_size　　

　　//設(shè)置binlog cache(默認(rèn)32K),每個(gè)線程單獨(dú)分配內(nèi)存空間

　　所有未提交的二進(jìn)制日志文件會(huì)被記錄到用戶工作空間的binlog cache中,等該事務(wù)提交時(shí)直接將緩沖區(qū)中的binlog寫入二進(jìn)制日志文件里

判斷binlog_cache_size是否設(shè)置過小的依據(jù),如果Binlog_cache_disk_use>0(次數(shù)),說明事務(wù)未提交,binlog在用戶工作空間存放不下,需要借用tmp目錄.

2、log_bin

　　//設(shè)置名字前綴

　　--log-bin [=file_name]：設(shè)置此參數(shù)表示啟用binlog功能,并指定路徑名稱,生產(chǎn)中都要開啟binlog.

　　sql_log_bin：會(huì)話級(jí)別的binlog開關(guān)控制,默認(rèn)是開啟的,可以在當(dāng)前會(huì)話級(jí)別動(dòng)態(tài)修改臨時(shí)關(guān)閉binlog(主從延遲優(yōu)化),set session sql_log_bin=0；

3、sync_binlog

　　//同步binlog的方式

　　0：默認(rèn),提交同步到文件系統(tǒng)緩存

　　1：commit,通過fsync方式,直接寫入disk的binlog文件中(最平安),與redo的雙一模式.

　　>1：sync_binlog=N,如果N>1,在意外發(fā)生的時(shí)候,就表示會(huì)有N-1個(gè)dml沒有被寫入binlog中,有可能就會(huì)發(fā)生主從數(shù)據(jù)不一致的情況.

4、max_binlog_size

　　//binlog文件大小,默認(rèn)1G

　　如果是row模式,需要增加binlog文件的大小,因?yàn)樾心Ｊ疆a(chǎn)生的日志量相對(duì)較大.如果超過了該值,就會(huì)產(chǎn)生新的日志文件,后綴名+1,并且記錄到.index文件里面.

5、binlog_format

　　//row、statement、mixed,設(shè)置binlog記錄的模式：行模式、語句模式、mixed模式.動(dòng)態(tài)參數(shù),可以會(huì)話級(jí)別修改

6、--binlog-do-db、--binlog-ingore-db

　　//command-line format,表示需要寫入或者忽略寫入哪些庫(kù)的日志,默認(rèn)為空,表示可以將所有庫(kù)的日志寫入到二進(jìn)制文件里面.

7、log-slave-updates

　　//啟用從機(jī)服務(wù)器上的slave日志功能,使這臺(tái)計(jì)算機(jī)可以用來構(gòu)成一個(gè)鏡像鏈(A->B->C) ,可以讓從庫(kù)上面產(chǎn)生二進(jìn)制日志文件,在從庫(kù)上再掛載一個(gè)從庫(kù).

8、binlog_rows_query_log_events

　　//便于定位執(zhí)行過的SQL語句

9、expire_logs_days

　　//binlog過期清理時(shí)間,默認(rèn)是0：不自動(dòng)清除

　　binlog的刪除策略,假設(shè)expire_logs_days=5,表示系統(tǒng)保留5天binlog,第六天到來時(shí)會(huì)刪除第一天的binlog.

　　1、刪除策略的風(fēng)險(xiǎn)：

　　　　1、刪除會(huì)導(dǎo)致過高的io,從而導(dǎo)致業(yè)務(wù)出現(xiàn)性能抖動(dòng)

　　　　2、主從延遲

　　2、解決：手工主動(dòng)刪除binlog

　　　　PURGE BINARY LOGS　　#同時(shí)刪除binlog文件和binlog索引文件記錄,如此說來用rm刪除binlog和vim修改對(duì)應(yīng)binlog索引文件記錄,效果同purge.

四、binlog_format有哪些

1、STATEMENT

每一條會(huì)修改數(shù)據(jù)的sql都會(huì)記錄在binlog中.

優(yōu)點(diǎn)：不需要記錄每一行的變化,減少了binlog日志量,節(jié)約了IO,提高性能.只需要記錄在 master 上所執(zhí)行的語句的細(xì)節(jié),以及執(zhí)行語句時(shí)候的上下文的信息.

缺點(diǎn)：由于記錄的只是執(zhí)行語句,為了這些語句能在slave上正確運(yùn)行,因此還必須記錄每條語句在執(zhí)行的時(shí)候的一些相關(guān)信息,以保證所有語句能在slave得到和在master端執(zhí)行時(shí)候相同的結(jié)果.像一些特定函數(shù)功能,slave可與master上要保持一致會(huì)有很多相關(guān)問題(如sleep()函數(shù),rand()函數(shù)等會(huì)出現(xiàn)問題warning).

2、ROW

不記錄sql語句上下文相關(guān)信息,僅保存哪條記錄被修改,也就是說日志中會(huì)記錄成每一行數(shù)據(jù)被修改的形式,然后在 slave 端再對(duì)相同的數(shù)據(jù)進(jìn)行修改.

優(yōu)點(diǎn)：binlog中可以不記錄執(zhí)行的sql語句的上下文相關(guān)的信息,僅需要記錄那一條記錄被修改成什么了.所以rowlevel的日志內(nèi)容會(huì)非常清楚的記錄下每一行數(shù)據(jù)修改的細(xì)節(jié).而且不會(huì)出現(xiàn)某些特定情況下的存儲(chǔ)過程,或function,以及trigger的調(diào)用和觸發(fā)無法被正確復(fù)制的問題.

缺點(diǎn)：在 row 模式下,所有的執(zhí)行的語句當(dāng)記錄到日志中的時(shí)候,都將以每行記錄的修改來記錄,這樣可能會(huì)產(chǎn)生大量的日志內(nèi)容.

3、MIXED

是以上兩種level的混合使用,一般的語句修改使用statment格式保存binlog,如一些函數(shù),statement無法完成主從復(fù)制的操作,則采用row格式保存binlog,MySQL會(huì)根據(jù)執(zhí)行的每一條具體的sql語句來區(qū)分對(duì)待記錄的日志形式,也就是在Statement和Row之間選擇一種;

新版本的MySQL中對(duì)row模式也被做了優(yōu)化,并不是所有的修改都會(huì)以rowl來記錄,像遇到表結(jié)構(gòu)變更的時(shí)候就會(huì)以statement模式來記錄.至于update或者delete等修改數(shù)據(jù)的語句,還是會(huì)記錄所有行的變更.

五、binlog的使用

1、二進(jìn)制日志兩個(gè)最重要的使用場(chǎng)景

1)、MySQL replication在master端開啟binlog,master把它的二進(jìn)制日志傳遞給slaves來達(dá)到master-slave數(shù)據(jù)一致的目的,也就是主從備份.

2)、數(shù)據(jù)恢復(fù),通過使用mysqlbinlog工具來使恢復(fù)數(shù)據(jù)

2、常用binlog日志操作命令

1)、查看所有binlog日志列表

mysql> show master logs;

2)、查看master狀態(tài),即最后(最新)一個(gè)binlog日志的編號(hào)名稱,及其最后一個(gè)操作事件pos結(jié)束點(diǎn)(Position)值

mysql> show master status;　　//結(jié)合上述binlog文件,進(jìn)行binlog生成速度監(jiān)控

3)、刷新log日志,自此刻開始產(chǎn)生一個(gè)新編號(hào)的binlog日志文件

mysql> flush logs;　　//注：每當(dāng)mysqld服務(wù)重啟時(shí),會(huì)自動(dòng)執(zhí)行此命令,刷新binlog日志;在mysqldump備份數(shù)據(jù)時(shí)加 -F 選項(xiàng)也會(huì)刷新binlog日志;

4)、重置(清空)所有binlog日志

mysql> reset master;

3、查看binlog日志內(nèi)容

1)、OS層面查看binlog文件

shell> mysqlbinlog -vv mysql-bin.000001

2)、數(shù)據(jù)庫(kù)層面show binlog events

mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

選項(xiàng)解析：

1)、in 'log_name'：指定要查詢的binlog文件名(不指定就是第一個(gè)binlog文件)

2)、from pos：指定從哪個(gè)pos起始點(diǎn)開始查起(不指定就是從整個(gè)文件首個(gè)pos點(diǎn)開始算)

3)、limit [offset,]：偏移量(不指定就是0)

4)、row_count：查詢總條數(shù)(不指定就是所有行)

4、從binlog日志恢復(fù)語法

1)、恢復(fù)語法格式：

# mysqlbinlog [選項(xiàng)] mysql-bin.0000xx | mysql -u用戶名 -p密碼 數(shù)據(jù)庫(kù)名

2)、常用選項(xiàng)：

--start-position=953 起始pos點(diǎn)

--stop-position=1437 結(jié)束pos點(diǎn)

--start-datetime="2017-6-8 13:18:54" 起始時(shí)間點(diǎn)

--stop-datetime="2017-6-8 13:21:53" 結(jié)束時(shí)間點(diǎn)

--database=TEST 指定只恢復(fù)TEST數(shù)據(jù)庫(kù)(一臺(tái)主機(jī)上往往有多個(gè)數(shù)據(jù)庫(kù),只限本地log日志)

3)、不常用選項(xiàng)：

-u --user=name #Connect to the remote server as username.連接到遠(yuǎn)程主機(jī)的用戶名

-p --password[=name] #Password to connect to remote server.連接到遠(yuǎn)程主機(jī)的密碼

-h --host=name #Get the binlog from server.從遠(yuǎn)程主機(jī)上獲取binlog日志

--read-from-remote-server #Read binary logs from a MySQL server.從某個(gè)MySQL服務(wù)器上讀取binlog日志

使用小結(jié)：

實(shí)際是將讀出的binlog日志內(nèi)容,通過管道符傳遞給mysql命令.這些命令、文件盡量寫成絕對(duì)路徑;所謂恢復(fù),就是讓MySQL將保存在binlog日志中指定段落區(qū)間的SQL語句逐個(gè)重新執(zhí)行一次而已.

關(guān)于雙一模式：

　　1、redolog、binlog：雙1(絕對(duì)平安)

　　　　1、innodb_flush_log_at_trx_commit=1

　　　　2、sync_binlog=1

　　2、innodb_support_xa=1：分布式事務(wù),默認(rèn)開啟ON

　　　　1、保證binlog里面存在的事務(wù)一定在redo log里面存在

　　　　2、保證binlog里面事務(wù)順序與redo log事務(wù)順序一致性

　　　　3、commit,要么成功要么失敗,防止出現(xiàn)主從不一致

　　所以在雙一的情況下,也要配合開啟innodb_support_xa,更平安.