1.MySQL 服務器簡介

通常所説的 MySQL 服務器指的是mysqld程序，當運⾏mysqld後對外提供MySQL 服務

1.1服務器配置和默認值

• mysqld 有很多選項和系統變量可以在啓動時進⾏配置，要查看服務器的默認選項和系統變量值，

可以執⾏以下命令：

# 查看所有mysqld 選項和可配置的系統變量列表及默認值
root@guangchen-vm:/var/lib/mysql# mysqld --verbose --help
# 內容看起來像這樣
abort-slave-event-count 0
allow-suspicious-udfs FALSE
archive ON
auto-increment-increment 1
auto-increment-offset 1
autocommit TRUE
automatic-sp-privileges TRUE
avoid-temporal-upgrade FALSE
back-log 80
basedir /usr/
... 省略
tmpdir /tmp
transaction-alloc-block-size 8192
transaction-isolation REPEATABLE-READ
transaction-prealloc-size 4096
transaction-read-only FALSE
transaction-write-set-extraction XXHASH64
updatable-views-with-limit YES

• 查看服務器在運⾏時系統變量的值，連接到MySQL並執⾏以下語句：

• 查看服務器在運⾏時的⼀些統計和狀態指⽰器，連接到MySQL並執⾏以下語句：

• 系統變量和狀態信息也可以使⽤ mysqladmin命令來查看

root@guangchen-vm:~# mysqladmin variables
root@guangchen-vm:~# mysqladmin extend-status

關於服務器的選項，系統變量，狀態變量可以參考MySQL官網

注意：系統變量，狀態變量的作用域分為：Global(全局)，Session（當前會話或連接），或 兩者都支持

1.2系統變量和選項

簡介：

當通過mysqld啓動數據庫服務器時，可以通過選項⽂件或命令⾏中提供選項，但是，在⼤多數情

況下，為確保服務器每次運⾏時都使⽤相同的選項，最好的⽅法是在選項⽂件中指定相應的選項。

mysqld從選項⽂件中的 [mysqld] 和 [server] 組(節點)中讀取選項內容

mysqld接受的選項可以通過 mysqld --verbose --help 查看，列表中的有些項⽬是可以在

服務器啓動時設置的系統變量，系統變量可以在連接MySQL後使⽤ SHOW VARIABLES 語句查

看，但有些內容只在 --help 中存在，使⽤ SHOW VARIABLES 時並沒有顯⽰，這是因為它們只

是選項⽽不是系統變量。

注意：選項值可以通過命令⾏和選項⽂件設置，部分系統變量可以通過SET語句動態設置值

常用選項

依舊（WPS + AI）

1.3使用系統變量

1. 以上我們介紹了通過選項⽂件和命令⾏設置相應系統變量的值，設置系統變量的語法與命令選項的語法相同，指定變量名稱時，破折號和下劃線可以互換使⽤。例如， --general_log=ON 和 -

-general-log=ON 是等價的。

2. 當使⽤選項設置⼀個數值的變量時，可以帶有後綴 K 、 M 或 G （⼤⼩寫不限）表⽰ 1024 、

1024^2 或 1024^3 ；從MySQL 8.0.14 開始，後綴也可以⽤ T 、 P 和 E 來表⽰ 1024^4 、

1024^5 或 1024^6 。

⽰例：為服務器指定 256 KB 的排序緩衝區⼤⼩和 1 GB 的最⼤數據包⼤⼩

# 在命令⾏的指定
mysqld --sort-buffer-size=256K --max-allowed-packet=1G

# 在選項⽂件中指定
[mysqld]
sort_buffer_size=256k
max_allowed_packet=1g

3. 系統變量有兩個作⽤域，分別是 Global (全局)和 Session (會話)， Global 全局變量影響服

務器的整體操作， Session 會話變量影響各個客⼾端連接的操作。給定的系統變量可以同時具有

全局值和會話值，它們的關係如下：

◦ 服務器啓動時，會將每個全局變量初始化並設置默認值，具體的值可以通過命令⾏或選項⽂件

更改。

◦ 服務器為每個客⼾端維護⼀組 Session 變量，在客⼾端連接時使⽤相應全局變量的當前值進

⾏初始化。

4. ⼤部分系統變量是動態的，在服務器運⾏時可以通過 SET 語句動態更改，並且⽆需停⽌和重新啓動服務器。在服務器運⾏時，使⽤ SET 語句設置系統變量，需要指定作⽤域(也可以在前⾯加上

@@ 修飾符)，然後指定系統變量的名稱，名稱必須使⽤下劃線⽽不是破折號，如下所⽰：

a. 設置全局系統變量最⼤連接數為1000

mysql> SET GLOBAL max_connections = 1000;
mysql> SET @@GLOBAL.max_connections = 1000;

b. 將全局系統變量持久化到 mysqld-auto.cnf ⽂件（同時設置運⾏時值）：

# PERSIST 表⽰持久化的同時設置全局變量的值

mysql> SET PERSIST max_connections = 1000;
mysql> SET @@PERSIST.max_connections = 1000;

c. 將全局系統變量持久化到 mysqld-auto.cnf ⽂件（不設置運⾏時值）：

# PERSIST 表⽰持久化的同時設置全局變量的值
mysql> SET PERSIST_ONLY max_connections = 1000;
mysql> SET @@PERSIST_ONLY.max_connections = 1000;

刪除持久化的系統變量可以使⽤語句

RESET PERSIST IF EXISTS system_var_name;

d. 設置 Session 系統變量，時區為"+8:00"：

mysql> SET SESSION time_zone='+8:00';
mysql> SET @@SESSION.time_zone='+8:00';
mysql> SET @@time_zone='+8:00';

5. 對於數值型的系統變量，⽤帶有後綴的值指定時，只適⽤於選項⽅式，⽽不能⽤在 SET ⽅式中；SET ⽅式可以使⽤表達式為系統變量指定值，⽽在選項⽅式中不允許，如下所⽰：

# 選項形式
root@guangchen-vm:~# mysqld --max_allowed_packet=16M # 允許
root@guangchen-vm:~# mysqld --max_allowed_packet=16*1024*1024 # 不允許

# 運⾏時SET形式
mysql> SET GLOBAL max_allowed_packet=16M; # 不允許
mysql> SET GLOBAL max_allowed_packet=16*1024*1024; # 允許

