MySQL日志詳解

前言

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

MySQL的日志分為以下四大類：

• 錯誤日志：記錄mysql服務的啟動，運行或停止mysql服務時出現的問題；
• 查詢日志：記錄建立的客戶端的連接和執行的語句；
• 二進制日志：記錄所有更改數據的語句，可以用于數據的復制；
• 慢查詢日志：記錄所有執行的時間超過long_query_time的所有查詢或不使用索引的查詢。

默認情況下，所有日志創建于MySQL數據目錄中，通過刷新日志，可以強制MySQL關閉和重新打開日志文件，Flush logs刷新日志或者執行mysqladmin flush-logs 如果正使用MySQL復制功能，在復制服務器上可以維護更多日志文件，這種日志我們稱為接替日志。啟動日志功能會降低MySQL數據庫的性能。

1）查看系統設置

<!--查看全局的系統狀態-->
mysql> show global variables\G
mysql> show global variables like '%log%';
<!--查看當前會話的系統狀態-->
mysql> show session variables\G
mysql> show session variables like '%log%';

若要修改上面查看出來的參數，可以在MySQL的主配置文件中的mysqld字段中寫入即可，如：binlog_cache_size = 1M。又或者可以在MySQL數據庫中進行臨時修改：set global binlog_cache_size = 1048576，這種臨時修改在MySQL重啟后就會失效。

2）查看運行狀態

<!--查看全局的運行狀態-->
mysql> show global status\G
<!--查看當前會話的運行狀態-->
mysql> show session status;
<!--查看MySQL的版本-->
[root@mysql ~]# mysql -V
mysql> status;
mysql> select version();

1、錯誤日志

在mysql數據庫中，錯誤日志功能是默認開啟的。默認情況下，錯誤日志存儲在mysql數據庫的數據目錄中。錯誤日志文件通常的名稱為hostname.err。其中，hostname表示服務器主機名。 錯誤日志信息可以自己進行配置的，錯誤日志所記錄的信息是可以通過log-error和log-warnings來定義的，其中log-error是定義是否啟用錯誤日志的功能和錯誤日志的存儲位置，log-warnings是定義是否將警告信息也定義至錯誤日志中。默認情況下錯誤日志大概記錄以下幾個方面的信息：服務器啟動和關閉過程中的信息（未必是錯誤信息，如mysql如何啟動InnoDB的表空間文件的、如何初始化自己的存儲引擎的等等）、服務器運行過程中的錯誤信息、事件調度器運行一個事件時產生的信息、在從服務器上啟動服務器進程時產生的信息，MySQL有很多系統變量可以設置，系統變量設置不同，會導致系統運行狀態的不同。因此mysql提供兩組命令，分別查看系統設置和運行狀態。

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

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

其中 log_error定義為錯誤日志文件路徑 ，log_error_verbosity值得含義如下:

錯誤日志的存放路徑在my.cnf的主配置文件中指定，如下：

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

刪除錯誤日志
在mysql5.5.7之前：數據庫管理員可以刪除很長時間之前的錯誤日志，以保證mysql服務器上的硬盤空間。mysql數據庫中，可以使用mysqladmin命令開啟新的錯誤日志。mysqladmin命令的語法如下：mysqladmin –u root –p flush-logs也可以登錄mysql數據庫中使用FLUSH LOGS語句來開啟新的錯誤日志。 在mysql5.5.7之后：服務器將關閉此項功能。只能使用重命名原來的錯誤日志文件，手動沖洗日志創建一個新的，方式如下：

[root@mysql ~]# cd /usr/local/mysql/data/
[root@mysql data]# mv mysqld.err{,.old}
[root@mysql data]# mysqladmin -uroot -p flush-logs
Enter password: 

2、二進制日志

二進制日志主要記錄MySQL數據庫的變化，二進制日志以一種有效的格式，并且是事務安全的方式包含更新日志中可用的信息。二進制日志包含了所有更新了數據或者已經潛在更新了數據。還包含關于每個更新數據庫的語句的執行時間，它不包含沒有修改任何數據的語句。使用二進制日志的主要目的是最大可能地恢復數據庫。

1）啟動二進制日志（默認情況下二進制日志是關閉的）

[root@mysql data]# vim /etc/my.cnf      #編輯主配置文件

