Mysql日志管理講析

下文內容主要給大家帶來Mysql日志管理講析，這里所講到的知識，與書籍略有不同，都是億速云專業技術人員在與用戶接觸過程中，總結出來的，具有一定的經驗分享價值，希望給廣大讀者帶來幫助。

一、簡介

由MySQL AB公司開發，是最流行的開放源碼SQL數據庫管理系統，主要特點：

• 1、是一種數據庫管理系統

• 2、是一種關聯數據庫管理系統

• 3、是一種開放源碼軟件，且有大量可用的共享MySQL軟件

• 4、MySQL數據庫云服務器具有快速、可靠和易于使用的特點

• 5、MySQL服務器工作在客戶端/服務器模式下，或嵌入式系統中

• 

• InnoDB存儲引擎將InnoDB表保存在一個表空間內，該表空間可由數個文件創建。這樣，表的大小就能超過單獨文件的最大容量。表空間可包括原始磁盤分區，從而使得很大的表成為可能。表空間的最大容量為64TB。

• 
  

二、MySQL日志管理     

 2.1. 日志全局變量

mysql> SHOW GLOBAL VARIABLES LIKE '%log%';  #查看關于log的全局變量 

+--------------------------+-------------------------+

| Variable_name      | Value     |

+--------------------------+-------------------------+

| back_log           | 50   |

| binlog_cache_size      | 32768  | 二進制日志1

| binlog_direct_non_transactional_updates | OFF |

| binlog_format         | MIXED   | 二進制日志2

| binlog_stmt_cache_size   | 32768   | 二進制日志3

| expire_logs_days         | 0   | 二進制日志4

| general_log   一般查詢日志1  | OFF |

| general_log_file 一般查詢日志2 | /mydata/data/lamp.log |

| innodb_flush_log_at_trx_commit | 1   |

| innodb_locks_unsafe_for_binlog | OFF  |

| innodb_log_buffer_size      | 8388608 |

| innodb_log_file_size       | 5242880 |

| innodb_log_files_in_group    | 2   |

| innodb_log_group_home_dir    | ./  |

| innodb_mirrored_log_groups   | 1   |

| log     一般查詢日志3    | OFF  |

| log_bin                | ON   | 二進制日志5

| log_bin_trust_function_creators | OFF  | 二進制日志6 

| log_error    錯誤日志1   | /mydata/data/lamp.err |

| log_output  一般查詢日志4  慢查詢日志1 | FILE |

| log_queries_not_using_indexes  慢查詢日志2 | OFF |

| log_slave_updates        | OFF  |

| log_slow_queries         | OFF  |

| log_warnings  錯誤日志2  | 1   | 

| max_binlog_cache_size |18446744073709547520 | 二進制日志7 

| max_binlog_size           | 1073741824 | 二進制日志8 

| max_binlog_stmt_cache_size|18446744073709547520 | 二進制日志9 

| max_relay_log_size        | 0  | 中繼日志1

| relay_log              |   | 中繼日志2

| relay_log_index          |   | 中繼日志3

| relay_log_info_file       | relay-log.info | 中繼日志4

| relay_log_purge         | ON   | 中繼日志5

| relay_log_recovery       | OFF  | 中繼日志6

| relay_log_space_limit      |  0  | 中繼日志7

| slow_query_log    慢查詢日志3 |  OFF  |

| slow_query_log_file 慢查詢日志4 |/mydata/data/lamp-slow.log |

| sql_log_bin          | ON  | 二進制日志10 

| sql_log_off          | OFF | 二進制日志11 

| sync_binlog          | 0   | 二進制日志12 

| sync_relay_log        | 0   | 中繼日志8

| sync_relay_log_info     | 0   | 中繼日志9

+------------------------+-------------------+

41 rows in set (0.00 sec)

 2.2. 日志分類

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

   1. 錯誤日志    記錄啟動、運行或停止時出現的問題

   2. 一般查詢日志  記錄建立的客戶端連接和執行的語句

   3. 慢查詢日志   記錄所有執行時間超過long_query_time秒的所有查詢或不使用索引的查詢

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

   5. 中繼日志     

   6. 事務日志   記錄InnoDB等支持事務的存儲引擎執行事務時產生的日志

   7. 滾動日志

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

 2.3. 日志詳解

    2.3.1. 錯誤日志  （默認是開啟）

  在對應的數據目錄中，以主機名+.err命名的文件，錯誤日志記錄的信息類型：

•   2.3.1.1. 記錄服務器運行中產生的錯誤信息

•   2.3.1.2. 記錄服務在啟動和停止是所產生的信息

