值得收藏！my.cnf配置文檔詳解

MySql對于開發人員來說應該都比較熟悉，不管是小白還是老碼農應該都能熟練使用。但是要說到的各種參數的配置，我敢說大部分人并不是很熟悉，當我們需要優化mysql，改變某項參數的時候。還是要到處在網上查找，有點不友善。今天就把我所知道的MySql的配置檔案my.cnf做一個簡單的說明吧，注意，我總結的mysql是Linux環境下的。

其實，如果你要做mysql性能優化，那麼熟悉my.cnf 的相關參數是必不可少的。不然，很多時候就會出現：網上查資料說是調下某個參數性能能提升，實際你調完之後卻沒有任何效果。是以我建議大家一定要把mysql重要的配置參數弄懂（加粗部分為重點參數）。

一、配置檔案位置

Mysql安裝成功後之後，會自動生成my.cnf檔案。my.cnf檔案可以自定義位置，也可以使用如下預設的位置，隻要放在預設位置，MySQL自動識别：

• /etc/my.cnf 　　 　　 全局選項
• /etc/mysql/my.cnf 　　全局選項
• SYSCONFDIR/my.cnf 　　全局選項
• $MYSQL_HOME/my.cnf　 伺服器特定選項（僅限伺服器）
• defaults-extra-file 　 指定的檔案 --defaults-extra-file（如果有的話）
• ~/.my.cnf 　　　　　　 使用者特定的選項, ~表示目前使用者的主目錄（的值 $HOME）。
• ~/.mylogin.cnf 　　　 使用者特定的登入路徑選項（僅限用戶端） 在上表中， 首先它會找/etc/my.cnf 這個檔案， 如果這個檔案不存在，那麼它接下來去找/etc/mysql/my.cnf這個檔案

如下圖所示：

二、配置參數參考

[client]

port = 3306

[mysqld]

#預設存儲引擎INNODB

default-storage-engine=INNODB

#GROUP_CONCAT長度

group_concat_max_len =99999

#端口号

#套接字檔案

#這裡要注意：有時候重新開機mysql會提示/tmp/mysql.sock不存在，此時通常會由于兩種情況導緻，解決方法可以參考我之前記錄的文章，親測有效：

https://www.cnblogs.com/zhangweizhong/p/12179452.html

socket = /usr/local/mysql/mysql.sock

#pid寫入檔案位置

pid-file = /usr/local/mysql/mysqld.pid

#資料庫檔案位置

datadir = /home/data/mysql/data

#控制檔案打開的個數；

open_files_limit = 10240

#SQL模式

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

#當外部鎖定（external-locking）起作用時，每個程序若要通路資料表,

#則必須等待之前的程序完成操作并解除鎖定。由于伺服器通路資料表時經常需要等待解鎖,

#是以在單伺服器環境下external locking會讓MySQL性能下降。

#是以在很多Linux發行版的源中，MySQL配置檔案中預設使用了skip-external-locking來避免external locking。

skip-external-locking

#跳過DNS反向解析

skip-name-resolve

#關閉TIMESTAMP類型預設值

explicit_defaults_for_timestamp

#不受client字元集影響，保證sever端字元集

skip-character-set-client-handshake

#初始連接配接字元集UTF8

init-connect='SET NAMES utf8'

#預設資料庫字元集

character-set-server=utf8

#查詢緩存0,1,2，分别代表了off、on、demand

query_cache_type = 1

#機關秒,握手時間超過connect_timeout,連接配接請求将會被拒絕

connect_timeout = 20

#設定在多少秒沒收到主庫傳來的Binary Logs events之後,從庫認為網絡逾時,Slave IO線程會重新連接配接主庫。

#該參數的預設值是3600s ，然而時間太久會造成資料庫延遲或者主備庫直接的連結異常不能及時發現。

#将 slave_net_timeout 設得很短會造成 Master 沒有資料更新時頻繁重連。一般線上設定為5s

slave_net_timeout = 30