[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
port=3306
server_id=1
socket=/usr/local/mysql/mysql.sock
log-error=/usr/local/mysql/data/mysqld.err
log-bin=/usr/local/mysql/data/binary_log      #指定二進制日志的路徑及名稱
expire_logs_days=10         #清除日志的天數
max_binlog_size=100M      #單個日志文件的大小限制，超出會新建一個日志文件
[root@mysql data]# systemctl restart mysqld      #重啟MySQL使配置生效
[root@mysql data]# ll | grep binary         #會在指定的路徑下生成以下兩個文件
-rw-r----- 1 mysql mysql      154 12月 30 20:59 binary_log.000001
-rw-r----- 1 mysql mysql       40 12月 30 20:59 binary_log.index

登錄到數據庫中也可以查看到，如下：

2）查看二進制日志

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

可以通過mysqlbinlog命令查看：

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

也可以退出MySQL在命令行使用mysqlbinlog命令查看：

[root@mysql data]# mysqlbinlog binary_log.000001 

3）刪除二進制日志

MySQL的二進制文件可以配置自動刪除，同時MySQL提供了手動刪除二進制文件的方法：
RESET MASTER：刪除所有的二進制日志文件；
PURGE MASTER LOGS：只刪除部分二進制日志文件；
Reset master：刪除所有二進制日志 ；
Purge master logs to ‘二進制名’ ：刪除單個二進制日志之前的。

mysql> purge master logs to "binary_log.000003";   <!--刪除...03之前的二進制日志文件-->
mysql> purge master logs before '20180101';    <!--刪除2018-01-01之前的日志文件-->

4）通過二進制日志還原MySQL數據

關于通過二進制日志還原的具體過程，還是參考我之前的博文吧！如下：
MySQL的備份與恢復詳解

3、事務日志

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

1）查看事務日志的定義

mysql> show global variables like 'innodb_lo%';

在上述指令輸出的部分內容解釋如下：

| innodb_flush_log_at_trx_commit | 1 #在事務提交時innodb是否同步日志從緩沖區到文件
中，當這個值為1（默認值）之時，在每個事務提交時，日志緩沖被寫到日志文件，對日志文件做到磁盤操作的刷新，性能會很差造成大量的磁盤I/O但這種方式最安全；如果設為2,每次提交事務都會寫日志，但并不會執行刷的操作。每秒定時會刷到日志文件。要注意的是，并不能保證100%每秒一定都會刷到磁盤，這要取決于進程的調度。每次事務提交的時候將數據寫入事務日志，而這里的寫入僅是調用了文件系統的寫入操作，而文件系統是有緩存的，所以這個寫入并不能保證數據已經寫入到物理磁盤。設置為0，日志緩沖每秒一次地被寫到日志文件，并且對日志文件做到磁盤操作的刷新，但是在一個事務提交不做任何操作。
注：刷寫的概念
刷寫其實是兩個操作，刷（flush）和寫（write），區分這兩個概念是很重要的。在大多數的操作系統中，把Innodb的log buffer（內存）寫入日志（調用系統調用write），只是簡單的把數據移到操作系統緩存中，操作系統緩存同樣指的是內存。并沒有實際的持久化數據。

所以，通常設為0和2的時候，在崩潰或斷電的時候會丟失最后一秒的數據，因為這個時候數據只是存在于操作系統緩存。之所以說“通常”，可能會有丟失不只1秒的數據的情況，比如說執行flush操作的時候阻塞了。
總結
設為1當然是最安全的，但性能頁是最差的（相對其他兩個參數而言，但不是不能接受）。如果對數據一致性和完整性要求不高，完全可以設為2,如果只最求性能，例如高并發寫的日志服務器，設為0來獲得更高性能。

| 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中設置幾組事務日志，默認是2 
| innodb_log_group_home_dir | ./ #定義innodb事務日志組的位置,此位置設置默認為MySQL的datadir 。

每個事務日志都是大小為50兆的文件（不同版本的mysql有差異）： 在mysql中默認以ib_logfile0,ib_logfile1名稱存在。

4、慢查詢日志（slow query log）

顧名思義，慢查詢日志中記錄的是執行時間較長的query，也就是我們常說的slow query。 慢查詢日志采用的是簡單的文本格式，可以通過各種文本編輯器查看其中的內容。其中 記錄了語句執行的時刻，執行所消耗的時間，執行用戶，連接主機等相關信息。 慢查詢日志的作用： 慢查詢日志是用來記錄執行時間超過指定時間的查詢語句。通過慢查詢日志，可以查找出哪些查詢語句的執行效率很低，以便進行優化。一般建議開啟，它對服務器性能的影響微乎其微，但是可以記錄mysql服務器上執行了很長時間的查詢語句。可以幫助我們定位性能問題的。MySQL 還提供了專門用來分析滿查詢日志的工具程序mysqldumpslow，用來幫助數據庫管理人員解決可能存在的性能問題。

1）查看慢查詢日志的定義

注：在不同的mysql版本中，開啟慢查詢日志參數不太一樣，不過都可以通過 show variables like "%slow%" 和show variables like "%long%"查看出來。

mysql> show global variables like '%slow_query_log%';    <!--查詢慢查詢日志是否開啟-->
+---------------------+--------------------------------------+
| Variable_name       | Value                                |
+---------------------+--------------------------------------+
| slow_query_log      | OFF                                  |     <!--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)
             <!--
