MySQL日誌管理

標籤：mysql日誌管理

一：日誌的組成部分

         1.1錯誤記錄檔

         1.2一般查詢日誌

         1.3慢查詢日誌

         1.4二進位日誌

         1.5中繼日誌

         1.6交易記錄

 

二：儲存引擎介紹

         2.1 MyISAM

         2.2 InnoDB

         2.3 MRG_MYISAM

         2.4 CSV

         2.5 ARCHIVE
         2.6 MEMORY

         2.7 BLACKHOLE

1.1錯誤記錄檔：

    主要是用於記錄伺服器運行當中的錯誤資訊

    伺服器啟動或者關閉過程中產生的資訊

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

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

    警告資訊

1.2一般查詢日誌：

    任何一個查詢語句所產生的日誌資訊都要記錄下來，所以資訊量比較大，所以一般查詢日誌預設是不開啟的

1.3慢查詢日誌：

    一般查詢日誌和慢查詢日誌都是查詢日誌，只不過慢查詢日誌記錄的是查詢時間比較長的，超出long_query_time定義的時間長度，預設為10秒，慢查詢，可以啟動慢查詢日誌，以記錄哪些查詢的執行速度比較慢，幫我們去定位問題的，定位服務器效能問題，

但是執行查詢速度慢，並不意味著，這個查詢本身慢，可能是因為其它資源佔用導致它無法擷取有效資源而無法執行查詢，以為慢查詢的查詢時間評估是根據它從開始啟動這個查詢到，查詢結束這個時間，而不是在CPU上執行的時間

1.4二進位日誌

1.4.1二進位日誌當中所記錄的每一個資訊或者每一個操作，我們稱之為一個二進位日誌事件

1.4.2二進位日誌的主要功能

    1.複製

    2.即時間點復原

1.4.3二進位日誌事件是基於什麼來實現的呢？

    1.可以基於語句：statement

    2.還可以基於行：row

    3.混合方式（基於語句和基於行）：mixed

1.4.4基於行好？還是基於語句好呢？

    基於語句：可能一條語句會操縱大量資料，但有可能會帶來非精確值，例如帶目前時間的操作

 

    基於行：直接複製這個語句的結果，直接複製相關資料而不是語句，以便產生精確值

                  

    混合方式：mixed

1.4.5二進位日誌的存放位置

    二進位日誌自己會滾動的，預設存放在資料目錄下，但是建議一定不要和資料目錄放到同一個存放裝置上，因為每一次存放裝置的IO操作（就是每一次資料的寫操作），都會引起二進位日誌的寫操作，所以兩者都會產生IO，那也就意味著，放到同一塊磁碟上，兩個IO會產生競爭，所以這本身就會帶來效能低下的狀況，所以建議一定不要存放到同一塊磁碟上，

而且為了避免由於資料檔案所在是裝置損壞，也導致二進位記錄檔損壞，所以要分開儲存

（總結：兩個原因：1效能。2：資料可靠性、安全性）

    

1.4.6查看當前系統上被伺服器識別的二進位日誌

mysql> SHOW BINARY logs;

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M01/6C/EE/wKioL1VXecrTIsBSAAFWCrRqtW0032.jpg" title="1.png" alt="wKioL1VXecrTIsBSAAFWCrRqtW0032.jpg" />

1.4.7當前使用的二進位日誌

mysql> SHOW MASTER STATUS;

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M01/6C/F4/wKiom1VXeOWCg3e0AACym968lWY507.jpg" title="2.png" alt="wKiom1VXeOWCg3e0AACym968lWY507.jpg" />

1.4.8查看某個日誌中的事件

文法：mysql>SHOW BINLOG EVENTS IN ‘二進位檔案名‘ [FROMPOSITION];

mysql> SHOW BINLOG EVENTS IN ‘mysql-bin.000006‘;

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M02/6C/EE/wKioL1VXerjh0sVSAAENxe8HJvk953.jpg" title="3.png" alt="wKioL1VXerjh0sVSAAENxe8HJvk953.jpg" />