#這個參數用來配置從伺服器的更新是否寫入二進制日志，這個選項預設是不打開的，

#但是，如果這個從伺服器B是伺服器A的從伺服器，同時還作為伺服器C的主伺服器，那麼就需要開發這個選項，

#這樣它的從伺服器C才能獲得它的二進制日志進行同步操作

log-slave-updates=1

#用于slave伺服器，io線程會把server id與自己相同的event寫入日志，與log-slave-updates選項沖突

replicate-same-server-id=0

server_id=10112879101

# 打開二進制日志功能.

# 在複制(replication)配置中,作為MASTER主伺服器必須打開此項

# 如果你需要從你最後的備份中做基于時間點的恢複,你也同樣需要二進制日志

log-bin =/home/data/mysql/binlog/mysql-bin.log

#relay-log日志

relay-log=mysql-relay-bin

#master-info-repository以及relay-log-info-repository打開以啟用崩潰安全的二進制日志/從伺服器功能（在事務表而不是平面檔案中存儲資訊）

master-info-repository=TABLE

relay-log-info-repository=TABLE

#不寫入binlog二進制日志中的資料庫

binlog-ignore-db=mysql # No sync databases

binlog-ignore-db=test # No sync databases

binlog-ignore-db=information_schema # No sync databases

binlog-ignore-db=performance_schema # No sync databases

#寫入binlog二進制日志中資料庫

binlog-do-db=business_db

binlog-do-db=user_db

binlog-do-db=plocc_system

#清理binlog

expire-logs-days=15

max_binlog_size = 1073741824 # Bin logs size ( 1G )

#使binlog在每1000次binlog寫入後與硬碟同步

sync_binlog = 1000

#指定隻複制哪個庫的資料

replicate-do-db=business_db

replicate-do-db=user_db

replicate-do-db=plocc_system

#開啟事件排程器Event Scheduler

event_scheduler=1

#MySQL能暫存的連接配接數量。當主要MySQL線程在一個很短時間内得到非常多的連接配接請求，這就起作用。

#如果MySQL的連接配接資料達到max_connections時，新來的請求将會被存在堆棧中，以等待某一連接配接釋放資源，

#該堆棧的數量即back_log，如果等待連接配接的數量超過back_log，将不被授予連接配接資源

#如果系統在短時間内有很多連接配接，則需要增大該參數的值，該參數值指定到來的TCP/IP連接配接的監聽隊列的大小。預設值50。

back_log = 500

#MySQL允許最大的程序連接配接數，

#如果經常出現Too Many Connections的錯誤提示，則需要增大此值。

max_connections = 6000

#每個使用者的最大的程序連接配接數

max_user_connection = 3000

#每個用戶端連接配接請求異常中斷的最大次數,如果達到了此限制.

#這個用戶端将會被MySQL服務阻止,直到執行了”FLUSH HOSTS” 或者服務重新開機

#非法的密碼以及其他在連結時的錯誤會增加此值.

#檢視 “Aborted_connects” 狀态來擷取全局計數器

max_connect_errors = 6000

#表調整緩沖區大小。

#table_cache 參數設定表高速緩存的數目。每個連接配接進來，都會至少打開一個表緩存。

#是以，table_cache 的大小應與 max_connections 的設定有關。例如，對于 200 個并行運作的連接配接，應該讓表的緩存至少有 200 × N ，這裡 N 是應用可以執行的查詢的一個聯接中表的最大數量。此外，還需要為臨時表和檔案保留一些額外的檔案描述符。

#當Mysql通路一個表時，如果該表在緩存中已經被打開，則可以直接通路緩存；如果還沒有被緩存但是在 Mysql 表緩沖區中還有空間，那麼這個表就被打開并放入表緩沖區；如果表緩存滿了，則會按照一定的規則将目前未用的表釋放，或者臨時擴大表緩存來存放，使用表緩存的好處是可以更快速地通路表中的内容。

#執行 flush tables 會清空緩存的内容。

