《MySQL系列連載之日志類型》要點：
本文介紹了MySQL系列連載之日志類型，希望對您有用。如果有疑問，可以聯(lián)系我們。

導讀

如果您在本文遇到任何問題或疑問請到QQ群中與我們交流.也可在下方進行評論.我將在第一時間和您進行交流,共同學習.
QQ群：201777608、526871767、1689067(加群時注明：運維派)

二進制日志

1.二進制日志文件作用

提供了增量備份的功能

提供了數(shù)據(jù)基于時間點的恢復,這個恢復的時間點可以由用戶控制

為mysql的復制架構提供基礎,將這主服務器的二進制日志復制到從服務器上并執(zhí)行同樣的操作,就可將數(shù)據(jù)進行同步

2.二進制日志格式

1、基于語句 statement

基于SQL語句的復制(statement-based replication,SBR)

每一條會修改數(shù)據(jù)的sql都會記錄到master的binlog中,slave在的時候sql進程會解析成和原來master端相同的sql再執(zhí)行.

優(yōu)點：在Statement模式下首先就是解決了row模式下的缺點,不需要記錄記錄每一行日志的變化,減少了binlog日志量,節(jié)省了I/O以及存儲資源,提高性能.因為它們只需要記錄在master上所執(zhí)行的語句的細節(jié)以及執(zhí)行語句時候的上下文信息.

缺點：在Statement模式下,由于它記錄的執(zhí)行語句,所以,為了讓這些語句在slave端也能正確執(zhí)行,那么它還必須記錄每條語句在執(zhí)行的時候的一些相關信息,也就是上下文信息,以保證所有語句在slave端被執(zhí)行的時候能夠得到和在master端執(zhí)行時候的結果.

另外,由于MySQL現(xiàn)在發(fā)展較快,很多的新功能不斷的加入,使MySQL的遇到了不小的挑戰(zhàn),自然的時候涉及到越復雜的內容,bug也就越容易出現(xiàn).在Statement中,目前已經(jīng)發(fā)現(xiàn)不少情況會造成MySQL的出現(xiàn)問題,主要是修改數(shù)據(jù)的時候使用了某些特定的函數(shù)或者功能的時候會出現(xiàn).

2、基于行 row

基于行的復制(row-based replication,RBR)

日志中會記錄成每一行數(shù)據(jù)被修改成的形式,然后在slave端再對相同的數(shù)據(jù)進行修改,只記錄要修改的數(shù)據(jù),只有value,不會有sql多表關聯(lián)的情況.

優(yōu)點：在row模式下,bin-log中可以不記錄執(zhí)行的sql語句的上下文相關信息,僅僅需要記錄哪一條記錄被修改了,修改成什么信樣了,所以row的日志內容會非常清楚的記錄下每一行數(shù)據(jù)修改的細節(jié),非常容易理解.而且不會出現(xiàn)在某些特定情況下的存儲過程和function,以及trigger的調用無法被正確問題.

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

3、混合方式 mixed

混合模式復制(mixed-based replication,MBR)

實際上就是前兩種模式的結合.在mixed模式下,MySQL會根據(jù)執(zhí)行的每一條具體的sql語句來區(qū)分對待記錄的日志形式,也就是在Statement和row之間選擇一種.Statement還是記錄執(zhí)行的語句.而新版本的MySQL中對row模式也做了優(yōu)化,并不是所有的修改都會以row模式來記錄,比如遇到表結構變更的時候就會以Statement模式來記錄,如果sql語句確實是update或者delete等修改數(shù)據(jù)的語句,那么還是會記錄所有行的變更.

3.二進制日志事件

position 基于位置

datetime 基于時間

4.二進制日志查看與刪除

mysql>show master status; 查看當前正在使用的二進制日志

mysql>show binlog events in ‘mysql-bin.000001’; 查看二進制日志記錄的事件[from position]

mysql>flush logs; 二進制日志滾動

mysql>show binary logs; 查看所有二進制日志

mysql>purge binary logs to ‘mysql-bin.000003’; 刪除二進制日志

5.查看二進制日志的命令

mysqlbinlog 相關選項,

–start-position #開始位置

–stop-position #結束位置

–start-datetime ‘yyyy-mm-dd hh:mm:ss’; #開始時間

–stop-datetime ”; #結束時間

6.配置MySQL的主配置文件

sql_log_bin = {ON|OFF} #用于控制二進制日志信息是否記錄進日志文件.默認為ON,表示啟用記錄功能.用戶可以在會話級別修改此變量的值,但其必須具有SUPER權限