説明：在設置全局變量時需要指定GLOBAL關鍵字的原因是為了防⽌出現以下問題:

• 如果要刪除的SESSION變量與GLOBAL變量名相同，那麼具有修改全局變量權限的客⼾端可能會

意外地更改GLOBAL變量，⽽不僅僅是隻修改SESSION變量。

• 如果已經有⼀個SESSION變量⽽且與GLOBAL變量同名，那麼本意是要修改GLOBAL變量，可能

只是修改了SESSION變量的值

所以沒有明確指定 GLOBAL 和 SESSION 時，對於當前客⼾端來説 SESSION 的優先級更⾼

6.要顯⽰系統變量名稱和值，請使⽤以下 SHOW VARIABLES 語句：

mysql> SHOW VARIABLES;

7.可以使⽤ LIKE ⼦句顯⽰與指定內容匹配的變量，也可以使⽤通配符

# 查看指定的系統變量
SHOW VARIABLES LIKE 'max_join_size';
# 查看指定系統變量SESSION作⽤域的值
SHOW SESSION VARIABLES LIKE 'max_join_size';

# 查看包含指定內容的系統變量
SHOW VARIABLES LIKE '%size%';
# 查看包含指定內容系統變量的GLOBAL作⽤域的值
SHOW GLOBAL VARIABLES LIKE '%size%';

8. ⼀部分系統變量是內置的，也有⼀些需要通過安裝服務器插件或組件才可以使⽤

在 Unix 和 Linux 系統上讀取的選項文件

*會逐級覆蓋

1.4服務器常用配置

1.Linux系統下編程 /etc/mysql/my.cnf

注意：
* 編譯前先備份原始文件

* 如果要修改數據目錄選項建議先停止MySQL服務，並把原data目錄整體複製到新路徑，配置完成後重啓服務

在 [mysqld] 節點下添加以下內容

如果數據庫服務在公網部署，建議修改默認端口號

# MySQL 服務節點
[mysqld]

# MySQL 服務啓動後監聽的端⼝號
port=3306

# 數據⽬錄的路徑，這⾥演⽰的是windows下的配置，注意分隔符⽤/，如果是\則需要轉義為\\.
# 把原data⽬錄整體複製到新路徑
datadir=D:/database/MySQL/data8.0

# MySQL 服務器的字符集與排序規則
character-set-server=utf8mb4  //可識別中文...
collation-server=utf8mb4_general_ci 

# 新建表時使⽤的存儲引擎，windows下已默認配置
default-storage-engine=INNODB

具體看WPS

2.Windows系統下打開D:/ProgramData/MySQL/MySQL Server 8.0/my.ini

2.5 查看狀態變量

MySQL服務器維護着當前系統信息的狀態變量（指示類型的變量，不能人為修改）

可以使⽤ SHOW [GLOBAL | SESSION] STATUS [like status_name]; 語句查看這些變量和對應的值。                                                                    （可以使用通配符）

GLOBAL顯⽰所有連接的值，SESSION顯⽰當前連接的值

mysql> SHOW GLOBAL STATUS;
+-----------------------------------+------------+
| Variable_name | Value |
+-----------------------------------+------------+
| Aborted_clients | 0 | #未正確關閉連接⽽中⽌的連接數
| Aborted_connects | 0 | #連接MySQL服務器失敗的次數
| Bytes_received | 155372598 | #從所有客⼾端接收的字節數
| Bytes_sent | 1176560426 | #發送給所有客⼾端的字節數
...
| Connections | 30023 | #嘗試連接到MySQL服務器的次數，不
論成功或失敗
| Created_tmp_disk_tables | 0 | #創建內部磁盤臨時表的數量
| Created_tmp_files | 3 | #創建了臨時⽂件的數量
| Created_tmp_tables | 2 | #創建內部臨時表的數量
...
| Threads_created | 217 | #為處理連接⽽創建的線程數
| Threads_running | 88 | #未休眠的線程數
| Uptime | 1389872 | #服務器已啓動的秒數
+-----------------------------------+------------+

具體含義可看官網

2. MySQL數據目錄

1. 數據⽬錄下的每個⼦⽬錄都是⼀個數據庫⽬錄，對應服務器管理的⼀個數據庫，包括MySQL 安裝成功後創建的標準數據庫：

        a. mysql⽬錄對應於mysql系統庫，包含mysql服務器運⾏時所需的信息，該數據庫包含數據字典表和系統表；

        b. performance_schema⽬錄對應於Performance Schema，提供了在運⾏時⽤於檢查服務器內部執⾏的信息；

        c. sys⽬錄對應於sys系統庫，提供⼀組對象來幫助解釋性能模式相關信息；

        d. 其他⼦⽬錄對應於⽤⼾或應⽤程序創建的數據庫，也就是説我們每創建⼀個數據庫，就會在數據⽬錄⽣成⼀個同名的⽬錄來保存對應的數據。

2. 服務器寫⼊的⽇志⽂件

3. InnoDB 表空間和⽇志⽂件

4. 默認或⾃動⽣成的 SSL 和 RSA 證書和密鑰⽂件

5. 服務器進程 ID ⽂件（當服務器運⾏時）

6. mysqld-auto.cnf ⽂件⽤來存儲持久化全局系統變量設置

通過選項重新配置服務器，可以將上述某些項⽬重新定位到指定⽬錄。

使⽤ --datadir 選項允許更改數據⽬錄本⾝的位置

3.日誌簡介

MySQL Server 有以下⼏種⽇志，可以記錄服務器正在發⽣的活動

默認情況下，除 Windows 上的錯誤⽇志外，不啓⽤任何⽇志，Linux下默認開啓錯誤⽇志和⼆進制

⽇志

• 在服務器運⾏期間可以控制⼀般查詢和慢查詢⽇志的禁⽤與開啓，也可以更改⽇志⽂件名

• ⼀般查詢⽇志和慢查詢⽇志記錄可以寫⼊⽇志表、⽇志⽂件或兩者同時寫⼊

• 默認情況下，所有啓⽤的⽇志將寫⼊數據⽬錄，可以通過刷新⽇志強制服務器關閉並重新打開⽇志⽂件

