簡介
該篇文章對MySQL中的日誌進行總結與簡單介紹,不會涉及的太深。主要的目的是為了對MySQL中的日誌檔案有一個體系化的瞭解。後面會對每一種日誌檔案做具體的分析與總結。
日誌分類
MySQL中的日誌檔案,配置檔案、錯誤日誌檔案、二進位制檔案(binary log)、慢查詢日誌(slow-query-log)、全量日誌(genera log)、審計日誌(audit log)、資料庫檔案&資料表檔案、儲存引擎檔案、中繼日誌(relay log)、程式檔案(PID)和Socket檔案。
引數檔案
引數檔案就是MySQL中的配置檔案,在Linux下的my.cnf檔案、Windows下的my.ini檔案。檔案內容主要分為server和client兩個模組。server模組配置的是有關MySQL的服務資訊,例如慢查詢日誌。client模組配置的是有關MySQL客戶端連線資訊,例如客戶端連線的埠號。
檔案格式大致如下:
[client]
port = 3306
default-character-set = utf8mb4
[mysqld]
user = mysql
port = 3306
sql_mode = ""
default-storage-engine = InnoDB
default-authentication-plugin = mysql_native_password
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect = 'SET NAMES utf8mb4'
slow_query_log
long_query_time = 3
slow-query-log-file = /var/lib/mysql/mysql.slow.log
log-error = /var/lib/mysql/mysql.error.log
default-time-zone = '+8:00'
錯誤日誌檔案
錯誤日誌檔案記錄了MySQL從啟動、執行和關閉幾個環節中的日誌資訊。例如連線MySQL連線失敗、查詢命令錯誤、SQL執行流程等等。對於定位MySQL錯誤有著很大的幫助。
檔案大致內容如下:
Version: '5.7.28-log' socket: '/var/run/mysqld/mysqld.sock' port: 3306 MySQL Community Server (GPL)
2021-04-17T21:23:00.865868Z 3 [Note] Aborted connection 3 to db: 'exam_wechat' user: 'root' host: '172.18.0.1' (Got timeout reading communication packets)
2021-04-17T21:23:00.865969Z 2 [Note] Aborted connection 2 to db: 'exam_wechat' user: 'root' host: '172.18.0.1' (Got timeout reading communication packets)
2021-04-19T22:33:24.137143Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 18415ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
2021-04-20T07:03:21.765208Z 79 [Note] Access denied for user 'root'@'172.18.0.1' (using password: NO)
2021-04-20T07:03:23.825044Z 81 [Note] Aborted connection 81 to db: 'unconnected' user: 'root' host: '172.18.0.1' (Got an error reading communication packets)
2021-04-20T07:14:25.033983Z 82 [Note] Access denied for user 'root'@'172.18.0.1' (using password: NO)
2021-04-20T07:14:27.442608Z 84 [Note] Aborted connection 84 to db: 'unconnected' user: 'root' host: '172.18.0.1' (Got an error reading communication packets)
2021-04-20T07:27:13.971644Z 83 [Note] Aborted connection 83 to db: 'unconnected' user: 'root' host: '172.18.0.1' (Got timeout reading communication packets)
2021-04-20T07:41:02.916249Z 85 [Note] Aborted connection 85 to db: 'unconnected' user: 'root' host: '172.18.0.1' (Got timeout reading communication packets)
如何開始錯誤日誌。只要在MySQL中的配置檔案中配置意向log_error即可。
mysql root@127.0.0.1:(none)> show variables like '%log_error%';
+---------------------+--------------------------------+
| Variable_name | Value |
+---------------------+--------------------------------+
| binlog_error_action | ABORT_SERVER |
| log_error | /var/lib/mysql/mysql.error.log |
| log_error_verbosity | 3 |
+---------------------+--------------------------------+
3 rows in set
Time: 0.010s
全量日誌檔案
全量日誌檔案記錄的是MySQL所有的SQL操作日誌記錄。例如增刪改查等操作都會被記錄下來。
mmysql root@127.0.0.1:(none)> show variables like '%general%';
Reconnecting...
+------------------+---------------------------------+
| Variable_name | Value |
+------------------+---------------------------------+
| general_log | OFF |
| general_log_file | /var/lib/mysql/7fdc5f723ff9.log |
+------------------+---------------------------------+
配置項有三種值,table,none和file。配置file則會記錄在日誌檔案中,配置none則不會記錄,配置table則會在MySQL預設的MySQL資料中建立一張表(表名叫做general-log)來記錄日誌。
不推薦開啟,記錄的日誌檔案太多,不僅僅有效能消耗同時也佔用太多無效空間。
# 日誌記錄檔案格式
mysqld, Version: 5.7.28-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /var/run/mysqld/mysqld.sock
Time Id Command Argument
2021-04-20T09:16:48.572888Z 88 Connect root@172.18.0.1 on using TCP/IP
2021-04-20T09:16:48.574591Z 88 Connect Access denied for user 'root'@'172.18.0.1' (using password: NO)
2021-04-20T09:16:50.325379Z 89 Connect root@172.18.0.1 on using TCP/IP
2021-04-20T09:16:50.329894Z 89 Query select connection_id()
2021-04-20T09:16:50.335222Z 89 Query SELECT @@VERSION
2021-04-20T09:16:50.339432Z 90 Connect root@172.18.0.1 on using TCP/IP
2021-04-20T09:16:50.339621Z 89 Query SELECT @@VERSION_COMMENT
2021-04-20T09:16:50.343525Z 90 Query select connection_id()
2021-04-20T09:16:50.347115Z 90 Query SHOW DATABASES
2021-04-20T09:16:50.380236Z 90 Query select TABLE_NAME, COLUMN_NAME from information_schema.columns
where table_schema = 'None'
order by table_name,ordinal_position
2021-04-20T09:16:50.391019Z 90 Query SELECT CONCAT("'", user, "'@'",host,"'") FROM mysql.user
2021-04-20T09:16:50.415062Z 90 Query SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE="FUNCTION" AND ROUTINE_SCHEMA = "None"
2021-04-20T09:16:50.432015Z 90 Query SELECT name from mysql.help_topic WHERE name like "SHOW %"
2021-04-20T09:16:52.572608Z 89 Query show variables like '%general%'
2021-04-20T09:17:13.532046Z 89 Query show variables like '%general%'
慢查詢日誌
慢查詢日誌是定位SQL語句查詢快與慢而記錄的一種日誌檔案。當某一條SQL語句查詢時間超過一個固定的閾值,這條SQL語句將被定義為慢查詢的SQL語句,被記錄在慢查詢日誌檔案中。
慢查詢的配置主要有如下三個引數。
是否開啟慢查詢與慢查詢日誌檔案。
mysql root@127.0.0.1:(none)> show variables like '%slow%';
+---------------------------+-------------------------------+
| Variable_name | Value |
+---------------------------+-------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/mysql.slow.log |
+---------------------------+-------------------------------+
5 rows in set
Time: 0.014s
慢查詢時間閾值。
mysql root@127.0.0.1:(none)> show variables like '%long_query_time%';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 3.000000 |
+-----------------+----------+
1 row in set
Time: 0.013
二進位制日誌檔案
二進位制日誌(binary log)檔案用於記錄MySQL的DML語句,記錄了操作之後的物理日誌內容,不會記錄MySQL中的select、show等語句。二進位制日誌檔案主要的作用如下:
使用者主從複製,主伺服器將二進位制檔案中的物理日誌傳送給從伺服器,從伺服器在將日誌寫入到自身。
用於資料恢復。根據物理日誌,找回資料丟失之前的操作日誌。
可以通過如下幾個引數進行配置:
mysql root@127.0.0.1:(none)> show variables like '%log_bin%';
Reconnecting...
+---------------------------------+--------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/mysql-bin |
| log_bin_index | /var/lib/mysql/mysql-bin.index |
+---------------------------------+--------------------------------+
6 rows in set
Time: 0.015s
log_bin是否開啟二進位制日誌檔案,log_bin_basename儲存的目錄以及日誌檔案字首,log_bin_index儲存日誌檔案索引(日誌檔名稱)。如果日誌檔案沒有指定檔名稱,則預設使用本機名稱。
日誌檔案列表。
-rw-r----- 1 mysql root 154 Apr 12 09:31 mysql-bin.000041
-rw-r----- 1 mysql root 154 Apr 12 19:45 mysql-bin.000042
-rw-r----- 1 mysql root 1459325 Apr 17 20:26 mysql-bin.000043
-rw-r----- 1 mysql mysql 24576 Apr 17 22:18 mysql-bin.000044
# cat mysql-bin.index
./mysql-bin.000001
./mysql-bin.000002
./mysql-bin.000003
./mysql-bin.000004
./mysql-bin.000005
./mysql-bin.000006
審計日誌
審計日誌用來記錄MySQL的網路活動,對MySQL的操作記錄做統計、分析與報告等。屬於對MySQL安全監控記錄類的日誌檔案。
MySQL自身不包含該功能的,並且該功能在MySQL官網也是收費的。這裡也不做具體的演示。
中繼日誌
中繼日誌是MySQL主從複製,在從伺服器上的一個重要角色。當主伺服器將二進位制檔案傳送給從伺服器時,從伺服器不會立馬執行,而是放在一個指定的一類日誌檔案中,從伺服器在開啟一個SQL執行緒去讀取中繼日誌檔案內容並寫入到自身資料中。
PID檔案
PID是一個MySQL例項的程式檔案號。MySQL屬於單程式服務,在啟動一個MySQL例項,就會建立一個PID檔案。
Socket檔案
Socket也是MySQL通訊的一種方式。MySQL通訊有兩種方式,TCP和Socket方式。TCP是走網路通訊,可以將服務部署到任意可以訪問的伺服器上。Socket是走的檔案通訊方式,必須在同一臺伺服器上。
# TCP模式
mysql -hxxxx -pxxxx -uxxxx -Pxxx
mysql -uxxxx -pxxxx -s /path/socket
資料庫與表
資料庫與表值的就是MySQL中的表結構檔案、資料檔案和索引檔案。
InnoDB儲存引擎的資料表結構
-rw-r----- 1 mysql root 13650 Apr 13 09:46 wechat_user.frm
-rw-r----- 1 mysql mysql 98304 Apr 17 13:43 wechat_user.ibd
MyISAM儲存引擎的資料表結構
-rw-r----- 1 mysql mysql 0 Apr 20 17:53 users.MYD
-rw-r----- 1 mysql mysql 1024 Apr 20 17:53 users.MYI
-rw-r----- 1 root root 8586 Apr 20 17:53 users.frm
儲存引擎檔案
不同的儲存引擎,實現起來也不同。InnoDB儲存引擎分為redolog和undolog兩種日誌檔案。
本作品採用《CC 協議》,轉載必須註明作者和本文連結