Mysql數(shù)據(jù)庫之Binlog日志使用總結(必看篇)

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

一、binlog日志介紹

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

2）binlog作用
因為有了數(shù)據(jù)更新的binlog，所以可以用于實時備份，與master/slave主從復制結合。

3）和binlog有關參數(shù)
log_bin

設置此參數(shù)表示啟用binlog功能，并指定路徑名稱

log_bin_index

設置此參數(shù)是指定二進制索引文件的路徑與名稱

binlog_do_db

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

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

max_binlog_cache_size

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

binlog_cache_size

此參數(shù)表示binlog使用的內(nèi)存大小，可以通過狀態(tài)變量binlog_cache_use和binlog_cache_disk_use來幫助測試。

binlog_cache_use：使用二進制日志緩存的事務數(shù)量

binlog_cache_disk_use:使用二進制日志緩存但超過binlog_cache_size值并使用臨時文件來保存事務中的語句的事務數(shù)量

max_binlog_size

Binlog最大值，最大和默認值是1GB，該設置并不能嚴格控制Binlog的大小，尤其是Binlog比較靠近最大值而又遇到一個比較大事務時，為了保證事務的完整性，不可能做切換日志的動作，只能將該事務的所有SQL都記錄進當前日志，直到事務結束

sync_binlog

這個參數(shù)直接影響mysql的性能和完整性

sync_binlog=0

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

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

Mysql中默認的設置是sync_binlog=0，即不作任何強制性的磁盤刷新指令，這時性能是最好的，但風險也是最大的。一旦系統(tǒng)繃Crash，在文件系統(tǒng)緩存中的所有Binlog信息都會丟失

4）binlog的刪除

binlog的刪除可以手工刪除或自動刪除：

a）自動刪除binlog

通過binlog參數(shù)（expire_logs_days ）來實現(xiàn)mysql自動刪除binlog

mysql> show binary logs;
mysql> show variables like 'expire_logs_days';      //該參數(shù)表示binlog日志自動刪除/過期的天數(shù)，默認值為0，表示不自動刪除
mysql> set global expire_logs_days=3;        //表示日志保留3天，3天后就自動過期。

b）手工刪除binlog

mysql> reset master;        //刪除master的binlog，即手動刪除所有的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> set sql_log_bin=1/0;       //如果用戶有super權限，可以啟用或禁用當前會話的binlog記錄
mysql> show master logs;          //查看master的binlog日志列表
mysql> show binary logs;           //查看master的binlog日志文件大小
mysql> show master status;     //用于提供master二進制日志文件的狀態(tài)信息
mysql> show slave hosts;        //顯示當前注冊的slave的列表。不以--report-host=slave_name選項為開頭的slave不會顯示在本列表中

mysql> flush logs;     //產(chǎn)生一個新的binlog日志文件

mysql binlog日志自動清理及手動刪除案例說明：

當開啟MySQL數(shù)據(jù)庫主從時，會產(chǎn)生大量如mysql-bin.00000* log的文件，這會大量耗費您的硬盤空間。 
mysql-bin.000001 
mysql-bin.000002 
mysql-bin.000003 
mysql-bin.000004 
mysql-bin.000005 
… 
 