• 通過 FLUSH LOGS 語句刷新⽇志來強制服務器關閉並重新打開⽇志⽂件，也可以使⽤ mysqladmin 的 flush-logs 或 refresh 參數，或mysqldump 的 --flush-logs 或 --master-data 選項

• 中繼⽇志僅⽤於主從複製過程中的從服務器。

3.1 ⼀般查詢日誌和慢查詢日誌的輸出形式

如果啓⽤⼀般查詢⽇志和慢查詢⽇志，⽇志的輸出⽅式可以指定為⽇志⽂件或 mysql 系統庫中

的 general_log 和 slow_log 表，也可以兩者同時指定。

啓動時的日誌控制

• log_output 系統變量指定⽇志輸出的形式，但並不會真正的啓⽤⽇志。 log_output 可以有

三個值，分別是： TABLE (表)、 FILE (⽂件)（默認值）、 NONE (不輸出)，可以同時指定多個值，並⽤逗號隔開，未指定值時默認是 FILE ，如果列表中存在 NONE 則其他的不⽣效，也就是説 NONE 的優先級最⾼。

• 通過設置 general_log 系統變量的值來控制⼀般查詢⽇志的 開啓 1 與 禁⽤ 0 ，如果要為⽇

志指定⾃定義的路徑或⽂件名可以使⽤ general_log_file 系統變量（絕對路徑）

• 通過設置 slow_query_log 系統變量的值來控制慢查詢⽇志的 開啓 1 與 禁⽤ 0 ，如果要為

⽇志指定⾃定義的路徑或⽂件名可以使⽤ slow_query_log_file 系統變量（絕對路徑）

• ⽰例，以選項⽂件中的配置為例：

◦ 將⼀般查詢⽇志寫⼊⽇志表和⽇志⽂件

[mysqld]
#⽇志寫⼊表和⽂件
log_output=TABLE,FILE 
#開啓⼀般查詢⽇志
general_log=1

◦ 僅將⼀般查詢⽇志和慢查詢⽇志寫⼊⽇志表

[mysqld]
log_output=TABLE #⽇志寫⼊表
general_log=1 #開啓⼀般查詢⽇志
slow_query_log=1 #開啓慢查詢⽇志

◦ 僅將慢查詢⽇志寫⼊⽇志⽂件

[mysqld]
log_output=FILE #⽇志⽂件
slow_query_log=1 #開啓慢查詢⽇志

• 將⼀般查詢⽇志和慢查詢⽇志寫⼊⽇志⽂件，並指定⾃定義的⽇志路徑

[mysqld]
#⽇志⽂件
log_output=FILE 
#開啓⼀般查詢⽇志 
general_log=1 
#指定⾃定義的⽂件名 
general_log_file=/var/lib/mysql/general.log 

#開啓慢查詢⽇志
slow_query_log=1 
#指定⾃定義的⽂件名
slow_query_log_file=/var/lib/mysql/slow_query.log

運行時的日誌控制

• 在運⾏時修改 log_output 的值，以更改⽇志的輸出形式，通過語句控制

• 語法：SET [GLOBAL|SESSION] variable_name=value

SET GLOBAL log_output=[FILE, TABLE, NONE]

•general_log[={0|1}] 和 slow_query_log[={0|1}] 可以表⽰啓⽤和禁⽤⼀般查詢⽇志和慢查詢⽇志

• general_log_file 和 slow_query_log_file 表⽰通⽤查詢⽇志和慢查詢⽇志⽂件名稱

• 只對當前會話禁⽤或啓⽤⼀般查詢⽇志記錄，將 SESSION 作⽤域的 sql_log_off 變量設置

為 ON 或 OFF

使用日誌表的優點

• 可以通過 SQL 語句的條件查詢過濾⽇志內容（使用where 條件），從⽽選擇滿⾜特定條件的⽇志記錄。⽐如，某個客⼾端的⽇志；

• 可以通過客⼾端程序連接到服務器並查詢表中的⽇志信息（使用客户端用SQL語句查詢，應用程序級別），⽆需登錄服務器主機訪問⽂件系統（需要有一個系統用户，並且有相應的權限，系統級別）

• ⽇志記錄具有標準格式，可看⽇志表的結構，可以使⽤以下語句：

SHOW CREATE TABLE mysql.general_log; # ⼀般查詢⽇志
SHOW CREATE TABLE mysql.slow_log; # 慢查詢⽇志

3.2 一般查詢日誌

• General query log - ⼀般查詢⽇志，記錄客⼾端連接或斷開連接的信息，也會記錄從客⼾端接收的每個SQL語句。如果開啓將會產⽣⼤量的內容，⾮常耗費服務器資源，所以默認為關閉(不開啓)，要啓⽤⼀般查詢⽇志可以使⽤：請使⽤ --general_log[={0|1}]

• 默認⽇志⽂件名為 host_name.log ，可以使⽤ general_log_file=file_name 修改；

• 記當客⼾端連接的⽇志⾏，使⽤ connection_type 來指⽰⽤於建⽴連接的協議。 TCP/IP 表

⽰不使⽤SSL建⽴的TCP/IP連接、 SSL/TLS 表⽰使⽤SSL建⽴的TCP/IP連接、 Socket 表⽰

Unix套接字⽂件連接、 Named Pipe 表⽰Windows命名管道連接、 Shared Memory 表⽰

Windows共享內存連接。

• Mysqld按照接收到SQL語句的順序將語句寫⼊查詢⽇志，這個順序可能與語句執⾏的順序不同。

• 表結構如下：

⼀般查詢日誌示例

• 查詢表中的⽇志內容

通過cast（）函數把編碼後的內容轉換為可讀的字符類型；cast（要轉換的內容 AS 目標數據類型）

# 查看⽇志
SELECT DATE_FORMAT(event_time, '%Y-%m-%d %H:%i:%s') AS time, 
 user_host, 
 thread_id, 
 server_id, 
 command_type, 
 CAST(argument AS CHAR) AS query 
FROM mysql.general_log;

• 查看⽂件中的⽇志內容（需要開啓一般日誌）

root@guangchen-002:/var/lib/mysql# cat general.log

3.3 慢查詢日誌

•慢查詢⽇志由執⾏時間超過系統變量 long_query_time 指定的秒數的 SQL 語句組成，並且檢

查的⾏數⼤於系統變量 min_examined_row_limit 指定值（查詢語句返回的結果集中所包含的行數，比如10）。被記錄的慢查詢需要進⾏優化，

