一文帶你了解MySQL四大類日志

更新時間：2023年04月10日 12:38:27 作者：大蝦好吃嗎

在MySQL中日志文件可以分為4種:二進制日志文件、錯誤日志文件、通用查詢?nèi)罩疚募吐樵內(nèi)罩疚募?下面這篇文章主要給大家介紹了關(guān)于MySQL四大類日志的相關(guān)資料,需要的朋友可以參考下

前言

MySQL日志記錄了MySQL數(shù)據(jù)庫日常操作和錯誤信息。MySQL有不同類型的日志文件（各自存儲了不同類型的日志），從日志當(dāng)中可以查詢到MySQL數(shù)據(jù)庫的運行情況、用戶的操作、錯誤的信息等。

MySQL日志分為4大類

錯誤日志：記錄mysql服務(wù)的啟動，運行或停止mysql服務(wù)時出現(xiàn)的問題
查詢?nèi)罩荆河涗浗⒌目蛻舳说倪B接和執(zhí)行的語句
二進制日志：記錄所有更改數(shù)據(jù)的語句，可以用于數(shù)據(jù)的復(fù)制
慢查詢?nèi)罩荆河涗浰袌?zhí)行的時間超過long_query_time的所有查詢或不使用索引的查詢默認(rèn)情況下，所有日志創(chuàng)建與MySQL數(shù)據(jù)目錄中，通過刷新日志，可以強制MySQL關(guān)閉和重新打開日志文件，F(xiàn)lush logs刷新日志或者執(zhí)行mysqladmin flush-logs 如果正使用MySQL復(fù)制功能，在復(fù)制服務(wù)器上可以維護更多日志文件，這種日志我們稱為接替日志。啟動日志功能會降低MySQL數(shù)據(jù)庫的性能。

錯誤日志

在mysql數(shù)據(jù)庫中，錯誤日志功能是默認(rèn)開啟的。默認(rèn)情況下，錯誤日志存儲在mysql數(shù)據(jù)庫的數(shù)據(jù)目錄中。錯誤日志文件通常的名稱為hostname.err。其中，hostname表示服務(wù)器主機名。錯誤日志信息可以自己進行配置的，錯誤日志所記錄的信息是可以通過log-error和log-warnings來定義的，其中l(wèi)og-error是定義是否啟用錯誤日志的功能和錯誤日志的存儲位置，log-warnings是定義是否將警告信息也定義至錯誤日志中。

默認(rèn)情況下錯誤日志大概記錄以下幾個方面的信息：服務(wù)器啟動和關(guān)閉過程中的信息（未必是錯誤信息，如mysql如何啟動InnoDB的表空間文件的、如何初始化自己的存儲引擎的等等）、服務(wù)器運行過程中的錯誤信息、事件調(diào)度器運行一個事件時產(chǎn)生的信息、在從服務(wù)器上啟動服務(wù)器進程時產(chǎn)生的信息注1：MySQL有很多系統(tǒng)變量可以設(shè)置，系統(tǒng)變量設(shè)置不同，會導(dǎo)致系統(tǒng)運行狀態(tài)的不同。因此mysql提供兩組命令，分別查看系統(tǒng)設(shè)置和運行狀態(tài)。

1、查看系統(tǒng)設(shè)置：

SHOW [GLOBAL | SESSION] VARIABLES [like_or_where] 
SHOW VARIABLES： shows the values of MySQL system variables.

2、運行狀態(tài)：

SHOW [GLOBAL | SESSION] STATUS [like_or_where] 
SHOW STATUS： provides server status information.

修改系統(tǒng)配置

方法1：配置文件設(shè)置my.cnf 如：binlog_cache_size = 1M

方法2：set global binlog_cache_size = 1048576;

注：查看mysql的版本

[root@localhost ~]# mysql -V
mysql  Ver 14.14 Distrib 5.7.40, for linux-glibc2.12 (x86_64) using  EditLine wrapper

或

