《MYSQL數(shù)據(jù)庫Mysql數(shù)據(jù)庫之Binlog日志使用總結(jié)(必看篇)》要點(diǎn)：
本文介紹了MYSQL數(shù)據(jù)庫Mysql數(shù)據(jù)庫之Binlog日志使用總結(jié)(必看篇)，希望對(duì)您有用。如果有疑問，可以聯(lián)系我們。

MYSQL必讀binlog二進(jìn)制日志對(duì)于mysql數(shù)據(jù)庫的重要性有多大,在此就不多說了.下面根據(jù)本人的日常操作經(jīng)歷,并結(jié)合網(wǎng)上參考資料,對(duì)binlog日志使用做一梳理：

MYSQL必讀一、binlog日志介紹

MYSQL必讀1)什么是binlog
binlog日志用于記錄所有更新了數(shù)據(jù)或者已經(jīng)潛在更新了數(shù)據(jù)(例如,沒有匹配任何行的一個(gè)DELETE)的所有語句.語句以“事件”的形式保留,它描述數(shù)據(jù)更改.

MYSQL必讀2)binlog作用
因?yàn)橛辛藬?shù)據(jù)更新的binlog,所以可以用于實(shí)時(shí)備份,與master/slave主從復(fù)制結(jié)合.

MYSQL必讀3)和binlog有關(guān)參數(shù)
log_bin

MYSQL必讀設(shè)置此參數(shù)表示啟用binlog功能,并指定路徑名稱

MYSQL必讀log_bin_index

MYSQL必讀設(shè)置此參數(shù)是指定二進(jìn)制索引文件的路徑與名稱

MYSQL必讀binlog_do_db

MYSQL必讀此參數(shù)表示只記錄指定數(shù)據(jù)庫的二進(jìn)制日志

MYSQL必讀binlog_ignore_db
此參數(shù)表示不記錄指定的數(shù)據(jù)庫的二進(jìn)制日志

MYSQL必讀max_binlog_cache_size

MYSQL必讀此參數(shù)表示binlog使用的內(nèi)存最大的尺寸

MYSQL必讀binlog_cache_size

MYSQL必讀此參數(shù)表示binlog使用的內(nèi)存大小,可以通過狀態(tài)變量binlog_cache_use和binlog_cache_disk_use來贊助測(cè)試.

MYSQL必讀binlog_cache_use：使用二進(jìn)制日志緩存的事務(wù)數(shù)量

MYSQL必讀binlog_cache_disk_use:使用二進(jìn)制日志緩存但超過binlog_cache_size值并使用臨時(shí)文件來保留事務(wù)中的語句的事務(wù)數(shù)量

MYSQL必讀max_binlog_size

MYSQL必讀Binlog最大值,最大和默認(rèn)值是1GB,該設(shè)置并不能嚴(yán)格控制Binlog的大小,尤其是Binlog比擬靠近最大值而又遇到一個(gè)比擬大事務(wù)時(shí),為了保證事務(wù)的完整性,不可能做切換日志的動(dòng)作,只能將該事務(wù)的所有SQL都記錄進(jìn)當(dāng)前日志,直到事務(wù)結(jié)束

MYSQL必讀sync_binlog

MYSQL必讀這個(gè)參數(shù)直接影響mysql的性能和完整性

MYSQL必讀sync_binlog=0

MYSQL必讀當(dāng)事務(wù)提交后,Mysql僅僅是將binlog_cache中的數(shù)據(jù)寫入Binlog文件,但不執(zhí)行fsync之類的磁盤 同步指令通知文件系統(tǒng)將緩存刷新到磁盤,而讓Filesystem自行決定什么時(shí)候來做同步,這個(gè)是性能最好的.

MYSQL必讀sync_binlog=n,在進(jìn)行n次事務(wù)提交以后,Mysql將執(zhí)行一次fsync之類的磁盤同步指令,同志文件系統(tǒng)將Binlog文件緩存刷新到磁盤.

MYSQL必讀Mysql中默認(rèn)的設(shè)置是sync_binlog=0,即不作任何強(qiáng)制性的磁盤刷新指令,這時(shí)性能是最好的,但風(fēng)險(xiǎn)也是最大的.一旦系統(tǒng)繃Crash,在文件系統(tǒng)緩存中的所有Binlog信息都會(huì)丟失

MYSQL必讀4)binlog的刪除

MYSQL必讀binlog的刪除可以手工刪除或自動(dòng)刪除：

MYSQL必讀a)自動(dòng)刪除binlog

MYSQL必讀通過binlog參數(shù)(expire_logs_days )來實(shí)現(xiàn)mysql自動(dòng)刪除binlog

MYSQL必讀mysql> show binary logs;
mysql> show variables like 'expire_logs_days'; ? ? ?//該參數(shù)表示binlog日志自動(dòng)刪除/過期的天數(shù),默認(rèn)值為0,表示不自動(dòng)刪除
mysql> set global expire_logs_days=3; ? ? ? ?//表示日志保存3天,3天后就自動(dòng)過期.

MYSQL必讀b)手工刪除binlog