可以使⽤ mysqldumpslow客⼾端程序對慢⽇志進⾏分析彙總。

• 獲取初始鎖的時間不計⼊執⾏時間，mysqld在執⾏完SQL語句並釋放所有鎖後才將符合條件的語

句寫⼊慢速查詢⽇志，因此⽇志順序可能與執⾏順序不同。

慢查詢日誌參數

• long_query_time 的默認值是10，最⼩值是0；

• 默認情況下，不記錄管理語句，也不記錄不使⽤索引的查詢

• 默認為關閉(不開啓)，要啓⽤慢查詢⽇志可以使⽤：請使⽤ --slow_query_log[={0|1}] .

• 默認⽇志⽂件名為 host_name-slow.log ，可以使⽤ slow_query_log_file=file_name 修改；

• 使⽤ --log-short-format 選項，以簡要格式記錄慢查詢⽇志

• 要記錄管理語句，啓⽤ log_slow_admin_statements 系統變量。管理語句包括 ALTER

TABLE 、 ANALYZE TABLE 、 CHECK TABLE 、 CREATE INDEX 、 DROP INDEX 、

OPTIMIZE TABLE 和 REPAIR TABLE 。

• 要記錄不使⽤索引的查詢，啓⽤ log_queries_not_using_indexes 系統變量。當記錄不使

⽤索引的查詢時，⽇志會快速增⻓，通過設置系統變量（當開啓了記錄不使用索引的查詢時，一定要記住配置一下每分鐘記錄的日誌數）

log_throttle_queries_not_using_indexes 限制每分鐘寫⼊慢查詢⽇志同類查詢的數

量，默認值是0，表⽰⽆限制。

慢查詢日誌內容

FILE格式

• 如果啓⽤慢查詢⽇志並將 FILE 作為輸出⽬標，每條語句前⾯都⽤⼀⾏來表⽰⽇志的字段，該⾏

以 # 字符開頭幷包含以下內容：

◦ Query_time: ：SQL語句的執⾏時間，單位秒

◦ Lock_time: 獲取鎖的時間，單位秒

◦ Rows_sent: 發送到客⼾端的⾏數（返回的查詢結果集的行數）

◦ Rows_examined: 服務器檢查的⾏數（服務器掃描數據表中具體數據行的數量）

• 啓⽤--log-slow-extra[={OFF|ON}]系統變量會將以下額外字段寫⼊到FILE中，TABLE形式不受影 響

◦ Thread_id: 線程標識符

◦ Errno: 錯誤碼，沒有發⽣錯誤則為 0

◦ Killed: 如果語句被終⽌，⽤錯誤碼錶⽰原因，如果語句正常終⽌則為 0。

◦ Bytes_received: 接收到SQL語句的Bytes值。

◦ Bytes_sent: 返回給客⼾端的Byte值。

◦ Read_first: 索引中第⼀個條⽬被讀取的次數，如果這個值很⾼，表明服務器正在執⾏⼤量完整索引掃描

◦ Read_last: 讀取索引中最後⼀個鍵的請求數，使⽤ ORDER BY 時關注

◦ Read_key: 基於索引讀取⼀⾏數據的請求數。如果這個值很⾼，表明表為當前查詢建⽴了正確的索引

◦ Read_next: 按索引排序讀取下⼀⾏的請求數，查詢具有範圍約束的索引列，或者進⾏索引

掃描，此值將遞增。

◦ Read_prev: 按索引排序讀取前⼀⾏的請求數。主要⽤於優化ORDER BYRDESC。

◦ Read_rnd: 基於固定位置讀取⼀⾏的請求數。這個值很⾼表⽰，正在執⾏⼤量需要對結果進

⾏排序的查詢，可能有很多查詢進⾏了全表掃描整，或者沒有正確使⽤索引的連接。

◦ Read_rnd_next: 讀取數據⽂件中下⼀⾏的請求數。如果進⾏⼤量的表掃描，這個值會很

⾼。通常，表⽰表沒有建⽴正確地索引，或者查詢沒有利⽤索引。

◦ Sort_merge_passes: 排序算法完成的歸併次數，如果這個值很⼤，考慮增加sort_buffer_size 系統變量的值。

◦ Sort_range_count: 使⽤範圍進⾏排序的次數。

◦ Sort_rows: 排序的⾏數。

◦ Sort_scan_count: 通過掃描表完成的排序數。

◦ Created_tmp_disk_tables: 服務器在執⾏語句時創建內部磁盤臨時表的數量。

◦ Created_tmp_tables: 服務器在執⾏語句時創建的內部臨時表的數量。

◦ Start: 執⾏SQL語句開始時間

◦ End: 執⾏SQL語句結束時間

TABLE格式

慢查詢⽇志表的表結構如下：

mysql> SHOW CREATE TABLE mysql.slow_log;

