MySQL配置檔案參數詳解

[client]

port = 3307

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

default-character-set=utf8

[mysql]

prompt = mysql(\\u@\h-\R:\\m:\\s [\\d])>

#提示目前資料庫操作使用者名、庫名、時間等

[mysqld]

basedir = /usr/local/mysql5_6

datadir = /usr/local/mysql5_6/data

server_id = 111

log-error = /usr/local/mysql5_6/error.log

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

character-set-server=utf8

max_connections = 3000

#MySQL允許最大的程序連接配接數，如果經常出現Too Many Connections的錯誤提示，則需要增大此值。

skip-external-locking

skip-name-resolve

max_allowed_packet = 32M

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

wait_timeout = 28800

#結束回話等待時間

thread_cache_size = 64

#伺服器線程緩存這個值表示可以重新利用儲存在緩存中線程的數量,當斷開連接配接時如果緩存中還有空間,那麼用戶端的線程将被放到緩存中,如果線程重新被請求，那麼請求将從緩存中讀取,如果緩存中是空的或者是新的請求，那麼這個線程将被重新建立,如果有很多新的線程，增加這個值可以改善系統性能.通過比較Connections 和 Threads_created 狀态的變量，可以看到這個變量的作用。設定規則如下：1GB 記憶體配置為8，2GB配置為16，3GB配置為32，4GB或更高記憶體，可配置更大。

explicit_defaults_for_timestamp=true

query_cache_limit = 4M

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

query_cache_min_res_unit = 2k

#預設是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%

tmp_table_size = 128M

# tmp_table_size 的預設大小是 32M。如果一張臨時表超出該大小，MySQL産生一個 The table tbl_name is full 形式的錯誤，如果你做很多進階 GROUP BY 查詢，增加 tmp_table_size 值。如果超過該值，則會将臨時表寫入磁盤。

thread_concurrency = 32

#設定thread_concurrency的值的正确與否,對mysql的性能影響很大,在多個cpu(或多核)的情況下，錯誤設定了thread_concurrency的值,會導緻mysql不能充分利用多cpu(或多核),出現同一時刻隻能一個cpu(或核)在工作的情況。thread_concurrency應設為CPU核數的2倍比如有一個雙核的CPU,那麼thread_concurrency的應該為4;2個雙核的cpu, thread_concurrency的值應為8

open_files_limit = 10240

#每個用戶端的連接配接也是一個檔案句柄。

back_log = 2048

#在MYSQL暫時停止響應新請求之前，短時間内的多少個請求可以被存在堆棧中。如果系統在短時間内有很多連接配接，則需要增大該參數的值，該參數值指定到來的TCP/IP連接配接的監聽隊列的大小。

innodb_flush_log_at_trx_commit = 1

#每次commit 日志緩存中的資料刷到磁盤中

innodb_lock_wait_timeout = 50

#InnoDB事務在被復原之前可以等待一個鎖定的逾時秒數。InnoDB在它自己的鎖定表中自動檢測事務死鎖并且復原事務。InnoDB用LOCKTABLES語句注意到鎖定設定。預設值是50秒

#innodb_auto_extend_increment = 64M

#可幫助降低碎片

innodb_buffer_pool_size = 8192M

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

innodb_log_buffer_size = 16M

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

innodb_buffer_pool_instances = 8

#用于優化更高并發的負載

innodb_log_file_size = 60M

#此參數确定資料日志檔案的大小，以M為機關，更大的設定可以提高性能，但也會增加恢複故障資料庫所需的時間

innodb_log_files_in_group = 3M

#為提高性能，MySQL可以以循環方式将日志檔案寫到多個檔案。推薦設定為3M

innodb_io_capacity = 400

#InnoDB曾經在代碼裡寫死了假設伺服器運作在每秒100個I/O操作的單硬碟上。預設值很糟糕。現在可以告訴InnoDB伺服器有多大的I/O能力。有時需要把這個值設定得相當高（像SSD這樣極快的儲存設備上需要設定為上萬）才能穩定地重新整理髒頁。

innodb_write_io_threads = 4

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

innodb_read_io_threads = 4

innodb_thread_concurrency = 8

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

innodb_file_per_table = ON

#其預設值在mysql5.6.6+後為ON。開啟此選項後，關于InnoDB表的資料和索引單獨存儲在自己的表空間中（.ibd結尾的檔案）。否則，存儲在系統的表空間中（ibdata）。

innodb_print_all_deadlocks = 1

#死鎖資訊列印到錯誤日志裡

#------快速預熱Buffer_Pool緩沖池-------#

#在之前的版本裡，如果一台高負荷的機器重新開機後，記憶體中大量的熱資料被清空，此時就會重新從磁盤加載到Buffer_Pool緩沖池裡，這樣當高峰期間，性能就會變得很差，連接配接數就會很高。在關閉MySQL時，會把記憶體中的熱資料儲存在磁盤裡ib_buffer_pool檔案中，位于資料目錄下,在啟動後，會自動加載熱資料到Buffer_Pool緩沖池裡。

innodb_buffer_pool_dump_at_shutdown = 1

#在關閉時把熱資料dump到本地磁盤。

innodb_buffer_pool_dump_now = 1

#采用手工方式把熱資料dump到本地磁盤。

innodb_buffer_pool_load_at_startup = 1

#在啟動時把熱資料加載到記憶體。

innodb_buffer_pool_load_now = 1

#采用手工方式把熱資料加載到記憶體。

table_open_cache = 4096

#從官方文檔看出在MySQL5.6.8+開始預設值為2000，就能簡單的判斷出原來預設值是不夠的。可以通過觀察Opened_tables其值及其一段時間的變化來檢查該變量。如果看到Opened_tables的值很大并且又不經常執行FLUSH TABLES（執行其指令強制所有的表重新關閉且打開），那麼可能你應該增加該變量的值。

