mysql-5.5 ---設定檔參數說明

標籤：設定檔   mysql   伺服器   使用者   

配置樣本：

[client]

character-set-server = utf8                               //設定mysql用戶端預設字元集

port    = 3306                                            //設定mysql用戶端串連服務端時預設使用的連接埠

socket  = /data/mysql/3306/mysql.sock                     //

[mysqld]

character-set-server = utf8                                //預設字元集

user    = mysql                                            //mysql使用者

port    = 3306                                             //mysql伺服器預設監聽連接埠

socket  = /data/mysql/3306/mysql.sock                      //串連資料庫socket路徑

basedir = /usr/local/webserver/mysql                       //mysql安裝目錄

datadir = /data/mysql/3306/data                            //mysql資料庫檔案所在目錄

log-error = /data/mysql/3306/mysql_error.log               //mysql錯誤記錄檔目錄

pid-file = /data/mysql/3306/mysql.pid                      //為mysqld程式指定一個存放進程ID的檔案

open_files_limit = 10240                                   //設定開啟檔案數量允許至少值

table_cache = 512                                          //同時開啟的資料表的數量

back_log = 600                                             //back_log是作業系統在監聽隊列中所能持續連線數

max_connections = 5000                                     //MySQL伺服器同時處理的資料庫連接的最大數量(預設設定是100)

max_connect_errors = 6000                                  //每個用戶端串連最大的錯誤允許數量，如果達到了此限制，這個用戶端將會被                                                                                                                         mysql服務阻止直到執行了“flush hosts”或者服務重啟

external-locking = FALSE                                   //允許外部檔案層級的鎖，開啟檔案鎖會對效能造成負面影響，所以只有在你                                                                                                                           在同樣的檔案上運行多個資料庫執行個體時才使用此選項（注意仍會有其他約束！），或者你在檔案層面上使用了其他一些軟體依賴來鎖定MyISAM表

max_allowed_packet = 32M                                    //服務所能處理的最大的請求大小

sort_buffer_size = 8M                                       //為排序操作分配的緩衝區的長度(預設設定是2M)

join_buffer_size = 1M

thread_cache_size = 300

thread_concurrency = 8

query_cache_size = 512M

query_cache_limit = 2M

query_cache_min_res_unit = 2k

default-storage-engine = MyISAM

thread_stack = 192K

transaction_isolation = READ-COMMITTED

tmp_table_size = 246M

max_heap_table_size = 246M

key_buffer_size = 512M

read_buffer_size = 4M

read_rnd_buffer_size = 16M

bulk_insert_buffer_size = 64M

myisam_sort_buffer_size = 128M

myisam_max_sort_file_size = 10G

myisam_repair_threads = 1

myisam_recover

interactive_timeout = 120

wait_timeout = 120

innodb_data_home_dir = /data/mysql/3306/data

innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend

innodb_additional_mem_pool_size = 16M

innodb_buffer_pool_size = 512M

innodb_file_io_threads = 4

innodb_thread_concurrency = 8

innodb_flush_log_at_trx_commit = 2

innodb_log_buffer_size = 16M

innodb_log_file_size = 128M

innodb_log_files_in_group = 3

innodb_max_dirty_pages_pct = 90

innodb_lock_wait_timeout = 120

innodb_file_per_table = 0

slow-query-log-file=/data/mysql/3306/slow.log

long_query_time = 1

replicate-ignore-db = mysql

replicate-ignore-db = test

replicate-ignore-db = information_schema

log-slave-updates

log-bin = /data/mysql/3306/binlog/binlog

binlog_cache_size = 4M

binlog_format = MIXED

max_binlog_cache_size = 64M

max_binlog_size = 1G

relay-log-index = /data/mysql/3306/relaylog/relaylog

relay-log-info-file = /data/mysql/3306/relaylog/relaylog

relay-log = /data/mysql/3306/relaylog/relaylog

expire_logs_days = 30

