MySQL 日志管理細說

2024-07-24 12:35:37

字體：大中小

來源：轉載

供稿：網友

　　大綱

　　一、日志分類

　　二、日志詳解

　　注：MySQL版本，Mysql-5.5.32(不同版本的mysql變量有所不同)

　　一、日志分類

　　·      錯誤日志

　　·      查詢日志

　　·      慢查詢日志

　　·      二進制日志

　　·      中繼日志

　　·      事務日志

　　·      滾動日志

　　二、日志詳解

　　1.錯誤日志

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

　　·      記錄了服務器運行中產生的錯誤信息

　　·      記錄了服務在啟動和停止是所產生的信息

　　·      在從服務器上如果啟動了復制進程的時候，復制進程的信息也會被記錄

　　·      記錄event錯誤日志

　　配置mysql的主配置文件：

　　log_error = /mydata/data/mysql.test.com.err #指定錯誤日志的位置，默認是在數據目錄下，這個位置mysql用戶必須有寫權限

　　log_warning = {0|1} #默認開啟，服務器運行中的警告日志也會記錄在錯誤日志中

　　2.查詢日志

　　說明：對除了慢查日志中記錄的查詢信息都將記錄下來,這將對服務器主機產生大量的壓力，所以對于繁忙的服務器應該關閉這個日志

　　與查詢日志相關的變量，

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

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

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

　　log_output = {TABLE|FILE|NONE} #指定存放查詢日志的位置，可以放在文件中，也可以放在數據庫的表中，放在表中比放在文件中更容易查看

　　3.慢查詢日志

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

　　配置mysql的主配置文件：

　　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} #定義一般查詢日志和慢查詢日志的保存方式，可以是TABLE、FILE、NONE，也可以是TABLE及FILE的組合(用逗號隔開)，默認為FILE。如果組合中出現了NONE，那么其它設定都將失效，同時，無論是否啟用日志功能，也不會記錄任何相關的日志信息

　　4.二進制日志

　　說明：默認開啟，精確的記錄了用戶對數據庫中的數據進行操作的命令和操作的數據對象。
　　二進制日志文件的作用：

　　·      提供了增量備份的功能

　　·      提供了數據基于時間點的恢復，這個恢復的時間點可以由用戶控制

　　·      為mysql的復制架構提供基礎，將這主服務器的二進制日志復制到從服務器上并執行同樣的操作，就可將數據進行同步

　　二進制日志格式：

　　·      基于語句 statement

　　·      基于行 row

　　·      混合方式 mixed

　　二進制日志事件：

　　·      position 基于位置

　　·      datetime 基于時間

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

　　mysql>show master status; 查看當前正在使用的二進制日志

　　mysql>show binlog events in 'mysql-bin.000001'; 查看二進制日志記錄的事件[from position]

　　mysql>flush logs; 二進制日志滾動

　　mysql>show binary logs; 查看所有二進制日志

　　mysql>purge binary logs to 'mysql-bin.000003'; 刪除二進制日志

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

　　mysqlbinlog

　　相關選項，

　　--start-position #開始位置

　　--stop-position #結束位置

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

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

　　配置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性能，還可以增加安全性！

　　5.中繼日志

　　說明：主要是在mysql服務器的中從架構中的從服務器上用到的，當從服務器想要和主服務器進行數據的同步時，從服務器將主服務器的二進制日志文件拷貝到己的主機上放在中繼日志中，然后調用SQL線程按照拷中繼日志文件中的二進制日志文件執行以便就可達到數據的同步

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

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

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

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

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

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

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

　　6.事務日志

　　說明：詳細的記錄了在什么時間發生了什么時候，在哪個時間對哪些數據進行了改變，能后實現事件的重放，一般只記錄對數據進行改變的操作，對于讀操作一般不進行記錄。

　　事物日志為數據庫服務器實現以下功能：
　　(1).將隨機IO轉換為順序IO，大大的提高了數據庫的性能，存儲的數據可能存在在磁盤的不同位置，降低了數據的讀取和操作性能。轉換為順序IO的原理為，先將數據存放在日志文件中，然后由RDBSM的后臺將日志中的數據存放到磁盤上，這樣就保證了存儲的數據是連續的。
　　(2).為事件重放提供基礎，事務日志詳細的記錄了時間發生的時間以及操作的數據對象，事務進程可以根據這些信息進行時間重放。
　　默認的事務日志文件有兩個，位于數據目錄下以ibdata+number結尾的數字，我們可以對事務日志的位置、文件大小、增長方式進行定義，定義的方法如下：
　　這里以使用支持事務的Innodb存儲引擎為例，

　　配置mysql的主配置文件：

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

　　innodb_data_file_path = ibdata1:1024M #指定InnoDB的各個數據文件及其大小，文件多于一個時彼此間用分號隔開

　　innodb_data_file_path = ibdata2:50M:autoextend #定義數據大小的增長方式

　　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。較為明智的取值范圍是從1MB到緩存池體積的1/n，其中n表示日志組中日志文件的個數。日志文件越大，在緩存池中需要執行的檢查點刷寫操作就越少，這意味著所需的I/O操作也就越少，然而這也會導致較慢的故障恢復速度

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

　　7.滾動日志

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

　　滾動二進制日志的命令：

　　mysql> FLUSH LOGS;

　　三、總結

　　通過上面的學習，大家應該對mysql日志管理有所了解。

（編輯：武林網）

上一篇：mysql有關錯誤

下一篇：Mysql通信文件