mysql> status;
--------------
mysql  Ver 14.14 Distrib 5.7.40, for linux-glibc2.12 (x86_64) using  EditLine wrapper
?
Connection id:      11
Current database:   
Current user:       root@localhost
SSL:            Not in use
Current pager:      stdout
Using outfile:      ''
Using delimiter:    ;
Server version:     5.7.40 MySQL Community Server (GPL)
Protocol version:   10
Connection:     Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:        /tmp/mysql.sock
Uptime:         87 days 2 hours 22 min 4 sec
?
Threads: 1  Questions: 61  Slow queries: 0  Opens: 114  Flush tables: 1  Open tables: 107  Queries per second avg: 0.000
--------------

或

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.40    |
+-----------+
1 row in set (0.00 sec)

一般而言，日志級別的定義沒有會話變量都只是在全局級別下進行定義錯誤日志的狀態(tài)：

mysql> show global variables like '%log_error%';
+---------------------+---------------------------------+
| Variable_name       | Value                           |
+---------------------+---------------------------------+
| binlog_error_action | ABORT_SERVER                    |
| log_error           | /usr/local/mysql/data/mysql.log |
| log_error_verbosity | 3                               |
+---------------------+---------------------------------+
3 rows in set (0.00 sec)

其中 log_error定義為錯誤日志文件路徑 log_error_verbosity:

verbosity value	message types logged
1	errors only
2	errors and warnings
3	errors,warinigs,and notes(default)

更改錯誤日志位置可以使用log-error來設(shè)置形式如下

[root@localhost ~]# vim /etc/my.cnf
log-error = /usr/local/mysql/data/mysqld.err

查看mysql錯誤日志

[root@localhost ~]# tail /usr/local/mysql/data/mysqld.err

為了方便維護需要，有時候會希望將錯誤日志中的內(nèi)容做備份并重新開始記錄，這時候就可以利用MySQL 的FLUSH LOGS 命令來告訴MySQL 備份舊日志文件并生成新的日志文件。備份文件名以“.old”結(jié)尾。

刪除錯誤日志：

在mysql5.5.7之前：數(shù)據(jù)庫管理員可以刪除很長時間之前的錯誤日志，以保證mysql服務(wù)器上的硬盤空間。mysql數(shù)據(jù)庫中，可以使用mysqladmin命令開啟新的錯誤日志。mysqladmin命令的語法如下：mysqladmin –uroot –p flush-logs也可以登錄mysql數(shù)據(jù)庫中使用FLUSH LOGS語句來開啟新的錯誤日志。

在mysql5.5.7之后：服務(wù)器將關(guān)閉此項功能。只能使用重命名原來的錯誤日志文件，手動沖洗日志創(chuàng)建一個新的：方式如下：

[root@localhost ~]# cd /usr/local/mysql/data/
[root@localhost data]# mv mysqld.err mysql.bak
[root@localhost data]# mysqladmin -uroot -p flush-logs
Enter password: 		#輸入root密碼

MySQL :: MySQL 5.7 Reference Manual :: 5.4.2 The Error Log

二進制日志

主要記錄MySQL數(shù)據(jù)庫的變化，二進制日志以一種有效的格式，并且是事務(wù)安全的方式包含更新日志中可用的信息。二進制日志包含了所有更新了數(shù)據(jù)或者已經(jīng)潛在更新了數(shù)據(jù)。二進制日志還包含關(guān)于每個更新數(shù)據(jù)庫的語句的執(zhí)行時間，它不包含沒有修改任何數(shù)據(jù)的語句。使用二進制日志的主要目的是最大可能地恢復(fù)數(shù)據(jù)庫。啟動二進制日志，默認(rèn)情況下二進制日志是關(guān)閉的編輯配置文件My.ini 或my.cnf