MYSQL必讀mysql> reset master; ? ? ? ?//刪除master的binlog,即手動(dòng)刪除所有的binlog日志
mysql> reset slave; ? ? ? ? ?//刪除slave的中繼日志
mysql> purge master logs before '2012-03-30 17:20:00'; ? ? ? ? //刪除指定日期以前的日志索引中binlog日志文件
mysql> purge master logs to 'binlog.000002'; ? ? ? //刪除指定日志文件的日志索引中binlog日志文件

MYSQL必讀mysql> set sql_log_bin=1/0; ? ? ? //如果用戶有super權(quán)限,可以啟用或禁用當(dāng)前會(huì)話的binlog記錄
mysql> show master logs; ? ? ? ? ?//查看master的binlog日志列表
mysql> show binary logs; ? ? ? ? ? //查看master的binlog日志文件大小
mysql> show master status; ? ? //用于提供master二進(jìn)制日志文件的狀態(tài)信息
mysql> show slave hosts; ? ? ? ?//顯示當(dāng)前注冊(cè)的slave的列表.不以--report-host=slave_name選項(xiàng)為開頭的slave不會(huì)顯示在本列表中

MYSQL必讀mysql> flush logs; ? ? //產(chǎn)生一個(gè)新的binlog日志文件

MYSQL必讀?

MYSQL必讀mysql binlog日志自動(dòng)清理及手動(dòng)刪除案例說明：

MYSQL必讀
當(dāng)開啟MySQL數(shù)據(jù)庫主從時(shí),會(huì)產(chǎn)生大量如mysql-bin.00000* log的文件,這會(huì)大量耗費(fèi)您的硬盤空間. 
mysql-bin.000001 
mysql-bin.000002 
mysql-bin.000003 
mysql-bin.000004 
mysql-bin.000005 
… 
 
刪除這些binlog日志有三種解決辦法： 
1.關(guān)閉mysql主從,關(guān)閉binlog； 
實(shí)例操作如下： 
[root@huqniupc ~]# vim /etc/my.cnf  //注釋掉log-bin和binlog_format 
# Replication Master Server (default) 
# binary logging is required for replication 
# log-bin=mysql-bin 
# binary logging format - mixed recommended 
# binlog_format=mixed 
然后重啟數(shù)據(jù)庫 
 
2.開啟mysql主從,設(shè)置expire_logs_days； 
實(shí)例操作如下： 
[root@huqniupc ~]# vim /etc/my.cnf //修改expire_logs_days,x是自動(dòng)刪除的天數(shù),一般將x設(shè)置為短點(diǎn),如10 
expire_logs_days = x //二進(jìn)制日志自動(dòng)刪除的天數(shù).默認(rèn)值為0,表示“沒有自動(dòng)刪除” 
此辦法需要重啟mysql 
 
當(dāng)然也可以不重啟mysql,開啟mysql主從,直接在mysql里設(shè)置expire_logs_days 
> show binary logs; 
> show variables like '%log%'; 
> set global expire_logs_days = 10; 
 
 
3.手動(dòng)清除binlog文件,(比如Mysql> PURGE MASTER LOGS TO ‘MySQL-bin.010′;) 
實(shí)例操作如下： 
[root@huqniupc ~]# /usr/local/mysql/bin/mysql -u root -p 
> PURGE MASTER LOGS BEFORE DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY);  //刪除10天前的MySQL binlog日志,附錄2有關(guān)于PURGE MASTER LOGS手動(dòng)刪除用法及示例 
> show master logs; 
  
也可以重置master,刪除所有binlog文件： 
# /usr/local/mysql/bin/mysql -u root -p 
> reset master; //附錄3有清除binlog時(shí),對(duì)從mysql的影響說明 
  
--------------------------------------------------------------- 
PURGE MASTER LOGS手動(dòng)刪除用法及示例,MASTER和BINARY是同義詞 
> PURGE {MASTER | BINARY} LOGS TO 'log_name'
> PURGE {MASTER | BINARY} LOGS BEFORE 'date'
刪除指定的日志或日期之前的日志索引中的所有二進(jìn)制日志.這些日志也會(huì)從記錄在日志索引文件中的清單中被刪除MySQL BIN-LOG 日志,這樣被給定的日志成為第一個(gè). 
 
實(shí)例： 
> PURGE MASTER LOGS TO 'MySQL-bin.010'; //清除MySQL-bin.010日志 
> PURGE MASTER LOGS BEFORE '2008-06-22 13:00:00';  //清除2008-06-22 13:00:00前binlog日志 
> PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 3 DAY); //清除3天前binlog日志BEFORE,變量的date自變量可以為'YYYY-MM-DD hh:mm:ss'格式. 
-----------------------------------------------------

MYSQL必讀5)清除binlog時(shí),對(duì)從mysql的影響

MYSQL必讀如果有一個(gè)活躍的slave從屬服務(wù)器,該服務(wù)器當(dāng)前正在讀取您正在試圖刪除的日志之一,則本語句不會(huì)起作用,而是會(huì)失敗,并伴隨一個(gè)錯(cuò)誤；不過如果slave從屬服務(wù)器是關(guān)閉的(或master-slave主從關(guān)系關(guān)閉),并且碰巧清理了其想要讀取的日志之一,則slave從屬服務(wù)器啟動(dòng)后不能復(fù)制；當(dāng)從屬服務(wù)器正在復(fù)制時(shí),本語句可以平安運(yùn)行,不需要停止它們.