在上面的結果中，long_query_time的值就是慢查詢超時時間，
默認為10s，只要執行耗時超過10s的語句就被定義為慢查詢語句
                                 -->

2）啟動和設置慢查詢日志

啟動慢查詢日志記錄：

方法1：通過配置文件my.cnf開啟慢查詢日志：

[root@mysql ~]#  vim /etc/my.cnf      #編輯主配置文件，在mysqld字段下寫入以下幾行
[mysqld]
slow_query_log=1             # 1表示開啟慢查詢
slow_query_log_file=/usr/local/mysql/data/mysql-slow.log   #指定慢查詢日志位置
long_query_time=0.0001         #指定超時時間，也就是超過這個時間就會被記錄到慢查詢日志
slow_launch_time=1        #如果建立線程花費了比這個值更長的時間，slow_launch_threads計數器將增加
[root@mysql ~]# systemctl restart mysqld          #重啟服務使配置生效

再次登錄數據庫查看相關信息，會發現更改已經生效，如下：

方法2：通過登錄MySQL服務器直接定義，方式如下：

mysql> set global slow_query_log=1;   
mysql> set global long_query_time=0.0001;

在上面的定義中，global表示全局生效，還有一個選項是session，表示僅在當前會話生效，其區別是，session在退出當前會話后就會被重置，global則是在重啟MySQL服務后才會被重置，而方法1中寫入配置文件中的方法，則是真正的永久生效。

注意：如果主配置文件中定義了long_query_time的值，并且MySQL命令行中使用set指令又定義了long_query_time的值，則配置文件中定義的值優先生效。

修改后的相關設置如下：

在終端命令行使用mysqldumpslow命令工具查看慢查詢日志：

若想要查詢到慢查詢日志，必須保證兩點，首先是將慢查詢的超時時間設置的短一些，比如我在上面設置為了0.0001，只要查詢的時間超過了這個值，則被定義為慢查詢。（為了驗證效果，在生產環境中還是要結合實際情況）。第二呢，就是在開啟慢查詢后還需要執行幾條查詢語句，以便產生日志記錄信息（自己隨便查詢兩句吧）。

mysqldumpslow指令的部分選項解釋：

使用選項查看慢查詢日志：

[root@mysql data]# mysqldumpslow -t 2 -a -s c mysql-slow.log
#以次數來排序，查詢前兩條，并且顯示語句的所有內容

5、數據文件

在MySQL 中每一個數據庫都會在定義好（或者默認）的數據目錄下存在一個以數據庫名字命名的文件夾，用來存放該數據庫中各種表數據文件。不同的MySQL 存儲引擎有各自不同的數據文件。如MyISAM 用“.MYD”作為擴展名，Innodb 用“.ibd”，Archive 用“.arc”，CSV 用“.csv”，等等。

“.frm”文件 與表相關的元數據（meta）信息都存放在“.frm”文件中，包括表結構的定義信息等。不論是什么存儲引擎（MySQL常用的兩個存儲引擎是MyISAM和InnoDB），每一個表都會有一個以表名命名的“.frm”文件。所有的“.frm”文件都存放在所屬數據庫的文件夾下面。

MyISAM數據庫表文件

• .MYD文件：表數據文件；.MYI文件：索引文件
• “.MYD”文件 “.MYD”文件是MyISAM存儲引擎專用，存放MyISAM 表的數據。每一個MyISAM 表都會有一個“.MYD”文件與之對應，同樣存放于所屬數據庫的文件夾下，和“.frm”文件在一起
• “.MYI”文件 “.MYI”文件也是專屬于MyISAM 存儲引擎的，主要存放MyISAM 表的索引相關信息。對于MyISAM 存儲來說，可以被cache 的內容主要就是來源于“.MYI”文件中。每
  一個MyISAM 表對應一個“.MYI”文件，存放于位置和“.frm”以及“.MYD”一樣。

Innodb數據庫表文件
InnoDB采用表空間（tablespace）來管理數據，存儲表數據和索引。