binlog_cache_size = 32768 #默認值32768 Binlog Cache 用于在打開了二進制日志(binlog)記錄功能的環(huán)境,是 MySQL 用來提高binlog的記錄效率而設計的一個用于短時間內臨時緩存binlog數(shù)據(jù)的內存區(qū)域.一般來說,如果我們的數(shù)據(jù)庫中沒有什么大事務,寫入也不是特別頻繁,2MB～4MB是一個合適的選擇.但是如果我們的數(shù)據(jù)庫大事務較多,寫入量比較大,可與適當調高binlog_cache_size.同時,我們可以通過binlog_cache_use 以及 binlog_cache_disk_use來分析設置的binlog_cache_size是否足夠,是否有大量的binlog_cache由于內存大小不夠而使用臨時文件(binlog_cache_disk_use)來緩存了

binlog_stmt_cache_size = 32768 #當非事務語句使用二進制日志緩存,但是超出binlog_stmt_cache_size時,使用一個臨時文件來存放這些語句
log_bin = mysql-bin #指定binlog的位置,默認在數(shù)據(jù)目錄下

binlog-format = {ROW|STATEMENT|MIXED} #指定二進制日志的類型,默認為MIXED.如果設定了二進制日志的格式,卻沒有啟用二進制日志,則MySQL啟動時會產(chǎn)生警告日志信息并記錄于錯誤日志中.

sync_binlog = 10 #設定多久同步一次二進制日志至磁盤文件中,0表示不同步,任何正數(shù)值都表示對二進制每多少次寫操作之后同步一次.當autocommit的值為1時,每條語句的執(zhí)行都會引起二進制日志同步,否則,每個事務的提交會引起二進制日志同步

max_binlog_cache_size = {4096 .. 18446744073709547520} #二進定日志緩存空間大小,5.5.9及以后的版本僅應用于事務緩存,其上限由max_binlog_stmt_cache_size決定.

max_binlog_stmt_cache_size = {4096 .. 18446744073709547520} #二進制日志緩存空間大小,5.5.9及以后的版本僅應用于事務緩存

expire_log_days = {0..99} #設定二進制日志的過期天數(shù),超出此天數(shù)的二進制日志文件將被自動刪除.默認為0,表示不啟用過期自動刪除功能.如果啟用此功能,自動刪除工作通常發(fā)生在MySQL啟動時或FLUSH日志時

注：一般建議將binlog日志與數(shù)據(jù)文件分開存放,不但可以提高mysql性能,還可以增加安全性!

事務日志

說明：詳細的記錄了在什么時間發(fā)生了什么時候,在哪個時間對哪些數(shù)據(jù)進行了改變,能后實現(xiàn)事件的重放,一般只記錄對數(shù)據(jù)進行改變的操作,對于讀操作一般不進行記錄.

事物日志為數(shù)據(jù)庫服務器實現(xiàn)以下功能：

(1).將隨機IO轉換為順序IO,大大的提高了數(shù)據(jù)庫的性能,存儲的數(shù)據(jù)可能存在在磁盤的不同位置,降低了數(shù)據(jù)的讀取和操作性能.轉換為順序IO的原理為,先將數(shù)據(jù)存放在日志文件中,然后由RDBSM的后臺將日志中的數(shù)據(jù)存放到磁盤上,這樣就保證了存儲的數(shù)據(jù)是連續(xù)的.

(2).為事件重放提供基礎,事務日志詳細的記錄了時間發(fā)生的時間以及操作的數(shù)據(jù)對象,事務進程可以根據(jù)這些信息進行時間重放.

默認的事務日志文件有兩個,位于數(shù)據(jù)目錄下以ibdata+number結尾的數(shù)字,我們可以對事務日志的位置、文件大小、增長方式進行定義,定義的方法如下：

這里以使用支持事務的Innodb存儲引擎為例, 配置mysql的主配置文件：

innodb_data_home_dir = /mydata/data #InnoDB所有共享表空間數(shù)據(jù)文件的目錄路徑,默認在數(shù)據(jù)目錄下

innodb_data_file_path = ibdata1:1024M #指定InnoDB的各個數(shù)據(jù)文件及其大小,文件多于一個時彼此間用分號隔開

innodb_data_file_path = ibdata2:50M:autoextend #定義數(shù)據(jù)大小的增長方式

innodb_log_group_home_dir = /mydata/data #設定InnoDB重要日志文件的存儲目錄.在缺省使用InnoDB日志相關的所有變量時,其默認會在數(shù)據(jù)目錄中創(chuàng)建兩個大小為5MB的名為ib_logfile0和ib_logfile1的日志文件

innodb_log_files_in_group = {2 .. 100} #設定日志組中日志文件的個數(shù).InnoDB以循環(huán)的方式使用這些日志文件.默認值為2

innodb_log_file_size = {108576 .. 4294967295} #設定日志組中每個日志文件的大小,單位是字節(jié),默認值是5MB.較為明智的取值范圍是從1MB到緩存池體積的1/n,其中n表示日志組中日志文件的個數(shù).日志文件越大,在緩存池中需要執(zhí)行的檢查點刷寫操作就越少,這意味著所需的I/O操作也就越少,然而這也會導致較慢的故障恢復速度

