MySQL錯誤記錄檔、二進位日誌、慢查詢日誌、交易記錄

標籤：資料一致性   for   一起   replicat   date   缺點   因此   函數   myisam   

錯誤記錄檔：Error log

預設情況下錯誤記錄檔大概記錄以下幾個方面的資訊：

1、伺服器啟動和關閉過程中的資訊（未必是錯誤資訊，例如，mysql如何啟動INNODB的資料表空間檔案的、如何初始化自己的儲存引擎的等）

2、伺服器運行過程中的錯誤資訊

3、事件調度器運行一個事件時產生的資訊

4、在從伺服器上啟動伺服器處理序時產生的資訊

注意：

1、可以根據自身需求設定不同錯誤記錄檔的值

1=只記錄 Errors 層級的日誌

2=記錄Errors、warnings 層級的日誌

3=記錄Errors、warnings、notes（defaults）層級的日誌

2、如何刪除舊的錯誤記錄檔

在mysql5.7之前：資料庫管理員可以刪除很長時間之前的錯誤記錄檔，以保證mysql伺服器上的硬碟空間。mysql資料庫中，可以使用mysqladmin命令開啟新的錯誤記錄檔：

命令文法如下：mysqladmin -u root  -p flush_logs 也可以登陸mysql資料庫中使用flush logs 語句來開啟新的錯誤記錄檔

在5.7之後：伺服器將關閉此項功能。只能使用重新命名原來的錯誤記錄檔檔案，手動沖洗日誌建立一個新的

方式如下：mv mysqld.err mysqld.err.old

二進位日誌：Binary Log & Binary Log Index

二進位日誌也就是我們常說的binlog日誌，也是MySQL server 中最重要的日誌之一，主要用於記錄修改資料或有可能引起資料改變的mysql語句，並記錄了：

1、語句發生時間

2、執行時間長度

3、操作的資料

4、等等

所以說通過二進位日誌可以查詢mysql資料庫中進行了那些變化

一般體積上限為1G

當我們通過“log-bin=file_name”開啟了記錄的功能之後，MySQL會將所有修改資料庫資料的query以二進位形式記錄到記錄檔中。當然，日誌中不僅有query語句這麼簡單，還包括每一條query所執行的時間，所消耗的資源，以及相關的事務資訊，所以binlog是事務安全的。

和錯誤記錄檔一樣，binlog記錄功能同樣需要“bin-log=file_name”參數的顯示指定才能開啟，如果未指定file_name，則會在資料目錄下記錄為mysql-bin.*（*代表0~9之間的某一個數字，來表示該日誌的序號）

bin-log還有其他一些附加選項參數：

max_binlog_size:來設定binlog的最大儲存上限，一般設為512M或1G，一般不能超過1G，當日誌達到該上限時，MySQL會重新建立一個日誌繼續記錄。不過偶爾也有超過該設定的binlog產生，一般都是因為在即將達到上限時，產生了一個較大的事務，為了保證事務的安全，mysql不會將同一個事務記錄到兩個binlog中。

binlog-do-db=db_name：如果有了該參數的顯示指定，MySQL會忽略針對其他資料庫執行的query，而僅僅記錄針對指定資料庫執行的query。

“binlog_ignore_db=db_name”與“binlog-do-db=db_name” 完全相反，他顯示指定忽略某個資料庫的binlog記錄，當指定了這個參數之後，MySQL會記錄指定資料庫以外所有的資料庫的binlog。

“binlog-do-db=db_name”與“binlog_ignore_db=db_name”兩個參數有一個共同的概念：

參數中的db_name不是值query語句更新的資料庫所在的資料庫，而是執行query的時候當前所處的資料庫。不論更新那個資料庫的資料，MySQL僅僅比較當前串連所處的資料庫（通過use db_name 切換後所在的資料庫）與參數設定的資料庫同名，而不會分析query語句所更新資料所在的資料庫。

其中，mysql_bin.index檔案（binary log index）的功能是記錄所有Binary Log 的絕對路徑，保證MySQL 各種線程能夠順利的根據他找到所有需要的Binary Log 檔案。

bin_cache_size=32768（預設）