MYSQL必讀6)binglog的查看

MYSQL必讀通過mysqlbinlog命令可以查看binlog的內(nèi)容

MYSQL必讀[root@localhost ~]# mysqlbinlog /home/mysql/binlog/binlog.000003 | more
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#120330 16:51:46 server id 1 end_log_pos 98 Start: binlog v 4, server v 5.0.45-log created 120330 1
6:51:46
# Warning: this binlog was not closed properly. Most probably mysqld crashed writing it.
# at 196
#120330 17:54:15 server id 1 end_log_pos 294 Query thread_id=3 exec_time=2 error_code=0
SET TIMESTAMP=1333101255/*!*/;
insert into tt7 select * from tt7/*!*/;
# at 294
#120330 17:54:46 server id 1 end_log_pos 388 Query thread_id=3 exec_time=28 error_code=0
SET TIMESTAMP=1333101286/*!*/;
alter table tt7 engine=innodb/*!*/;

MYSQL必讀解析binlog格式：

MYSQL必讀位置

MYSQL必讀位于文件中的位置,“at 196”說明“事件”的起點(diǎn),是以第196字節(jié)開始；“end_log_pos 294”說明以第294字節(jié)結(jié)束

MYSQL必讀時(shí)間戳

MYSQL必讀事件發(fā)生的時(shí)間戳：“120330 17:54:46”

MYSQL必讀事件執(zhí)行時(shí)間

MYSQL必讀事件執(zhí)行花費(fèi)的時(shí)間:"exec_time=28"

MYSQL必讀錯(cuò)誤碼

MYSQL必讀錯(cuò)誤碼為：“error_code=0”

MYSQL必讀服務(wù)器的標(biāo)識(shí)

MYSQL必讀服務(wù)器的標(biāo)識(shí)id：“server id 1”

MYSQL必讀注意下面幾點(diǎn)：

MYSQL必讀1.mysql的日志切不可想象是可以恢復(fù)到任何時(shí)間的狀態(tài),這個(gè)恢復(fù)是有前提的!
至少得有一個(gè)從日志記錄開始后的數(shù)據(jù)庫備份,通過日志恢復(fù)數(shù)據(jù)庫實(shí)際上只是一個(gè)對(duì)以前操作的回放過程罷了,不用想得太復(fù)雜.

MYSQL必讀既然是回放操作,那么就得注意了,如果是執(zhí)行了兩次恢復(fù)那就相當(dāng)于是回放了兩次,后果可想而知.

MYSQL必讀所以：

MYSQL必讀1)恢復(fù)前務(wù)必先備份數(shù)據(jù).

MYSQL必讀2)由于二進(jìn)制文件多,并且必要恢復(fù)的數(shù)據(jù)跨度大,可以考慮將日志文件合并在恢復(fù).

MYSQL必讀2. 開啟binlog日志功能

MYSQL必讀要想通過日志恢復(fù)數(shù)據(jù)庫,必需首先在my.cnf文件里定義,log-bin=mysql-bin,這樣產(chǎn)生的binlog日志名就是以mysql-bin命名的

MYSQL必讀3.什么時(shí)候會(huì)生成新的binlog文件

MYSQL必讀1)在備份的時(shí)候加入--flush-logs

MYSQL必讀2)重新啟動(dòng)mysql服務(wù)的時(shí)候

MYSQL必讀特別提示,mysql每次啟動(dòng)都會(huì)重新生成一個(gè)類似mysql-bin.00000n的文件,如果你的mysql每天都要重新啟動(dòng)一次的話,這時(shí)候你就要特別注意不要選錯(cuò)日志文件了.

MYSQL必讀二、binlog日志格式介紹?

MYSQL必讀(1)Mysql binlog日志有三種格式,分別是Statement、MiXED、ROW

MYSQL必讀1)Statement：每一條會(huì)修改數(shù)據(jù)的sql都會(huì)記錄在binlog中

MYSQL必讀優(yōu)點(diǎn)：不必要記錄每一行的變化,減少了binlog日志量,節(jié)約了IO,提高性能.(相比row能節(jié)約多少性能與日志量,這個(gè)取決于應(yīng)用的SQL情況,正常同一條記錄修改或者插入row格式所產(chǎn)生的日志量還小于Statement產(chǎn)生的日志量,但是考慮到如果帶條件的update操作,以及整表刪除,alter表等操作,ROW格式會(huì)產(chǎn)生大量日志,因此在考慮是否使用ROW格式日志時(shí)應(yīng)該跟據(jù)應(yīng)用的實(shí)際情況,其所產(chǎn)生的日志量會(huì)增加多少,以及帶來的IO性能問題.)

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

MYSQL必讀使用以下函數(shù)的語句也無法被復(fù)制：