CREATE TABLE `slow_log` (
 `start_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE
CURRENT_TIMESTAMP(6),
 `user_host` mediumtext NOT NULL,
 `query_time` time(6) NOT NULL,
 `lock_time` time(6) NOT NULL,
 `rows_sent` int NOT NULL,
 `rows_examined` int NOT NULL,
 `db` varchar(512) NOT NULL,
 `last_insert_id` int NOT NULL,
 `insert_id` int NOT NULL,
 `server_id` int unsigned NOT NULL,
 `sql_text` mediumblob NOT NULL,
 `thread_id` bigint unsigned NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8mb3 COMMENT='Slow log'

3.4 錯誤日誌

錯誤⽇志⼀般會記錄mysqld 啓動和關閉的次數、診斷消息，以及服務器運⾏期間發⽣的錯誤和警

告；例如MySQL需要⾃動檢查或修復⼀個表，就會在錯誤⽇志中寫⼊⼀條記錄。錯誤⽇志默認使⽤ UTF-8 ( utf8mb3 )編碼格式，並使⽤英語⽣成記錄

錯誤⽇志輸出的位置，可以是控制枱或指定⽂件，"控制枱"表⽰ stderr 標準錯誤輸出。

Windows 的默認錯誤日誌路徑

在Windows系統中，mysqld使⽤ --log-error（文件路徑） 和 --console（控制枱） 選項來確定默認的錯誤⽇志⽬標

是控制枱還是⽂件，規則如下：

• 如果指定了 --console 選項，默認在控制枱輸出錯誤⽇志，如果 --console 和 --log

error 同時指定，則 --console 優先級更⾼，並且 --log-error 將失效。

• 如果沒有指定 --log-error 或者沒有指定具體的⽂件名，默認在數據⽬錄中⽣成名為

host_name.err 的⽇志⽂件。

• 可以通過指定絕對路徑，來更改默認的⽇志位置。

[mysqld]
log-error=D:/log/MySQL/Error/error_log.err # ⾃定義錯誤⽇志的路徑

Unix 和Linux 系統的默認錯誤⽇志路徑

在 Unix 和Linux 系統中，mysqld使⽤ --log-error 選項來指定默認錯誤⽇志⽬標，可以指定控制

台或是⽂件，如果是⽂件，規則如下：

• 如果錯誤⽇志輸出⽬標是控制枱，則服務器將 log_error 系統變量設置為 stderr . 否則，將

以⽂件形式輸⼊錯誤⽇志，並以 log_error 的值為⽂件名。

• 如果顯⽰寫出 --log-error 但沒有指定具體⽂件，則默認路徑是數據⽬錄中 host_name.err 的⽂件；

• 可以通過指定絕對路徑，來更改默認的⽇志位置

[mysqld]
log-error=/var/log/mysql/error_log.err # ⾃定義錯誤⽇志的路徑

錯誤日誌中事件的字段

time ：件時間戳，精度為微秒；

msg ：事件消息字符串；

prio ：事件優先級，包括 System event - 系統(0)、 Error event - 錯誤(1)、 Warning

event - 警告(2)或 Note/information event - 通知/提⽰事件(3)，值越⼩優先級越⾼；

err_code ：事件錯誤代碼；

err_symbol ：以字符串形式表⽰的事件錯誤符，例如 'ER_DUP_KEY' ；

SQL_state ：事件 SQLSTATE 值，與 err_symbol 對應，例如 'ER_DUP_KEY' 對應的

SQLSTATE為23000

subsystem ：事件發⽣的⼦系統。可能的值： InnoDB (InnoDB存儲引擎）、 Repl (複製⼦系

統)、 Server (其他)。

當MySQL服務啓動失敗時，首先要查看一下錯誤日誌

可選錯誤事件字段

OS_errno ：操作系統錯誤號；

OS_errmsg ：操作系統錯誤消息；

label ：與值對應的 prio 描述 ；

user ：客⼾端⽤⼾；

host ：客⼾端主機；

thread ：產⽣錯誤事件 的線程的 ID；

query_id ：查詢 ID。

可以通過以下SQL查看已定義的錯誤類型

mysql> SELECT *
 FROM performance_schema.events_errors_summary_global_by_error
 WHERE SUM_ERROR_RAISED <> 0\G

刷新錯誤⽇志⽂件和重命名

• 如果使⽤ FLUSH ERROR LOGS（當重新打開時找不到日誌文件就會重新生成一個新的） 、 FLUSH LOGS 語句或 mysqladmin flush-logs 命令刷

新錯誤⽇志，服務器會將正在寫⼊的任何錯誤⽇志⽂件關閉並重新打開。

• 如果要⼿動重命名錯誤⽇志⽂件，可以在重命名操作之後執⾏刷新操作，服務器會以原⽂件名⽣成 ⼀個新的錯誤⽇志⽂件，例如⽇志⽂件名為 host_name.err ，可以按以下步驟操作：

mv host_name.err host_name.err-old # 重命名⽇志⽂件
mysqladmin flush-logs # 刷新操作
mv host_name.err-old backup-directory # 把重命名的⽇志⽂件移動到備份⽬錄

3.5 二進制日誌

簡介：

• ⼆進制⽇志包含數據庫更改的"事件"，不會記錄 SELECT 和 SHOW ，例如：記錄表的創建操作或表數據的更改，⼆進制⽇志還包含每個語句更新數據時花費的時間信息，啓動⼆進制⽇志，對服務器性能稍微有些影響（因為涉及到磁盤的IO）

• 除了基於⾏的⽇志模式，它還包含可能進⾏更改數據的語句事件，例如 DELETE 操作沒有匹配到

查找到的⾏；

• ⼆進制⽇志的作⽤：

◦ 主從節點數據複製：從節點服務器讀取主節點服務器上的⼆進制⽇志⽂件，並根據⼆進制⽇志

中記錄的事件在從節點上執⾏相同的操作，保證主從節點服務器上數據⼀致，實現數據複製功

能。

◦ 數據恢復：從某個時間點恢復備份數據後，將重新執⾏備份時間點之後記錄在⼆進制⽇志中的

事件。這些事件使數據庫從備份點更新到當前最新狀態。

• ⼆進制⽇志的語句中如果涉及⽤⼾的密碼，則由服務器進⾏加密，不會以純⽂本形式出現

選項和變量

• 查看⼆進制⽇志相關的系統變量

mysql> show variables like '%bin%';

• 查看⼆進制⽇志相關的狀態變量

mysql> show status like '%bin%';

• 默認情況下啓⽤⼆進制⽇志， log_bin 系統變量為 ON ；

• 禁⽤⼆進制⽇志，可以指定 --skip-log-bin 或 --disable-log-bin 選項。如果同時指定了 --log-bin 則後指定的選項優先；

• 選項 --log-bin[=base_name] ⽤於指定⼆進制⽇志⽂件的基本名稱，如果不指定 --log bin 選項，默認基本名稱為 binlog ，建議為⼆進制⽇志指定⼀個基本名；

• ⼆進制⽇志⽂件名是由基本名+數字擴展名組成的，服務器每次創建⼀個新的⽇志⽂件時，數字擴展名都會增加，從⽽保證有序的⽂件系列，發⽣以下事件時，服務器都會在創建⼀個新的⽇志⽂件:

◦ 服務器已啓動或重新啓動

◦ 服務器刷新⽇志

◦ 當前⽇志⽂件的⼤⼩達到 max_binlog_size (單個⽇志⽂件的最⼤字節數，最⼩值 4096 字

節，最⼤值和默認值 1GB).

⼆進制⽇志⽂件⼤⼩可能會超出 max_binlog_size 設定的值，因為⼆進制⽇志在記錄事務

時，會完整的記錄整個事務，不存在把⼀個事務拆分的情況，如果遇到⼀個⼤事務時，即使記錄

整個事務會超過⽇志⼤⼩限制，也會保證事務的完整性

二進制日誌以事物為單位記錄日誌

•mysqld還會創建⼀個包含⼆進制⽇志⽂件名的⽇志索引⽂件，默認情況下，這與⼆進制⽇志⽂件

具有相同的基本名稱，擴展名為 .index . 可以使⽤選項 --log-bin-index[=file_name] 修改索引⽂件名;

• ⼆進制⽇志⽂件和索引⽂件的默認位置是數據⽬錄。可以使⽤ --log-bin[=file_name] 選項

指定⾃定義路徑， file_name 格式 = 絕對路徑+基本名。 --log-bin 對應的系統變量是

log_bin_basename ；

• MySQL 5.7 中，啓⽤⼆進制⽇志必須指定服務器 ID，對應 server_id 選項，否則服務器將⽆法

啓動。在 MySQL 8.0 中， server_id 系統變量默認設置為 1，在集羣環境中，每台MySQL服務

器必須有唯⼀的 server_id ；

• ⼆進制⽇志記錄事件⽀持三種格式類型：基於⾏的⽇志記錄、基於語句的⽇志記錄和混合⽇志記

錄，稍候具體介紹；

• ⼆進制⽇志記錄在語句或事務完成之後，釋放鎖或在提交完成之前進⾏。這樣做是為了確保按照提交順序記錄⽇志；

• 在⼀個未提交的事務中，對⽀持事務的表(如InnoDB表)的更改都會被緩存(UPDATE, DELETE或

INSERT)，直到服務器收到 COMMIT 語句，mysqld在執⾏ COMMIT 之前將整個事務寫⼊⼆進制⽇志；

• 如果事務回滾，則在整個事務中記錄⼀個 ROLLBACK 語句，但是對⾮事務性表(如MyISAM表)的修改不能回滾，所以這些修改將被複制到從節點；

• 對⾮事務表的更新在執⾏後⽴即存儲在⼆進制⽇志中；

• 當處理事務的線程啓動時，它會分配⼀個⼤⼩為 binlog_cache_size 的緩衝區來緩存語句。如

果語句⼤⼩⼤緩衝區的值，線程則打開⼀個臨時⽂件來存儲事務，臨時⽂件在線程結束時刪除；

• Binlog_cache_use 狀態變量顯⽰使⽤該緩衝區(可能還有臨時⽂件)存儲事務的數量；

Binlog_cache_disk_use 狀態變量顯⽰有多少事務實際上使⽤了臨時⽂件。結合這兩個變量

可以把 binlog_cache_size 調優到⼀個⾜夠⼤的值，從⽽避免使⽤臨時⽂件；

• 系統變量 max_binlog_cache_size （默認值和最⼤值都是 4GB，最⼩值為 4096）⽤於限制

緩存區⼤⼩，如果事務語句⼤於這個值指定的字節數，事務將會失敗並回滾；

• 如果使⽤基於⾏的⽇志記錄⽅式， 為了保證⽇志的準確性， CREATE ... SELECT 或

INSERT ... SELECT 語句的併發插⼊將轉換為普通插⼊；如果使⽤基於語句的⽇志記錄⽅

式，則將原始語句寫⼊⽇志。

• 由於服務器崩潰或其他原因，導致對⼆進制⽇志⽂件⽆法進⾏寫⼊、刷新或者同步到磁盤。那麼主從節點上的⽇志就會出現不⼀致，當遇到這種問題時，可以通過系統變量binloglog_error_action 控制處理⽅式：

◦ 默認值 ABORT_SERVER ，服務器停⽌⼆進制⽇志記錄並關閉，排查完問題並重啓後，服務器

按意外停⽌執⾏恢復操作；

◦ IGNORE_ERROR 表⽰，服務器繼續進⾏當前的事務並記錄錯誤，然後停⽌⽇志記錄，排查問

題後，需要確認啓⽤ log_bin ，然後再次啓動服務器，對⽇志要求不⾼的場景可以設置此

值，不推薦在集羣環境使⽤；

• 默認情況下， sync_binlog=1 ，表⽰每個事務在寫⼊緩存後⽴即同步到磁盤，也可以設置為其

他值，⽐如 sync_binlog=N 表⽰N次事務提交到緩存之後再同步到磁盤，如果

sync_binlog=0 則MySQL不控制同步磁盤的頻率，完全由操作系統控制。需要注意：如果當

sync_binlog 的值設置為 0 或 N 那麼當服務器崩潰時，緩上存中的有些⽇志不能同步到磁

盤，可能造成⼀些更改丟失，所以 sync_binlog=1 是最安全的，但同時效率也是最低的。

• 可以使⽤ RESET MASTER 語句刪除所有⼆進制⽇志⽂件，或者使⽤ PURGE binary LOGS 刪

除⼀部分⼆進制⽇志⽂件，具體演⽰：

# 重置⼆進⾏⽇志⽂件和索引⽂件為初始狀態
mysql> RESET MASTER; 

# 刪除指定⽇志⽂件之前的所有⽇志⽂件並更新索引
mysql> PURGE BINARY LOGS TO 'mysql-bin.010'; 
# 刪除指定時間之前的所有⽇志⽂件並更新索引
mysql> PURGE BINARY LOGS BEFORE '2019-04-02 22:46:26'

TIPS:

術語 "⼆進制⽇志⽂件" 通常表⽰包含單獨編號的⽇志⽂件。術語 "⼆進制⽇志" 表⽰所有⼆進制⽇志⽂件和索引⽂件

查看二進制日誌

使⽤客⼾端⼯具mysqlbinlog查看

mysqlbinlog binlog.000001 > binlog.000001
            -二進制日誌名    -導出的目標文件名

• 通過SQL語句查看

mysql> show binlog events in 'binlog.000001' from N limit S;

⼆進制⽇志格式

記錄⼆進制⽇志時使⽤的格式有以下⼏種：

• 基於語句的⽇志格式，最初MySQL 是基於 SQL 語句複製實現主從節點同步，通過指定選項 --

binlog-format=STATEMENT 使⽤此格式 。

• 基於⾏的⽇志格式(默認)中，主節點將事件寫⼊⼆進制⽇志，表⽰各個表的⾏受到的影響，可以通過指定選項 --binlog-format=ROW 使⽤此格式 。

• 混合⽇志記錄格式，默認情況下使⽤基於語句的⽇志記錄，如果MySQL認為基於語句的格式不能保證主從複製過程中的數據安全時，會⾃動切換到基於⾏的⽇志格式，⽐如主節點在語句中⽤了

UUID() 函數，那麼⽇志⽂件中記錄的是UUID⽣成的真實值⽽不是直接使⽤原始的SQL語句，使

⽤混合⽇志格式中以指定選項 --binlog-format=MIXED 。

TIPS: 設置⼆進制⽇志格式

--binlog-format=[STATEMENT|ROW|MIXED]

• 基於語句與基於⾏的區別：

# 基於語句，記錄執⾏的SQL語句
-執行了什麼語句就原封不動的記錄相應的SQL
update student set age = 18 where id between 10 and 20;



# 基於⾏，記錄每⼀⾏的更改
-當使用MySQL內部的一些函數時，必須以行的格式記錄日誌
-當主節點執行了UUID函數的時候，會記錄生成的具體的值
update student set age = 18 where id = 10;
update student set age = 18 where id = 11;
update student set age = 18 where id = 12;
...
update student set age = 18 where id = 19;
update student set age = 18 where id = 20;

3.6 Redo Log 和 Undo Log

Redo Log ：重做⽇志，⽤於恢復數據（對於已經提交的事務崩潰重啓之後，依舊可以重新執行並寫入磁盤）

Undo Log ：撤消⽇志⽤於回滾操作；

3.7 服務器日誌維護

MySQL 服務器可以創建多種不同的⽇志⽂件來幫助我們查看服務器的活動。但是必須定期清理這

些⽂件，以免⽇志佔⽤過多的磁盤空間。在啓⽤⽇志的情況下，通常希望備份和刪除舊的⽇志⽂件，並把⽇志寫到新⽂件。

• 默認⼆進制⽇志的過期時間為30天，過期後將⾃動刪除，要指定⾃定義過期時間，可以使⽤系統變量 binlog_expire_logs_seconds=N 單位為秒，在下⼀次啓動服務器和刷新⽇志時刪除過期

⽇志⽂件；

• 強制使⽤新的⽇志⽂件可以⼿動刷新⽇志，當執⾏ FLUSH LOGS 語句或 mysqladmin flush

logs 、 mysqladmin refresh 、 mysqldump --flush-logs 、 mysqldump -- master-data 命令時，會發⽣⽇志刷新。此外當⼆進制⽇志⽂件⼤⼩達到 max_binlog_size 系統變量指定的值時，服務器會⾃動刷新⼆進制⽇志。

• FLUSH LOGS ⽀持可選的修飾符以啓⽤個別⽇志的選擇性刷新：

FLUSH BINARY LOGS # 刷新⼆進制⽇志
FLUSH ERROR LOGS # 刷新錯誤⽇志
FLUSH GENERAL LOGS # 刷新⼀般查詢⽇志
FLUSH RELAY LOGS # 刷新中繼⽇志
FLUSH SLOW LOGS # 刷新慢查詢⽇志

• 刷新⼀般查詢⽇志、慢查詢⽇志或錯誤⽇志只是關閉並重新打開⽇志⽂件，如果要備份可以先重命名再執⾏刷新操作，⽐如⼀般查詢⽇志、慢查詢⽇志或錯誤⽇志⽂件名分別為： mysql.log 、

mysql-slow.log 和 err.log ，可以在命令⾏中使⽤如下⼀系列命令：

cd mysql-data-directory #進⼊⽇志⽬錄
mv mysql.log mysql.log.old #重命名⼀般查詢⽇志
mv mysql-slow.log mysql-slow.log.old #重命名慢查詢⽇志
mv err.log err.log.old #重命名錯誤⽇志
mysqladmin flush-logs #刷新⽇志

要在運⾏時重命名⼀般查詢⽇志或慢查詢⽇志

◦ ⾸先連接到服務器並禁⽤⽇志：

SET GLOBAL general_log = 'OFF';
SET GLOBAL slow_query_log = 'OFF';

◦ 在禁⽤⽇志的情況下，重命名⽇志⽂件，例如⽤ mv 命令從命令⾏執⾏重命名操作

◦ 再次啓⽤⽇志：

SET GLOBAL general_log = 'ON';
SET GLOBAL slow_query_log = 'ON';

◦ 這種⽅法適⽤於任何平台且不需要重啓服務器

3.8 配置日誌輸出位置

# 服務器節點
[mysqld]
# ⼀般查詢⽇志和慢查詢⽇志記錄⽅式為⽂件
log-output=FILE
# 開啓⼀般查詢⽇志
general-log=1
# ⼀般查詢⽇志路徑和⽂件名
general_log_file=/var/log/mysql/general.log
# 開啓慢查詢⽇志
slow-query-log=1
# 慢查詢⽇志路徑和⽂件名
slow_query_log_file=/var/log/mysql/slow-query.log
# 慢查詢⽇志時間限制
long_query_time=10
# 錯誤⽇志路徑和⽂件名
log-error=/var/log/mysql/error.err
# ⼆進制⽇志路徑和基本名
log-bin=/var/log/mysql/binlog
# 服務器編號
server-id=1

4. mysql System Schema（mysql系統庫）

Mysql Schema是⼀個系統庫，表中存儲了MySQL服務器運⾏時所需的信息。⼴義上，mysql

schema包含存儲數據庫對象元數據的數據字典和⽤於其他操作⽬的的系統表。數據字典表和系統表位於數據⽬錄下⼀個名為 mysql.ibd 的表空間⽂件中，使⽤的是InnoDB存儲引擎。

數據字典和系統表都是保存數據庫對象屬性的存儲結構

4.1 數據字典

• MySQL 的數據字典，⽤來存儲有關數據庫對象⾃⾝的信息，不可以隨意修改，否則可能造成服務器⽆法運⾏：以下列出⼏個常⻅的數據字典表：

◦ character_sets ：有關可⽤字符集的信息 utf8mb4

◦ check_constraints ：有關表上定義的 CHECK 約束的信息

◦ collations ：每個字符集的排序規則信息

◦ column_type_elements ：列類型的信息

◦ columns ：有關表中列的信息

◦ indexes : 有關表索引的信息

◦ tables ：有關數據庫中表的信息

◦ tablespace_files ：有關表空間使⽤的⽂件信息

◦ tablespaces ：有關活動表空間的信息

◦ triggers ：有關觸發器的信息

• 數據字典表是受保護的，只能在調試版本中訪問，在發⾏版中沒有權限訪問，如果在發⾏版本中查詢表中的數據，出提⽰拒絕訪問：

mysql> select * from mysql.character_sets; # 訪問可⽤字符集的信息
ERROR 3554 (HY000): Access to data dictionary table 'mysql.catalogs' is
rejected.

• INFORMATION_SCHEMA 實現了某些數據字典的視圖，可以通過視圖查看某些數據字典的內容：

# 通過INFORMATION_SCHEMA中的視圖訪問可⽤字符集的信息
mysql> SELECT * FROM INFORMATION_SCHEMA.CHARACTER_SETS;

4.2 系統表

系統表按功能⼤致可以分為以下⼏類：權限授予系統表、對象信息系統表、⽇志系統表、服務器端幫助系統表、時區系統表、複製系統表、優化器系統表、雜項系統表。mysql系統庫中的表如下所⽰：

mysql> use mysql
Database changed
mysql> show tables;
.....

權限授予系統表

包含有關⽤⼾帳⼾及帳⼾擁有的權限授權信息，主要的表有:

• user ：⽤⼾帳⼾、全局權限和其他列。

• global_grants ：為⽤⼾分配的動態全局權限；

• db ：數據庫級權限。

• tables_priv ：表級權限。

• columns_priv : 列級權限。

• procs_priv : 存儲過程和函數權限。

• proxies_priv : 代理⽤⼾權限。

• default_roles ：列出了⽤⼾連接和認證後要激活的默認⻆⾊。

• role_edges ： user 表的關聯表，區分 user 表中某⼀⾏記錄是帳⼾還是⻆⾊

• password_history ：密碼更改的信息

對象信息系統表

包含有關組件、可加載的服務器插件和函數的信息：

component ：使⽤ INSTALL COMPONENT 安裝的服務器組件，表中列出了在服務器啓動期間安裝的組件。

func : 使⽤ CREATE FUNCTION 安裝的可加載函數，表中列出了在服務器啓動期間加載的函數。

plugin ：使⽤ INSTALL PLUGIN 安裝的服務器插件，表中列出了在服務器啓動期間安裝的插

件。

日誌系統表

服務器使⽤⽇志系統表進⾏⽇志記錄：

• general_log ：⼀般查詢⽇志表。

• slow_log ：慢查詢⽇志表。

⽇志表使⽤ CSV 存儲引擎

服務器端幫助系統表

包含服務器端幫助信息：

• help_category ：有關幫助類別的信息。

• help_keyword ：與幫助主題關聯的關鍵字。

• help_relation ：幫助關鍵字和主題之間的映射。

• help_topic ：幫助主題內容

時區系統表

包含時區信息：

• time_zone ：時區 ID 以及是否使⽤閏秒。

• time_zone_leap_second ：發⽣閏秒時如何修正。

• time_zone_name : 時區 ID 和名稱之間的映射。

• time_zone_transition , time_zone_transition_type : 時區説明及偏移量。

複製系統表

服務器使⽤以下系統表來⽀持複製

• gtid_executed ：⽤於存儲 GTID 的值。

• ndb_binlog_index ：⽤於NDB Cluster 複製的⼆進制⽇志信息。只有在⽀持 NDB

CLUSTER 的服務器才會創建此表，我們的課程不討論NDB 的相關內容

• slave_master_info , slave_relay_log_info , slave_worker_info : ⽤於存儲從節點服務器上的複製信息。

以上⼏張表都使⽤ InnoDB 存儲引擎。

優化器系統表

這些系統表供優化器使⽤：

• innodb_index_stats , innodb_table_stats : ⽤於 InnoDB 的持久優化器統計信息。

• server_cost , engine_cost ：優化器成本模型使⽤的表，包含查詢期間發⽣的操作成本估

算信息。 server_cost 包含服務器操作的優化器成本估算。 engine_cost 包含對特定存儲

引擎操作的估計。

雜項系統表

audit_log_filter , audit_log_user : 如果安裝了 MySQL Enterprise Audit，存儲審計⽇志過濾器定義和⽤⼾帳⼾。

• firewall_group_allowlist , firewall_groups , firewall_memebership ,

firewall_users , firewall_whitelist ：如果安裝了 MySQL Enterprise Firewall，這

些存儲表防⽕牆的使⽤信息。

• servers ：由 FEDERATED 存儲引擎使⽤。

• innodb_dynamic_metadata ：由 InnoDB 存儲引擎⽤來存儲快速變化的表元數據，例如⾃

動遞增計數器值和索引樹損壞標誌。

5.在一台機器上運行多個MySQL實例

• 有時候我們希望在⼀台機器上運⾏多個 MySQL 實例，⽐如在開發環境測試新的MySQL版本，同時⼜要保證原有的版本不受影響；MySQL 允許在⼀台機器上安裝不同版本的發⾏版，⽐如同時運⾏5.7和8.0版本，只是要做⼀些相應的配置。

具體看WPS

6.面試題

介紹⼀下你知道的MySQL服務器配置⽂件的選項

這個問題主要考察選項的使⽤，回答這個問題可以先總體描述選項⽂件的使⽤場景，再介紹⼏個常⻅的選項即可

1. 選項⽂件主要作⽤是設置MySQL服務常⽤的系統變量初始值，以便服務器啓動時⾃動讀取；

2. 選項⽂件中可以⽤ [程序名] 的形式分別為不同的程序指定具體選項的值，也可以⽤

[client] 、 [server] 的形式分別指定客⼾端程序和服務器程序公共配置。

3. 常⻅的選項有，客⼾端和服務器使⽤的字符集，客⼾端默認的⽤⼾名和密碼，服務器的排序規則，服務器的端⼝號，⾃定義數據⽬錄的路徑，⽇志⽂件的路徑，服務器允許的最⼤連接數，還有⼀些關於存儲引擎的選項也可以⽤於性能調優，⽐如關於INNODB的選項，等等