•   2.3.1.3. 在從服務器上啟動復制進程時，復制進程的信息也會被記錄

•   2.3.1.4. 記錄event事件錯誤日志及警告信息

配置mysql的主配置文件：

log_error = /mydata/data/mysql.test.com.err 

#指定錯誤日志的位置，默認是在數據目錄下，這個位置mysql用戶必須有寫權限

log_warning = {0|1} 

#默認開啟，服務器運行中的警告日志也會記錄在錯誤日志中

    2.3.2. 一般查詢日志  （默認是關閉）

              對除慢查日志中記錄的查詢信息外，將都記錄下來,這將對服務器產生大量壓力，

        故繁忙的服務器應關閉這個日志與查詢日志相關的變量。

log = {ON|OFF}        #是否啟用查詢日志，該指令在mysq5.6中已廢棄

general_log = {ON|OFF}   #啟動或關閉查詢日志，默認是關閉的

general_log_file = /mydata/data/mysql.log #指定日志位置，默認在數據目錄下

log_output = {TABLE|FILE|NONE} 

     #指定存放查詢日志的位置，可以放在文件中，也可以放在數據庫的表中，放在表中比放在文件中更容易查看。如果組合中出現了NONE，那么其它設定都將失效，且無論是否啟用日志功能，也不會記錄任何相關的日志信息

    2.3.3. 慢查詢日志 （默認是關閉）

  默認關閉狀態，記錄下來查詢時間超過設定時長的查詢，這些查詢日志將被慢查日志記錄下來  

slow_query_log  = {ON | OFF}   #開啟慢慢查詢日志，默認是關閉的 

slow_query_log_file = /mydata/data/mysql-slow.log 

                   #慢查詢日志的存放位置，默認在數據目錄下

log_query_time = 10  #定義默認的時長，默認時長為10秒

log_query_not_using_indexes = {ON|OFF} 

                 #是否將沒有使用索引的查詢操作記錄到慢查詢日志

log_output = {TABLE|FILE|NONE}     

#定義一般查詢日志和慢查詢日志的保存方式，默認為FILE，也可以TABLE及FILE的組合(用逗號隔開)，若組合中出現了NONE，那其它設定都將失效，且無論是否啟用日志功能，也不會記錄任何相關日志信息

    2.3.4. 二進制日志  （默認是開啟）

   精確記錄用戶對數據庫中的數據進行操作的命令和操作的數據對象，及任何引起或可能引起數據庫變化的操作。

2.3.4.1. 二進制日志文件的作用：

• 1）提供增量備份的功能；

• 2）提供數據基于時間點的恢復，這個恢復的時間點可以由用戶控制；

• 3）為mysql的復制架構提供基礎，將主服務器的二進制日志復制到從服務器上并執行同樣的操作，即可實現數據進行同步；

2.3.4.2. 二進制日志格式：

• 基于語句   statement

• 基于行    row

• 混合方式   mixed

2.3.4.3. 二進制日志事件：

• position 基于位置

• datetime 基于時間

2.3.4.4. 二進制日志的查看與刪除方式：

mysql>show binlog events in 'mysql-bin.000012'; 

mysql>SHOW BINLOG EVENTS IN 'mysql-bin.00011' FROM 107 

      #查看日志文件的事件信息且從事件107位置開始顯示

mysql>purge binary logs to 'mysql-bin.000003';

                  #刪除此日志之前的所有日志文件000001和000002

Usage:

mysql>SHOW BINARY LOGS; #查看所有的二進制日志文件

+------------------+-----------+

| Log_name   | File_size |

+------------------+-----------+

| mysql-bin.000001 | 1165 |

| mysql-bin.000002 | 126 |

| mysql-bin.000003 |  126 |

| mysql-bin.000004 |  884 |

| mysql-bin.000005 |  126 |

...

| mysql-bin.000015 | 1325 |

| mysql-bin.000016 |  390 |

| mysql-bin.000017 |  371 |

+------------------+-----------+

17 rows in set (0.00 sec)

mysql> PURGE BINARY LOGS TO 'mysql-bin.000003';#刪除之前的所有日志文件001和002

mysql> SHOW BINARY LOGS ;

+------------------+-----------+

| Log_name   | File_size |

+------------------+-----------+

| mysql-bin.000003 |   126 |

| mysql-bin.000004 |   884 |

| mysql-bin.000005 |   126 |

...

| mysql-bin.000015 |  1325 |

| mysql-bin.000016 |   390 |

| mysql-bin.000017 |   371 |

+------------------+-----------+

15 rows in set (0.00 sec)

2.3.4.5. 文件系統中查看二進制日志的命令：mysqlbinlog

