MySQL之日誌和mysqlbinlog工具的使用

MySQL之日誌和mysqlbinlog工具的使用

查看日誌相關的全域變數：
    mysql>SHOW GLOBAL VARIABLES LIKE '%log%'

修改相關變數：
    1.對於開關某個功能，使用SET GLOBAL xxx=xxx即可。
    2.涉及到檔案的操作，只能修改設定檔，新增內容到設定檔並重啟服務後生效。

錯誤記錄檔：
    記錄內容：
        伺服器的啟動和關閉產生的資訊
        伺服器運行過程中的錯誤資訊
        時間調度器上運行一個事件時產生的資訊
        在從伺服器上啟動和關閉從伺服器處理序時產生的資訊

    檔案位置：
        預設是存放在資料目錄，檔案名稱為SERVERHOSTNAME.err的檔案。

    相關變數：
        log_error 指定錯誤記錄檔檔案
        log_warning 是否記錄警告資訊，1表示記錄。預設為1。

一般查詢日誌：
    記錄內容：
        查詢的操作

    檔案位置：
        預設是存放在資料目錄，檔案名稱為SERVERHOSTNAME.log的檔案

    相關變數：
        general_log  是否啟用一般查詢日誌，預設是OFF。避免記錄大量的io。
        general_log_file 指定一般查詢記錄檔的位置
        log 是否記錄所有語句到日誌，預設為OFF。mysql5.6已廢棄。

        log_output 指定一般查詢日誌和慢查詢日誌的輸出位置，預設是使用TABLE。在編譯時間會變為FILE。可以使用TABLE表示記錄到表中或者NONE表示不記錄。TABLE和FILE可以一起使用，用逗號隔開即可。注意此變數優先順序高。一旦設定為NONE，即時general_log設定為ON，也不記錄。

        sql_log_off 控制是否禁止將一般查詢日誌資訊記錄進記錄檔，預設為OFF。

慢查詢日誌：
    記錄內容：
        查詢時間較長的操作

    檔案位置：
        預設在資料目錄名為SERVERHOSTNAME-slow.log。

    相關變數：
        long_query_time 指定查詢時間長度閾值，超過此時間都定義為慢查詢。注意此長為實際操作執行的時間長度而不是cpu執行時間長度。最小值為0，預設為10，單位為秒，支援毫秒級解析度。

        slow_query_log 是否啟用慢查詢日誌，日誌的輸出位置也取決於log_output的設定。

        slow_query_log_file 指定慢查詢記錄檔位置。
        max_long_data_size
        performance_schema_events_waits_history_long_size 收集事件等待曆史的長度

二進位日誌：
    記錄內容：
        記錄任何可能引起資料庫變化的操作，包括DDL，DML，授權語句等等。使用mysql專屬的二進位格式，為複製和即時間點復原提供支援。

        二進位日誌的格式：
            基於語句：statment
            基於行：row
            混合方式：mixed

            檔案頭+事件...

            事件的組成：
                position：上個事件的結束位置和下個事件的開始位置。
                starttime：事件的起始時間
                action：事件的動作。

    檔案位置：
        使用工具mysqlbinlog查看，一般的文本編輯檔案是無法產看的。

        二進位記錄檔：預設在資料目錄以mysql-bin或SERVERHOSTNAME開頭，末尾為.bin.00000NUM的檔案。每次重啟伺服器後日誌將進行滾動。保留舊檔案，新建立檔案。使用SHOW MASTER STATUS可以查看當前正在使用的檔案。使用SHOW BINLOG EVENTS in ‘mysql-bin.00000NUM’ [FROM POSITION] 查看具體內容。記錄檔的大小會大於資料的大小，因為裡邊還記錄了其他的額外資訊。

        索引檔案：記錄了二進位記錄檔的相關資訊。預設在資料目錄，名字為mysql-bin.index。

        建議將二進位記錄檔和資料分開存放在不同磁碟，這樣既能保證安全性，又能保證記錄檔之間不出現競爭io的情況。

        也可以執行FLUSH LOGS來手動滾動日誌。注意只有二進位和中繼日誌才是真的滾動，其他日誌都是關閉後開啟。

        使用SHOW BINARY LOGS查看日誌

        使用PURGE BINARY LOGS TO ‘BINLOGFILE’ 可以刪除指定檔案之前的記錄檔

    相關變數：
        binlog_format STATMENT|ROW|MIXED 指定二進位記錄檔的格式

        log_bin ON|OFF [FILE] 指定二進位記錄檔的位置，沒有指定FILE預設寫在資料檔案下，是否啟用二進位記錄檔功能。在mysql啟動時使用 --log-bin=mysql-bin或者是修改設定檔，在[mysqld]中添加log-bin=mysql-bin和binlog_format=mixed。

        sql_log_bin ON|OFF 控制是否將日誌寫入二進位記錄檔

        binlog_cache_size 緩衝大小，跟隨binlog_stmt_cache_size大小變化

        binlog_stmt_cache_size 語句緩衝大小

        sync_binlog NUM 設定對二進位記錄檔寫入NUM次後就同步到磁碟。0表示不同步，1表示事務提交後才寫入二進位日誌中。其他正數都表示二進位日誌在NUM次寫入後就同步到磁碟。注意若autocommit=1，則每條語句的執行都將直接提交。否則事務只有執行COMMIT操作，明確提交後才寫入二進位日誌中。

        max_binlong_cache_size 上限值

        max_binlog_size

        max_binlog_stmt_cache_size 上限值

        expire_logs_days DAY 設定日誌到期時間為DAY天，到期會被自動刪除。預設為0。
       
       

