Mysql之my.cnf配置詳解

標籤：預設值   res   erro   作用   過程   dir   製作   特徵   比較   

Mysql之my.cnf配置詳解

mysql5.6的版本有400多個變數可以配置,可以通過下列命令獲得
mysql> show variables;

設定檔參數如下：

#用戶端設定，即用戶端預設的串連參數[client]#預設串連連接埠　　　　　　　　　　　　　　　　port = 3306#用於本地串連的socket通訊端socket = /data/mysqldata/3306/mysql.sock#編碼default-character-set = utf8mb4#服務端基本設定[mysqld]#MySQL監聽連接埠port = 3307#為MySQL用戶端程式和伺服器之間的本地通訊指定一個通訊端檔案socket = /data/mysqldata/3307/mysql.sock#pid檔案所在目錄pid-file = /data/mysqldata/3307/mysql.pid#使用該目錄作為根目錄（安裝目錄）basedir = /usr/local/mysql-5.7.11#資料檔案存放的目錄datadir = /data/mysqldata/3307/data#MySQL存放臨時檔案的目錄tmpdir = /data/mysqldata/3307/tmp#服務端預設編碼（資料庫層級）character_set_server = utf8mb4#服務端預設的比對規則，定序collation_server = utf8mb4_bin#MySQL啟動使用者user = mysql#This variable applies when binary logging is enabled. #It controls whether stored function creators can be trusted not to create stored functions that will cause 　#unsafe events to be written to the binary log. #If set to 0 (the default), users are not permitted to create or alter stored functions unless they have the SUPER #privilege in addition to the CREATE ROUTINE or ALTER ROUTINE privilege. 開啟了binlog後，必須設定這個值為1.主要是考慮binlog安全log_bin_trust_function_creators = 1#效能最佳化的引擎，預設關閉performance_schema = 0#secure_auth 為了防止低版本的MySQL用戶端(<4.1)使用舊的密碼認證方式訪問高版本的伺服器。MySQL 5.6.7開始secure_auth 預設為啟用值1secure_auth = 1#開啟全文索引#ft_min_word_len = 1#自動修複MySQL的myisam表#myisam_recover#明確時間戳記預設null方式explicit_defaults_for_timestamp#計劃任務（事件調度器）event_scheduler#跳過外部鎖定;External-locking用於多進程條件下為MyISAM資料表進行鎖定skip-external-locking#跳過用戶端網域名稱解析；當新的客戶串連mysqld時，mysqld建立一個新的線程來處理請求。該線程先檢查是否主機名稱在主機名稱緩衝中。如果不在，線程試圖解析主機名稱。#使用這一選項以消除MySQL進行DNS解析的時間。但需要注意，如果開啟該選項，則所有遠程主機串連授權都要使用IP地址方式，否則MySQL將無法正常處理串連請求!skip-name-resolve　　　　　　　　　　　　　　#MySQL綁定IP#bind-address = 127.0.0.1　　　　　　　　　　#為了安全起見，複製環境的資料庫還是設定--skip-slave-start參數，防止複製隨著mysql啟動而自動啟動skip-slave-start　　　　　　　　　　　　　　 #The number of seconds to wait for more data from a master/slave connection before aborting the read. MySQL主從複製的時候，slave_net_timeout = 30  　　　　　　　　　　　#當Master和Slave之間的網路中斷，但是Master和Slave無法察覺的情況下（比如防火牆或者路由問題）。#Slave會等待slave_net_timeout設定的秒數後，才能認為網路出現故障，然後才會重連並且追趕這段時間主庫的資料。#1.用這三個參數來判斷主從是否延遲是不準確的Slave_IO_Running,Slave_SQL_Running,Seconds_Behind_Master.還是用pt-heartbeat吧。#2.slave_net_timeout不要用預設值，設定一個你能接受的延時時間。#設定是否支援命令load data local infile。如果指定local關鍵詞，則表明支援從客戶主機讀檔案local-infile = 0#指定MySQL可能的串連數量。當MySQL主線程在很短的時間內得到非常多的串連請求，該參數就起作用，之後主線程花些時間（儘管很短）檢查串連並且啟動一個新線程。#back_log參數的值指出在MySQL暫時停止回應新請求之前的短時間內多少個請求可以被存在堆棧中。back_log = 1024#sql_mode,定義了mysql應該支援的sql文法，資料校正等!  NO_AUTO_CREATE_USER：禁止GRANT建立密碼為空白的使用者。#sql_mode = ‘PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION‘#NO_ENGINE_SUBSTITUTION 如果需要的儲存引擎被禁用或未編譯，可以防止自動替換儲存引擎sql_mode = NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER　　#索引塊的緩衝區大小，對MyISAM表效能影響最大的一個參數.決定索引處理的速度，尤其是索引讀的速度。預設值是16M，通過檢查狀態值Key_read_requests#和Key_reads，可以知道key_buffer_size設定是否合理key_buffer_size = 32M　　　　　　　　　　　　#一個查詢語句包的最大尺寸。訊息緩衝區被初始化為net_buffer_length位元組，但是可在需要時增加到max_allowed_packet個位元組。#該值太小則會在處理大包時產生錯誤。如果使用大的BLOB列，必須增加該值。#這個值來限制server接受的資料包大小。有時候大的插入和更新會受max_allowed_packet 參數限制，導致寫入或者更新失敗。max_allowed_packet = 512M#線程緩衝；主要用來存放每一個線程自身的標識資訊，如線程id，線程運行時基本資料等等，我們可以通過 thread_stack 參數來設定為每一個線程棧分配多大的記憶體。thread_stack = 256K　　　　　　　　　　　　　#是MySQL執行排序使用的緩衝大小。如果想要增加ORDER BY的速度，首先看是否可以讓MySQL使用索引而不是額外的排序階段。#如果不能，可以嘗試增加sort_buffer_size變數的大小。sort_buffer_size = 16M　　　　　　　　　　　 #是MySQL讀入緩衝區大小。對錶進行順序掃描的請求將分配一個讀入緩衝區，MySQL會為它分配一段記憶體緩衝區。read_buffer_size變數控制這一緩衝區的大小。#如果對錶的順序掃描請求非常頻繁，並且你認為頻繁掃描進行得太慢，可以通過增加該變數值以及記憶體緩衝區大小提高其效能。read_buffer_size = 16M#應用程式經常會出現一些兩表（或多表）Join的操作需求，MySQL在完成某些 Join 需求的時候（all/index join），為了減少參與Join的“被驅動表”的 #讀取次數以提高效能，需要使用到 Join Buffer 來協助完成 Join操作。當 Join Buffer 太小，MySQL 不會將該 Buffer 存入磁碟檔案， #而是先將Join Buffer中的結果集與需要 Join 的表進行 Join 操作， #然後清空 Join Buffer 中的資料，繼續將剩餘的結果集寫入此 Buffer 中，如此往複。這勢必會造成被驅動表需要被多次讀取，成倍增加 IO 訪問，降低效率。join_buffer_size = 16M　　　　　　　　　　　#是MySQL的隨機讀緩衝區大小。當按任意順序讀取行時(例如，按照排序次序)，將分配一個隨機讀緩衝區。進行排序查詢時，MySQL會首先掃描一遍該緩衝，以避免磁碟搜尋，#提高查詢速度，如果需要排序大量資料，可適當調高該值。但MySQL會為每個客戶串連發放該緩衝空間，所以應盡量適當設定該值，以避免記憶體開銷過大。read_rnd_buffer_size = 32M　#通訊緩衝區在查詢期間被重設到該大小。通常不要改變該參數值，但是如果記憶體不足，可以將它設定為查詢期望的大小。#（即，客戶發出的SQL語句期望的長度。如果語句超過這個長度，緩衝區自動地被擴大，直到max_allowed_packet個位元組。）　　　　　　　　　　　　　　　　　　　net_buffer_length = 16K　　　　　　　　　　 　　　　　　　　　　　　　　　　　　　　　  #當對MyISAM表執行repair table或建立索引時，用以緩衝排序索引；設定太小時可能會遇到” myisam_sort_buffer_size is too small”myisam_sort_buffer_size = 128M　#預設8M，當對MyISAM非空表執行insert … select/ insert … values(…),(…)或者load data infile時，使用樹狀cache快取資料，每個thread分配一個；#註：當對MyISAM表load 大檔案時，調大　bulk_insert_buffer_size/myisam_sort_buffer_size/key_buffer_size會極大提升速度　　　　 bulk_insert_buffer_size = 32M　　　　　　  #thread_cahe_size線程池，線程緩衝。用來緩衝閒置線程，以至於不被銷毀，如果線程緩衝在的空閑線程，需要重建立立新串連，#則會優先調用線程池中的緩衝，很快就能響應串連請求。每建立一個串連，都需要一個線程與之匹配。thread_cache_size = 384　　　　　　　　   #工作原理： 一個SELECT查詢在DB中工作後，DB會把該語句緩衝下來，當同樣的一個SQL再次來到DB裡調用時，DB在該表沒發生變化的情況下把結果從緩衝中返回給Client。#在資料庫寫入量或是更新量也比較大的系統，該參數不適合分配過大。而且在高並發，寫入量大的系統，建系把該功能禁掉。query_cache_size = 0　　　　　　　　　    #決定是否緩衝查詢結果。這個變數有三個取值：0,1,2，分別代表了off、on、demand。query_cache_type = 0　　　　　　　　　    　　　　　　　#它規定了內部記憶體暫存資料表的最大值，每個線程都要分配。（實際起限制作用的是tmp_table_size和max_heap_table_size的最小值。）#如果記憶體暫存資料表超出了限制，MySQL就會自動地把它轉化為基於磁碟的MyISAM表，儲存在指定的tmpdir目錄下tmp_table_size = 1024M#獨立的記憶體表所允許的最大容量.# 此選項為了防止意外建立一個超大的記憶體表導致永盡所有的記憶體資源.max_heap_table_size = 512M  　　　　　　   #mysql開啟最大檔案數open_files_limit = 10240　　　　　　　　　 #MySQL無論如何都會保留一個用於管理員（SUPER）登陸的串連，用於管理員串連資料庫進行維護操作，即使當前串連數已經達到了max_connections。#因此MySQL的實際最大可串連數為max_connections+1；#這個參數實際起作用的最大值（實際最大可串連數）為16384，即該參數最大值不能超過16384，即使超過也以16384為準；#增加max_connections參數的值，不會佔用太多系統資源。系統資源（CPU、記憶體）的佔用主要取決於查詢的密度、效率等；#該參數設定過小的最明顯特徵是出現”Too many connections”錯誤；max_connections = 2000#用來限制使用者資源的，0不限制；對整個伺服器的使用者限制max-user-connections = 0#max_connect_errors是一個MySQL中與安全有關的計數器值，它負責阻止過多嘗試失敗的用戶端以防止暴力破解密碼的情況。max_connect_errors的值與效能並無太大關係。#當此值設定為10時，意味著如果某一用戶端嘗試串連此MySQL伺服器，但是失敗（如密碼錯誤等等）10次，則MySQL會無條件強制阻止此用戶端串連。max_connect_errors = 100000　　　　　　　　 #表描述符緩衝大小，可減少檔案開啟/關閉次數table_open_cache = 5120　　　　　　　　　　#interactive_time -- 指的是mysql在關閉一個互動的串連之前所要等待的秒數(互動串連如mysql gui tool中的串連）interactive_timeout = 86400　　　　　　　　#wait_timeout -- 指的是MySQL在關閉一個非互動的串連之前所要等待的秒數wait_timeout = 86400　　　　　　　　　　　　#二進位日誌緩衝大小 #我們知道InnoDB儲存引擎是支援事務的，實現事務需要依賴於日誌技術，為了效能，日誌編碼採用二進位格式。那麼，我們如何記日誌呢？有日誌的時候，就直接寫磁碟？#可是磁碟的效率是很低的，如果你用過Nginx，，一般Nginx輸出access log都是要緩衝輸出的。因此，記錄二進位日誌的時候，我們是否也需要考慮Cache呢？#答案是肯定的，但是Cache不是直接持久化，於是面臨安全性的問題——因為系統宕機時，Cache中可能有殘餘的資料沒來得及寫入磁碟。因此，Cache要權衡，要恰到好處：#既減少磁碟I/O，滿足效能要求；又保證Cache無殘留，及時持久化，滿足安全要求。binlog_cache_size = 16M#開啟慢查詢slow_query_log = 1#超過的時間為1s；MySQL能夠記錄執行時間超過參數 long_query_time 設定值的SQL語句，預設是不記錄的。long_query_time = 1#記錄管理語句和沒有使用index的查詢記錄log-slow-admin-statements log-queries-not-using-indexes　　　　　　　# *** Replication related settings ***#在複製方面的改進就是引進了新的複製技術：基於行的複製。簡言之，這種新技術就是關注表中發生變化的記錄，而非以前的照抄 binlog 模式。#從 MySQL 5.1.12 開始，可以用以下三種模式來實現：基於SQL語句的複製(statement-based replication, SBR)，基於行的複製(row-based replication, RBR)，混合模式複製(mixed-based replication, MBR)。相應地，binlog的格式也有三種：STATEMENT，ROW，MIXED。MBR 模式中，SBR 模式是預設的。binlog_format = ROW# 為每個session 最大可分配的記憶體，在事務過程中用來儲存二進位日誌的緩衝。#max_binlog_cache_size = 102400#開啟二進位日誌功能，binlog資料位元置log-bin = /data/mysqldata/3307/binlog/mysql-binlog-bin-index = /data/mysqldata/3307/binlog/mysql-bin.index#relay-log日誌記錄的是從伺服器I/O線程將主伺服器的二進位日誌讀取過來記錄到從伺服器本地檔案，#然後SQL線程會讀取relay-log日誌的內容並應用到從伺服器#binlog傳到備機被寫道relaylog裡，備機的slave sql線程從relaylog裡讀取然後應用到本地。relay-log = /data/mysqldata/3307/relay/mysql-relay-binrelay-log-index = /data/mysqldata/3307/relay/mysql-relay-bin.index  #服務端ID，用來高可用時做區分server_id = 100#log_slave_updates是將從伺服器從主伺服器收到的更新記入到從伺服器自己的二進位記錄檔中。log_slave_updates = 1　　　　　　　　　　 #二進位日誌自動刪除的天數。預設值為0,表示“沒有自動刪除”。啟動時和二進位日誌迴圈時可能刪除。expire-logs-days = 15　　　　　　　　　　 #如果二進位日誌寫入的內容超出給定值，日誌就會發生滾動。你不能將該變數設定為大於1GB或小於4096位元組。 預設值是1GB。max_binlog_size = 512M　　　　　　　　　　    #replicate-wild-ignore-table參數能同步所有跨資料庫的更新，比如replicate-do-db或者replicate-ignore-db不會同步類似 replicate-wild-ignore-table = mysql.%　　#設定需要複製的Table#replicate-wild-do-table = db_name.%#複製時跳過一些錯誤;不要胡亂使用這些跳過錯誤的參數，除非你非常確定你在做什麼。當你使用這些參數時候，MYSQL會忽略那些錯誤，#這樣會導致你的主從伺服器資料不一致。#slave-skip-errors = 1062,1053,1146#這兩個參數一般用在主主同步中，用來錯開自增值, 防止索引值衝突auto_increment_offset = 1auto_increment_increment = 2　　　　　　　　#將中繼日誌的資訊寫入表:mysql.slave_realy_log_inforelay_log_info_repository = TABLE　　　　　#將master的串連資訊寫入表：mysql.salve_master_infomaster_info_repository = TABLE　　　　　　 #中繼日誌自我修複；當slave從庫宕機後，假如relay-log損壞了，導致一部分中繼日誌沒有處理，則自動放棄所有未執行的relay-log，#並且重新從master上擷取日誌，這樣就保證了relay-log的完整性relay_log_recovery = on　　　　　　　　　　# *** innodb setting ***#InnoDB 用來高速緩衝資料和索引記憶體緩衝大小。 更大的設定可以使訪問資料時減少磁碟 I/O。innodb_buffer_pool_size = 4G#單獨指定資料檔案的路徑與大小innodb_data_file_path = ibdata1:1G:autoextend#每次commit 日誌緩衝中的資料刷到磁碟中。通常設定為 1，意味著在事務提交前日誌已被寫入磁碟， 事務可以運行更長以及服務崩潰後的修複能力。#如果你願意減弱這個安全，或你啟動並執行是比較小的交易處理，可以將它設定為 0 ，以減少寫記錄檔的磁碟 I/O。這個選項預設設定為 0。innodb_flush_log_at_trx_commit = 0#sync_binlog=n，當每進行n次事務提交之後，MySQL將進行一次fsync之類的磁碟同步指令來將binlog_cache中的資料強制寫入磁碟。#sync_binlog = 1000#對於多核的CPU機器，可以修改innodb_read_io_threads和innodb_write_io_threads來增加IO線程，來充分利用多核的效能innodb_read_io_threads = 8　　innodb_write_io_threads = 8　　　　　　　　#Innodb Plugin引擎開始引入多種格式的行儲存機制，目前支援：Antelope、Barracuda兩種。其中Barracuda相容Antelope格式。innodb_file_format = Barracuda#限制Innodb能開啟的表的數量innodb_open_files = 65536#開始片段回收線程。這個應該能讓片段回收得更及時而且不影響其他線程的操作innodb_purge_threads = 1　#分散式交易innodb_support_xa = FALSE　#InnoDB 將日誌寫入日誌磁碟檔案前的緩衝大小。理想值為 1M 至 8M。大的日誌緩衝允許事務運行時不需要將日誌儲存入磁碟而只到事務被提交(commit)。　#因此，如果有大的交易處理，設定大的日誌緩衝可以減少磁碟I/O。　　　　　innodb_log_buffer_size = 256M#日誌組中的每個記錄檔的大小(單位 MB)。如果 n 是日誌組中記錄檔的數目，那麼理想的數值為 1M 至下面設定的緩衝池(buffer pool)大小的 1/n。較大的值，#可以減少重新整理緩衝池的次數，從而減少磁碟 I/O。但是大的記錄檔意味著在崩潰時需要更長的時間來恢複資料。innodb_log_file_size = 1G#指定有三個日誌組innodb_log_files_in_group = 3#在復原(rooled back)之前，InnoDB 事務將等待逾時的時間(單位 秒)#innodb_lock_wait_timeout = 120#innodb_max_dirty_pages_pct作用：控制Innodb的髒頁在緩衝中在那個百分比之下，值在範圍1-100,預設為90.這個參數的另一個用處：#當Innodb的記憶體配置過大，致使swap佔用嚴重時，可以適當的減小調整這個值，使達到swap空間釋放出來。建義：這個值最大在90%，最小在15%。#太大，緩衝中每次更新需要致換資料頁太多，太小，放的資料頁太小，更新操作太慢。innodb_max_dirty_pages_pct = 75　　　　　#innodb_buffer_pool_size 一致 可以開啟多個記憶體緩衝池，把需要緩衝的資料hash到不同的緩衝池中，這樣可以並行的記憶體讀寫。innodb_buffer_pool_instances = 4 　　　 #這個參資料控制Innodb checkpoint時的IO能力innodb_io_capacity = 500　　　　　　　　#作用：使每個Innodb的表，有自已獨立的資料表空間。如刪除檔案後可以回收那部分空間。#分配原則：只有使用不使用。但ＤＢ還需要有一個公用的資料表空間。innodb_file_per_table = 1#當更新/插入的非叢集索引的資料所對應的頁不在記憶體中時（對非叢集索引的更新操作通常會帶來隨機IO），會將其放到一個insert buffer中，#當隨後頁面被讀到記憶體中時，會將這些變化的記錄merge到頁中。當伺服器比較空閑時，後台線程也會做merge操作innodb_change_buffering = inserts　　　　#該值影響每秒重新整理髒頁的操作，開啟此配置後，重新整理髒頁會通過判斷產生重做日誌的速度來判斷最合適的重新整理髒頁的數量；innodb_adaptive_flushing = 1　　　　　　#資料庫交易隔離等級 ，讀取提交內容transaction-isolation = READ-COMMITTED　　#innodb_flush_method這個參數控制著innodb資料檔案及redo log的開啟、刷寫入模式#InnoDB使用O_DIRECT模式開啟資料檔案，用fsync()函數去更新日誌和資料檔案。innodb_flush_method = O_DIRECT　　　　　　#預設設定值為1.設定為0：表示Innodb使用內建的記憶體配置程式；設定為1：表示InnoDB使用作業系統的記憶體配置程式。#innodb_use_sys_malloc = 1　　　　　　　　[mysqldump]#它強制 mysqldump 從伺服器查詢取得記錄直接輸出而不是取得所有記錄後將它們緩衝到記憶體中quick　　　　　　　　　　　　　　　　　#限制server接受的資料包大小;指代mysql伺服器端和用戶端在一次傳送資料包的過程當中資料包的大小max_allowed_packet = 512M   　　　　    　　#TCP/IP和通訊端通訊緩衝區大小,建立長度達net_buffer_length的行net_buffer_length = 16384   　　　　    　　[mysql]#auto-rehash是自動補全的意思auto-rehash　　　　　　　　　　　　　　#isamchk資料檢測恢複工具[isamchk]   　　　　　　　　　　　　　　key_buffer = 256Msort_buffer_size = 256Mread_buffer = 2Mwrite_buffer = 2M#使用myisamchk公用程式來獲得有關你的資料庫桌表的資訊、檢查和修複他們或最佳化他們[myisamchk]key_buffer = 256Msort_buffer_size = 256Mread_buffer = 2Mwrite_buffer = 2M[mysqlhotcopy]#mysqlhotcopy使用lock tables、flush tables和cp或scp來快速備份資料庫.它是備份資料庫或單個表最快的途徑,完全屬於物理備份,但只能用於備份MyISAM儲存引擎和運行在資料庫目錄所在的機器上.interactive-timeout#與mysqldump備份不同,mysqldump屬於邏輯備份,備份時是執行的sql語句.使用mysqlhotcopy命令前需要要安裝相應的軟體依賴包.

Mysql之my.cnf配置詳解