充分理解 MySQL 配置文件中各個變量的意義對我們有針對性的優化 MySQL 數據庫性能有非常大的意義。通常我們需要根據不同的數據量級，不同的生產環境情況對MySQ配置文件進行優化

linux下 MySQL配置文件是 my.cnf 存放在 /etc/my.cnf、/etc/mysql/my.cnf,/usr/etc/my.cnf，~/.my.cnf，如果沒有設置使用指定目錄的my.cnf，MySQl會使用按照優先級使用上面的文件，越在前面的優先級越高。

本教程將帶領大家逐條解析最新的 MySQL 8.0 的配置文件，爭取搞懂每一條變量。當然，我們理解了變量的意義外，更重要的是在實踐中進行微調，以達到優化性能的目的。

1. 通用配置

1.1 數據文件存放目錄

mysql> show variables like 'datadir';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| datadir       | /usr/local/mysql/data/ |
+---------------+------------------------+
復制代碼

1.2 進程id存放文件目錄

mysql> show variables like 'pid_file';
+---------------+----------------------------------------+
| Variable_name | Value                                  |
+---------------+----------------------------------------+
| pid_file      | /usr/local/mysql/data/mysqld.local.pid |
+---------------+----------------------------------------+
1 row in set (0.01 sec)
復制代碼

1.3 端口號


mysql> show variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  |
+---------------+-------+
1 row in set (0.00 sec)
復制代碼

1.4 默認存儲引擎

default_storage_engine=InnoDB
復制代碼

1.5 是否需要密碼校驗

skip-grant-tables作用是啟動 MySQL 服務器時跳過授權認證階段，即不需要輸入用戶名和密碼即可登錄 MySQL 服務器。啟動 MySQL 服務器時使用 skip-grant-tables 選項后，所有連接到 MySQL 服務器的用戶都將具有 root 權限

skip-grant-tables
復制代碼

2.連接數

2.1 最大連接數

mysql> show variables  like '%max_connections%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| max_connections        | 151   |
+------------------------+-------+
1 rows in set (0.01 sec)
mysql> set global max_connections=256;
Query OK, 0 rows affected (0.00 sec)
復制代碼

mysql的最大連接數max_connections，如果數據庫的并發連接請求比較大，應該調高該值，調整連接數的時候，應該充分考慮硬件資源等因素，連接數過大，容易造成阻塞

2.2 單個用戶最大連接數

mysql> show variables like '%max_user_connections%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| max_user_connections | 0     |
+----------------------+-------+
1 row in set (0.01 sec)
復制代碼

注：max_user_connections默認值為0，表示不限制。

2.3 最大等待連接數

mysql> show variables like '%back_log%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| back_log      | 151   |
+---------------+-------+
1 row in set (0.00 sec)
復制代碼

back_log表示MySQL能夠暫存的連接數量，當MySQL在一個很短時間內得到非常多的連接請求時，就會起作用；舉個例子，假如MySQL的連接數量達到max_connections時，新的請求會被存儲在堆棧中，以等待某一個連接釋放資源，如果等待連接的數量超過back_log,則不再接受連接資源。

2.4 交互式最大連接時長

mysql> show variables like '%interactive_timeout%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| interactive_timeout        | 28800 |
+----------------------------+-------+
復制代碼

interactive_timeout針對交互式連接，所謂的交互式連接，即在mysql_real_connect()函數中使用了CLIENT_INTERACTIVE選項。說得直白一點，通過MySQL客戶端連接數據庫的是交互式連接，如通過mysql -u -p命令連接到MySQL。

interactive_timeout默認是28800，單位秒，即8個小時，有興趣的小伙伴可以將該參數改成10s，然后重新連接，看10s之后，會不會報錯

2.5 非交互式最大連接時長

mysql> show variables like '%wait_timeout%';
+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| wait_timeout             | 28800    |
+--------------------------+----------+
復制代碼

wait_timeout針對非交互式連接，其默認也是28800，單位秒，即8個小時；像我們常見的JDBC鏈接，就是非交互式連接。

3.日志

3.1 錯誤日志文件名稱

cat /etc/my.cnf
# 
log-error=/var/log/mysqld.log
復制代碼

log-error用于指定錯誤日志文件名稱，用于記錄當mysqld啟動和停止時，以及服務器在運行中發生任何嚴重錯誤時的相關信息

3.2 開啟binlog