[root@localhost ~]# vim /etc/my.cnf
[mysqld]
log_bin=my-bin				//二進制日志[路徑[指定日志文件的名字]]
Expire_logs_days = 10 									//清除日志的天數(shù)
Max_binlog_size = 100M 									//單個日志文件的大小限制，超出會新建一個默認(rèn)為1GB
server_id=1												//mysql5.7版本以后需要添加serverid
[root@localhost ~]# service mysqld restart

Show variables 或show variables like 'log_%'; 語句來查詢?nèi)罩驹O(shè)置

mysql> show variables like 'log_bin%';
+---------------------------------+------------------------------------+
| Variable_name                   | Value                              |
+---------------------------------+------------------------------------+
| log_bin                         | ON                                 |
| log_bin_basename                | /usr/local/mysql/data/my-bin       |
| log_bin_index                   | /usr/local/mysql/data/my-bin.index |
| log_bin_trust_function_creators | OFF                                |
| log_bin_use_v1_row_events       | OFF                                |
+---------------------------------+------------------------------------+
5 rows in set (0.00 sec)

查看二進制日志

MySQL二進制日志存儲了所有的變更信息，MySQL二進制日志經(jīng)常使用。當(dāng)MySQL創(chuàng)建二進制日志文件時，首先創(chuàng)建一個以’filename’為名稱，以’.index’為后綴的文件；在創(chuàng)建一個以’filename’為名稱，以’.000001’為后綴的文件。當(dāng)MySQL服務(wù)重啟一次，以’.000001’為后綴的文件會增加一個，并且后綴名加1遞增。如果日志長度超過max_binlog_size的上限，也會創(chuàng)建一個新的日志。 Show binary logs;可以查看當(dāng)前的二進制日志文件個數(shù)及其文件名。二進制日志并不能直接查看，如果想要查看日志內(nèi)容，可以通過mysqlbinlog命令查看。

mysql> show binary logs;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| my-bin.000001 |       154 |
+---------------+-----------+
1 row in set (0.00 sec)

或者

mysql> show master logs;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| my-bin.000001 |       154 |
+---------------+-----------+
1 row in set (0.00 sec)

查看二制日志的內(nèi)容

退出mysql在命令行

[root@mysql ~]# mysqlbinlog myusql-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
mysqlbinlog: File 'myusql-bin.000001' not found (Errcode: 2 - No such file or directory)
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

刪除二進制日志

MySQL的二進制文件可以配置自動刪除，同時MySQL提供了手動刪除二進制文件的方法：

RESET MASTER 刪除所有的二進制日志文件；PURGE MASTER LOGS只刪除部分二進制日志文件。 Resetmaster; 刪除所有二進制日志 Purge master logs to ‘二進制名’ 刪除單個二進制日志之前的

語法格式：

PURGE { BINARY | MASTER } LOGS {
    TO 'log_name'
  | BEFORE datetime_expr
}

例：

mysql> purge master logs to 'my-bin.000001';
Query OK, 0 rows affected (0.02 sec)
 
mysql> purge master logs before '20230101';			#刪除指定日期之前的日志
Query OK, 0 rows affected, 1 warning (0.02 sec)

暫時停止二進制日志的功能

如果MySQL的配置文件已經(jīng)啟動了二進制日志，MySQL會一直記錄二進制日志，修改配置文件，可以停止二進制日志，但是需要重啟MySQL數(shù)據(jù)庫。MySQL提供了暫時停止二進制日志的功能，通過SET SQL_LOG_BIN語句可以使MySQL暫時停止二進制

mysql> set sql_log_bin=1;			#0暫停 1恢復(fù)
Query OK, 0 rows affected (0.00 sec)

事務(wù)日志(或稱redo日志)