--start-position       #開始位置  

--stop-position        #結束位置

--start-datetime 'yyyy-mm-dd hh:mm:ss';    #開始時間

--stop-datetime '';    #結束時間

Usage:

[root@lamp ~]#mysqlbinlog mysql-bin.000001 #查看日志事件的全部信息

[root@lamp ~]# mysqlbinlog --start-datetime '2017-04-18 09:01:13' mysql-bin.000011         #查看日志事件從某時間點之后的信息

[root@lamp ~]# mysqlbinlog --start-position 193 mysql-bin.000011 

                  #查看日志時間從193位置開始之后的信息。

2.3.4.6. 配置mysql的主配置文件：  

sql_log_bin = {ON|OFF}

 #用于控制二進制日志信息是否記錄進日志文件。默認為ON，表示啟用記錄功能。用戶可以在會話級別修改此變量的值，但其必須有SUPER權限。

binlog_cache_size = 32768 

 #默認值32768，Binlog Cache用于在打開了二進制日志（binlog）記錄功能的環境，它是MySQL用來提高binlog的記錄效率而設計的一個用于短時間內臨時緩存binlog數據的內存區域。

 一般來說，如果我們的數據庫中沒有什么大事務，寫入也不是特別頻繁，2MB～4MB是一個合適的選擇。但是如果我們的數據庫大事務較多，寫入量比較大，可與適當調高binlog_cache_size。

 同時，我們可以通過binlog_cache_use 以及 binlog_cache_disk_use來分析設置的binlog_cache_size是否足夠，是否有大量的binlog_cache由于內存大小不夠而使用臨時文件（binlog_cache_disk_use）來緩存 

binlog_stmt_cache_size = 32768 

 #當非事務語句使用二進制日志緩存，但是超出binlog_stmt_cache_size時，使用一個臨時文件來存放這些語句  

log_bin = mysql-bin    

 #指定binlog的位置，默認在數據目錄下

binlog-format = {ROW|STATEMENT|MIXED} 

 #指定二進制日志的類型，默認為MIXED。如果設定了二進制日志的格式，卻沒有啟用二進制日志，則MySQL啟動時會產生警告日志信息并記錄于錯誤日志中。 

sync_binlog = 10 

 #設定多久同步一次二進制日志至磁盤文件中，0表示不同步，任何正數值都表示對二進制每多少次寫操作之后同步一次。當autocommit的值為1時，每條語句的執行都會引起二進制日志同步，否則，每個事務的提交會引起二進制日志同步

max_binlog_cache_size = {4096 .. 18446744073709547520} 

 #二進定日志緩存空間大小，5.5.9及以后的版本僅應用于事務緩存，其上限由max_binlog_stmt_cache_size決定。

max_binlog_stmt_cache_size = {4096 .. 18446744073709547520} 

 #二進定日志緩存空間大小，5.5.9及以后的版本僅應用于事務緩存

expire_log_days = {0..99} 

 #設定二進制日志的過期天數，超出此天數的二進制日志文件將被自動刪除。默認為0，表示不啟用過期自動刪除功能。如果啟用此功能，自動刪除工作通常發生在MySQL啟動時或FLUSH日志時

注：一般建議將binlog日志與數據文件分開存放，不但可以提高mysql性能，還可以增加安全性！

    2.3.5. 中繼日志

   主要在mysql服務器的中從架構中的從服務器上使用，當主從服務器進行數據同步時，從服務器將主服務器的二進制日志文件拷貝到己的主機上，并放在中繼日志中，再調用SQL線程按照中繼日志文件中的二進制日志文件執行，以達到數據的同步。

    開啟的方法：  只能在從服務器上開啟   
配置mysql的主配置文件：    

relay-log = host_name-relay-bin 

  #指定中繼日志的位置和名字，默認為host_name-relay-bin。也可以使用絕對路徑，以指定非數據目錄來存儲中繼日志  

relay-log-index = host_name-relay-bin.index 

 #指定中繼日志的名字的索引文件的位置和名字，默認為數據目錄中的host_name-relay-bin.index

relay-log-info-file = relay-log.info 

 #設定中繼服務用于記錄中繼信息的文件，默認為數據目錄中的relay-log.info

relay_log_purge = {ON|OFF} 

 #設定對不再需要的中繼日志是否自動進行清理。默認值為ON

relay_log_space_limit = 0 

 #用于存儲所有中繼日志文件的可用空間大小。默認為0，表示不限定。最大值取決于系統平臺位數