刪除這些binlog日志有三種解決方法： 
1.關閉mysql主從，關閉binlog； 
實例操作如下： 
[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主從，設置expire_logs_days； 
實例操作如下： 
[root@huqniupc ~]# vim /etc/my.cnf //修改expire_logs_days,x是自動刪除的天數(shù)，一般將x設置為短點，如10 
expire_logs_days = x //二進制日志自動刪除的天數(shù)。默認值為0,表示“沒有自動刪除” 
此方法需要重啟mysql 
 
當然也可以不重啟mysql,開啟mysql主從，直接在mysql里設置expire_logs_days 
> show binary logs; 
> show variables like '%log%'; 
> set global expire_logs_days = 10; 
 
 
3.手動清除binlog文件，(比如Mysql> PURGE MASTER LOGS TO ‘MySQL-bin.010′;） 
實例操作如下： 
[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有關于PURGE MASTER LOGS手動刪除用法及示例 
> show master logs; 
  
也可以重置master，刪除所有binlog文件： 
# /usr/local/mysql/bin/mysql -u root -p 
> reset master; //附錄3有清除binlog時，對從mysql的影響說明 
  
--------------------------------------------------------------- 
PURGE MASTER LOGS手動刪除用法及示例,MASTER和BINARY是同義詞 
> PURGE {MASTER | BINARY} LOGS TO 'log_name'
> PURGE {MASTER | BINARY} LOGS BEFORE 'date'
刪除指定的日志或日期之前的日志索引中的所有二進制日志。這些日志也會從記錄在日志索引文件中的清單中被刪除MySQL BIN-LOG 日志，這樣被給定的日志成為第一個。 
 
實例： 
> 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'格式。 
-----------------------------------------------------

5）清除binlog時，對從mysql的影響

如果有一個活躍的slave從屬服務器，該服務器當前正在讀取您正在試圖刪除的日志之一，則本語句不會起作用，而是會失敗，并伴隨一個錯誤；不過如果slave從屬服務器是關閉的（或master-slave主從關系關閉），并且碰巧清理了其想要讀取的日志之一，則slave從屬服務器啟動后不能復制；當從屬服務器正在復制時，本語句可以安全運行，不需要停止它們。

6）binglog的查看

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

[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/*!*/;

解析binlog格式：

位置

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

時間戳

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

事件執(zhí)行時間

事件執(zhí)行花費的時間:"exec_time=28"

錯誤碼

錯誤碼為：“error_code=0”

服務器的標識

服務器的標識id：“server id 1”

注意下面幾點：

1.mysql的日志切不可想象是可以恢復到任何時間的狀態(tài)，這個恢復是有前提的！
至少得有一個從日志記錄開始后的數(shù)據(jù)庫備份，通過日志恢復數(shù)據(jù)庫實際上只是一個對以前操作的回放過程而已，不用想得太復雜。

既然是回放操作，那么就得注意了，如果是執(zhí)行了兩次恢復那就相當于是回放了兩次，后果可想而知。

所以：

1）恢復前務必先備份數(shù)據(jù).

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

2. 開啟binlog日志功能

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

3.什么時候會生成新的binlog文件

1）在備份的時候加入--flush-logs

2）重新啟動mysql服務的時候

特別提示，mysql每次啟動都會重新生成一個類似mysql-bin.00000n的文件，如果你的mysql每天都要重新啟動一次的話，這時候你就要特別注意不要選錯日志文件了。

二、binlog日志格式介紹 

（1）Mysql binlog日志有三種格式，分別是Statement、MiXED、ROW

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

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

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

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

* LOAD_FILE()
* UUID()
* USER()
* FOUND_ROWS()
* SYSDATE() (除非啟動時啟用了 --sysdate-is-now 選項)

同時在INSERT ...SELECT 會產(chǎn)生比 RBR 更多的行級鎖

2）Row:不記錄sql語句上下文相關信息，僅保存哪條記錄被修改

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

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

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

Mixed日志說明：

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

（2）binlog基本配制與格式設定

1）基本配制

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

binlog-do-db=需要備份的數(shù)據(jù)庫名，如果備份多個數(shù)據(jù)庫，重復設置這個選項即可
binlog-ignore-db=不需要備份的數(shù)據(jù)庫苦命，如果備份多個數(shù)據(jù)庫，重復設置這個選項即可

2）Binlog日志格式選擇

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

3）mysqlbinlog格式選擇

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

（3）Mysql Binlog日志分析

通過MysqlBinlog指令查看具體的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/*!*/;

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

1.開始事物的時間:

SET TIMESTAMP=1350355892/*!*/;

BEGIN

2.sqlevent起點

#at 1643330 :為事件的起點，是以1643330字節(jié)開始。

3.sqlevent 發(fā)生的時間點

#121016 10:51:32:是事件發(fā)生的時間，

4.serverId

server id 1 :為master 的serverId

5.sqlevent終點及花費時間，錯誤碼

end_log_pos 1643885:為事件的終點，是以1643885 字節(jié)結束。

execTime 0: 花費的時間

error_code=0:錯誤碼

Xid:事件指示提交的XA事務

三、mysql日志（重點binlog日志）的優(yōu)化說明

MySQL系統(tǒng)的伸縮性很強，既可以在充足的硬件資源環(huán)境下高效運行，也可以在極少資源環(huán)境下很好的運行，
但不管怎樣，盡可能充足的硬件資源對MySQL的性能提升總是有幫助的。

下面著重分析一下MySQL的日志（主要是Binlog）對系統(tǒng)性能的影響，并根據(jù)日志的相關特性得出相應的優(yōu)化思路。

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

MySQL的日志主要包括錯誤日志（ErrorLog），更新日志（UpdateLog），二進制日志（Binlog），查詢?nèi)罩荆≦ueryLog），慢查詢?nèi)罩荆⊿lowQueryLog）等。
特別注意：更新日志是老版本的MySQL才有的，目前已經(jīng)被二進制日志替代。

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

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

2）Binlog 相關參數(shù)及優(yōu)化策略

我們首先看看Binlog的相關參數(shù)，通過執(zhí)行如下命令可以獲得關于Binlog的相關參數(shù)。
當然，其中也顯示出了“innodb_locks_unsafe_for_binlog”這個Innodb存儲引擎特有的與Binlog相關的參數(shù)：

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)

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

“max_binlog_cache_size”：和"binlog_cache_size"相對應，但是所代表的是binlog能夠使用的最大cache內(nèi)存大小。當我們執(zhí)行多語句事務的時候，max_binlog_cache_size如果不夠大的話，系統(tǒng)可能會報出“Multi-statementtransactionrequiredmorethan'max_binlog_cache_size'bytesofstorage”的錯誤。

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

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

sync_binlog=0，當事務提交之后，MySQL不做fsync之類的磁盤同步指令刷新binlog_cache中的信息到磁盤，而讓Filesystem自行決定什么時候來做同步，或者cache滿了之后才同步到磁盤。

sync_binlog=n，當每進行n次事務提交之后，MySQL將進行一次fsync之類的磁盤同步指令來將binlog_cache中的數(shù)據(jù)強制寫入磁盤。

在MySQL中系統(tǒng)默認的設置是sync_binlog=0，也就是不做任何強制性的磁盤刷新指令，這時候的性能是最好的，但是風險也是最大的。因為一旦系統(tǒng)Crash，在binlog_cache中的所有binlog信息都會被丟失。而當設置為“1”的時候，是最安全但是性能損耗最大的設置。因為當設置為1的時候，即使系統(tǒng)Crash，也最多丟失binlog_cache中未完成的一個事務，對實際數(shù)據(jù)沒有任何實質(zhì)性影響。從以往經(jīng)驗和相關測試來看，對于高并發(fā)事務的系統(tǒng)來說，“sync_binlog”設置為0和設置為1的系統(tǒng)寫入性能差距可能高達5倍甚至更多。

另：

MySQL的復制（Replication），實際上就是通過將Master端的Binlog通過利用IO線程通過網(wǎng)絡復制到Slave端，然后再通過SQL線程解析Binlog中的日志再應用到數(shù)據(jù)庫中來實現(xiàn)的。所以，Binlog量的大小對IO線程以及Msater和Slave端之間的網(wǎng)絡都會產(chǎn)生直接的影響。

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

Binlog_Do_DB：設定哪些數(shù)據(jù)庫（Schema）需要記錄Binlog；

Binlog_Ignore_DB：設定哪些數(shù)據(jù)庫（Schema）不要記錄Binlog；

Replicate_Do_DB：設定需要復制的數(shù)據(jù)庫（Schema），多個DB用逗號（“,”）分隔；

Replicate_Ignore_DB：設定可以忽略的數(shù)據(jù)庫（Schema）；

Replicate_Do_Table：設定需要復制的Table；

Replicate_Ignore_Table：設定可以忽略的Table；

Replicate_Wild_Do_Table：功能同Replicate_Do_Table，但可以帶通配符來進行設置；

Replicate_Wild_Ignore_Table：功能同Replicate_Ignore_Table，可帶通配符設置；

通過上面這八個參數(shù)，我們就可以非常方便按照實際需求，控制從Master端到Slave端的Binlog量盡可能的少，從而減小Master端到Slave端的網(wǎng)絡流量，減少IO線程的IO量，還能減少SQL線程的解析與應用SQL的數(shù)量，最終達到改善Slave上的數(shù)據(jù)延時問題。

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

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

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

而如果是在Slave端設置后面的六個參數(shù)，在性能優(yōu)化方面可能比在Master端要稍微遜色一點，因為不管是需要還是不需要復制的Event都被會被IO線程讀取到Slave端，這樣不僅僅增加了網(wǎng)絡IO量，也給Slave端的IO線程增加了RelayLog的寫入量。但是仍然可以減少Slave的SQL線程在Slave端的日志應用量。雖然性能方面稍有遜色，但是在Slave端設置復制過濾機制，可以保證不會出現(xiàn)因為默認Schema的問題而造成Slave和Master數(shù)據(jù)不一致或者復制出錯的問題。

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

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) 

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

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

MySQL的其他日志由于使用很少（QueryLog）或者性能影響很少，在此就不做過多分析了。

以上這篇Mysql數(shù)據(jù)庫之Binlog日志使用總結(必看篇)就是小編分享給大家的全部內(nèi)容了，希望能給大家一個參考，也希望大家多多支持腳本之家。