MYSQL資料庫-物理檔案

標籤：mysql   記錄檔   

物理檔案組成

記錄檔：錯誤記錄檔，查詢日誌，慢查詢日誌，交易記錄，二進位日誌

日誌是 mysql 資料庫的覺要組成部分。記錄檔中記錄珩 mysql 資料庫運行期間發生的變化，也就是說用來記錄 mysql 資料庫的用戶端串連狀況、 SQL 陳述式的執行情況和錯誤資訊

1.錯誤記錄檔（error log）

錯誤記錄檔功能預設是開啟的，儲存在mysql資料庫的資料目錄中。

錯誤記錄檔檔案通常名稱為hostname.err    hostname表示伺服器主機名稱

錯誤記錄檔資訊也可以自己進行配置，通過log-error和log-warnings，前者是定義儲存位置後者是定義是否將警告資訊也定義到錯誤記錄檔中

注意，當首次安裝mysql5.7時初始密碼會儲存在錯誤記錄檔中

2.二進位檔案（binlog）

用於記錄修改資料或有可能引起資料改變的mysql語句，井且記錄語句發生時間執行時間長度，操作的資料等

可以通過    show global variables like ‘%log_bin%‘;    進行查詢

在主設定檔中加入     log-bin=file_name    開啟記錄功能，

MYSQL會將所有修改資料庫資料的query以二進位形式記錄到記錄檔中，記錄檔中還包括每一條query所執行的時間，所消耗的資源，以及相關的事務資訊

binlog的附加選項參數

“max_binlog_size”    設定binlog的最大儲存上限，一般設定為512M或1G，不可以超過1G當日誌達到上限時mysql會重新建立一個日誌並繼續開始記錄

“binlog-do-db=db_name”    對某個db_name（資料庫）記錄binlog，

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

"binlog-ignore-db=db_name"與"binlog-do-db=db_name"完全相反，它顯式指定忽略某個(db_name)資料庫的binlog記錄， 當指定了這個參數之後， MySQL會記錄指定資料庫以外所有的資料庫的binlog。

啟動binlog記錄後，mysql資料庫目錄下還會產生一個mysql-bin.index檔案

主要功能是記錄所有Binary Log 的絕對路徑， 保證MySQL各種線程能夠順利的根據它找到所有需要的Binary Log檔案

MYSQL複製主要有三種方式：

基於SQL語句的複製(statement-based replication, SBR),

基於行的複製(row-based replication, RBR), 

混合模式複製(mixed-based replication, MBR)

對應的，binlog的格式也有三種： STATEMENT, ROW, MIXED。

statenment模式（SBR）

每一條會修改資料的sql語句會記錄到binlog中，優點是不需要記錄每一行的資料變化

減少了binlog日誌量，節約IO，提高效能，缺點是在某些情況下master-slave中的資料不一致

row模式（RBR）

不記錄每條SQL語句的資訊，僅記錄那條資料被修改了，改成什麼樣子，缺點是會產生大量日誌，日誌暴漲

mixed模式（MBR）

混合模式，以上兩種模式混合使用，一般的複製使用statement模式儲存binlog，對於statement模式無法複製的操作使用row模式儲存binlog，mysql會自行判斷何時使用

“sync_binlog=10”    設定多久同步一次二進位日誌到磁碟檔案中 0表示不同步，1表示每條語句都同步

幾種查詢二進位日誌資訊的方法

show binlog events\G;     查看所有二進位資訊

show bin log events in‘mysql-bin.000001‘\G;     查看指定的二進位資訊

show binlog events in ‘mysql-bin.000001‘from 727;     從指定的事件位置開始

mysqlbinlog    mysql-bin.00001    在命令列下查看

幾種刪除二進位日誌資訊的方法

purge binary logs to ‘mysql-bin.000006‘；    刪除‘mysql-bin.000006‘之前的二進位檔案

reset master    刪除所有二進位檔案並重建一個為進位檔案

3.交易記錄

交易記錄 (InnoDB 特有的日誌）可以協助提高事務的效率。

使用交易記錄， 儲存引擎在修改表的資料時只需要修改其記憶體拷貝，再把修改行為記錄到持久在硬碟上的交易記錄中，而不用每次都將修改的資料本身持久到磁碟。交易記錄採用追加的方式，因此寫日誌的操作是磁碟上一小塊地區內的順序I/O, 而不像隨機I/O需要在磁碟的多個地方移動磁頭， 所以採用交易記錄的方式相對來說要快得多。

交易記錄持久以後，記憶體中被修改的資料在後台可以慢慢的刷回到磁碟。 

4.慢查詢日誌

慢查詢日誌中記錄的是執行時間較長的query，採用簡單的文字格式設定記錄方便查看

通過慢查詢日誌，可以尋找出哪些查詢語句的執行效率很低，以便進行最佳化 

同樣也是在主配置中加入

“slow_query_log=1”開啟，

“slow_query_log_file=/usr/local/mysql/data/mysqld-slow.log”儲存位置

  "long_query_time=1"    需要記錄的時間，

或者直接在資料庫中直接定義。

分析日誌

進入慢查詢日誌存放目錄使用mysqldumpslow分析    

“mysqldumpslow     mysqld-slow.log”

本文出自 “JianYu” 部落格，請務必保留此出處http://jianyu97.blog.51cto.com/12222102/1983293

MYSQL資料庫-物理檔案