skip-name-resolve

#master-connect-retry = 10

slave-skip-errors = 1032,1062,126,1114,1146,1048,1396

server-id = 1

[mysqldump]

quick

max_allowed_packet = 32M

[myisamchk]

key_buffer_size = 256M

sort_buffer_size = 256M

read_buffer = 2M

write_buffer = 2M

[mysqlhotcopy]

interactive-timeout

參數說明：

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。

back_log 非動態變數，需要重啟服務。指定MySQL可能的串連數量。當MySQL主線程在很短的時間內接收到非常多的串連請求，該參數生效，主線程花費很短的時間檢查串連並且啟動一個新線程。back_log參數的值指出在MySQL暫時停止回應新請求之前的短時間內多少個請求可以被存在堆棧中。如果系統在一個短時間內有很多串連，則需要增大該參數的值，該參數值指定到來的TCP/IP串連的偵聽隊列的大小。不同的作業系統在這個隊列大小上有它自己的限制。試圖設定back_log高於你的作業系統的限制將是無效的。預設值為50。對於Linux系統推薦設定為小於512的整數。

max_connections  MySQL允許最大串連數。

max_connect_errors 可以允許多少個錯誤串連。

max_allowed_packet 設定最大包,限制server接受的資料包大小，避免超長SQL的執行有問題 預設值為16M，當MySQL用戶端或mysqld伺服器收到大於max_allowed_packet位元組的資訊包時，將發出“資訊包過大”錯誤，並關閉串連。對於某些用戶端，如果通訊資訊包過大，在執行查詢期間，可能會遇到“丟失與MySQL伺服器的串連”錯誤。預設值16M。可以參看：dev-doc: http://dev.mysql.com/doc/refman/5.5/en/packet-too-large.html

Sort_Buffer_Size 是一個connection級參數，在每個connection（session）第一次需要使用這個buffer的時候，一次性分配設定的記憶體。Sort_Buffer_Size 並不是越大越好，由於是connection級的參數，過大的設定+高並發可能會耗盡系統記憶體資源。例如：500個串連將會消耗 500*sort_buffer_size(8M)=4G記憶體。Sort_Buffer_Size 超過2KB的時候，就會使用mmap() 而不是 malloc() 來進行記憶體配置，導致效率降低。官方參考文檔: http://dev.mysql.com/doc/refman/5.5/en/server-parameters.html

sort_buffer_size 屬重點最佳化參數。

join_buffer_size用於表間關聯緩衝的大小。

thread_cache_size伺服器線程緩衝這個值表示可以重新利用儲存在緩衝中線程的數量,當中斷連線時如果緩衝中還有空間,那麼用戶端的線程將被放到緩衝中,如果線程重新被請求，那麼請求將從緩衝中讀取,如果緩衝中是空的或者是新的請求，那麼這個線程將被重新建立,如果有很多新的線程，增加這個值可以改善系統效能.通過比較 Connections 和 Threads_created 狀態的變數，可以看到這個變數的作用。

thread_concurrency設定thread_concurrency的值的正確與否, 對mysql的效能影響很大, 在多個cpu(或多核)的情況下，錯誤設定了thread_concurrency的值, 會導致mysql不能充分利用多cpu(或多核), 出現同一時刻只能一個cpu(或核)在工作的情況。thread_concurrency應設為CPU核心數的2倍. 比如有一個雙核的CPU, 那麼thread_concurrency的應該為4; 2個雙核的cpu, thread_concurrency的值應為8（屬重點最佳化參數）