max_connect_errors = 1000

#設定每個主機的連接配接請求異常中斷的最大次數，當超過該次數，MYSQL伺服器将禁止host的連接配接請求，直到mysql伺服器重新開機或通過flushhosts指令清空此host的相關資訊。

expire_logs_days = 7

#如果啟用了二進制日志，應該打開這個選項，可以讓伺服器在指定的天數之後清理舊的二進制日志。如果不啟用，最終伺服器的空間會被耗盡，導緻伺服器卡住或崩潰。

sort_buffer_size = 1M

#Sort_Buffer_Size是一個connection級參數，在每個connection（session）第一次需要使用這個buffer的時候，一次性配置設定設定的記憶體。Sort_Buffer_Size并不是越大越好，由于是connection級的參數，過大的設定+高并發可能會耗盡系統記憶體資源。例如：500個連接配接将會消耗500*sort_buffer_size(8M)=4G記憶體，Sort_Buffer_Size 超過2KB的時候，就會使用mmap() 而不是 malloc() 來進行記憶體配置設定，導緻效率降低。

join_buffer_size = 8M

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

read_buffer_size = 1M

#MySql讀入緩沖區大小。對表進行順序掃描的請求将配置設定一個讀入緩沖區，MySql會為它配置設定一段記憶體緩沖區。read_buffer_size變量控制這一緩沖區的大小。如果對表的順序掃描請求非常頻繁，并且你認為頻繁掃描進行得太慢，可以通過增加該變量值以及記憶體緩沖區大小提高其性能。和sort_buffer_size一樣，該參數對應的配置設定記憶體也是每個連接配接獨享。

read_rnd_buffer_size = 16M

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

myisam_sort_buffer_size = 256M

# MyISAM表發生變化時重新排序所需的緩沖

bulk_insert_buffer_size = 64M

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

key_buffer_size = 1024M

#批定用于索引的緩沖區大小，增加它可以得到更好的索引處理性能，對于記憶體在4GB左右的伺服器來說，該參數可設定為256MB或384MB。

myisam_max_sort_file_size = 10G

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

#-------------------mysql5.6的主從增加了不少參數，提升了主從同步的安全和效率，以下主從參數詳解--------------------#

server-id=1

binlog-format=ROW

log-bin=master-bin.log

log-bin-index=master-bin.index

log-slave-updates=true

#gtid-mode=on

#enforce-gtid-consistency=true

#這兩個參數是啟用mysql5.6中的UUID同步模式，兩個參數必須一起打開，否則報錯，slave在做同步複制時，無須找到binlog日志和POS點，直接change master to master_auto_position=1即可，自動找點同步。

#GTID的局限性： （鑒于這些局限性，慎用,是以做了關閉處理）

#1.CREATE TABLE...SELECT語句不支援。因為該語句會被拆分成createtable和insert兩個事務，并且這個兩個事務被配置設定了同一個GTID，這會導緻insert被備庫忽略掉。

#2.不支援CREATE TEMPORARY TABLE、DROP TEMPORARY TABLE 臨時表操作。

master-info-repository=TABLE

relay-log-info-repository=TABLE

sync-master-info=1

slave-parallel-workers=2

#預設是0，不開啟，最大并發數為1024個線程。主從複制啟用4個sql線程，提高從伺服器吞吐量，減少延遲,使用并發的 SQL 線程對不同資料庫并行應用事件

binlog-checksum=CRC32

master-verify-checksum=1

slave-sql-verify-checksum=1

binlog-rows-query-log-events=1

#這四個參數是啟用binlog/relaylog的校驗，防止日志出錯

sync_binlog=1

#預設情況下，并不是每次寫入時都将binlog與硬碟同步。是以如果作業系統或機器(不僅僅是MySQL伺服器)崩潰，有可能binlog中最後的語句丢失了。要想防止這種情況，你可以使用sync_binlog全局變量(1是最安全的值，但也是最慢的)，使binlog在每N次binlog寫入後與硬碟同步。即使sync_binlog設定為1,出現崩潰時，也有可能表内容和binlog内容之間存在不一緻性。如果使用InnoDB表，MySQL伺服器處理COMMIT語句，它将整個事務寫入binlog并将事務送出到InnoDB中。如果在兩次操作之間出現崩潰，重新開機時，事務被InnoDB復原，但仍然存在binlog中。可以用--innodb-safe-binlog選項來增加InnoDB表内容和binlog之間的一緻性。(注釋：在MySQL5.1中不需要--innodb-safe-binlog；由于引入了XA事務支援，該選項廢棄了），該選項可以提供更大程度的安全，使每個事務的binlog(sync_binlog=1)和(預設情況為真)InnoDB日志與硬碟同步，該選項的效果是崩潰後重新開機時，在滾回事務後，MySQL伺服器從binlog剪切復原的InnoDB事務。這樣可以確定binlog回報InnoDB表的确切資料等，并使從伺服器保持與主伺服器保持同步(不接收 復原的語句)。

relay_log_purge = 1

relay_log_recovery = 1

#這兩個是啟用relaylog的自動修複功能，避免由于網絡之類的外因造成日志損壞，主從停止。

slave-skip-errors = 1062

binlog_cache_size = 4M

max_binlog_cache_size = 2G

#指定binary log緩存的最大容量，如果設定的過小，則在執行複雜查詢語句時MySQL會出錯。

max_binlog_size = 1G

#指定binary log檔案的最大容量，預設為1GB

[mysqldump]

quick

max_allowed_packet = 2048M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

     本文轉自aaron428 51CTO部落格，原文連結http://blog.51cto.com/aaronsa/1740634：，如需轉載請自行聯系原作者