MYSQL必讀* LOAD_FILE()
* UUID()
* USER()
* FOUND_ROWS()
* SYSDATE() (除非啟動(dòng)時(shí)啟用了 --sysdate-is-now 選項(xiàng))

MYSQL必讀同時(shí)在INSERT ...SELECT 會(huì)產(chǎn)生比 RBR 更多的行級(jí)鎖

MYSQL必讀2)Row:不記錄sql語句上下文相關(guān)信息,僅保留哪條記錄被修改

MYSQL必讀優(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ù)制的問題

MYSQL必讀缺點(diǎn):所有的執(zhí)行的語句當(dāng)記錄到日志中的時(shí)候,都將以每行記錄的修改來記錄,這樣可能會(huì)產(chǎn)生大量的日志內(nèi)容,好比一條update語句,修改多條記錄,則binlog中每一條修改都會(huì)有記錄,這樣造成binlog日志量會(huì)很大,特別是當(dāng)執(zhí)行alter table之類的語句的時(shí)候,由于表結(jié)構(gòu)修改,每條記錄都發(fā)生改變,那么該表每一條記錄都會(huì)記錄到日志中.

MYSQL必讀3)Mixedlevel: 是以上兩種level的混合使用,一般的語句修改使用statment格式保留binlog,如一些函數(shù),statement無法完成主從復(fù)制的操作,則采用row格式保留binlog,MySQL會(huì)根據(jù)執(zhí)行的每一條具體的sql語句來區(qū)分對(duì)待記錄的日志形式,也就是在Statement和Row之間選擇一種.新版本的MySQL中隊(duì)row level模式也被做了優(yōu)化,并不是所有的修改都會(huì)以row level來記錄,像遇到表結(jié)構(gòu)變更的時(shí)候就會(huì)以statement模式來記錄.至于update或者delete等修改數(shù)據(jù)的語句,還是會(huì)記錄所有行的變更.

MYSQL必讀Mixed日志說明：

MYSQL必讀在slave日志同步過程中,對(duì)于使用now這樣的時(shí)間函數(shù),MIXED日志格式,會(huì)在日志中產(chǎn)生對(duì)應(yīng)的unix_timestamp()*1000的時(shí)間字符串,slave在完成同步時(shí),取用的是sqlEvent發(fā)生的時(shí)間來保證數(shù)據(jù)的準(zhǔn)確性.另外對(duì)于一些功能性函數(shù)slave能完成相應(yīng)的數(shù)據(jù)同步,而對(duì)于上面指定的一些類似于UDF函數(shù),導(dǎo)致Slave無法知曉的情況,則會(huì)采用ROW格式存儲(chǔ)這些Binlog,以保證產(chǎn)生的Binlog可以供Slave完成數(shù)據(jù)同步.

MYSQL必讀(2)binlog基本配制與格式設(shè)定

MYSQL必讀1)基本配制

MYSQL必讀binlog日志格式可以通過mysql的my.cnf文件的屬性binlog_format指定.如以下：
binlog_format = MIXED ? ? ? ? ? ? ?//binlog日志格式
log_bin =目錄/mysql-bin.log ? ? ? //binlog日志名
expire_logs_days = 7 ? ? ? ? ? ? ? ? //binlog過期清理時(shí)間
max_binlog_size 100m ? ? ? ? ? ? ?//binlog每個(gè)日志文件大小

MYSQL必讀binlog-do-db=必要備份的數(shù)據(jù)庫名,如果備份多個(gè)數(shù)據(jù)庫,重復(fù)設(shè)置這個(gè)選項(xiàng)即可
binlog-ignore-db=不必要備份的數(shù)據(jù)庫苦命,如果備份多個(gè)數(shù)據(jù)庫,重復(fù)設(shè)置這個(gè)選項(xiàng)即可

MYSQL必讀2)Binlog日志格式選擇

MYSQL必讀Mysql默認(rèn)是使用Statement日志格式,保舉使用MIXED.
由于一些特殊使用,可以考慮使用ROWED,如自己通過binlog日志來同步數(shù)據(jù)的修改,這樣會(huì)節(jié)省很多相關(guān)操作.對(duì)于binlog數(shù)據(jù)處理會(huì)變得非常輕松,相對(duì)mixed,解析也會(huì)很輕松(當(dāng)然前提是增加的日志量所帶來的IO開銷在容忍的范圍內(nèi)即可).

MYSQL必讀3)mysqlbinlog格式選擇

MYSQL必讀mysql對(duì)于日志格式的選定原則:如果是采用 INSERT,UPDATE,DELETE 等直接操作表的情況,則日志格式根據(jù) binlog_format 的設(shè)定而記錄,如果是采用 GRANT,REVOKE,SET PASSWORD 等管理語句來做的話,那么無論如何 都采用 SBR 模式記錄

MYSQL必讀(3)Mysql Binlog日志分析

MYSQL必讀通過MysqlBinlog指令查看具體的mysql日志,如下:

MYSQL必讀
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
SET TIMESTAMP=1350355892/*!*/;
BEGIN
/*!*/;
# at 1643330
#121016 10:51:32 server id 1 end_log_pos 1643885 Query thread_id=272571 exec_time=0 error_code=0
SET TIMESTAMP=1350355892/*!*/;
Insert into T_test….)
/*!*/;
# at 1643885
#121016 10:51:32 server id 1 end_log_pos 1643912 Xid = 0
COMMIT/*!*/;
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