mysql> SHOW BINLOG EVENTS IN ‘mysql-bin.000006‘ FROM 379;

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M00/6C/EE/wKioL1VXeu6TrtBvAADJEOeN6lE081.jpg" title="4.png" alt="wKioL1VXeu6TrtBvAADJEOeN6lE081.jpg" />

1.4.9在備份以後，需要清除某個記錄檔之前的記錄檔

mysql> PURGE BINARY LOGS TO ‘mysql-bin.000006‘;

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M00/6C/F4/wKiom1VXeazwNF8pAADac-BCMQM817.jpg" title="5.png" alt="wKiom1VXeazwNF8pAADac-BCMQM817.jpg" />

1.4.10手動滾動一下二進位日誌

mysql> FLUSH LOGS;

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M01/6C/EE/wKioL1VXe2GTQjZkAADUihvXtVQ947.jpg" title="6.png" alt="wKioL1VXe2GTQjZkAADUihvXtVQ947.jpg" />

1.4.11詳細查看資料庫輸出二進位記錄檔中的內容

mysqlbinlog     --start-position    --stop-position    --start-datetime ‘yyyy-MM-dd hh:mm:ss‘    --stop-datetime ‘yyyy-MM-dd hh:mm:ss‘

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M02/6C/EE/wKioL1VXe_Ciin02AAGOa06g6zk166.jpg" style="float:none;" title="7.png" alt="wKioL1VXe_Ciin02AAGOa06g6zk166.jpg" />

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M02/6C/F4/wKiom1VXenbDK80rAADNWCfnT0I166.jpg" style="float:none;" title="8.png" alt="wKiom1VXenbDK80rAADNWCfnT0I166.jpg" />

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M00/6C/EE/wKioL1VXe_Hj0pQMAAFOtCgS4j8976.jpg" style="float:none;" title="9.png" alt="wKioL1VXe_Hj0pQMAAFOtCgS4j8976.jpg" />

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M00/6C/F4/wKiom1VXenfwDSuHAABVz4kS1_Y076.jpg" style="float:none;" title="10.png" alt="wKiom1VXenfwDSuHAABVz4kS1_Y076.jpg" />

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M01/6C/EE/wKioL1VXfAawp2I4AAFmp7PSR5g982.jpg" title="11.png" alt="wKioL1VXfAawp2I4AAFmp7PSR5g982.jpg" />

1.4.12和log相關的伺服器變數說明

mysql> SHOW GLOBAL VARIABLES LIKE ‘%log%‘;

binlog_format：二進位日誌的格式

 

binlog_cache_size：二進位日誌緩衝大小，它的值的上限取決於binlog_stmt_cache_size

 

binlog_stmt_cache_size：和事務相關的二進位日誌語句緩衝大小

 

log_bin：是否啟用二進位日誌，所以上面的緩衝大小（binlog_stmt_cache_size）是否生效還要取決於此指令

注意，二進位日誌功能的開啟，並不意味著它就一定會記錄二進位日誌，它是總開關，是要不要記錄二進位日誌，如果要記錄的話，它就可能給我們所指定的二進位記錄檔中記錄日誌了。這個log_bin它還可以接受一個檔案路徑，表示把二進位記錄檔記錄到另外一個位置，否則唯寫個on它就表示記錄在mysql的資料目錄下，叫mysql-bin0001  0002，它指定了二進位檔案是否啟用，到底記錄在哪個檔案中，而是否真正的記錄日誌，還要取決於另外一個指定sql_log_bin

sql_log_bin：是用於控制是否記錄二進位記錄檔的，而log_bin是指定二進位檔案的輸出路徑的，如果把它的值設定為off，就算你寫了很多DML或者DDL語句，它也不會記錄相關資訊。這個指令非常重要，尤其是在將來實現資料庫恢複的時候，比方說，mysql伺服器奔潰了，我做了邏輯備份，需要把備份檔案匯入到資料庫中，將資料恢複到某一個時刻在這個恢複的過程當中，應該把二進位日誌的功能關掉了，當恢複結束之後，再開啟