開啟MySQL的binlog 需要在配置文件my.cnf添加如下配置，然后重啟服務：

server_id=2
log_bin=mysql-bin
binlog_format=ROW
expire_logs_days=30
復制代碼

注意server_id是必填的，其它配置可選填；重啟之后，登錄MySQL，查看binlog是否開啟：

mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name                   | Value                          |
+---------------------------------+--------------------------------+
| log_bin                         | ON                             |
| log_bin_basename                | /var/lib/mysql/mysql-bin       |
| log_bin_index                   | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF                            |
| log_bin_use_v1_row_events       | OFF                            |
| sql_log_bin                     | ON                             |
+---------------------------------+--------------------------------+
6 rows in set (0.00 sec)
復制代碼

log_bin 為ON表示已經開啟,OFF表示關閉，MySQl默認是不開啟binlog的，感興趣的同學可先不修改my.cnf文件，先查看一下log_bin的值。

3.3 指定數據庫開啟binlog

默認情況下，如果我們開啟binlog，MySQL默認會記錄所有數據庫的binlog日志；這樣我們MySQL的binlog日志會變得很大，做數據恢復的是也會變得很慢，所以有的時候，我們可以只指定一些核心重要的庫才開啟binlog。

如下，在MySQL開啟binlog的前提下，只對數據庫test開啟binlog

binlog_do_db=test
復制代碼

重啟之后，登錄MySQL,查看binlog_do_db選項：

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      157 | test         |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
復制代碼

3.4 指定數據庫不開啟binlog

與binlog_do_db配置相反，binlog_ignore_db表示指定不將更新記錄到二進制日志的數據庫，既指定哪些數據庫不開啟binlog；這兩個參數為互斥關系，一般只選擇其一設置即可。

3.5 查詢日志記錄

general_log允許您記錄服務器執行的所有 SQL 語句。這對于調試和故障排除以及審計目的非常有用。

啟用 general_log 后，MySQL 將所有執行的 SQL 語句寫入日志文件中，包括 SELECT、INSERT、UPDATE、DELETE 等操作，以及連接和斷開連接的事件。

mysql> show variables like 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log   | OFF   |
+---------------+-------+
1 row in set (0.00 sec)
復制代碼

您可以使用 SET GLOBAL general_log = 'ON'; 命令來啟用 general_log，或使用 SET GLOBAL general_log = 'OFF'; 命令來禁用它。您還可以使用 SHOW VARIABLES LIKE 'general_log%'; 命令來查看有關 general_log 的當前設置。但是，啟用 general_log 可能會對服務器性能產生負面影響，并且可能會產生大量的日志數據，因此建議僅在需要時啟用它，并定期清理日志文件

3.6 慢查詢日志記錄

slow_query_log 可以將執行時間超過預設閾值的查詢定義為蠻SQL，這對于優化查詢性能非常有用，因為可以查找和分析耗時較長的查詢語句并進行優化。

當 slow_query_log 啟用后，MySQL 會將所有執行時間超過預設閾值的查詢語句記錄到一個日志文件中：

mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | OFF   |
+----------------+-------+
復制代碼

除了記錄慢查詢語句外，slow_query_log 還可以記錄一些其他信息，例如查詢的鎖定等待時間、掃描行數、返回行數、執行時間等。

3.7 慢查詢的時間

long_query_time表示慢查詢的時間，超過這個時間的查詢語句才會記錄到慢查詢文件中，如下，默認值為10 秒。

mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
復制代碼

4.緩存

4.1 查詢緩存

查詢緩存在MySQL8已經被去掉，如果小伙伴用的是MySQL7以上的，可不關注此塊內容；如下，通過執行命令 show status like '%query_cache%'來查看查詢緩存的相關屬性

mysql> show variables like '%query_cache%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| have_query_cache             | YES     |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 1048576 |
| query_cache_type             | OFF     |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+
復制代碼

如上所示，下面再將一些核心屬性做簡要解釋：

query_cache_size：查詢緩存的大小，未來版本被刪除
query_cache_limit: 超出此大小的查詢將不被緩存
query_cache_min_res_unit：緩存塊最小大小，默認是4k
query_cache_type：緩存類型，決定緩存什么樣的查詢 0：表示禁用 1：表示將緩存所有結果，除非sql語句中使用sql_no_cache禁用查詢緩存 2：表示只緩存select語句中通過sql_cache指定需要緩存的查詢