#一般來說，可以通過檢視資料庫運作峰值時間的狀态值 Open_tables 和 Opened_tables ，判斷是否需要增加 table_cache 的值（其中 open_tables 是目前打開的表的數量， Opened_tables 則是已經打開的表的數量）。

#即如果open_tables接近table_cache的時候，并且Opened_tables這個值在逐漸增加，那就要考慮增加這個#值的大小了。還有就是Table_locks_waited比較高的時候，也需要增加table_cache。

table_cache = 614

#表描述符緩存大小，可減少檔案打開/關閉次數

table_open_cache = 2048

#設定在網絡傳輸中一次消息傳輸量的最大值。系統預設值 為1MB，最大值是1GB，必須設定1024的倍數。

#當與大的BLOB字段一起工作時相當必要

max_allowed_packet = 64M

# 在一個事務中binlog為了記錄SQL狀态所持有的cache大小

# 如果你經常使用大的,多聲明的事務,你可以增加此值來擷取更大的性能.

# 所有從事務來的狀态都将被緩沖在binlog緩沖中然後在送出後一次性寫入到binlog中

# 如果事務比此值大, 會使用磁盤上的臨時檔案來替代.

# 此緩沖在每個連接配接的事務第一次更新狀态時被建立

binlog_cache_size = 1M

# 獨立的記憶體表所允許的最大容量.

# 此選項為了防止意外建立一個超大的記憶體表導緻用盡所有的記憶體資源.

max_heap_table_size = 256M

#Sort_Buffer_Size被用來處理類似ORDER BY以及GROUP BY隊列所引起的排序，每一個要做排序的請求，都會分到一個sort_buffer_size大的緩存

#Sort_Buffer_Size 是一個connection級參數，在每個connection（session）第一次需要使用這個buffer的時候，一次性配置設定設定的記憶體。

#Sort_Buffer_Size 并不是越大越好，由于是connection級的參數，過大的設定+高并發可能會耗盡系統記憶體資源。例如：500個連接配接将會消耗 500*sort_buffer_size(8M)=4G記憶體

#如果超過Sort_Buffer_Size設定的大小，MySQL會将資料寫入磁盤來完成排序，導緻效率降低。

#屬重點優化參數

sort_buffer_size = 8M

#用于表間關聯緩存的大小，和sort_buffer_size一樣，該參數對應的配置設定記憶體也是每個連接配接獨享。

#大部分表關聯都比較影響查詢性能,

#是以将此值設大能夠減輕性能影響。

#通過 “Select_full_join” 狀态變量檢視表關聯的數量

join_buffer_size = 8M

#thread_cache_size表示可以重新利用儲存在緩存中線程的數量,當斷開連接配接時如果緩存中還有空間,那麼用戶端的線程将被放到緩存中,

#如果線程重新被請求，那麼請求将從緩存中讀取,如果緩存中是空的或者是新的請求，那麼這個線程将被重新建立,如果有很多新的線程，減少線程建立的開銷

#可以通過比較 Connections 和 Threads_created 狀态變量，來檢視thread_cache_size的設定是否起作用。

#設定規則：1GB 記憶體配置為8，2GB配置為16，3GB配置為32，4GB或更高記憶體，可配置更大。

thread_cache_size = 128

#此值表示允許應用程式在同一時間運作的線程的數量.

#設定thread_concurrency的值的正确與否,對mysql的性能影響很大, 在多個cpu(或多核)的情況下，錯誤設定了thread_concurrency的值, 會導緻mysql不能充分利用多cpu(或多核), 出現同一時刻隻能一個cpu(或核)在工作的情況。

#thread_concurrency應設為CPU核數的2倍

thread_concurrency = 8

#此值用來緩沖 SELECT 的結果并且在下一次同樣查詢的時候不再執行直接傳回結果,如果你有大量的相同的查詢并且很少修改表，那麼query_cache_size可以極大的提高資料庫性能,