query_cache_size 對於使用MySQL的使用者，對於這個變數大家一定不會陌生。前幾年的MyISAM引擎最佳化中，這個參數也是一個重要的最佳化參數。但隨著發展，這個參數也爆露出來一些問題。機器的記憶體越來越大，人們也都習慣性的把以前有用的參數分配的值越來越大。這個參數加大後也引發了一系列問題。我們首先分析一下query_cache_size的工作原理：一個SELECT查詢在DB中工作後，DB會把該語句緩衝下來，當同樣的一個SQL再次來到DB裡調用時，DB在該表沒發生變化的情況下把結果從緩衝中返回給Client。這裡有一個關建點，就是DB在利用Query_cache工作時，要求該語句涉及的表在這段時間內沒有發生變更。那如果該表在發生變更時，Query_cache裡的資料又怎麼處理呢？首先要把Query_cache和該表相關的語句全部置為失效，然後在寫入更新。那麼如果Query_cache非常大，該表的查詢結構又比較多，查詢語句失效也慢，一個更新或是Insert就會很慢，這樣看到的就是Update或是Insert怎麼這麼慢了。所以在資料庫寫入量或是更新量也比較大的系統，該參數不適合分配過大。而且在高並發，寫入量大的系統，建系把該功能禁掉。重點最佳化參數（主庫 增刪改-MyISAM）

query_cache_limit 指定單個查詢能夠使用的緩衝區大小，預設為1M 。

query_cache_min_res_unit 預設是4KB，設定值大對大資料查詢有好處，但如果你的查詢都是小資料查詢，就容易造成記憶體片段和浪費。

查詢快取片段率 = Qcache_free_blocks / Qcache_total_blocks * 100%

如果查詢快取片段率超過20%，可以用FLUSH QUERY CACHE整理緩衝片段，或者試試減小query_cache_min_res_unit，如果你的查詢都是小資料量的話。

查詢快取利用率 = (query_cache_size – Qcache_free_memory) / query_cache_size * 100%

查詢快取利用率在25%以下的話說明query_cache_size設定的過大，可適當減小;查詢快取利用率在80%以上而且Qcache_lowmem_prunes > 50的話說明query_cache_size可能有點小，要不就是片段太多。

查詢快取命中率 = (Qcache_hits – Qcache_inserts) / Qcache_hits * 100%

thread_stack 限定用於每個資料庫線程的棧大小。預設設定足以滿足大多數應用。

transaction_isolation 設定預設的交易隔離等級.可用的層級有:READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE  

1.READ UNCOMMITTED-讀未提交2.READ COMMITTE-讀已提交3.REPEATABLE READ -可重複讀4.SERIALIZABLE -串列

tmp_table_size 的預設大小是 32M。如果一張暫存資料表超出該大小，MySQL產生一個 The table tbl_name is full 形式的錯誤，如果你做很多進階 GROUP BY 查詢，增加 tmp_table_size 值。

key_buffer_size 索引緩衝大小: 它決定了資料庫索引處理的速度，尤其是索引讀的速度。

read_buffer_size MySql讀入緩衝區大小。對錶進行順序掃描的請求將分配一個讀入緩衝區，MySql會為它分配一段記憶體緩衝區。read_buffer_size變數控制這一緩衝區的大小。如果對錶的順序掃描請求非常頻繁，並且你認為頻繁掃描進行得太慢，可以通過增加該變數值以及記憶體緩衝區大小提高其效能。

read_rnd_buffer_size MySql的隨機讀（查詢操作）緩衝區大小。當按任意順序讀取行時(例如，按照排序次序)，將分配一個隨機讀緩衝區。進行排序查詢時，MySql會首先掃描一遍該緩衝，以避免磁碟搜尋，提高查詢速度，如果需要排序大量資料，可適當調高該值。但MySql會為每個客戶串連發放該緩衝空間，所以應盡量適當設定該值，以避免記憶體開銷過大。

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

myisam_sort_buffer_size MyISAM表發生變化時重新排序所需的緩衝 。

myisam_max_sort_file_size MySQL重建索引時所允許的最大臨時檔案的大小 (當 REPAIR, ALTER TABLE 或者 LOAD DATA INFILE)……如果檔案大小比此值更大,索引會通過索引值緩衝建立(更慢)