雖然查詢緩存這個模塊已經在MySQL8被去掉，但是如果你使用的是MySQL7及以下版本，也可以充分利用該查詢緩存的特性，提高MySql查詢效率，如一些經常不變的配置表

4.2 排序緩存區大小

sort_buffer_size表示每個需要排序的線程分派該大小的緩沖區：

mysql> show variables like '%sort_buffer_size%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| innodb_sort_buffer_size | 1048576 |
| myisam_sort_buffer_size | 8388608 |
| sort_buffer_size        | 262144  |
+-------------------------+---------+
復制代碼

sort_buffer_size 表示所有存儲引擎的默認配置，innodb_sort_buffer_size表示InonoDB存儲引擎，默認大小為1M，myisam_sort_buffer_size表示myisam存儲引擎，默認大小為8M。

4.3 join 緩沖區大小

當 MySQL 執行一個聯接查詢時，它需要將兩個或多個表中的數據進行匹配。為了提高連接操作的性能，MySQL 會將其中一個表中的數據先讀入緩存中，然后在緩存中執行連接操作，而不是每次都從磁盤讀取數據默認情況下:

mysql> show variables like '%join_buffer_size%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| join_buffer_size | 262144 |
+------------------+--------+
復制代碼

join_buffer_size 的值為 262144（字節），即 256 KB。如果你的 MySQL 服務器上經常執行大型聯接查詢，可以考慮增加這個值，以提高查詢性能。不過，需要注意的是，過大的緩沖區可能會導致內存使用過高，因此需要根據實際情況進行調整

4.5 thread_cache_size-線程緩存池

MySQL中的thread_cache_size 是一個控制線程緩存的系統變量。該變量指定了 MySQL 服務器在緩存中保留的線程數。線程緩存可以減少創建和銷毀線程的開銷，從而提高 MySQL 的性能。當客戶端連接到 MySQL 服務器時，服務器會檢查是否有已經存在的線程可供使用。如果存在，則使用該線程服務客戶端請求。否則，服務器將創建一個新線程來處理客戶端請求；

mysql> show variables like 'Thread_cache%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| thread_cache_size | 9     |
+-------------------+-------+
1 row in set (0.00 sec)
復制代碼

thread_cache_size用來緩存空閑的線程，如果該值是0，這意味著線程緩存被禁用，如果設置了一個非零的值，則 MySQL 服務器將嘗試在緩存中保留指定數量的線程。如果緩存已滿，則服務器將在需要時創建新線程，并在使用后將其放回緩存。下面我們再來看看與其關聯的四個狀態變量：

mysql> show global status like 'Threads_%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 0     |
| Threads_connected | 2     |
| Threads_created   | 2     |
| Threads_running   | 2     |
+-------------------+-------+

復制代碼

Threads_cached：代表當前此時此刻線程緩存中有多少空閑線程
Threads_connected：代表當前已建立連接的數量
Threads_created ：代表從最近一次服務啟動，已創建線程的數量
Threads_running：代表當前激活的（非睡眠狀態）線程數。并不是代表正在使用的線程數，有時候連接已建立，但是連接處于sleep狀態，這里相對應的線程也是sleep狀態

適當地設置 thread_cache_size 可以幫助減少服務器創建和銷毀線程的開銷，從而提高 MySQL 的性能建議將 thread_cache_size 設置為等于服務器最大連接數的 10% - 25%。

5.Innodb存儲引擎

5.1 buffer pool 數據緩沖區

當查詢需要訪問表中的數據時，InnoDB存儲引擎會先查看緩沖池中是否有相關的數據。如果緩沖池中已經有數據，InnoDB存儲引擎就可以直接從緩沖池中獲取數據，而不需要從磁盤中讀取數據。這樣可以大大提高查詢的速度，其默認大小為128M:

mysql> show variables  like '%innodb_buffer_pool_size%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
復制代碼

innodb_buffer_pool_size變量的值越大，InnoDB存儲引擎可以緩存的數據就越多，查詢的速度就會越快。但是，將其設置得過大可能會占用過多的內存資源，導致系統性能下降。通常情況下，建議將innodb_buffer_pool_size設置為系統內存的70%~80%左右

5.2 事務日志刷盤時間點

innodb_flush_log_at_trx_commit用于控制redo log buffer的數據何時寫入到磁盤。