MYSQL必讀1.開始事物的時(shí)間:

MYSQL必讀SET TIMESTAMP=1350355892/*!*/;

MYSQL必讀BEGIN

MYSQL必讀2.sqlevent起點(diǎn)

MYSQL必讀#at 1643330 :為事件的起點(diǎn),是以1643330字節(jié)開始.

MYSQL必讀3.sqlevent 發(fā)生的時(shí)間點(diǎn)

MYSQL必讀#121016 10:51:32:是事件發(fā)生的時(shí)間,

MYSQL必讀4.serverId

MYSQL必讀server id 1 :為master 的serverId

MYSQL必讀5.sqlevent終點(diǎn)及花費(fèi)時(shí)間,錯(cuò)誤碼

MYSQL必讀end_log_pos 1643885:為事件的終點(diǎn),是以1643885 字節(jié)結(jié)束.

MYSQL必讀execTime 0: 花費(fèi)的時(shí)間

MYSQL必讀error_code=0:錯(cuò)誤碼

MYSQL必讀Xid:事件指示提交的XA事務(wù)

MYSQL必讀三、mysql日志(重點(diǎn)binlog日志)的優(yōu)化說明

MYSQL必讀MySQL系統(tǒng)的伸縮性很強(qiáng),既可以在充足的硬件資源環(huán)境下高效運(yùn)行,也可以在極少資源環(huán)境下很好的運(yùn)行,
但不管怎樣,盡可能充足的硬件資源對(duì)MySQL的性能提升總是有贊助的.

MYSQL必讀下面著重分析一下MySQL的日志(主要是Binlog)對(duì)系統(tǒng)性能的影響,并根據(jù)日志的相關(guān)特性得出相應(yīng)的優(yōu)化思路.

MYSQL必讀1)日志產(chǎn)生的性能影響
由于日志的記錄帶來的直接性能損耗便是數(shù)據(jù)庫系統(tǒng)中最為昂貴的IO資源.

MYSQL必讀MySQL的日志主要包含錯(cuò)誤日志(ErrorLog),更新日志(UpdateLog),二進(jìn)制日志(Binlog),查詢?nèi)罩?QueryLog),慢查詢?nèi)罩?SlowQueryLog)等.
特別注意：更新日志是老版本的MySQL才有的,目前已經(jīng)被二進(jìn)制日志替代.

MYSQL必讀在默認(rèn)情況下,系統(tǒng)僅僅打開錯(cuò)誤日志,關(guān)閉了其他所有日志,以達(dá)到盡可能減少IO損耗提高系統(tǒng)性能的目的.
但是在一般稍微重要一點(diǎn)的實(shí)際應(yīng)用場(chǎng)景中,都至少必要打開二進(jìn)制日志,因?yàn)檫@是MySQL很多存儲(chǔ)引擎進(jìn)行增量備份的基礎(chǔ),也是MySQL實(shí)現(xiàn)復(fù)制的基本條件.
有時(shí)候?yàn)榱诉M(jìn)一步的mysql性能優(yōu)化,定位執(zhí)行較慢的SQL語句,很多系統(tǒng)也會(huì)打開慢查詢?nèi)罩緛碛涗泩?zhí)行時(shí)間超過特定數(shù)值(由我們自行設(shè)置)的SQL語句.

MYSQL必讀一般情況下,在生產(chǎn)系統(tǒng)中很少有系統(tǒng)會(huì)打開查詢?nèi)罩?因?yàn)椴樵內(nèi)罩敬蜷_之后會(huì)將MySQL中執(zhí)行的每一條Query都記錄到日志中,會(huì)該系統(tǒng)帶來比較大的IO負(fù)擔(dān),而帶來的實(shí)際效益卻并不是非常大.一般只有在開發(fā)測(cè)試環(huán)境中,為了定位某些功能具體使用了哪些SQL語句的時(shí)候,才會(huì)在短時(shí)間段內(nèi)打開該日志來做相應(yīng)的分析.
所以,在MySQL系統(tǒng)中,會(huì)對(duì)性能產(chǎn)生影響的MySQL日志(不包含各存儲(chǔ)引擎自己的日志)主要就是Binlog了.

MYSQL必讀2)Binlog 相關(guān)參數(shù)及優(yōu)化策略

MYSQL必讀我們首先看看Binlog的相關(guān)參數(shù),通過執(zhí)行如下命令可以獲得關(guān)于Binlog的相關(guān)參數(shù).
當(dāng)然,其中也顯示出了“innodb_locks_unsafe_for_binlog”這個(gè)Innodb存儲(chǔ)引擎特有的與Binlog相關(guān)的參數(shù)：

