《MYSQL數據庫Mysql數據庫之Binlog日志使用總結(必看篇)》要點：
本文介紹了MYSQL數據庫Mysql數據庫之Binlog日志使用總結(必看篇)，希望對您有用。如果有疑問，可以聯系我們。
MYSQL必讀binlog二進制日志對于mysql數據庫的重要性有多大,在此就不多說了.下面根據本人的日常操作經歷,并結合網上參考資料,對binlog日志使用做一梳理：
MYSQL必讀一、binlog日志介紹
MYSQL必讀1)什么是binlog
binlog日志用于記錄所有更新了數據或者已經潛在更新了數據(例如,沒有匹配任何行的一個DELETE)的所有語句.語句以“事件”的形式保留,它描述數據更改.
MYSQL必讀2)binlog作用
因為有了數據更新的binlog,所以可以用于實時備份,與master/slave主從復制結合.
MYSQL必讀3)和binlog有關參數
log_bin
MYSQL必讀設置此參數表示啟用binlog功能,并指定路徑名稱
MYSQL必讀log_bin_index
MYSQL必讀設置此參數是指定二進制索引文件的路徑與名稱
MYSQL必讀binlog_do_db
MYSQL必讀此參數表示只記錄指定數據庫的二進制日志
MYSQL必讀binlog_ignore_db
此參數表示不記錄指定的數據庫的二進制日志
MYSQL必讀max_binlog_cache_size
MYSQL必讀此參數表示binlog使用的內存最大的尺寸
MYSQL必讀binlog_cache_size
MYSQL必讀此參數表示binlog使用的內存大小,可以通過狀態變量binlog_cache_use和binlog_cache_disk_use來贊助測試.
MYSQL必讀binlog_cache_use：使用二進制日志緩存的事務數量
MYSQL必讀binlog_cache_disk_use:使用二進制日志緩存但超過binlog_cache_size值并使用臨時文件來保留事務中的語句的事務數量
MYSQL必讀max_binlog_size
MYSQL必讀Binlog最大值,最大和默認值是1GB,該設置并不能嚴格控制Binlog的大小,尤其是Binlog比擬靠近最大值而又遇到一個比擬大事務時,為了保證事務的完整性,不可能做切換日志的動作,只能將該事務的所有SQL都記錄進當前日志,直到事務結束
MYSQL必讀sync_binlog
MYSQL必讀這個參數直接影響mysql的性能和完整性
MYSQL必讀sync_binlog=0
MYSQL必讀當事務提交后,Mysql僅僅是將binlog_cache中的數據寫入Binlog文件,但不執行fsync之類的磁盤 同步指令通知文件系統將緩存刷新到磁盤,而讓Filesystem自行決定什么時候來做同步,這個是性能最好的.
MYSQL必讀sync_binlog=n,在進行n次事務提交以后,Mysql將執行一次fsync之類的磁盤同步指令,同志文件系統將Binlog文件緩存刷新到磁盤.
MYSQL必讀Mysql中默認的設置是sync_binlog=0,即不作任何強制性的磁盤刷新指令,這時性能是最好的,但風險也是最大的.一旦系統繃Crash,在文件系統緩存中的所有Binlog信息都會丟失
MYSQL必讀4)binlog的刪除
MYSQL必讀binlog的刪除可以手工刪除或自動刪除：
MYSQL必讀a)自動刪除binlog
MYSQL必讀通過binlog參數(expire_logs_days )來實現mysql自動刪除binlog
MYSQL必讀mysql> show binary logs;
mysql> show variables like 'expire_logs_days'; ? ? ?//該參數表示binlog日志自動刪除/過期的天數,默認值為0,表示不自動刪除
mysql> set global expire_logs_days=3; ? ? ? ?//表示日志保存3天,3天后就自動過期.
MYSQL必讀b)手工刪除binlog
MYSQL必讀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必讀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二進制日志文件的狀態信息
mysql> show slave hosts; ? ? ? ?//顯示當前注冊的slave的列表.不以--report-host=slave_name選項為開頭的slave不會顯示在本列表中
MYSQL必讀mysql> flush logs; ? ? //產生一個新的binlog日志文件
MYSQL必讀?
MYSQL必讀mysql binlog日志自動清理及手動刪除案例說明：
MYSQL必讀
當開啟MySQL數據庫主從時,會產生大量如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
然后重啟數據庫
2.開啟mysql主從,設置expire_logs_days；
實例操作如下：
[root@huqniupc ~]# vim /etc/my.cnf //修改expire_logs_days,x是自動刪除的天數,一般將x設置為短點,如10
expire_logs_days = x //二進制日志自動刪除的天數.默認值為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'格式.
-----------------------------------------------------
MYSQL必讀5)清除binlog時,對從mysql的影響
MYSQL必讀如果有一個活躍的slave從屬服務器,該服務器當前正在讀取您正在試圖刪除的日志之一,則本語句不會起作用,而是會失敗,并伴隨一個錯誤；不過如果slave從屬服務器是關閉的(或master-slave主從關系關閉),并且碰巧清理了其想要讀取的日志之一,則slave從屬服務器啟動后不能復制；當從屬服務器正在復制時,本語句可以平安運行,不需要停止它們.
MYSQL必讀6)binglog的查看
MYSQL必讀通過mysqlbinlog命令可以查看binlog的內容
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”說明“事件”的起點,是以第196字節開始；“end_log_pos 294”說明以第294字節結束
MYSQL必讀時間戳
MYSQL必讀事件發生的時間戳：“120330 17:54:46”
MYSQL必讀事件執行時間
MYSQL必讀事件執行花費的時間:"exec_time=28"
MYSQL必讀錯誤碼
MYSQL必讀錯誤碼為：“error_code=0”
MYSQL必讀服務器的標識
MYSQL必讀服務器的標識id：“server id 1”
MYSQL必讀注意下面幾點：
MYSQL必讀1.mysql的日志切不可想象是可以恢復到任何時間的狀態,這個恢復是有前提的!
至少得有一個從日志記錄開始后的數據庫備份,通過日志恢復數據庫實際上只是一個對以前操作的回放過程罷了,不用想得太復雜.
MYSQL必讀既然是回放操作,那么就得注意了,如果是執行了兩次恢復那就相當于是回放了兩次,后果可想而知.
MYSQL必讀所以：
MYSQL必讀1)恢復前務必先備份數據.
MYSQL必讀2)由于二進制文件多,并且必要恢復的數據跨度大,可以考慮將日志文件合并在恢復.
MYSQL必讀2. 開啟binlog日志功能
MYSQL必讀要想通過日志恢復數據庫,必需首先在my.cnf文件里定義,log-bin=mysql-bin,這樣產生的binlog日志名就是以mysql-bin命名的
MYSQL必讀3.什么時候會生成新的binlog文件
MYSQL必讀1)在備份的時候加入--flush-logs
MYSQL必讀2)重新啟動mysql服務的時候
MYSQL必讀特別提示,mysql每次啟動都會重新生成一個類似mysql-bin.00000n的文件,如果你的mysql每天都要重新啟動一次的話,這時候你就要特別注意不要選錯日志文件了.
MYSQL必讀二、binlog日志格式介紹?
MYSQL必讀(1)Mysql binlog日志有三種格式,分別是Statement、MiXED、ROW
MYSQL必讀1)Statement：每一條會修改數據的sql都會記錄在binlog中
MYSQL必讀優點：不必要記錄每一行的變化,減少了binlog日志量,節約了IO,提高性能.(相比row能節約多少性能與日志量,這個取決于應用的SQL情況,正常同一條記錄修改或者插入row格式所產生的日志量還小于Statement產生的日志量,但是考慮到如果帶條件的update操作,以及整表刪除,alter表等操作,ROW格式會產生大量日志,因此在考慮是否使用ROW格式日志時應該跟據應用的實際情況,其所產生的日志量會增加多少,以及帶來的IO性能問題.)
MYSQL必讀缺點：由于記錄的只是執行語句,為了這些語句能在slave上正確運行,因此還必須記錄每條語句在執行的時候的一些相關信息,以保證所有語句能在slave得到和在master端執行時候相同 的結果.另外mysql 的復制,像一些特定函數功能,slave可與master上要堅持一致會有很多相關問題(如sleep()函數, last_insert_id(),以及user-defined functions(udf)會出現問題).
MYSQL必讀使用以下函數的語句也無法被復制：
MYSQL必讀* LOAD_FILE()
* UUID()
* USER()
* FOUND_ROWS()
* SYSDATE() (除非啟動時啟用了 --sysdate-is-now 選項)
MYSQL必讀同時在INSERT ...SELECT 會產生比 RBR 更多的行級鎖
MYSQL必讀2)Row:不記錄sql語句上下文相關信息,僅保留哪條記錄被修改
MYSQL必讀優點： binlog中可以不記錄執行的sql語句的上下文相關的信息,僅必要記錄那一條記錄被修改成什么了.所以rowlevel的日志內容會非常清楚的記錄下每一行數據修改的細節.而且不會出現某些特定情況下的存儲過程,或function,以及trigger的調用和觸發無法被正確復制的問題
MYSQL必讀缺點:所有的執行的語句當記錄到日志中的時候,都將以每行記錄的修改來記錄,這樣可能會產生大量的日志內容,好比一條update語句,修改多條記錄,則binlog中每一條修改都會有記錄,這樣造成binlog日志量會很大,特別是當執行alter table之類的語句的時候,由于表結構修改,每條記錄都發生改變,那么該表每一條記錄都會記錄到日志中.
MYSQL必讀3)Mixedlevel: 是以上兩種level的混合使用,一般的語句修改使用statment格式保留binlog,如一些函數,statement無法完成主從復制的操作,則采用row格式保留binlog,MySQL會根據執行的每一條具體的sql語句來區分對待記錄的日志形式,也就是在Statement和Row之間選擇一種.新版本的MySQL中隊row level模式也被做了優化,并不是所有的修改都會以row level來記錄,像遇到表結構變更的時候就會以statement模式來記錄.至于update或者delete等修改數據的語句,還是會記錄所有行的變更.
MYSQL必讀Mixed日志說明：
MYSQL必讀在slave日志同步過程中,對于使用now這樣的時間函數,MIXED日志格式,會在日志中產生對應的unix_timestamp()*1000的時間字符串,slave在完成同步時,取用的是sqlEvent發生的時間來保證數據的準確性.另外對于一些功能性函數slave能完成相應的數據同步,而對于上面指定的一些類似于UDF函數,導致Slave無法知曉的情況,則會采用ROW格式存儲這些Binlog,以保證產生的Binlog可以供Slave完成數據同步.
MYSQL必讀(2)binlog基本配制與格式設定
MYSQL必讀1)基本配制
MYSQL必讀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每個日志文件大小
MYSQL必讀binlog-do-db=必要備份的數據庫名,如果備份多個數據庫,重復設置這個選項即可
binlog-ignore-db=不必要備份的數據庫苦命,如果備份多個數據庫,重復設置這個選項即可
MYSQL必讀2)Binlog日志格式選擇
MYSQL必讀Mysql默認是使用Statement日志格式,保舉使用MIXED.
由于一些特殊使用,可以考慮使用ROWED,如自己通過binlog日志來同步數據的修改,這樣會節省很多相關操作.對于binlog數據處理會變得非常輕松,相對mixed,解析也會很輕松(當然前提是增加的日志量所帶來的IO開銷在容忍的范圍內即可).
MYSQL必讀3)mysqlbinlog格式選擇
MYSQL必讀mysql對于日志格式的選定原則:如果是采用 INSERT,UPDATE,DELETE 等直接操作表的情況,則日志格式根據 binlog_format 的設定而記錄,如果是采用 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.開始事物的時間:
MYSQL必讀SET TIMESTAMP=1350355892/*!*/;
MYSQL必讀BEGIN
MYSQL必讀2.sqlevent起點
MYSQL必讀#at 1643330 :為事件的起點,是以1643330字節開始.
MYSQL必讀3.sqlevent 發生的時間點
MYSQL必讀#121016 10:51:32:是事件發生的時間,
MYSQL必讀4.serverId
MYSQL必讀server id 1 :為master 的serverId
MYSQL必讀5.sqlevent終點及花費時間,錯誤碼
MYSQL必讀end_log_pos 1643885:為事件的終點,是以1643885 字節結束.
MYSQL必讀execTime 0: 花費的時間
MYSQL必讀error_code=0:錯誤碼
MYSQL必讀Xid:事件指示提交的XA事務
MYSQL必讀三、mysql日志(重點binlog日志)的優化說明
MYSQL必讀MySQL系統的伸縮性很強,既可以在充足的硬件資源環境下高效運行,也可以在極少資源環境下很好的運行,
但不管怎樣,盡可能充足的硬件資源對MySQL的性能提升總是有贊助的.
MYSQL必讀下面著重分析一下MySQL的日志(主要是Binlog)對系統性能的影響,并根據日志的相關特性得出相應的優化思路.
MYSQL必讀1)日志產生的性能影響
由于日志的記錄帶來的直接性能損耗便是數據庫系統中最為昂貴的IO資源.
MYSQL必讀MySQL的日志主要包含錯誤日志(ErrorLog),更新日志(UpdateLog),二進制日志(Binlog),查詢日志(QueryLog),慢查詢日志(SlowQueryLog)等.
特別注意：更新日志是老版本的MySQL才有的,目前已經被二進制日志替代.
MYSQL必讀在默認情況下,系統僅僅打開錯誤日志,關閉了其他所有日志,以達到盡可能減少IO損耗提高系統性能的目的.
但是在一般稍微重要一點的實際應用場景中,都至少必要打開二進制日志,因為這是MySQL很多存儲引擎進行增量備份的基礎,也是MySQL實現復制的基本條件.
有時候為了進一步的mysql性能優化,定位執行較慢的SQL語句,很多系統也會打開慢查詢日志來記錄執行時間超過特定數值(由我們自行設置)的SQL語句.
MYSQL必讀一般情況下,在生產系統中很少有系統會打開查詢日志.因為查詢日志打開之后會將MySQL中執行的每一條Query都記錄到日志中,會該系統帶來比較大的IO負擔,而帶來的實際效益卻并不是非常大.一般只有在開發測試環境中,為了定位某些功能具體使用了哪些SQL語句的時候,才會在短時間段內打開該日志來做相應的分析.
所以,在MySQL系統中,會對性能產生影響的MySQL日志(不包含各存儲引擎自己的日志)主要就是Binlog了.
MYSQL必讀2)Binlog 相關參數及優化策略
MYSQL必讀我們首先看看Binlog的相關參數,通過執行如下命令可以獲得關于Binlog的相關參數.
當然,其中也顯示出了“innodb_locks_unsafe_for_binlog”這個Innodb存儲引擎特有的與Binlog相關的參數：
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"：在事務過程中容納二進制日志SQL語句的緩存大小.二進制日志緩存是服務器支持事務存儲引擎并且服務器啟用了二進制日志(―log-bin選項)的前提下為每個客戶端分配的內存,注意,是每個Client都可以分配設置大小的binlogcache空間.如果讀者朋友的系統中經常會出現多語句事務的華,可以嘗試增加該值的大小,以獲得更有的性能.當然,我們可以通過MySQL的以下兩個狀態變量來判斷當前的binlog_cache_size的狀況：Binlog_cache_use和Binlog_cache_disk_use.
MYSQL必讀“max_binlog_cache_size”：和"binlog_cache_size"相對應,但是所代表的是binlog能夠使用的最大cache內存大小.當我們執行多語句事務的時候,max_binlog_cache_size如果不夠大的話,系統可能會報出“Multi-statementtransactionrequiredmorethan'max_binlog_cache_size'bytesofstorage”的錯誤.
MYSQL必讀“max_binlog_size”：Binlog日志最大值,一般來說設置為512M或者1G,但不能超過1G.該大小并不能非常嚴格控制Binlog大小,尤其是當到達Binlog比擬靠近尾部而又遇到一個較大事務的時候,系統為了保證事務的完整性,不可能做切換日志的動作,只能將該事務的所有SQL都記錄進入當前日志,直到該事務結束.這一點和Oracle的Redo日志有點不一樣,因為Oracle的Redo日志所記錄的是數據文件的物理位置的變化,而且里面同時記錄了Redo和Undo相關的信息,所以同一個事務是否在一個日志中對Oracle來說并不關鍵.而MySQL在Binlog中所記錄的是數據庫邏輯變化信息,MySQL稱之為Event,實際上就是帶來數據庫變化的DML之類的Query語句.
MYSQL必讀“sync_binlog”：這個參數是對于MySQL系統來說是至關重要的,他不僅影響到Binlog對MySQL所帶來的性能損耗,而且還影響到MySQL中數據的完整性.對于“sync_binlog”參數的各種設置的說明如下：
MYSQL必讀sync_binlog=0,當事務提交之后,MySQL不做fsync之類的磁盤同步指令刷新binlog_cache中的信息到磁盤,而讓Filesystem自行決定什么時候來做同步,或者cache滿了之后才同步到磁盤.
MYSQL必讀sync_binlog=n,當每進行n次事務提交之后,MySQL將進行一次fsync之類的磁盤同步指令來將binlog_cache中的數據強制寫入磁盤.
MYSQL必讀在MySQL中系統默認的設置是sync_binlog=0,也就是不做任何強制性的磁盤刷新指令,這時候的性能是最好的,但是風險也是最大的.因為一旦系統Crash,在binlog_cache中的所有binlog信息都會被丟失.而當設置為“1”的時候,是最平安但是性能損耗最大的設置.因為當設置為1的時候,即使系統Crash,也最多丟失binlog_cache中未完成的一個事務,對實際數據沒有任何實質性影響.從以往經驗和相關測試來看,對于高并發事務的系統來說,“sync_binlog”設置為0和設置為1的系統寫入性能差距可能高達5倍甚至更多.
MYSQL必讀另：
MYSQL必讀MySQL的復制(Replication),實際上便是通過將Master端的Binlog通過利用IO線程通過網絡復制到Slave端,然后再通過SQL線程解析Binlog中的日志再應用到數據庫中來實現的.所以,Binlog量的大小對IO線程以及Msater和Slave端之間的網絡都會產生直接的影響.
MYSQL必讀MySQL中Binlog的產生量是沒方法改變的,只要我們的Query改變了數據庫中的數據,那么就必須將該Query所對應的Event記錄到Binlog中.那我們是不是就沒有方法優化復制了呢?當然不是,在MySQL復制環境中,實際上是是有8個參數可以讓我們控制需要復制或者需要忽略而不進行復制的DB或者Table的,分別為：
MYSQL必讀Binlog_Do_DB：設定哪些數據庫(Schema)必要記錄Binlog；
MYSQL必讀Binlog_Ignore_DB：設定哪些數據庫(Schema)不要記錄Binlog；
MYSQL必讀Replicate_Do_DB：設定必要復制的數據庫(Schema),多個DB用逗號(“,”)分隔；
MYSQL必讀Replicate_Ignore_DB：設定可以忽略的數據庫(Schema)；
MYSQL必讀Replicate_Do_Table：設定必要復制的Table；
MYSQL必讀Replicate_Ignore_Table：設定可以忽略的Table；
MYSQL必讀Replicate_Wild_Do_Table：功能同Replicate_Do_Table,但可以帶通配符來進行設置；
MYSQL必讀Replicate_Wild_Ignore_Table：功能同Replicate_Ignore_Table,可帶通配符設置；
MYSQL必讀
通過上面這八個參數,我們就可以非常方便依照實際需求,控制從Master端到Slave端的Binlog量盡可能的少,從而減小Master端到Slave端的網絡流量,減少IO線程的IO量,還能減少SQL線程的解析與應用SQL的數量,最終達到改善Slave上的數據延時問題.
MYSQL必讀實際上,上面這八個參數中的前面兩個是設置在Master端的,而后面六個參數則是設置在Slave端的.雖然前面兩個參數和后面六個參數在功能上并沒有非常直接的關系,但是對于優化MySQL的Replication來說都可以啟到相似的功能.當然也有必定的區別,其主要區別如下：
MYSQL必讀如果在Master端設置前面兩個參數,不僅僅會讓Master端的Binlog記錄所帶來的IO量減少,還會讓Master端的IO線程就可以減少Binlog的讀取量,傳遞給Slave端的IO線程的Binlog量自然就會較少.這樣做的好處是可以減少網絡IO,減少Slave端IO線程的IO量,減少Slave端的SQL線程的工作量,從而最大幅度的優化復制性能.當然,在Master端設置也存在必定的弊端,因為MySQL的判斷是否需要復制某個Event不是根據產生該Event的Query所更改的數據
MYSQL必讀
所在的DB,而是根據執行Query時刻所在的默認Schema,也就是我們登錄時候指定的DB或者運行“USEDATABASE”中所指定的DB.只有當前默認DB和配置中所設定的DB完全吻合的時候IO線程才會將該Event讀取給Slave的IO線程.所以如果在系統中出現在默認DB和設定必要復制的DB不一樣的情況下改變了必要復制的DB中某個Table的數據的時候,該Event是不會被復制到Slave中去的,這樣就會造成Slave端的數據和Master的數據不一致的情況出現.同樣,如果在默認Schema下更改了不必要復制的Schema中的數據,則會被復制到Slave端,當Slave端并沒有該Schema的時候,則會造成復制出錯而停止.
MYSQL必讀而如果是在Slave端設置后面的六個參數,在性能優化方面可能比在Master端要稍微遜色一點,因為不管是必要還是不必要復制的Event都被會被IO線程讀取到Slave端,這樣不僅僅增加了網絡IO量,也給Slave端的IO線程增加了RelayLog的寫入量.但是仍然可以減少Slave的SQL線程在Slave端的日志應用量.雖然性能方面稍有遜色,但是在Slave端設置復制過濾機制,可以保證不會出現因為默認Schema的問題而造成Slave和Master數據不一致或者復制出錯的問題.
MYSQL必讀3)慢查詢日志Query Log 相關參數及使用建議
再來看看SlowQueryLog的相關參數配置.有些時候,我們為了定位系統中效率比擬地下的Query語句,則需要打開慢查詢日志,也就是SlowQueryLog.我們可以如下查看系統慢查詢日志的相關設置：
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必讀“log_slow_queries”參數顯示了系統是否已經打開SlowQueryLog功能,而“long_query_time”參數則告訴我們當前系統設置的SlowQuery記錄執行時間超過多長的Query.在MySQLAB發行的MySQL版本中SlowQueryLog可以設置的最短慢查詢時間為1秒,這在有些時候可能沒方法完全滿足我們的要求,如果希望能夠進一步縮短慢查詢的時間限制,可以使用Percona提供的microslow-patch(件成為mslPatch)來突破該限制.mslpatch不僅僅能將慢查詢時間減小到毫秒級別,同時還能通過一些特定的規則來過濾記錄的SQL,如僅記錄涉及到某個表的SlowQuery等等附加功能.
MYSQL必讀打開SlowQueryLog功能對系統性能的整體影響沒有Binlog那么大,畢竟SlowQueryLog的數據量比擬小,帶來的IO損耗也就較小,但是,系統需要計算每一條Query的執行時間,所以消耗總是會有一些的,主要是CPU方面的消耗.如果大家的系統在CPU資源足夠豐富的時候,可以不必在乎這一點點損耗,畢竟他可能會給我們帶來更大性能優化的收獲.但如果我們的CPU資源也比擬緊張的時候,也完全可以在大部分時候關閉該功能,而只需要間斷性的打開SlowQueryLog功能來定位可能存在的慢查詢.
MYSQL必讀MySQL的其他日志由于使用很少(QueryLog)或者性能影響很少,在此就不做過多分析了.
MYSQL必讀以上這篇Mysql數據庫之Binlog日志使用總結(必看篇)便是小編分享給大家的全部內容了,希望能給大家一個參考,也希望大家多多支持維易PHP.
維易PHP培訓學院每天發布《MYSQL數據庫Mysql數據庫之Binlog日志使用總結(必看篇)》等實戰技能，PHP、MYSQL、LINUX、APP、JS,CSS全面培養人才。