mysql> show variables like '%innodb_flush_log_at_trx_commit%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
復制代碼

該參數有以下三種可能的值：

0：表示事務提交時不立即將日志寫入磁盤，而是將日志緩存在內存中，直到發生檢查點或者緩沖池滿時才寫入磁盤。這種方式可以提高性能，但是在數據庫崩潰時可能會丟失最近提交的事務數據。
1：表示事務提交時立即將日志寫入磁盤，并等待磁盤寫操作完成后才返回客戶端。這種方式可以保證最大程度上的數據安全性，但會對性能產生較大影響。
2：表示事務提交時將日志緩存在內存中，但每秒鐘將緩沖區中的日志寫入磁盤。這種方式可以在一定程度上平衡性能和數據安全性。

5.3 innodb_thread_concurrency 并發線程數

innodb_thread_concurrency定義了在 InnoDB 引擎中可并發運行的線程數量，包括讀取線程、寫入線程、刷新線程和其他一些管理線程。innodb_thread_concurrency 的原理其實就是通過限制 InnoDB 線程池中可并發執行的線程數量來避免系統資源被過度占用，從而提高系統的性能和穩定性。然而，在實際應用中，由于不同的應用場景和系統配置可能會產生不同的結果，因此需要根據實際情況進行調整。一般來說，在高負載的數據庫系統中，適當地調整 innodb_thread_concurrency 的值可以有效地提高系統的并發性能

mysql> show variables like '%innodb_thread_concurrency%';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| innodb_thread_concurrency | 0     |
+---------------------------+-------+
復制代碼

默認情況下，innodb_thread_concurrency 的值被設置為 0，表示沒有限制。

5.4 redolog文件大小

innodb_log_file_size 參數用于設置 InnoDB 存儲引擎單個日志文件大小，以字節為單位；如下，其默認大小為48MB:

mysql> show variables like '%innodb_log_file_size%';
+----------------------+----------+
| Variable_name        | Value    |
+----------------------+----------+
| innodb_log_file_size | 50331648 |
+----------------------+----------+
復制代碼

如果將 innodb_log_file_size 大小設置過大，可能會導致性能下降或造成磁盤空間的浪費。因此，建議根據實際需求和系統負載進行調整。通常建議將 innodb_log_file_size 設置為 1GB 或更小的值。如果需要更大的日志文件，可以增加日志文件的數量，而不是增加每個文件的大小

5.5 redolog文件個數

innodb_log_files_in_group表示redo log組中包含的 redo log 文件的數量。其默認值是2：

mysql> show variables like '%innodb_log_files_in_group%';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| innodb_log_files_in_group | 2     |
+---------------------------+-------+
復制代碼

通常情況下，建議將 innodb_log_files_in_group 設置為 2 或 3，這樣可以在一個 redo log 文件被寫滿時，立即切換到下一個 redo log 文件，從而避免出現寫滿 redo log 文件時的性能問題。但是，修改 innodb_log_files_in_group 參數的值會涉及到重建 redo log 文件，因此在修改該參數之前，請務必備份數據，以防止數據丟失。

5.6 innodb_file_per_table

innodb_file_per_table作用是控制 InnoDB 存儲引擎是否將每個表的數據和索引存儲到單獨的文件中：

mysql> show variables like '%innodb_file_per_table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
復制代碼

當 innodb_file_per_table 設置為 ON 時，每個表的數據和索引都將被存儲在單獨的 .ibd 文件中。這樣的好處是可以方便地進行備份和恢復操作，并且可以更加靈活地管理磁盤空間。此外，如果某個表需要進行修復或優化，可以更加方便地針對該表進行操作，而不會影響到其他表。

當 innodb_file_per_table 設置為 OFF 時，所有表的數據和索引都將被存儲在共享的 ibdata 文件中。這樣的好處是可以減少文件系統的碎片，并且可以更加高效地利用磁盤空間。但是，在這種情況下，如果需要恢復某個表的數據，就需要恢復整個 ibdata 文件，這可能會比較耗時和麻煩。

因此，根據具體的應用場景和需求，可以選擇打開或關閉 innodb_file_per_table 配置選項。

原文鏈接：
https://juejin.cn/post/7207718128939221049

日日操夜夜添-日日操影院-日日草夜夜操-日日干干-精品一区二区三区波多野结衣-精品一区二区三区高清免费不卡

27個常見的MySQL服務器參數配置