MYSQL必讀
mysql> show variables like '%binlog%'; 
+-----------------------------------------+----------------------+ 
| Variable_name              | Value        | 
+-----------------------------------------+----------------------+ 
| binlog_cache_size            | 16777216       | 
| binlog_checksum             | CRC32        | 
| binlog_direct_non_transactional_updates | OFF         | 
| binlog_error_action           | IGNORE_ERROR     | 
| binlog_format              | MIXED        | 
| binlog_gtid_simple_recovery       | OFF         | 
| binlog_max_flush_queue_time       | 0          | 
| binlog_order_commits          | ON          | 
| binlog_row_image            | FULL         | 
| binlog_rows_query_log_events      | OFF         | 
| binlog_stmt_cache_size         | 32768        | 
| binlogging_impossible_mode       | IGNORE_ERROR     | 
| innodb_api_enable_binlog        | OFF         | 
| innodb_locks_unsafe_for_binlog     | OFF         | 
| max_binlog_cache_size          | 18446744073709547520 | 
| max_binlog_size             | 1073741824      | 
| max_binlog_stmt_cache_size       | 18446744073709547520 | 
| simplified_binlog_gtid_recovery     | OFF         | 
| sync_binlog               | 1          | 
+-----------------------------------------+----------------------+ 
19 rows in set (0.00 sec)

MYSQL必讀“binlog_cache_size"：在事務(wù)過程中容納二進(jìn)制日志SQL語句的緩存大小.二進(jìn)制日志緩存是服務(wù)器支持事務(wù)存儲(chǔ)引擎并且服務(wù)器啟用了二進(jìn)制日志(―log-bin選項(xiàng))的前提下為每個(gè)客戶端分配的內(nèi)存,注意,是每個(gè)Client都可以分配設(shè)置大小的binlogcache空間.如果讀者朋友的系統(tǒng)中經(jīng)常會(huì)出現(xiàn)多語句事務(wù)的華,可以嘗試增加該值的大小,以獲得更有的性能.當(dāng)然,我們可以通過MySQL的以下兩個(gè)狀態(tài)變量來判斷當(dāng)前的binlog_cache_size的狀況：Binlog_cache_use和Binlog_cache_disk_use.

MYSQL必讀“max_binlog_cache_size”：和"binlog_cache_size"相對(duì)應(yīng),但是所代表的是binlog能夠使用的最大cache內(nèi)存大小.當(dāng)我們執(zhí)行多語句事務(wù)的時(shí)候,max_binlog_cache_size如果不夠大的話,系統(tǒng)可能會(huì)報(bào)出“Multi-statementtransactionrequiredmorethan'max_binlog_cache_size'bytesofstorage”的錯(cuò)誤.

MYSQL必讀“max_binlog_size”：Binlog日志最大值,一般來說設(shè)置為512M或者1G,但不能超過1G.該大小并不能非常嚴(yán)格控制Binlog大小,尤其是當(dāng)?shù)竭_(dá)Binlog比擬靠近尾部而又遇到一個(gè)較大事務(wù)的時(shí)候,系統(tǒng)為了保證事務(wù)的完整性,不可能做切換日志的動(dòng)作,只能將該事務(wù)的所有SQL都記錄進(jìn)入當(dāng)前日志,直到該事務(wù)結(jié)束.這一點(diǎn)和Oracle的Redo日志有點(diǎn)不一樣,因?yàn)镺racle的Redo日志所記錄的是數(shù)據(jù)文件的物理位置的變化,而且里面同時(shí)記錄了Redo和Undo相關(guān)的信息,所以同一個(gè)事務(wù)是否在一個(gè)日志中對(duì)Oracle來說并不關(guān)鍵.而MySQL在Binlog中所記錄的是數(shù)據(jù)庫邏輯變化信息,MySQL稱之為Event,實(shí)際上就是帶來數(shù)據(jù)庫變化的DML之類的Query語句.

MYSQL必讀“sync_binlog”：這個(gè)參數(shù)是對(duì)于MySQL系統(tǒng)來說是至關(guān)重要的,他不僅影響到Binlog對(duì)MySQL所帶來的性能損耗,而且還影響到MySQL中數(shù)據(jù)的完整性.對(duì)于“sync_binlog”參數(shù)的各種設(shè)置的說明如下：

MYSQL必讀sync_binlog=0,當(dāng)事務(wù)提交之后,MySQL不做fsync之類的磁盤同步指令刷新binlog_cache中的信息到磁盤,而讓Filesystem自行決定什么時(shí)候來做同步,或者cache滿了之后才同步到磁盤.

MYSQL必讀sync_binlog=n,當(dāng)每進(jìn)行n次事務(wù)提交之后,MySQL將進(jìn)行一次fsync之類的磁盤同步指令來將binlog_cache中的數(shù)據(jù)強(qiáng)制寫入磁盤.

MYSQL必讀在MySQL中系統(tǒng)默認(rèn)的設(shè)置是sync_binlog=0,也就是不做任何強(qiáng)制性的磁盤刷新指令,這時(shí)候的性能是最好的,但是風(fēng)險(xiǎn)也是最大的.因?yàn)橐坏┫到y(tǒng)Crash,在binlog_cache中的所有binlog信息都會(huì)被丟失.而當(dāng)設(shè)置為“1”的時(shí)候,是最平安但是性能損耗最大的設(shè)置.因?yàn)楫?dāng)設(shè)置為1的時(shí)候,即使系統(tǒng)Crash,也最多丟失binlog_cache中未完成的一個(gè)事務(wù),對(duì)實(shí)際數(shù)據(jù)沒有任何實(shí)質(zhì)性影響.從以往經(jīng)驗(yàn)和相關(guān)測(cè)試來看,對(duì)于高并發(fā)事務(wù)的系統(tǒng)來說,“sync_binlog”設(shè)置為0和設(shè)置為1的系統(tǒng)寫入性能差距可能高達(dá)5倍甚至更多.

