MySQL配置資訊解讀（my.cnf）

    從年後換了工作到現在差不多兩個月了，比較忙，所以寫部落格的時間越來越少了。

    以前學生時代用MySQL，從安裝開始就是“下一步”，設定嚮導弄中設定使用者、連接埠、編碼什麼的就好了。後來工作了公司用的Oracle，但是普通程式員也接觸不到。現在正好有機會自己去接觸一下資料庫（MySQL），儘力把握住，搞清楚MySQL源碼是如何編譯的，是怎麼配置的，儲存引擎之間的區別等等。

    這片博文記錄一下自己理解的my.cnf（Window平台上的是my.ini）中的配置，這些配置官網應該都是有解釋的吧？！可能是我自己沒有找到好的資源。

    my.cnf預設是不存在的，可以在mysql的安裝目錄的support-files中看到下面這些檔案：

    

    可以將my-huge.cnf、my-innodb-heavy-4G.cnf、my-large.cnf、my-medium.cnf、my-small.cnf中合適自己機器的檔案拷貝命名成my.cnf。

    下面詳細看一下這幾個設定檔的內容。

    my-huge.cnf（巨大的、龐大的）

# Example MySQL config file for very large systems.
# 這是一個龐大的系統的配置樣本
# This is for a large system with memory of 1G-2G where the system runs mainly
# MySQL.
# 一個擁有1至2G記憶體，主要用於運行MySQL的系統
# MySQL programs look for option files in a set of
# locations which depend on the deployment platform.
# You can copy this option file to one of those
# locations. For information about these locations, see:
# http://dev.mysql.com/doc/mysql/en/option-files.html
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.

# The following options will be passed to all MySQL clients
[client]
#password= your_password
port= 3306 連接埠號碼
socket= /tmp/mysql.sock mysql.sock檔案是伺服器與本地用戶端進行通訊的Unix通訊端檔案

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port= 3306 連接埠號碼
socket= /tmp/mysql.sock mysql.sock檔案是伺服器與本地用戶端進行通訊的Unix通訊端檔案
skip-locking 避免MySQL的外部鎖定，減少出錯幾率增強穩定性。
key_buffer_size = 384M 指定索引緩衝的大小，它決定索引處理的速度，尤其是索引讀的速度

通過檢查狀態值Key_read_requests和Key_reads，可以知道key_buffer_size設定是否合理。比例key_reads /key_read_requests應該儘可能的低，至少是1:100，1:1000更好（上述狀態值可以使用SHOW STATUS LIKE ‘key_read%’獲得）

max_allowed_packet = 1M 設定最大包,限制server接受的資料包大小，避免超長SQL的執行有問題（當MySQL用戶端或mysqld伺服器收到大於max_allowed_packet位元組的資訊包時，將發出“資訊包過大”錯誤，並關閉串連）
table_open_cache = 512 mysql每開啟一個表都會讀入一些資料到table_open_cache中，當mysql在這個緩衝中找不到對應的資訊時，才會去磁碟上直接讀取
sort_buffer_size = 2M 查詢排序時所能使用的緩衝區大小。注意：該參數對應的分配記憶體是每串連獨佔！如果有100個串連，那麼實際分配的總共排序緩衝區大小為100 × 2 ＝ 200MB。所以，對於記憶體在4GB左右的伺服器推薦設定為6-8M。
read_buffer_size = 2M 讀查詢操作所能使用的緩衝區大小。和sort_buffer_size一樣，該參數對應的分配記憶體也是每串連獨享！read_buffer_size只對MyISAM表有效
read_rnd_buffer_size = 8M 對所有儲存引擎的表都有限
myisam_sort_buffer_size = 64M MySQL重建索引時所允許的最大臨時檔案的大小 (當 REPAIR, ALTER TABLE 或者 LOAD DATA INFILE)
thread_cache_size = 8 伺服器線程緩衝這個值表示可以重新利用儲存在緩衝中線程的數量,當中斷連線時如果緩衝中還有空間,那麼用戶端的線程將被放到緩衝中,如果線程重新被請求，那麼請求將從緩衝中讀取,如果緩衝中是空的或者是新的請求，那麼這個線程將被重新建立,如果有很多新的線程，增加這個值可以改善系統效能
query_cache_size = 32M query_cache_size的工作原理：一個SELECT查詢在DB中工作後，DB會把該語句緩衝下來，當同樣的一個SQL再次來到DB裡調用時，DB在該表沒發生變化的情況下把結果從緩衝中返回給Client。這裡有一個關建點，就是DB在利用Query_cache工作時，要求該語句涉及的表在這段時間內沒有發生變更。那如果該表在發生變更時，Query_cache裡的資料又怎麼處理呢？首先要把Query_cache和該表相關的語句全部置為失效，然後在寫入更新。那麼如果Query_cache非常大，該表的查詢結構又比較多，查詢語句失效也慢，一個更新或是Insert就會很慢，這樣看到的就是Update或是Insert怎麼這麼慢了。所以在資料庫寫入量或是更新量也比較大的系統，該參數不適合分配過大。而且在高並發，寫入量大的系統，建系把該功能禁掉。
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8 設定thread_concurrency的值的正確與否, 對mysql的效能影響很大, 在多個cpu(或多核)的情況下，錯誤設定了thread_concurrency的值, 會導致mysql不能充分利用多cpu(或多核), 出現同一時刻只能一個cpu(或核)在工作的情況。thread_concurrency應設為CPU核心數的2倍. 比如有一個雙核的CPU, 那麼thread_concurrency的應該為4; 2個雙核的cpu, thread_concurrency的值應為8

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking 開啟該選項可以徹底關閉MySQL的TCP/IP串連方式，如果WEB伺服器是以遠端連線的方式訪問MySQL資料庫伺服器則不要開啟該選項！否則將無法正常串連！

# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin 二進位日誌包含所有更新資料的語句，其目的是在恢複資料庫時用它來把資料儘可能恢複到最後的狀態。另外，如果做同步複製( Replication )的話，也需要使用二進位記錄傳送修改情況。 開啟二進位日誌，需要設定參數log-bin。log_bin指定記錄檔，如果不提供檔案名稱，MySQL將自己產生預設檔案名稱

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id= 1

# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
# the syntax is:
#
# CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
# MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
#
# where you replace <host>, <user>, <password> by quoted strings and
# <port> by the master's port number (3306 by default).
#
# Example:
#
# CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
# MASTER_USER='joe', MASTER_PASSWORD='secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
# start replication for the first time (even unsuccessfully, for example
# if you mistyped the password in master-password and the slave fails to
# connect), the slave will create a master.info file, and any later
# change in this file to the variables' values below will be ignored and
# overridden by the content of the master.info file, unless you shutdown
# the slave server, delete master.info and restart the slaver server.
# For that reason, you may want to leave the lines below untouched
# (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id = 2
#
# The replication master for this slave - required
#master-host = <hostname>
#
# The username the slave will use for authentication when connecting
# to the master - required
#master-user = <username>
#
# The password the slave will authenticate with when connecting to
# the master - required
#master-password = <password>
#
# The port the master is listening on.
# optional - defaults to 3306
#master-port = <port>
#
# binary logging - not required for slaves, but recommended
#log-bin=mysql-bin
#
# binary logging format - mixed recommended
#binlog_format=mixed

# Uncomment the following if you are using InnoDB tables 使用innoDB的配置
#innodb_data_home_dir = /usr/local/mysql/data INNODB儲存資料目錄 
#innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend 指定表資料和索引儲存的空間，可以是一個或者多個檔案。最後一個資料檔案必須是自動擴充的，也只有最後一 個檔案允許自動擴充。這樣，當空間用完後，自動擴充資料檔案就會自動成長
#innodb_log_group_home_dir = /usr/local/mysql/data 日誌位置
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 384M 定義了 InnoDB 儲存引擎的表資料和索引資料的最大記憶體緩衝區大小。和 MyISAM 儲存引擎不同， MyISAM 的 key_buffer_size 只能緩衝索引鍵，而 innodb_buffer_pool_size 卻可以快取資料塊和索引鍵。適當的增加這個參數的大小，可以有效減少 InnoDB 類型的表的磁碟 I/O 。在一個以 InnoDB 為主的專用資料庫伺服器上，可以考慮把該參數設定為實體記憶體大小的 60%-80% 。
#innodb_additional_mem_pool_size = 20M 這個參數用來設定 InnoDB 儲存的資料目錄資訊和其它內部資料結構的記憶體池大小。應用程式裡的表越多，你需要在這裡分配越多的記憶體。對於一個相對穩定的應用，這個參數的大小也是相對 穩定的，也沒有必要預留非常大的值。如果 InnoDB 用光了這個池內的記憶體， InnoDB 開始從作業系統分配記憶體，並且往 MySQL 錯誤記錄檔寫警告資訊。預設值是 1MB ，當發現錯誤記錄檔中已經有相關的警告資訊時，就應該適當的增加該參數的大小。
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 100M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1如果設定為1，InnoDB會在每個提交磁碟沖洗交易記錄,這提供了完整的ACID的行為。如果你想安全，並且正在執行一些小操作，你可以設定為0或者2在減少磁碟I / O日誌。

#innodb_lock_wait_timeout = 50 InnoDB 有其內建的死結檢測機制，能導致未完成的交易回復。但是，如果結合InnoDB使用MyISAM的lock tables 語句或第三方事務引擎,則InnoDB無法識別死結。為消除這種可能性，可以將innodb_lock_wait_timeout設定為一個整數值，指示 MySQL在允許其他事務修改那些最終受交易回復的資料之前要等待多長時間(秒數)

[mysqldump]
quick 沒有指定 --quick 或 --opt 選項，則會將整個結果集放在記憶體中。如果匯出大資料庫的話可能會出現問題
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout 伺服器關閉互動式串連前等待活動的秒數。互動式用戶端定義為在mysql_real_connect()中使用

 

其他檔案：my-large.cnf my-medium.cnf  my-small.cnf是針對不同的機器效能的配置。my-innodb-heavy-4G.cnf是一個針對 4G 記憶體系統（主要運行只有 InnoDB 表的 MySQL 並使用幾個串連數執行複雜的查詢）的 MySQL 設定檔例子。

 

    通過修改my.cnf的配置可以最佳化mysql的效能。my.cnf檔案的儲存位置會影響配置的有效性。