事務(wù)日志（InnoDB特有的日志）可以幫助提高事務(wù)的效率。使用事務(wù)日志，存儲引擎在修改表的數(shù)據(jù)時只需要修改其內(nèi)存拷貝，再把修改行為記錄到持久在硬盤上的事務(wù)日志中，而不用每次都將修改的數(shù)據(jù)本身持久到磁盤。事務(wù)日志采用追加的方式，因此寫日志的操作是磁盤上一小塊區(qū)域內(nèi)的順序I/O，而不像隨機I/O需要在磁盤的多個地方移動磁頭，所以采用事務(wù)日志的方式相對來說要快得多。事務(wù)日志持久以后，內(nèi)存中被修改的數(shù)據(jù)在后臺可以慢慢的刷回到磁盤。目前大多數(shù)的存儲引擎都是這樣實現(xiàn)的。如果數(shù)據(jù)的修改已經(jīng)記錄到事務(wù)日志并持久化，但數(shù)據(jù)本身還沒有寫回磁盤，此時系統(tǒng)崩潰，存儲引擎在重啟時能夠自動恢復(fù)這部分修改的數(shù)據(jù)。具有的恢復(fù)方式則視存儲引擎而定。

查看事務(wù)日志的定義：

mysql> show global variables like '%log%';
#省略部分內(nèi)容
| innodb_flush_log_at_timeout                | 1 
| innodb_flush_log_at_trx_commit             | 1 
| innodb_locks_unsafe_for_binlog             | OFF                  
| innodb_log_buffer_size                     | 16777216            
| innodb_log_checksums                       | ON                  
| innodb_log_compressed_pages                | ON                  
| innodb_log_file_size                       | 50331648          #日志文件大小  
| innodb_log_files_in_group                  | 2                 # DB中設(shè)置幾組事務(wù)日志，默認(rèn)是2   
| innodb_log_group_home_dir                  | ./				 #定義innodb事務(wù)日志組的位置,此位置設(shè)置默認(rèn)為MySQL的datadir
#省略部分內(nèi)容
每個事務(wù)日志都是大小為50兆的文件（不同版本的mysql有差異）： 在mysql中默認(rèn)以ib_logfile0,ib_logfile1名稱存在

innodb_flush_log_at_trx_commit # 在事務(wù)提交時innodb是否同步日志從緩沖區(qū)到文件中，當(dāng)這個值為1（默認(rèn)值）之時，在每個事務(wù)提交時，日志緩沖被寫到日志文件，對日志文件做到磁盤操作的刷新，性能會很差造成大量的磁盤I/O但這種方式最安全；如果設(shè)為2,每次提交事務(wù)都會寫日志，但并不會執(zhí)行刷的操作。每秒定時會刷到日志文件。要注意的是，并不能保證100%每秒一定都會刷到磁盤，這要取決于進程的調(diào)度。每次事務(wù)提交的時候?qū)?shù)據(jù)寫入事務(wù)日志，而這里的寫入僅是調(diào)用了文件系統(tǒng)的寫入操作，而文件系統(tǒng)是有緩存的，所以這個寫入并不能保證數(shù)據(jù)已經(jīng)寫入到物理磁盤。設(shè)置為0，日志緩沖每秒一次地被寫到日志文件，并且對日志文件做到磁盤操作的刷新，但是在一個事務(wù)提交不做任何操作。

注：刷寫的概念

刷寫其實是兩個操作，刷（flush）和寫（write），區(qū)分這兩個概念是很重要的。在大多數(shù)的操作系統(tǒng)中，把Innodb的log buffer（內(nèi)存）寫入日志（調(diào)用系統(tǒng)調(diào)用write），只是簡單的把數(shù)據(jù)移到操作系統(tǒng)緩存中，操作系統(tǒng)緩存同樣指的是內(nèi)存。并沒有實際的持久化數(shù)據(jù)。所以，通常設(shè)為0和2的時候，在崩潰或斷電的時候會丟失最后一秒的數(shù)據(jù)，因為這個時候數(shù)據(jù)只是存在于操作系統(tǒng)緩存。之所以說“通常”，可能會有丟失不只1秒的數(shù)據(jù)的情況，比如說執(zhí)行flush操作的時候阻塞了。

總結(jié)