• .ibd文件：單表表空間文件，每個表使用一個表空間文件（file per table），存放用戶數據庫表數據和索引。
• InnoDB共享表空間（即InnoDB文件集，ibfile set）：ibdata1、ibdata2等，存儲InnoDB系統信息和用戶數據庫表數據和索引，所有表共用。
• “.ibd”文件和ibdata 文件 這兩種文件都是存放Innodb 數據的文件，之所以有兩種文件來存放Innodb 的數據（包括索引），是因為Innodb 的數據存儲方式能夠通過配置來決定是使用共享表空間存放存儲數據，還是獨享表空間存放存儲數據。獨享表空間存儲方式使用“.ibd”文件來存放數據，且每個表一個“.ibd”文件，文件存放在和MyISAM數據相同的位置。
• 如果選用共享存儲表空間來存放數據，則會使用ibdata 文件來存放，所有表共同使用一個
  （或者多個，可自行配置）ibdata 文件。 ibdata 文件可以通過innodb_data_home_dir 和innodb_data_file_path兩個參數共同配置組成， innodb_data_home_dir 配置數據存放的總目錄， 而innodb_data_file_path 配置每一個文件的名稱。 innodb_data_file_path 中可以一次配置多個ibdata 文件。文件可以是指定大小，也可以是自動擴展的，但是Innodb 限制了僅僅只有最后一個ibdata 文件能夠配置成自動擴展類型。當我們需要添加新的ibdata 文件的時候，只能添加在innodb_data_file_path配置的最后，而且必須重啟MySQL 才能完成ibdata 的添加工作。不過如果我們使用獨享表空間存儲方式的話，就不會有這樣的問題。

總結
共享表空間以及獨占表空間都是針對數據的存儲方式而言的。 共享表空間: 某一個數據庫的所有的表數據，索引文件全部放在一個文件中。 獨占表空間: 每一個表都將會生成以獨立的文件方式來進行存儲，每一個表都有一個.frm表描述文件，還有一個.ibd文件。其中這個文件包括了單獨一個表的數據內容以及索引內容。

兩者之間的優缺點
共享表空間：
優點： 可以放表空間分成多個文件存放到各個磁盤上。數據和文件放在一起方便管理。
缺點： 所有的數據和索引存放到一個文件中，多個表及索引在表空間中混合存儲，這樣對于一個表做了大量刪除操作后表空間中將會有大量的空隙，特別是對于統計分析，日值系統這類應用最不適合用共享表空間。
獨立表空間：
優點：

• 每個表都有自已獨立的表空間。
• 每個表的數據和索引都會存在自已的表空間中。
• 可以實現單表在不同的數據庫中移動。
• 空間可以回收
  • a) Drop table操作自動回收表空間，如果對于統計分析或是日值表，刪除大量數據后可以通過:alter table TableName engine=innodb;回縮不用的空間。
  • b)對于使用獨立表空間的表，不管怎么刪除，表空間的碎片不會太嚴重的影響性能，而且還有機會處理。
    缺點： 單表增加過大，如超過100個G。 相比較之下，使用獨占表空間的效率以及性能會更高一點。

1）查看當前數據庫的表空間管理類型

mysql> show variables like '%innodb_file_per%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+

ON代表獨立表空間管理，OFF代表共享表空間管理；（查看單表的表空間管理方式，需要查看每個表是否有單獨的數據文件）。

2）修改為Innodb共享表空間

[root@mysql ~]# vim /etc/my.cnf        #編輯主配置文件，在mysqld字段下寫入以下配置
innodb_file_per_table=0   #0為使用共享表空間，1為獨占表空間
innodb_data_file_path=ibdata1:100M:autoextend    #設置一個可自動擴展大小，尺寸為100M的數據文件
innodb_data_home_dir=/usr/local/mysql/data #定義共享表空間默認存放路徑
[root@mysql ~]# systemctl restart mysqld    #啟動報錯了
Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.

查看日志如下：

注：不同版本的mysql報錯略有不同，注意看錯誤日志的內容。大概意思就是設置的pages頁為6400，超過了它的最大值4864，那么算一下，設置的初始大小100M，pages大小就是6400，則表示1M=64pages，它的最大值是4864，也就是說，我們設置初始大小最大可以是4864/64=76M。
再次修改配置文件如下：

[root@mysql ~]# vim /etc/my.cnf        #再次修改主配置文件
innodb_data_file_path=ibdata1:76M:autoextend
#修改初始大小為76M
[root@mysql ~]# systemctl restart mysqld          #再次重啟MySQL服務
#同樣，如果還是啟動失敗，則再次查看錯誤日志，是否pages頁大小設置的還是不合理

3）查看修改后數據庫的表空間管理類型

此時狀態為OFF，則表示使用的是共享表存儲空間，之后創建的表都會使用定義的共享表空間來存儲數據。

———————— 本文至此結束，感謝閱讀 ————————