MySQL學習筆記之日志管理(1/2)

日志是mysql數據庫中一個很重要的東西，日志類型有：錯誤日志，日志文件，二進制日志，慢日志，事務日志等等，下面我們一起來了解一下mysql日志.

一、日志類型：

MySQL有幾個不同的日志文件，可以幫助你找出mysqld內部發生的事情：

日志文件  記入文件中的信息類型
錯誤日志  記錄啟動、運行或停止時出現的問題。
查詢日志  記錄建立的客戶端連接和執行的語句。
二進制日志  記錄所有更改數據的語句。主要用於復制和即時點恢復。
慢日志  記錄所有執行時間超過long_query_time秒的所有查詢或不使用索引的查詢。
事務日志  記錄InnoDB等支持事務的存儲引擎執行事務時產生的日志。

默認情況下，所有日志創建於mysqld數據目錄中。通過刷新日志，你可以強制 mysqld來關閉和重新打開日志文件（或者在某些情況下切換到一個新的日志）。當你執行一個FLUSH LOGS語句或執行mysqladmin flush-logs或mysqladmin refresh時，出現日志刷新。如果你正使用MySQL復制功能，從復制服務器將維護更多日志文件，被稱為接替日志。
二、錯誤日志：
錯誤日志主要記錄如下幾種日志：

    服務器啟動和關閉過程中的信息
    服務器運行過程中的錯誤信息
    事件調度器運行一個時間是產生的信息
    在從服務器上啟動從服務器進程是產生的信息

錯誤日志定義：