設(shè)為1當(dāng)然是最安全的，但性能頁是最差的（相對其他兩個參數(shù)而言，但不是不能接受）。如果對數(shù)據(jù)一致性和完整性要求不高，完全可以設(shè)為2,如果只最求性能，例如高并發(fā)寫的日志服務(wù)器，設(shè)為0來獲得更高性能

慢查詢?nèi)罩荆簊low query log

顧名思義，慢查詢?nèi)罩局杏涗浀氖菆?zhí)行時間較長的query，也就是我們常說的slow query。慢查詢?nèi)罩静捎玫氖呛唵蔚奈谋靖袷?，可以通過各種文本編輯器查看其中的內(nèi)容。其中記錄了語句執(zhí)行的時刻，執(zhí)行所消耗的時間，執(zhí)行用戶，連接主機等相關(guān)信息。慢查詢?nèi)罩镜淖饔茫?慢查詢?nèi)罩臼怯脕碛涗泩?zhí)行時間超過指定時間的查詢語句。通過慢查詢?nèi)罩?，可以查找出哪些查詢語句的執(zhí)行效率很低，以便進行優(yōu)化。一般建議開啟，它對服務(wù)器性能的影響微乎其微，但是可以記錄mysql服務(wù)器上執(zhí)行了很長時間的查詢語句?？梢詭椭覀兌ㄎ恍阅軉栴}的。MySQL 還提供了專門用來分析滿查詢?nèi)罩镜墓ぞ叱绦騧ysqldumpslow，用來幫助數(shù)據(jù)庫管理人員解決可能存在的性能問題。

查看慢查詢?nèi)罩镜亩x：

mysql> show global variables like '%slow_query_log%';
+---------------------+--------------------------------------+
| Variable_name       | Value                                |
+---------------------+--------------------------------------+
| slow_query_log      | OFF                                  |
| slow_query_log_file | /usr/local/mysql/data/mysql-slow.log |
+---------------------+--------------------------------------+
2 rows in set (0.01 sec)
 
mysql> show global variables like '%long%';
+----------------------------------------------------------+-----------+
| Variable_name                                            | Value     |
+----------------------------------------------------------+-----------+
| long_query_time                                          | 10.000000 |
| performance_schema_events_stages_history_long_size       | 10000     |
| performance_schema_events_statements_history_long_size   | 10000     |
| performance_schema_events_transactions_history_long_size | 10000     |
| performance_schema_events_waits_history_long_size        | 10000     |
+----------------------------------------------------------+-----------+
5 rows in set (0.00 sec)

啟動和設(shè)置慢查詢?nèi)罩荆?/p>

方法1：通過配置文件my.cnf開啟慢查詢?nèi)罩荆?/p>

注：在不同的mysql版本中，開啟慢查詢?nèi)罩緟?shù)不太一樣，不過都可以通過 show variables like "%slow%" 和show variables like "%long%"查看出來。

mysql> show global variables like '%slow%';
+---------------------------+--------------------------------------+
| Variable_name             | Value                                |
+---------------------------+--------------------------------------+
| log_slow_admin_statements | OFF                                  |
| log_slow_slave_statements | OFF                                  |
| slow_launch_time          | 2                                    |
| slow_query_log            | OFF                                  |
| slow_query_log_file       | /usr/local/mysql/data/mysql-slow.log |
+---------------------------+--------------------------------------+
5 rows in set (0.00 sec)

其中： slow_query_log： off關(guān)閉狀態(tài) on開啟狀態(tài) slow_query_log_file 慢查詢?nèi)罩敬娣诺攸c long_query_time選項來設(shè)置一個時間值，時間以秒為單位，可以精確到微秒。如果查詢時間超過了這個時間值（默認(rèn)為10秒），這個查詢語句將被記錄到慢查詢?nèi)罩局? 設(shè)置為0的話表示記錄所有的查詢。 slow_launch_time 表示如果建立線程花費了比這個值更長的時間,slow_launch_threads 計數(shù)器將增加注：如果不指定存儲路徑，慢查詢?nèi)罩灸J(rèn)存儲到mysql數(shù)據(jù)庫的數(shù)據(jù)文件下，如果不指定文件名，默認(rèn)文件名為hostname-slow.log 修改my.cnf文件：