myisam_repair_threads 如果一個表擁有超過一個索引, MyISAM 可以通過並行排序使用超過一個線程去修複他們。這對於擁有多個CPU以及大量記憶體情況的使用者,是一個很好的選擇。

myisam_recover 自動檢查和修複沒有適當關閉的 MyISAM 表

innodb_data_file_path 資料表空間檔案 重要資料

innodb_additional_mem_pool_size 這個參數用來設定 InnoDB 儲存的資料目錄資訊和其它內部資料結構的記憶體池大小，類似於Oracle的library cache。這不是一個強制參數，可以被突破。

innodb_buffer_pool_size 這對Innodb表來說非常重要。Innodb相比MyISAM表對緩衝更為敏感。MyISAM可以在預設的 key_buffer_size 設定下啟動並執行可以，然而Innodb在預設的 innodb_buffer_pool_size 設定下卻跟蝸牛似的。由於Innodb把資料和索引都緩衝起來，無需留給作業系統太多的記憶體，因此如果只需要用Innodb的話則可以設定它高達 70-80% 的可用記憶體。一些應用於 key_buffer 的規則有 — 如果你的資料量不大，並且不會暴增，那麼無需把 innodb_buffer_pool_size 設定的太大了

innodb_file_io_threads 檔案IO的線程數，一般為 4，但是在 Windows 下，可以設定得較大。

innodb_thread_concurrency 在InnoDb核心內的允許線程數量。最優值依賴於應用程式,硬體以及作業系統的調度方式。過高的值可能導致線程的互斥顛簸。

innodb_flush_log_at_trx_commit 如果將此參數設定為1，將在每次提交事務後將日誌寫入磁碟。為***能，可以設定為0或2，但要承擔在發生故障時遺失資料的風險。設定為0表示交易記錄寫入記錄檔，而記錄檔每秒重新整理到磁碟一次。設定為2表示交易記錄將在提交時寫入日誌，但記錄檔每次重新整理到磁碟一次。

innodb_log_buffer_size 此參數確定些記錄檔所用的記憶體大小，以M為單位。緩衝區更大能提高效能，但意外的故障將會遺失資料.MySQL開發人員建議設定為1－8M之間

innodb_log_file_size 此參數確定資料記錄檔的大小，以M為單位，更大的設定可以提高效能，但也會增加恢複故障資料庫所需的時間

innodb_log_files_in_group 為提高效能，MySQL可以以迴圈方式將記錄檔寫到多個檔案。推薦設定為3 ，推薦閱讀http://www.taobaodba.com/html/221_innodb_max_dirty_pages_pct_checkpoint.html

innodb_max_dirty_pages_pct Buffer_Pool中Dirty_Page所佔的數量，直接影響InnoDB的關閉時間。參數innodb_max_dirty_pages_pct可以直接控制了Dirty_Page在Buffer_Pool中所佔的比率，而且幸運的是innodb_max_dirty_pages_pct是可以動態改變的。所以，在關閉InnoDB之前先將innodb_max_dirty_pages_pct調小，強制資料區塊Flush一段時間，則能夠大大縮短MySQL關閉的時間。

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

innodb_file_per_table 獨享資料表空間（0代表關閉）

innodb_file_per_table = 0

slow_query_log 啟用慢查詢日誌，也可以在mysql啟動時啟用–slow-query-log-file=/data/mysql/3306/slow.log參數。

log-slave-updates 配置從庫上的更新操作是否寫二進位檔案，如果這台從庫，還要做其他從庫的主庫，那麼就需要打這個參數，以便從庫的從庫能夠進行日誌同步這個參數要和—logs-bin一起使用

binlog_format 基於SQL語句的複製(statement-based replication, SBR)，基於行的複製(row-based replication, RBR)，混合模式複製(mixed-based replication, MBR)。相應地，binlog的格式也有三種：STATEMENT，ROW，MIXED。

mysql-5.5 ---設定檔參數說明