bin_cache_size ：一個事務，在沒提交（uncommitted）的時候，產生的日誌，記錄到cache中：等到事務提交（committed）需要提交的時候，則把日誌持久化到磁碟。一般來說，如果我們資料庫中沒有什麼大事務，寫入也不是特別頻繁，2MB~4MB是一個合適的選擇。但是如果我們的資料庫大事務較多，寫入量較大，可以適當調高binlog_cache_size。同時，我們可以通過binlog_cache_use以及binlog_cache_disk_use來分析設定的binlog_cache_size是否足夠，是否有大量的binlog_cache由於記憶體大小不夠而使用臨時檔案來緩衝了

binlog_stmt_cache_size=32768  #當非事務語句使用二進位日誌緩衝，但是超出binlog_stmt_cache_size時，使用一個臨時檔案來存放這些語句。

binlog-format={ROW|STATEMENT|MIXED}#指定二進位日誌的類型，預設為MIXED。

1、STATEMENT模式（SBR）

每一條會修改資料的sql語句會記錄到binlog中。

優點是並不需要記錄每一行的資料變化，減少了binlog日品質，節約IO，提高效能，

缺點是在某些情況下會導致master-slave中的資料不一致（如sleep（）函數，last__insert_id()，以及user-defind functions（udf）等會出現問題）

2、ROW模式（RBR）

不記錄每條sql語句的資訊，僅需記錄那條資料被修改了，修改成什麼樣了

缺點是會產生大量的日誌，使日誌暴漲。

3、MIXED模式（MBR）

以上兩種模式的混合使用，一般的複製使用STATEMENT模式儲存binlog，對於STATEMENT模式無法複製的操作使用ROW模式儲存binlog，MySQL會根據執行的sql語句選擇日誌儲存方式。即交替使用行和語句、由MySQL伺服器自行判斷。 

其中基於行的定義格式資料量會大一些但是可以保證資料的準確性。

sync_binlog=10 #設定多久同步一次二進位日誌至磁碟檔案中，0表示不同步，任何正數值都表示對二進位沒多少次寫操作之後同步一次。當autocommit的值為一時，每條語句執行都會引起二進位日誌同步，否則，每個事務的提交會引起二進位日誌的同步。

max_binlog_cache_size #二進位日誌緩衝空間大小，5.5.9即以後的版本僅用於事務緩衝，其上線由max_stmt_cache_size決定。

expire_log_days={0，99} #設定二進位日誌日誌的到期天數，超過次天數的二進位日誌將會自動刪除，預設為0，表示不啟用到期自動刪除功能。如果啟用此功能，自動刪除通常發生在MySQL啟動時或FLUSH日誌時。

當前二進位檔案及所處位置

show binary logs；

查看二進位檔案資訊

show master status；

查看所有的二進位資訊

show binlog event\G

查看指定日誌的二進位資訊

show binlog events in 'mysql-bin.*' \G

**命令列下查看二進位日誌

mysqlbinlog  “binlog_name”

刪除二進位日誌資訊

purge {binary|master} logs {to ‘log_name’ | before datetime_expr}

例：purge binary logs to 'mysql-bin.000006'

刪除所有的二進位日誌（慎用）

reset master；

不建議生產環境使用此操作

交易記錄：（或稱redo日誌）

交易記錄（InnoDB特有的日誌）可以協助提高事務的效率。使用交易記錄，儲存引擎在修改表的資料時只需要修改其記憶體拷貝，再把修改行為記錄到持久在硬碟上的交易記錄中，而不用每次都將修改的資料持久到硬碟。交易記錄採用追加的方式，因此寫日誌的操作是磁碟上一小塊地區內的順序I/O，而不像隨機I/O需要在多個地方移動磁頭，所以採用交易記錄的方式相對來說要快的多。交易記錄持久以後，記憶體中被修改的資料在後台可以慢慢刷回磁碟。目前大多數的儲存引擎都是這樣實現的。

如果事務的修改已經記錄到了交易記錄並持久化，單資料本身還沒有寫會磁碟，此時系統崩潰，儲存引擎再重啟時能夠自動回復這部分修改的資料。具有的恢複方式則視儲存引擎而定。

查看mysql已提供什麼養的搜尋引擎

show engines

查看mysql當前預設的儲存引擎

show variables like '%storage_engine%'

查看某個表用了什麼引擎

show create teble 表名；

改變表的儲存引擎

create table 庫名.表明 engine=“innodb”