[mysqld]
slow_query_log=1
slow_query_log_file=/usr/local/mysql/data/mysql-slow.log
long_query_time=1
slow_launch_time=1
#重啟mysqld服務(wù) 再次查詢慢查詢?nèi)罩径x

方法2：通過登錄mysql服務(wù)器直接定義，方式如下：

mysql> set global slow_query_log=1;		#開啟慢查詢?nèi)罩?
Query OK, 0 rows affected (0.00 sec)
 
mysql> set session long_query_time=0.0001;		#更改時間（當(dāng)前session中，退出則重置）
 
 
Query OK, 0 rows affected (0.00 sec)
 
mysql> set global long_query_time=0.0001;		#更改時間（全局中，重啟服務(wù)則重置）
Query OK, 0 rows affected (0.00 sec)
 
mysql> show variables like 'long%';			#查詢定義時間
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 0.000100 |
+-----------------+----------+
1 row in set (0.00 sec)

查看慢查詢?nèi)罩?/p>

[root@mysql ~]# cat /usr/local/mysql/data/mysql-slow.log

用系統(tǒng)查看文件內(nèi)容命令如cat直接查看慢日志文件第一行表示記錄日志時的時間。其格式是 YYYY-MM-DD HH:MM:SS。我們可以看出上面的查詢記錄于 2016 年8 月 29 日下午 15:47：24 - 注意：這個是服務(wù)器時間. MySql 用戶、服務(wù)器以及主機名第三行表示總的查詢時間、鎖定時間、"發(fā)送"或者返回的行數(shù) Query_time: 0.000304 表示用了0.000304秒 Lock_time: 0.000128 表示鎖了0.000128秒 Rows_sent: 4 表示返回4行 Rows_examined: 4 表示一共查了4行 SET timestamp=UNIXTIME; 這是查詢實際發(fā)生的時間何將其變成一個有用的時間，將 Unix 時間轉(zhuǎn)成一個可讀的時間，可以使用 date –d@日志中的時間戳以看到查詢進行的同時記錄了該日志，但是對于一臺超負(fù)載的服務(wù)器常常并非如此。因此記住：SETtimestamp= value 才是實際的查詢的執(zhí)行時間。

慢查詢分析mysqldumpslow 們可以通過打開log文件查看得知哪些SQL執(zhí)行效率低下。從日志中，可以發(fā)現(xiàn)查詢時間超過long_query_time時間的query為慢查詢，而小于long_query_time時間的沒有出現(xiàn)在此日志中。如果慢查詢?nèi)罩局杏涗泝?nèi)容很多，可以使用mysqldumpslow工具（MySQL客戶端安裝自帶）來對慢查詢?nèi)罩具M行分類匯總。mysqldumpslow對日志文件進行了分類匯總，顯示匯總后摘要結(jié)果進入log的存放目錄，運行

[root@mysql ~]# mysqldumpslow /usr/local/mysql/data/mysql-slow.log

注： mysqldumpslow -s c -t 10 /database/mysql/slow-query.log 這會輸出記錄次數(shù)最多的10條SQL語句，其中： -s, 是表示按照何種方式排序，c、t、l、r分別是按照記錄次數(shù)、時間、查詢時間、返回的記錄數(shù)來排序，ac、at、al、ar，表示相應(yīng)的倒序； -t, 是top n的意思，即為返回前面多少條的數(shù)據(jù)； -g, 后邊可以寫一個正則匹配模式，大小寫不敏感的；

例如： /path/mysqldumpslow -s r -t 10/database/mysql/slow-log 得到返回記錄集最多的10個查詢。/path/mysqldumpslow -s t -t 10 -g “left join” /database/mysql/slow-log 得到按照時間排序的前10條里面含有左連接的查詢語句。