#需要注意的是：有時候資料庫出現了性能問題，大家就習慣的認為把這個值調大就行了。然而，這個參數加大後也引發了一系列問題。

#我們首先分析一下 query_cache_size的工作原理：一個SELECT查詢在DB中工作後，DB會把該語句緩存下來，當同樣的一個SQL再次來到DB裡調用時，DB在該表沒發生變化的情況下把結果從緩存中傳回給Client。

#這裡有一個關建點，就是DB在利用Query_cache工作時，要求該語句涉及的表在這段時間内沒有發生變更。那如果該表在發生變更時，Query_cache裡的資料又怎麼處理呢？

#首先要把Query_cache和該表相關的語句全部置為失效，然後在寫入更新。那麼如果Query_cache非常大，該表的查詢結構又比較多，查詢語句失效也慢，一個更新或是Insert就會很慢，這樣看到的就是Update或是Insert怎麼這麼慢了。

#是以在資料庫寫入量或是更新量也比較大的系統，該參數不适合配置設定過大。而且在高并發，寫入量大的系統，建議把該功能禁掉。

#重點優化參數

query_cache_size = 64M

#指定單個查詢能夠使用的緩沖區大小，隻有小于此設定值的結果才會被緩沖

#此設定用來保護查詢緩沖,防止極大的結果集将其他所有的查詢結果都覆寫

#預設為1M

query_cache_limit = 2M

#被全文檢索索引的最小的字長.

#你也許希望減少它,如果你需要搜尋更短字的時候.

#注意在你修改此值之後,

#你需要重建你的 FULLTEXT 索引

ft_min_word_len = 4

#設定MYSQL線程使用的堆大小，此容量的記憶體在每次連接配接時被預留.

#MySQL 本身常不會需要超過64K的記憶體

#如果你使用你自己的需要大量堆的UDF函數

#或者你的作業系統對于某些操作需要更多的堆,

#你也許需要将其設定的更高一點.

thread_stack = 192K

#設定預設的事務隔離級别.可用的級别如下:

#READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE

transaction_isolation = READ-COMMITTED

#此值表示記憶體中臨時表的最大大小，超過限值後就往硬碟寫

#此限制是針對單個表的,而不是總和

#注意：

# 1. max_heap_table_size 比 tmp_table_size 小時，則系統會把 max_heap_table_size 的值作為最大的記憶體臨時表的上限。這樣可達到提高聯接查詢速度的效果，建議盡量優化查詢，要確定查詢過程中生成的臨時表在記憶體中，避免臨時表過大導緻生成基于硬碟的MyISAM表。

# 2. 通過show global status like '%created_tmp%' 查詢：Created_tmp_disk_tables和Created_tmp_tables的值，Created_tmp_disk_tables / Created_tmp_tables 值越小越好

tmp_table_size = 256M

#binlog日志類型

#mixed：混合型

binlog_format=mixed

#開啟慢查詢日志

slow_query_log

#檔案格式

log_output = FILE

# 所有的使用了比這個時間(以秒為機關)更多的查詢會被認為是慢速查詢.

# 不要在這裡使用”0″, 否則會導緻所有的查詢,甚至非常快的查詢頁被記錄下來(由于MySQL 目前時間的精确度隻能達到秒的級别).

long_query_time = 0.5

#慢查詢日志位置

slow_query_log_file=/usr/local/mysql/mysqld_slow.log

MyISAM 相關選項

#用于索引的緩沖區大小，增加它可以提高索引處理性能，

#對于記憶體在4GB左右的伺服器來說，該參數可設定為256MB或384MB。

#通過show variables like ‘%key_buffer_size%';

#通過 show global status like ‘%key_blocks_unused%' 檢視是否還有剩餘，如果剩餘很多，就不需要再加大key_buffer_size了

#1. 隻用用于MyISAM表

#2. 可以設定為記憶體的30%-40%左右

#3. 即使你并不使用MyISAM 表, 最好也設定8-64M記憶體，由于它同樣會被内部臨時表使用