查看交易記錄的定義

show global variables like 'innodb_flush_log_at%'

#在事務提交時innodb是否同步處理記錄從緩衝區到檔案中，

當這個值為1（預設值）之時，在每個事務提交時，日誌被寫到記錄檔，對記錄檔做到磁碟操作的重新整理，效能會很差造成大量的磁碟I/O但這種方式最安全：

如果設為2，每次提交事務都會寫到日誌，但並不會執行刷操作。每秒定時刷到記錄檔,並不能保證100%每秒一定會重新整理到磁碟，這要取決於進程的調度。

設定為0，日至緩衝每秒一次的被寫入到記錄檔，並且對記錄檔做到磁碟操作的重新整理，但是在一個事物提交不做任何操作。

**刷寫的概念

刷寫其實是兩個操作，刷（flush）和寫（write），區分這兩個概念是很重要的。在大多數的作業系統中，把InnoDB的log buffer （記憶體）寫入日誌（調用系統調用write），只是簡單的把資料轉移到作業系統緩衝中，作業系統緩衝同樣指的是記憶體。並沒有持久化資料。

所以在0和2的時候，在崩潰或斷電的時候會丟失最後一秒的資料，因為這個時候資料只存在於作業系統的緩衝。之所以說通常，可能會丟失不止一秒的資料的情況，比如說執行flush操作的時候阻塞了。

總結：

設為1當然是最安全的，但是效能也是最差的（相對於其他兩個參數而言，但不是不能接受）。如果對資料一致性和完整性要求不高，完全可以設為2，如果只要求效能，例如高並發寫的Log Service器，設為0來獲得更高的效能。

慢查詢日誌： slow query log

顧名思義，慢查詢日誌中記錄的是執行時間較長的query，也就是我們常說的 slow query。慢查詢日誌採用的是簡單的文字格式設定，可以通過各種文字編輯器查看其中的內容。其中記錄了語句執行的時刻，執行所消耗的時間，執行使用者，串連主機等相關資訊。

慢查詢日誌的作用：

慢查詢日誌是用來記錄執行時間較長的query，也就是我們常說的slow query 。

通過慢查詢日誌可以查出來那些查詢語句執行效率比較低，以便進行最佳化，一般建議開啟，他對伺服器效能的影響微乎其微，但是可以記錄mysql伺服器上執行了很長時間的查詢語句。可以協助我們定位效能問題。mysql還提供了專門用來分析慢查詢日誌的工具程式mysqldumpslow，用來協助資料庫管理員解決可能存在的效能問題。

查看慢查詢日誌的定義

show global variables like '%slow_query_log%'

啟動和設定慢查詢日誌

方法一：通過my.cnf開啟慢查詢日誌

方法二：登陸MySQL伺服器直接設定（使用set_query_log=1）

大多是版本都可以使用show variables like '%slow%' 或 show variables like '%long%'

其中

slow_query_log；off關閉狀態，on開啟狀態

slow_query_log_file: 慢查詢日誌存放地點

long_query_time： 設定時間值，時間以秒為單位，可以精確到微秒，如果超過了這個時間（預設是10秒）這個查詢語句將記錄到慢查詢日誌當中。設定為0的話，表示記錄所有的查詢。

註：

1、如果不指定路徑，預設儲存到mysql資料庫的資料檔案下，如果不指定檔案名稱，預設檔案名稱為hostname-slow.log

2、將Unix時間轉成一個可讀的時間，可以使用date [email protected]日誌中的時間戳記

3、mysqldumpslow 選項 參數

-s 是表示按照何種方式排序 ，c，t，l，r 分別是按照記錄次數，時間，查詢時間，返回的記錄數來排序，ac，at，al，ar表示相應的倒序。

-t ，是top n的意思，即為返回前面多少條的資料

-g，後面可以寫一個Regex，大小寫不敏感的

檔案類型：

1、.frm 檔案

與表相關的中繼資料（meta）資訊都存放在“.frm”檔案中，包括表結構的定義資訊等。不論是什儲存引擎專用麼儲存引擎，每一個表都會有一個以表命名的.frm檔案。存放在所屬資料庫的檔案夾下面

2、MyISAM資料庫表檔案：.MYD檔案：表資料檔案  .MYI：表索引檔案