MYSQL必讀另：

MYSQL必讀MySQL的復(fù)制(Replication),實(shí)際上便是通過將Master端的Binlog通過利用IO線程通過網(wǎng)絡(luò)復(fù)制到Slave端,然后再通過SQL線程解析Binlog中的日志再應(yīng)用到數(shù)據(jù)庫中來實(shí)現(xiàn)的.所以,Binlog量的大小對(duì)IO線程以及Msater和Slave端之間的網(wǎng)絡(luò)都會(huì)產(chǎn)生直接的影響.

MYSQL必讀MySQL中Binlog的產(chǎn)生量是沒方法改變的,只要我們的Query改變了數(shù)據(jù)庫中的數(shù)據(jù),那么就必須將該Query所對(duì)應(yīng)的Event記錄到Binlog中.那我們是不是就沒有方法優(yōu)化復(fù)制了呢?當(dāng)然不是,在MySQL復(fù)制環(huán)境中,實(shí)際上是是有8個(gè)參數(shù)可以讓我們控制需要復(fù)制或者需要忽略而不進(jìn)行復(fù)制的DB或者Table的,分別為：

MYSQL必讀Binlog_Do_DB：設(shè)定哪些數(shù)據(jù)庫(Schema)必要記錄Binlog；

MYSQL必讀Binlog_Ignore_DB：設(shè)定哪些數(shù)據(jù)庫(Schema)不要記錄Binlog；

MYSQL必讀Replicate_Do_DB：設(shè)定必要復(fù)制的數(shù)據(jù)庫(Schema),多個(gè)DB用逗號(hào)(“,”)分隔；

MYSQL必讀Replicate_Ignore_DB：設(shè)定可以忽略的數(shù)據(jù)庫(Schema)；

MYSQL必讀Replicate_Do_Table：設(shè)定必要復(fù)制的Table；

MYSQL必讀Replicate_Ignore_Table：設(shè)定可以忽略的Table；

MYSQL必讀Replicate_Wild_Do_Table：功能同Replicate_Do_Table,但可以帶通配符來進(jìn)行設(shè)置；

MYSQL必讀Replicate_Wild_Ignore_Table：功能同Replicate_Ignore_Table,可帶通配符設(shè)置；

MYSQL必讀
通過上面這八個(gè)參數(shù),我們就可以非常方便依照實(shí)際需求,控制從Master端到Slave端的Binlog量盡可能的少,從而減小Master端到Slave端的網(wǎng)絡(luò)流量,減少IO線程的IO量,還能減少SQL線程的解析與應(yīng)用SQL的數(shù)量,最終達(dá)到改善Slave上的數(shù)據(jù)延時(shí)問題.

MYSQL必讀實(shí)際上,上面這八個(gè)參數(shù)中的前面兩個(gè)是設(shè)置在Master端的,而后面六個(gè)參數(shù)則是設(shè)置在Slave端的.雖然前面兩個(gè)參數(shù)和后面六個(gè)參數(shù)在功能上并沒有非常直接的關(guān)系,但是對(duì)于優(yōu)化MySQL的Replication來說都可以啟到相似的功能.當(dāng)然也有必定的區(qū)別,其主要區(qū)別如下：

MYSQL必讀如果在Master端設(shè)置前面兩個(gè)參數(shù),不僅僅會(huì)讓Master端的Binlog記錄所帶來的IO量減少,還會(huì)讓Master端的IO線程就可以減少Binlog的讀取量,傳遞給Slave端的IO線程的Binlog量自然就會(huì)較少.這樣做的好處是可以減少網(wǎng)絡(luò)IO,減少Slave端IO線程的IO量,減少Slave端的SQL線程的工作量,從而最大幅度的優(yōu)化復(fù)制性能.當(dāng)然,在Master端設(shè)置也存在必定的弊端,因?yàn)镸ySQL的判斷是否需要復(fù)制某個(gè)Event不是根據(jù)產(chǎn)生該Event的Query所更改的數(shù)據(jù)

MYSQL必讀
所在的DB,而是根據(jù)執(zhí)行Query時(shí)刻所在的默認(rèn)Schema,也就是我們登錄時(shí)候指定的DB或者運(yùn)行“USEDATABASE”中所指定的DB.只有當(dāng)前默認(rèn)DB和配置中所設(shè)定的DB完全吻合的時(shí)候IO線程才會(huì)將該Event讀取給Slave的IO線程.所以如果在系統(tǒng)中出現(xiàn)在默認(rèn)DB和設(shè)定必要復(fù)制的DB不一樣的情況下改變了必要復(fù)制的DB中某個(gè)Table的數(shù)據(jù)的時(shí)候,該Event是不會(huì)被復(fù)制到Slave中去的,這樣就會(huì)造成Slave端的數(shù)據(jù)和Master的數(shù)據(jù)不一致的情況出現(xiàn).同樣,如果在默認(rèn)Schema下更改了不必要復(fù)制的Schema中的數(shù)據(jù),則會(huì)被復(fù)制到Slave端,當(dāng)Slave端并沒有該Schema的時(shí)候,則會(huì)造成復(fù)制出錯(cuò)而停止.