key_buffer_size = 2048M

#MySql讀入緩沖區大小。當對表進行全表掃描請求是，将MySql會為它配置設定一個讀入緩沖區

#如果對表的順序掃描請求非常頻繁，并且你認為頻繁掃描進行得太慢，可以通過增加該變量值以及記憶體緩沖區大小提高其性能。

#該參數對應的配置設定記憶體也是每個連接配接獨享。

read_buffer_size = 2M

#MySql的随機讀（查詢操作）緩沖區大小。可以提高很多ORDER BY的性能,當需要時由每個線程配置設定。

#當進行排序查詢時，MySql會配置設定一個随機讀緩存區。首先掃描一遍該緩沖，以避免磁盤搜尋，提高查詢速度，

#如果需要排序大量資料，可适當調高該值。

#需要注意的是：MySql會為每個客戶連接配接發放該緩沖空間，是以應盡量适當設定該值，以避免記憶體開銷過大。

read_rnd_buffer_size = 16M

#批量插入資料緩存大小，可以有效提高批量插入效率，預設為8M

# 1. 不要将此值設定大于 “key_buffer_size”.

# 2. 設定0會關閉此參數。

bulk_insert_buffer_size = 16M

#MyISAM表發生變化,重建索引時所需的緩沖

#注意：此參數每個線程中配置設定,不宜設定過大。

myisam_sort_buffer_size = 128M

#MySQL重建索引時所允許的臨時檔案的大小 (當 REPAIR, ALTER TABLE 或者 LOAD DATA INFILE).

#如果臨時檔案大于此值,索引會通過鍵值緩沖建立(更慢)

myisam_max_sort_file_size = 1G

#如果一個表擁有多個索引, MyISAM 會通過并行排序使用多個線程去修複他們。

#一般用于擁有多個CPU以及大量記憶體情況。

myisam_repair_threads = 1

# 自動檢查和修複沒有适當關閉的 MyISAM 表.

myisam_recover

INNODB相關選項

#如果你的MySQL服務包含InnoDB支援但是并不打算使用的話,

#使用此選項會節省記憶體以及磁盤空間,并且加速某些部分

#skip-innodb

#這對Innodb表來說非常重要，Innodb把所有的資料和索引都緩存起來，此參數設定越大，資料存取時所需要的磁盤I/O越少。

#Innodb比MyISAM表對緩沖更為敏感。MyISAM可以在預設的 key_buffer_size 設定下運作的可以，然而Innodb在預設的 innodb_buffer_pool_size 設定下卻跟蝸牛似的。

#1. 當然如果你的資料量不大，并且不會暴增，也無需把 innodb_buffer_pool_size 設定的太大

#2. 如果在一個獨立使用的資料庫伺服器上,你可以設定這個變量到伺服器實體記憶體大小的80%

#3. 注意在32位系統上你每個程序可能被限制在 2-3.5G,

innodb_buffer_pool_size = 2048M

# InnoDB 将資料儲存在一個或者多個資料檔案中成為表空間

# 如果你隻有單個邏輯驅動儲存你的資料,一個單個的自增檔案就足夠好了

# 其他情況下.每個裝置一個檔案一般都是個好的選擇

# 你也可以配置InnoDB來使用裸盤分區

innodb_data_file_path = ibdata1:1024M:autoextend

# 檔案IO的線程數，一般為 4

# 此值在Unix下被寫死為4,但是在Windows下可以設定得較大.

innodb_file_io_threads = 4

# 允許線程數量。

# 伺服器有幾個CPU就設定為幾，建議用預設設定。

# 過高的值可能導緻線程的互斥。

innodb_thread_concurrency = 16

# 如果設定為1 ,InnoDB會在每次事務送出後将事務日志寫到磁盤上,

# 基于性能考慮，可以設定為0或2，但要承擔在發生故障時丢失資料的風險。

# 0代表日志隻大約每秒寫入日志檔案并且日志檔案重新整理到磁盤.