3、InnoDB採用資料表空間（tablespace）來管理資料，儲存表資料和索引

.ibd檔案：但表資料表空間檔案，每個表使用一個資料表空間檔案（file per table），存放使用者資料庫表資料和索引

InnoDB共用資料表空間（即InnoDB檔案集，ib-file set）：ibdata1、ibdata2等，儲存InnoDB系統資訊和使用者資料庫表資料和索引，所有表共用

4、.ibd檔案和ibdata檔案

這兩種檔案都是存放InnoDB資料的檔案，之所以有兩種檔案來存放InnoDB的資料（包括索引），是因為InnoDB的資料存放區方式能夠通過配置來決定是使用共用資料表空間存放儲存資料，還是獨享資料表空間來儲存資料。

獨享資料表空間：.ibd

共用資料表空間：.ibdata

ibdata檔案可以通過

innodb_data_file_path 配置資料存放的總目錄

可以一次配置多個idbata檔案，檔案可以指定大小，也可以設為自動擴充，但只有最後一個ibdata檔案可以設定為自動擴充。

必須重啟才能完成ibdata的添加工作

總結：

共用資料表空間以及獨佔資料表空間都是針對資料的儲存方式而言的

共用資料表空間：某一個資料庫的所有表資料，索引檔案全部放在一個檔案中

獨佔資料表空間：每一個都將會產生以獨立的檔案方式來進行儲存，每一個表都有一個.frm表描述檔案，還有一個。ibd檔案，其中這個檔案包括了單獨一個表的資料內容以及索引內容

兩者之間的優缺點：

共用資料表空間

優點：

可以放資料表空間分成多個檔案存放到各個磁碟上。資料和檔案放在一起方便管理

缺點：

所有資料和索引都放在一個檔案中，多個表及索引在資料表空間中混合儲存，這樣對於一個表做了大量刪除操作後資料表空間中將會有大量的空隙，特別是對於統計分析，日值系統這類應用最不適合用共用資料表空間。

獨立資料表空間

優點：

1、每個表都有自己獨立的資料表空間

2、每個表的資料和索引都會存在自己的資料表空間中

3、可以實現單表在不同資料庫中移動

4、空間可以回收

a、Drop table 操作自動回收資料表空間，如果對於統計分析或是日值表，刪除大量資料以後，可以通過：altertable TableName engine = innodb;回縮不用的空間

b、對於使用獨立資料表空間的表，不管怎麼刪除，資料表空間的片段不會太嚴重的影響效能，而且還有機會處理。

缺點：

單表增加過大，如超過100個G

相比之下，使用獨佔資料表空間的效率和效能會更高一點

ON代表獨立資料表空間， OFF代表共用資料表空間

replication（預存程序）相關檔案

1、master.info檔案

master.info檔案存在於Slave端的資料目錄下，裡面存放了該Slave的Master端的相關資訊，包括master的主機地址、串連使用者、串連密碼、串連連接埠、當前日誌位置，已經讀取到的日誌位置資訊。

2、relay log 和relay log index

mysql-relay-bin.xxxxxn檔案用於存放Slave端的I/O線程從master端所讀取到的binary log資訊，然後由Slave端的SQL線程從該relay log 中讀取並解析相應的日誌資訊，轉化成master所執行的SQL語句，然後再Slave端應用

mysql-relay-bin.index檔案的功能類似於mysql-bin.index，同樣是記錄日誌的存放位置的絕對路徑，只不過她所記錄的不是binary log 而實relay log

3、relay-log.index檔案

類似於master.info ，它存放通過Slave的I/O線程寫入到本地的relay log 的相關資訊，共slave端的SQL線程及某些管理操作隨時能夠擷取當前複製的相關資訊。

其他檔案：

1、system config file

MySQL的系統設定檔一般是 my.cnf

2、pid file

pid file 是mysql應用程式在unix/linux環境下的一個進程檔案

3、socket file

socket檔案只在unix/linux 環境下才有，可直接只用socket檔案來串連mysql，，速度比TCP的要快，但是只適用於mysql和應用在一台PC上。

總結：

1、查看系統設定

show [global|session] variables [like_or_where]

2、運行狀態

show [global|session] status [like_or_where]

3、重新整理日誌

flush log

MySQL錯誤記錄檔、二進位日誌、慢查詢日誌、交易記錄