可以用--log-error[=file_name]選項來指定mysqld保存錯誤日志文件的位置。如果沒有給定file_name值，mysqld使用錯誤日志名host_name.err 並在數據目錄中寫入日志文件。如果你執行FLUSH LOGS，錯誤日志用-old重新命名後綴並且mysqld創建一個新的空日志文件。(如果未給出--log-error選項，則不會重新命名）。

查看當前錯誤日志配置：

  

 代碼如下 復制代碼   mysql> SHOW GLOBAL VARIABLES LIKE '%log_error%';
    +---------------+---------------------------------------+
    | Variable_name  | Value                                                  |
    +---------------+---------------------------------------+
    | log_error          | /data/mysql/localhost.localdomain.err |
    +---------------+---------------------------------------+
    1 row in set (0.45 sec)

是否記錄警告日志：

   

 代碼如下 復制代碼 mysql> SHOW GLOBAL VARIABLES LIKE '%log_warnings%';
    +---------------+-------+
    | Variable_name | Value   |
    +---------------+-------+
    | log_warnings    | 1         |
    +---------------+-------+
    1 row in set (0.00 sec)

三、通用查詢日志

    啟動開關：general_log={ON|OFF}
    日志文件變量：general_log_file[=/PATH/TO/file]
    全局日志開關：log={ON|OFF}   該開關打開後，所有日志都會被啟用
    記錄類型：log_output={TABLE|FILE|NONE}

因此，要啟用通用查詢日志，需要至少配置general_log=ON，log_output={TABLE|FILE}。而general_log_file如果沒有指定，默認名是host_name.log。

看看上述幾個值的默認配置：

   

 代碼如下 復制代碼

mysql> SHOW GLOBAL VARIABLES LIKE '%general_log%';
    +------------------+---------------------------+
    | Variable_name      | Value                                |
    +------------------+---------------------------+
    | general_log           | OFF                                  |
    | general_log_file     | /data/mysql/localhost.log |
    +------------------+---------------------------+

    mysql> SHOW GLOBAL VARIABLES LIKE '%log_output%';
    +---------------+-------+
    | Variable_name  | Value  |
    +---------------+-------+
    | log_output       | FILE     |
    +---------------+-------+

四、慢查詢日志：

MySQL如果啟用了slow_query_log=ON選項，就會記錄執行時間超過long_query_time的查詢（初使表鎖定的時間不算作執行時間）。日志記錄文件為slow_query_log_file[=file_name]，如果沒有給出file_name值， 默認為主機名，後綴為-slow.log。如果給出了文件名，但不是絕對路徑名，文件則寫入數據目錄。

默認與慢查詢相關變量：

   

 代碼如下 復制代碼 mysql> SHOW GLOBAL VARIABLES LIKE '%slow_query_log%';
    +----------------------------+--------------------------------+
    | Variable_name                     | Value                                        |
    +----------------------------+--------------------------------+
    | slow_query_log                    | OFF                                          |
    | slow_query_log_file              | /data/mysql/localhost-slow.log |
    +----------------------------+--------------------------------+

服務器參數設定方法與通用查詢日志相同，不做解釋。

默認沒有啟用慢查詢，為了服務器調優，建議開啟。

 

 代碼如下 復制代碼    mysql> SET GLOBAL slow_query_log=ON;
    Query OK, 0 rows affected (1.45 sec)

    ##如果要長久生效，則需要在配置文件中定義。

那麼多久算是慢呢？

如果查詢時長超過long_query_time的定義值（默認10秒），即為慢查詢：

   

 代碼如下 復制代碼 mysql> SHOW GLOBAL VARIABLES LIKE 'long_query_time';
    +-----------------+-----------+
    | Variable_name    | Value         |
    +-----------------+-----------+
    | long_query_time | 10.000000 |
    +-----------------+-----------+

 
五、二進制日志：
二進制日志啟動開關：log-bin [= file_name]

在5.6及以上版本一定要手動指定。5.6以下版本默認file_name為$datadir/mysqld-binlog

二進制日志用於記錄所有更改數據的語句。主要用於復制和即時點恢復。

查看二進制日志的工具為：mysqlbinlog

二進制日志包含了所有更新了數據或者已經潛在更新了數據（例如，沒有匹配任何行的一個DELETE）的所有語句。語句以“事件”的形式保存，它描述數據更改。二進制日志還包含關於每個更新數據庫的語句的執行時間信息。它不包含沒有修改任何數據的語句。

二進制日志的主要目的是在數據庫存在故障時，恢復時能夠最大可能地更新數據庫（即時點恢復），因為二進制日志包含備份後進行的所有更新。二進制日志還用於在主復制服務器上記錄所有將發送給從服務器的語句。
那麼二進制日志是記錄執行的語句還是執行後的結果數據呢？

第一種情況：

加入一個表有10萬行數據，而現在要執行一個如下語句將amount字段的值全部在原來的基礎上增加1000：

 代碼如下 復制代碼

    UPDATE sales.january SET amount=amount+1000；

此時如果要記錄執行後的結果數據的話，日志會非常大。

因此在這種情況下應記錄執行語句。這種方式就是基於語句的二進制日志。

第二種情況：

如果向某個字段插入的是當前的時間呢？如下：

 代碼如下 復制代碼

    INSERT INTO tb SET Birthdate=CURRENT_TIME();

此時就不能記錄語句了，因為不同時間執行的結果是不一樣的。這是應該記錄這一行的值，這種就是基於行（row）的二進制日志。

在有些情況，可能會結合兩種方式來記錄，這種叫做混合方式的二進制日志。
二進制日志記錄時間：

默認情況下，並不是每次寫入時都將二進制日志與硬盤同步。因此如果操作系統或機器(不僅僅是MySQL服務器)崩潰，有可能二進制日志中最後的語句丟失了。要想防止這種情況，你可以使用sync_binlog全局變量(1是最安全的值，但也是最慢的)，使二進制日志在每N次二進制日志寫入後與硬盤同步。

對非事務表的更新執行完畢後立即保存到二進制日志中。對於事務表，例如BDB或InnoDB表，所有更改表的更新(UPDATE、DELETE或INSERT) 被緩存起來，直到服務器接收到COMMIT語句。在該點，執行完COMMIT之前，mysqld將整個事務寫入二進制日志。當處理事務的線程啟動時，它為緩沖查詢分配binlog_cache_size大小的內存。如果語句大於該值，線程則打開臨時文件來保存事務。線程結束後臨時文件被刪除。
二進制日志的管理：

    日志的滾動：

在my.cnf中設定max_binlog_size = 200M，表示限制二進制日志最大尺寸為200M，超過200M後進行滾動。MySQL的滾動方式與其他日志不太一樣，滾動時會創建一個新的編號大1的日志用於記錄最新的日志，而原日志名字不會被改變。

每次重啟MySQL服務，日志都會自動滾動一次。

另外如果需要手動滾動，則使用命令：

 代碼如下 復制代碼

    mysql> FLUSH LOGS;

    日志的查看：

查看有哪些二進制日志文件：

   

 代碼如下 復制代碼

mysql> SHOW BINARY LOGS;

    +----------------------+-----------+
    | Log_name                   | File_size    |
    +----------------------+-----------+
    | mysqld-binlog.000001 |       143   |
    | mysqld-binlog.000002 |       120   |
    +----------------------+-----------+

查看當前正在使用的是哪一個二進制日志文件：

 代碼如下 復制代碼

    mysql> SHOW MASTER STATUS;
    +----------------------+----------+--------------+------------------+-------------------+
    | File                             | Position   | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +----------------------+----------+--------------+------------------+-------------------+
    | mysqld-binlog.000002 |      120   |                       |                            |                              |
    +----------------------+----------+--------------+------------------+-------------------+

    ##做個操作後再次查看：

    mysql> use jiaowu;

    Database changed
    mysql> INSERT INTO students (Name,Age) VALUES ('stu1',24);
    Query OK, 1 row affected (0.07 sec)

    mysql> SHOW MASTER STATUS;
    +----------------------+----------+--------------+------------------+-------------------+
    | File                             | Position   | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +----------------------+----------+--------------+------------------+-------------------+
    | mysqld-binlog.000002 |      394   |                      |                             |                              |
    +----------------------+----------+--------------+------------------+-------------------+
    1 row in set (0.00 sec)

    ##可以看到Position（位置）已經改變。

查看二進制日志內容：

  

 代碼如下 復制代碼

  mysql> SHOW BINLOG EVENTS IN 'mysqld-binlog.000002';
    +----------------------+-----+-------------+-----------+-------------+------------------------------------------------------------------+
    | Log_name             | Pos | Event_type  | Server_id | End_log_pos | Info                                                             |
    +----------------------+-----+-------------+-----------+-------------+------------------------------------------------------------------+
    | mysqld-binlog.000002 |   4 | Format_desc |         1 |         120 | Server ver: 5.6.13-log, Binlog ver: 4                            |
    | mysqld-binlog.000002 | 120 | Query       |         1 |         203 | BEGIN                                                            |
    | mysqld-binlog.000002 | 203 | Intvar      |         1 |         235 | INSERT_ID=11                                                     |
    | mysqld-binlog.000002 | 235 | Query       |         1 |         363 | use `jiaowu`; INSERT INTO students (Name,Age) VALUES ('stu1',24) |
    | mysqld-binlog.000002 | 363 | Xid         |         1 |         394 | COMMIT /* xid=13 */                                              |
    +----------------------+-----+-------------+-----------+-------------+------------------------------------------------------------------+
    5 rows in set (0.01 sec)

    ##該語句還可以加上Position（位置），指定顯示從哪個Position（位置）開始：

    mysql> SHOW BINLOG EVENTS IN 'mysqld-binlog.000002' FROM 203;
    +----------------------+-----+------------+-----------+-------------+------------------------------------------------------------------+
    | Log_name             | Pos | Event_type | Server_id | End_log_pos | Info                                                             |
    +----------------------+-----+------------+-----------+-------------+------------------------------------------------------------------+
    | mysqld-binlog.000002 | 203 | Intvar     |         1 |         235 | INSERT_ID=11                                                     |
    | mysqld-binlog.000002 | 235 | Query      |         1 |         363 | use `jiaowu`; INSERT INTO students (Name,Age) VALUES ('stu1',24) |
    | mysqld-binlog.000002 | 363 | Xid        |         1 |         394 | COMMIT /* xid=13 */                                              |
    +----------------------+-----+------------+-----------+-------------+------------------------------------------------------------------+
    3 rows in set (0.00 sec)

首頁 1 2 末頁