max_relay_log_size = {4096..1073741824} 

 #從服務器上中繼日志的體積上限，到達此限度時，自動進行中繼日志滾動。此值為0時，將使用max_binlog_size參數，同時為二進制日志和中繼日志設定日志文件體積上限

    2.3.6.  事務日志

   詳細的記錄在什么時間發生了什么事務，在哪個時間對哪些數據進行了改變，能夠實現事件的重放，一般只記錄對數據進行改變的操作，對于讀操作一般不進行記錄。事務性存儲引擎用于保證原子性、一致性、隔離性和持久性。

2.3.6.1.  主要功能   

  2.3.6.1.1.  將隨機IO轉換為順序IO，大大的提高了數據庫的性能，存儲的數據可能存在在磁盤的不同位置，降低了數據的讀取和操作性能。轉換為順序IO的原理為，先將數據存放在日志文件中，然后由RDBSM的后臺將日志中的數據存放到磁盤上，這樣就保證了存儲的數據是連續的。    
  2.3.6.1.2.  為事件重放提供基礎，事務日志詳細的記錄了時間發生的時間以及操作的數據對象，事務進程可以根據這些信息進行時間重放。

    
2.3.6.2.  默認的事務日志文件有兩個，位于數據目錄下以ibdata+number結尾的數字，我們可以對事務日志的位置、文件大小、增長方式進行定義，定義的方法如下：    

    配置mysql的主配置文件：(以使用支持事務的Innodb存儲引擎為例)

innodb_flush_log_at_commit  

 #默認值為1，共三個級別:

    0：每一秒同步，并執行磁盤flush操作；

    1：每事務同步，并執行磁盤flush操作；

    2：每事務同步，但不執行磁盤flush操作，由操作來執行flush; IO減少但不安全

 定義從內存空間到日志文件同步方式，而從日志文件到生成數據文件(硬盤上)的同步由mysql的后臺服務線程自動進行，也可控制；  在內存空間中先緩存到分用戶空間，再緩存到分內核空間，當值為0時將不會緩存到分內核空間，而直接同步到日志文件中

innodb_data_home_dir = /mydata/data  

  #InnoDB所有共享表空間數據文件的目錄路徑，默認在數據目錄下

innodb_data_file_path = ibdata1:1024M  

  #指定InnoDB的各個數據文件及其大小，文件大于一個時，彼此間用分號隔開。

innodb_mirrored_log_groups

   #事務日志是否啟用鏡像設定 

innodb_data_file_path = ibdata2:50M:autoextend

  #定義數據大小的增長方式

innodb_log_group_home_dir = /mydata/data 

  #設定InnoDB重要日志文件的存儲目錄。缺省時使用InnoDB日志相關的所有變量時，其默認會在數據目錄中創建兩個大小為5MB的名為ib_logfile0和ib_logfile1的日志文件

innodb_log_group_home_dir = /mydata/data 

  #設定InnoDB重要日志文件的存儲目錄。在缺省使用InnoDB日志相關的所有變量時，其默認會在數據目錄中創建兩個大小為5MB的名為ib_logfile0和ib_logfile1的日志文件

innodb_log_files_in_group = {2 .. 100} 

  #設定日志組中日志文件的個數。InnoDB以循環的方式使用這些日志文件。默認值為2

innodb_log_file_size = {108576 .. 4294967295} 

  #設定日志組中每個日志文件的大小，單位是字節，默認值是5MB，建議不超過10MB。較為明智的取值范圍是從1MB到緩存池體積的1/n，其中n表示日志組中日志文件的個數。日志文件越大，在緩存池中需要執行的檢查點刷寫操作就越少，這意味著所需的I/O操作也就越少，然而這也會導致較慢的故障恢復速度

innodb_log_buffer_size = {262144 .. 4294967295} 

  #設定InnoDB用于輔助完成日志文件寫操作的日志緩沖區大小，單位是字節，默認為8MB。較大的事務可以借助于更大的日志緩沖區來避免在事務完成之前將日志緩沖區的數據寫入日志文件，以減少I/O操作進而提升系統性能。因此，在有著較大事務的應用場景中，建議為此變量設定一個更大的值

    2.3.7.  滾動日志

   只要是針對二進制日志進行滾動的，對某個類型的日志文件滾動一次就生成一個新的相對應的日志文件，通過這種方法保證日志文件的特定大小，從而保證服務器在對日志文件查詢時有較高的響應能力。

滾動二進制日志的命令：

 mysql> FLUSH LOGS;        #實現二進制日志滾動

對于以上關于Mysql日志管理講析，如果大家還有更多需要了解的可以持續關注我們億速云的行業推新，如需獲取專業解答，可在官網聯系售前售后的，希望該文章可給大家帶來一定的知識更新。