# 2代表每次送出後日志寫入日志檔案,但是日志檔案每秒重新整理到磁盤上。

# 幾點說明：

# 1. 如果是遊戲伺服器，建議此值設定為2；如果是對資料安全要求極高的應用，建議設定為1；

# 2. 設定為0性能最高，但如果發生故障，資料可能會有丢失的危險!

# 3. 預設值1每一次事務送出都需要把日志寫到硬碟，這是很費時的。

# 4. 特别是使用電池供電緩存(Battery backed up cache)時。設成2對于很多運用，特别是從MyISAM表轉過來的是可以的，

# 5. 它的意思是不寫入硬碟而是寫入系統緩存。日志仍然會每秒flush到硬碟，是以你一般不會丢失超過1-2秒的更新。

# 6. 設成0會更快一點，但安全方面比較差，即使MySQL挂了也可能會丢失事務的資料。而值2隻會在整個作業系統挂了時才可能丢資料

innodb_flush_log_at_trx_commit = 2

#此參數用于寫日志檔案所用的記憶體大小，以M為機關。緩沖區更大能提高性能，但意外的故障将會丢失資料。

#當緩存大小達到設定值後, InnoDB會将這些資料寫到到磁盤上。

#由于基本上每秒都會重新整理一次,是以沒有必要将此值設定的太大，MySQL開發人員建議設定為1－8M之間

innodb_log_buffer_size = 16M

#此參數用于确定日志檔案的大小

#為避免在日志檔案覆寫上不必要的緩沖池重新整理

#一般設定為日志檔案總合大小到你緩沖池大小的25%~100%

#但這同時也會增加恢複故障資料庫所需的時間

innodb_log_file_size = 1024M

#日志組中的檔案總數. 為提高性能，MySQL會以循環方式将日志寫到多個檔案中

#通常來說2~3是比較好的.

innodb_log_files_in_group = 3

# InnoDB的日志檔案所在位置. 預設是MySQL的datadir.

# 你可以将其指定到一個獨立的硬碟上或者一個RAID1卷上來提高其性能

#innodb_log_group_home_dir

# 在InnoDB緩沖池中最大允許的髒頁面的比例.

# 如果達到限額, InnoDB會開始重新整理他們防止他們妨礙到幹淨資料頁面.

# 這是一個軟限制,不被保證絕對執行.

innodb_max_dirty_pages_pct = 90

# InnoDB用來重新整理日志的方法.

# 表空間總是使用雙重寫入重新整理方法

# 預設值是 “fdatasync”, 另一個是 “O_DSYNC”.

innodb_flush_method=O_DSYNC

# 在被復原前,一個InnoDB的事務應該等待一個鎖被準許多久.

# InnoDB在其擁有的鎖表中自動檢測事務死鎖并且復原事務.

# 如果你使用 LOCK TABLES 指令, 或者在同樣事務中使用除了InnoDB以外的其他事務安全的存儲引擎

# 那麼一個死鎖可能發生而InnoDB無法注意到.

# 這種情況下這個timeout值對于解決這種問題就非常有幫助.

innodb_lock_wait_timeout = 30

[mysqldump]

# 不要在将記憶體中的整個結果寫入磁盤之前緩存. 在導出非常巨大的表時需要此項

quick

[mysql]

no-auto-rehash

#指定一個請求的最大連接配接時間，對于4GB左右的記憶體伺服器來說，可以将其設定為5-10。

wait_timeout = 10

#開啟該選可以徹底關閉MySQL的TCP/IP連接配接方式，如果WEB伺服器是以遠端連接配接的方式通路MYSQL資料庫伺服器的，則不要開啟該選項，否則将無法正常連接配接。

skip_networking

#将沒有使用索引的查詢也記錄下來

log-queries-not-using-indexes

最後

以上就把my.cnf 配置參數介紹完了，有些是自己日常mysql 優化總結的結果，有些來源于網絡。僅供大家參考。