中繼日誌：
    記錄內容：
        從主伺服器的二進位記錄檔中複製而來的事件，本質上也是二進位記錄檔。
    檔案位置：
        位於從伺服器上。
    相關變數：

交易記錄：
    記錄內容：
        支援事務的引擎上特有，是為了保證事務的ACID特性，將隨機io轉換為順序io，提高效率，保證事務不會丟失。

        事務ID號+未經處理資料+新資料
        TID<OLD_VLAUE><NEW_VALUE>

        事務只能對錶中的內容進行ROLLBACK，對於像DROP TABLE這樣的操作是無法復原的。

    檔案位置：
        預設為資料目錄內，名稱為ib_logfileNUM。建議記錄檔和資料檔案分開存放，並為日誌做鏡像

    相關變數：
        innodb_flush_log_at_trx_commit 0|1|2 設定啟用事務提交後將記憶體中的日誌事件同步到記錄檔中,1表示每當有事務提交或磁碟排清寫入，預設值。2表示每當有事務提交就同步。0表示每1秒同步一次，不在核心中緩衝，直接寫入磁碟。

        innodb_log_buffer_size 記憶體緩衝大小
        innodb_log_size  記錄檔大小
        innodb_log_files_in_group 日誌組中的記錄檔個數
        innodb_log_group_home_dir 日誌存放位置，預設為資料目錄
        innodb_mirrored_log_groups 是否對記錄檔組做鏡像

        innodb_support_xa=TURE|FLASE 是否啟用分散式交易，預設是啟用的。若在僅有一個線程修改資料的環境下，關閉後此以提高InnoDB的效率。

#mysqlbinlog [OPTION] BINLOGFILE 二進位日至檔案查看工具
    [OPTION]
        --start-datetime
        --stop-datetime
        --start-position
        --stop-position

    例如：
        #mysqlbinlog /mydata/mdata/mysql-bin.00005

        #mysqlbinlog --start-position=177 --stop-position=358 /mydata/mdata/mysql-bin.00005

        #mysqlbinlog --start-datetime='2015-07-21 19:22:31' /mydata/mdata/mysql-bin.00005

        #mysqlbinlog /mydata/mdata/mysql-bin.00005 > a.sql 從二進位檔案讀取內容匯出成sql指令檔。

執行個體：
    0.開啟二進位日誌記錄功能：
        #vim /etc/my.cnf
            [mysqld]
            log_bin=mysql-bin
            binlog_format=MIXED

        或在服務啟動時指定
        #mysqld_safe --log_bin=mysql-bin --binlog_format='MIXED' --user=root &

    1.查看所有二進位記錄檔：
        mysql>SHOW BINARY LOGS;

    2.顯示當前正在使用的二進位記錄檔：
        mysql>SHOW MASTER STATUS；

    3.查看指定二進位日至檔案的具體內容：
        mysql>SHOW BINLOG EVENTS in ‘mysql-bin.000005’ FROM POSITION=177;
        或
        #mysqlbinlog /mydata/mdata/mysql-bin.000005

    4.手工滾動二進位檔案：
        mysql>FLUSH LOGS;
   
    5.刪除指定二進位記錄檔之前的記錄檔
        mysql>PURGE BINARY LOGS TO ‘mysql-bin.00005’;

    6.匯出二進位記錄檔為sql��本：
        #mysqlbinlog /mydata/mdata/mysql-bin.000005 > a.sql

本文永久更新連結地址：