innodb_log_buffer_size = {262144 .. 4294967295} #設定InnoDB用于輔助完成日志文件寫操作的日志緩沖區(qū)大小,單位是字節(jié),默認為8MB.較大的事務可以借助于更大的日志緩沖區(qū)來避免在事務完成之前將日志緩沖區(qū)的數(shù)據(jù)寫入日志文件,以減少I/O操作進而提升系統(tǒng)性能.因此,在有著較大事務的應用場景中,建議為此變量設定一個更大的值

錯誤日志

說明：在對應的數(shù)據(jù)目錄中,以主機名+.err命名的文件,錯誤日志記錄的信息類型：

記錄了服務器運行中產(chǎn)生的錯誤信息

記錄了服務在啟動和停止是所產(chǎn)生的信息

在從服務器上如果啟動了復制進程的時候,復制進程的信息也會被記錄

記錄event錯誤日志

配置mysql的主配置文件：

log_error = /mydata/data/mysql.test.com.err #指定錯誤日志的位置,默認是在數(shù)據(jù)目錄下,這個位置mysql用戶必須有寫權限

log_warning = {0|1} #默認開啟,服務器運行中的警告日志也會記錄在錯誤日志中

一般查詢日志

說明：對除了慢查日志中記錄的查詢信息都將記錄下來,這將對服務器主機產(chǎn)生大量的壓力,所以對于繁忙的服務器應該關閉這個日志.

與查詢日志相關的變量

log = {ON|OFF} #是否啟用查詢日志,該指令在mysq5.6中已廢棄

general_log = {ON|OFF} #啟動或關閉查詢日志,默認是關閉的

general_log_file = /mydata/data/mysql.log #指定查詢日志的位置,默認在數(shù)據(jù)目錄下

log_output = {TABLE|FILE|NONE} #指定存放查詢日志的位置,可以放在文件中,也可以放在數(shù)據(jù)庫的表中,放在表中比放在文件中更容易查看

中繼日志

說明：主要是在mysql服務器的中從架構中的從服務器上用到的, 當從服務器想要和主服務器進行數(shù)據(jù)的同步時, 從服務器將主服務器的二進制日志文件拷貝到己的主機上放在中繼日志中, 然后調用SQL線程按照中繼日志文件中的二進制日志文件執(zhí)行就可達到數(shù)據(jù)的同步

開啟的方法：(只在從服務器上開啟)

配置mysql的主配置文件：

relay-log = file_name
#指定中繼日志的位置和名字,默認為host_name-relay-bin.也可以使用絕對路徑,以指定非數(shù)據(jù)目錄來存儲中繼日志

relay-log-index = file_name #指定中繼日志的名字的索引文件的位置和名字,默認為數(shù)據(jù)目錄中的host_name-relay-bin.index

relay-log-info-file = file_name #設定中繼服務用于記錄中繼信息的文件,默認為數(shù)據(jù)目錄中的relay-log.info

relay_log_purge = {ON|OFF} #設定對不再需要的中繼日志是否自動進行清理.默認值為ON

relay_log_space_limit = 0 #設定用于存儲所有中繼日志文件的可用空間大小.默認為0,表示不限定.最大值取決于系統(tǒng)平臺位數(shù)

max_relay_log_size = {4096..1073741824} #設定從服務器上中繼日志的體積上限,到達此限度時其會自動進行中繼日志

慢查詢日志

說明：默認為關閉狀態(tài),記錄下來查詢時間超過設定時長的查詢,這些查詢日志將被慢查日志記錄下來

配置mysql的主配置文件：

slow_query_log = {ON | OFF} #是否開啟慢慢查詢日志,默認是關閉的

slow_query_log_file = /mydata/data/mysql-slow.log #慢查詢日志的存放位置,默認在數(shù)據(jù)目錄下

log_query_time = 10 #定義默認的時長,默認時長為10秒

log_query_not_using_indexes = {ON|OFF} #設定是否將沒有使用索引的查詢操作記錄到慢查詢日志

log_output = {TABLE|FILE|NONE} #定義一般查詢日志和慢查詢日志的保存方式,可以是TABLE、FILE、NONE,也可以是TABLE及FILE的組合(用逗號隔開),默認為FILE.如果組合中出現(xiàn)了NONE,那么其它設定都將失效,同時,無論是否啟用日志功能,也不會記錄任何相關的日志信息

后記

第一篇 MySQL系列連載 之日志類型(1)

第二篇 MySQL系列連載 之主從復制原理(2)

第三篇 MySQL系列連載 之多實例主從(異步)復制實踐(3)

第四篇 Mysql系列連載 之多實例主從復制(半同步)復制實踐(4)

第五篇 MySQL系列連載 之復制工具(percona-toolkit)實踐(5)

感謝您的閱讀.

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