MYSQL必讀而如果是在Slave端設(shè)置后面的六個(gè)參數(shù),在性能優(yōu)化方面可能比在Master端要稍微遜色一點(diǎn),因?yàn)椴还苁潜匾€是不必要復(fù)制的Event都被會(huì)被IO線程讀取到Slave端,這樣不僅僅增加了網(wǎng)絡(luò)IO量,也給Slave端的IO線程增加了RelayLog的寫入量.但是仍然可以減少Slave的SQL線程在Slave端的日志應(yīng)用量.雖然性能方面稍有遜色,但是在Slave端設(shè)置復(fù)制過濾機(jī)制,可以保證不會(huì)出現(xiàn)因?yàn)槟J(rèn)Schema的問題而造成Slave和Master數(shù)據(jù)不一致或者復(fù)制出錯(cuò)的問題.

MYSQL必讀3)慢查詢?nèi)罩綫uery Log 相關(guān)參數(shù)及使用建議
再來看看SlowQueryLog的相關(guān)參數(shù)配置.有些時(shí)候,我們?yōu)榱硕ㄎ幌到y(tǒng)中效率比擬地下的Query語句,則需要打開慢查詢?nèi)罩?也就是SlowQueryLog.我們可以如下查看系統(tǒng)慢查詢?nèi)罩镜南嚓P(guān)設(shè)置：

MYSQL必讀
mysql> show variables like 'log_slow%'; 
+------------------+-------+ 
| Variable_name | Value | 
+------------------+-------+ 
| log_slow_queries | ON | 
+------------------+-------+ 
1 row in set (0.00 sec) 
 
mysql> show variables like 'long_query%'; 
+-----------------+-------+ 
| Variable_name | Value | 
+-----------------+-------+ 
| long_query_time | 1 | 
+-----------------+-------+ 
1 row in set (0.01 sec) 

MYSQL必讀“l(fā)og_slow_queries”參數(shù)顯示了系統(tǒng)是否已經(jīng)打開SlowQueryLog功能,而“l(fā)ong_query_time”參數(shù)則告訴我們當(dāng)前系統(tǒng)設(shè)置的SlowQuery記錄執(zhí)行時(shí)間超過多長(zhǎng)的Query.在MySQLAB發(fā)行的MySQL版本中SlowQueryLog可以設(shè)置的最短慢查詢時(shí)間為1秒,這在有些時(shí)候可能沒方法完全滿足我們的要求,如果希望能夠進(jìn)一步縮短慢查詢的時(shí)間限制,可以使用Percona提供的microslow-patch(件成為mslPatch)來突破該限制.mslpatch不僅僅能將慢查詢時(shí)間減小到毫秒級(jí)別,同時(shí)還能通過一些特定的規(guī)則來過濾記錄的SQL,如僅記錄涉及到某個(gè)表的SlowQuery等等附加功能.

MYSQL必讀打開SlowQueryLog功能對(duì)系統(tǒng)性能的整體影響沒有Binlog那么大,畢竟SlowQueryLog的數(shù)據(jù)量比擬小,帶來的IO損耗也就較小,但是,系統(tǒng)需要計(jì)算每一條Query的執(zhí)行時(shí)間,所以消耗總是會(huì)有一些的,主要是CPU方面的消耗.如果大家的系統(tǒng)在CPU資源足夠豐富的時(shí)候,可以不必在乎這一點(diǎn)點(diǎn)損耗,畢竟他可能會(huì)給我們帶來更大性能優(yōu)化的收獲.但如果我們的CPU資源也比擬緊張的時(shí)候,也完全可以在大部分時(shí)候關(guān)閉該功能,而只需要間斷性的打開SlowQueryLog功能來定位可能存在的慢查詢.

MYSQL必讀MySQL的其他日志由于使用很少(QueryLog)或者性能影響很少,在此就不做過多分析了.

MYSQL必讀以上這篇Mysql數(shù)據(jù)庫之Binlog日志使用總結(jié)(必看篇)便是小編分享給大家的全部?jī)?nèi)容了,希望能給大家一個(gè)參考,也希望大家多多支持維易PHP.

維易PHP培訓(xùn)學(xué)院每天發(fā)布《MYSQL數(shù)據(jù)庫Mysql數(shù)據(jù)庫之Binlog日志使用總結(jié)(必看篇)》等實(shí)戰(zhàn)技能，PHP、MYSQL、LINUX、APP、JS,CSS全面培養(yǎng)人才。

轉(zhuǎn)載請(qǐng)注明本頁網(wǎng)址：
http://www.snjht.com/jiaocheng/13675.html