sync_binlog:多長時間同步一次二進位日誌

expire_logs_days：日誌的到期天數，例如值為30，那麼記錄檔超過30天以後會自動刪除，所以二進位日誌是可以自動的去清理的

1.4.13二進位日誌為什麼要做緩衝呢？

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M01/6C/F4/wKiom1VXfMvi1QBdAAOCIJuUP-I703.jpg" style="float:none;" title="12.png" alt="wKiom1VXfMvi1QBdAAOCIJuUP-I703.jpg" />

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M02/6C/EE/wKioL1VXfkWivMIwAAHXoIMiJMc052.jpg" style="float:none;" title="13.png" alt="wKioL1VXfkWivMIwAAHXoIMiJMc052.jpg" />

1.5中繼日誌

    從伺服器上，從主伺服器的二進位記錄檔中複製而來的事件並儲存為的記錄檔

1.6交易記錄：ACID將隨機IO轉換為順序IO

    事務性儲存引擎用於保證原子性、一致性、隔離性和持久性

    innodb_flush_log_at_trx_commit={0|1|2}

        0:沒秒同步，並執行磁碟flush操作

        1:每事務同步，並執行磁碟flush操作

        2:每事務同步，但不執行磁碟flush操作

    innodb_log_buffer_size：記憶體緩衝大小

    

    innodb_log_file_size：記錄檔大小

    

    innodb_log_files_in_group：日誌寫滿了，需要向另外一個檔案去寫，這些檔案就構成交易記錄組，這裡的值為2表示，有兩個記錄檔

    innodb_log_group_home_dir：交易記錄存放的位置，預設為“./”，這個目前的目錄指的是資料目錄

    innodb_mirrored_log_groups：是否為日誌組做鏡像

2.儲存引擎介紹

2.1MyISAM：mysql5.5.8之前預設的儲存引擎

    資料存放區格式：

        表格式.frm

        資料檔案.MYD

        索引檔案.MYI

    特點：

        1.不支援事務，所以也沒有基於事務的特性ACID，所以就少了很多額外的開銷所以效能好，但是未必就一定效能好，也就是說事務性儲存引擎未必就效能差

        2.由於它不支援事務，導致它的很多操作都比較粗糙，比如，不支援外鍵。

        3.MyISAM預設情況下只支援表鎖，鎖粒度比較大，導致並發能力不強。鎖本身有些是共用的，比如讀鎖，所以在讀操作非常多的情境下，MyISAM有很好的表現而對於讀寫操作都一樣多的時候，MyISAM效能很差

        4.支援索引類型比較多，B樹索引，全文（FULLTEXT）索引，空間索引，表壓縮

2.2InnoDB：

    表格式.frm

    資料表空間.ibd 預設情況下MySQL將所有的事務表放到一個資料表空間中，我們要想使用很多InnoDB的進階特性，比如表壓縮，單表匯入匯出，備份  比如每表一個資料表空間

    事務性引擎

    行級鎖

    支援B樹索引，聚簇索引，自適應hash索引

    使用資料表空間，支援使用raw磁碟裝置，它可以將資料放在沒有檔案系統的磁碟分割上

2.3MRG_MYISAM：

         能夠將兩個或兩個以上的MyISAM表合并成一張表處理

 

2.4CSV：利用文字檔來儲存資料，方便移植，但不管理起來效能比較差

                

2.5ARCHIVE：歸檔的，主要目的是可以將表中的資料壓縮存放，只為了做資料採礦的

        

2.6MEMORY：記憶體儲存引擎，將表中的所有資料都放到記憶體裡面，效能特別好，但是資料安全性沒有保證

        

2.6BLACKHOLE：黑洞儲存引擎

本文出自 “凡夫俗子” 部落格，請務必保留此出處http://caoyt.blog.51cto.com/9978141/1652007

MySQL日誌管理