mysql配置文件my.cnf的事例并附解釋

#更改磁盤調度算法

echo 'deadline' >/sys/block/xvdb/queue/scheduler

#關掉NUMA特性

#numactl --interleave=all

下面是my.cnf

[client]
port??????????? = 3306
socket????????? = /tmp/mysql.sock
[mysqld_safe]
#google優化過的內存分配模塊,這個模塊可以讓MySQL在高并發下內存占用更加穩定.系統不自帶,需要自行安裝,建議使用.沒有安裝模塊設置這個參數會報錯.
#malloc-lib=tcmalloc
[mysqld]
port??????????? = 3306
socket????????? = /tmp/mysql.sock
log-error??????? = /data/mysql/data/mysql.err
#表名的大小寫敏感選項,默認為0,即開啟大小寫敏感,1為大小寫不敏感
#lower_case_table_names = 1
#改變mysql的時區,默認是SYSTEM,即與系統同步,show variables like '%time_zone%'查看,按實際情況更改
default-time-zone = '+8:00'
#改變日志顯示時間,默認是UTC,和中國時區相差了8小時,看著很奇怪,改成系統SYSTEM就可以了
log_timestamps = SYSTEM
#默認存儲引擎,5.5之后默認就都是innodb
default-storage-engine=INNODB
#默認表的存儲引擎,5.5之后默認就都是innodb,不設置就跟隨default-storage-engine配置
#default_table_type = InnoDB
#MySQL程序的路徑,代碼調用路徑
basedir=/usr/local/mysql
#全局數據文件及結構的存放位置；表數據(包括innodb引擎),索引,日志(除非單獨設置)等文件都會存放在這里
datadir=/data/mysql/data
#innodb引擎的共享表空間數據文件根目錄,如果不設置,默認使用datadir參數的目錄
#innodb_data_home_dir = /data/mysql/data
#全局默認字符集類型,按需求設定,utf8,utf8mb4,gb2312等
character-set-server = utf8mb4
#全局默認字符校對規則,utf8默認校對規則是utf8_general_ci(不區分大小寫),utf8_bin是區分大小寫的,按需求設置.
#collation_server = utf8_bin
#當連接處于輸入密碼階段,超過connect_timeout的連接請求將會被拒絕.默認是10秒,一般不用設置,也算是不長不短了,最好不要設置太長,連接掛起也是要耗費一定資源
#connect_timeout=5
#關閉一個非交互的連接(純sleep狀態)之前所要等待的秒數,其取值范圍為1-31536000(linux),默認值28800.太長不利于高效利用資源,但某些耗費時間的sql和一些php/python/ruby等的腳本調用則不能設置太短時間,不然會提前中斷.如果有連接池則最好默認或設置更大一些,而且告訴開發,連接池的超時時間不應該超過這個時間,不然會有奇怪的事情
wait_timeout=86400
#關閉一個交互的連接(sleep和其他狀態)之前所要等待的秒數,默認值為28800,調小一些有利于資源回收,如果有需求用到長連接可以調大,因為超時就會強制關閉sql,如果有連接池則最好默認或設置更大一些,而且告訴開發,連接池的超時時間不應該超過這個時間,不然會有奇怪的事情
interactive_timeout = 86400
#在連接繁忙階段(非sleep),對TCP/IP鏈接有效,只針對在Activity狀態下的線程,客戶端發送、接收、或者處理數據包的讀超時時間,默認是30,一般不用設置,除非你網絡狀態很差
#net_read_timeout=300
#在連接繁忙階段(非sleep),對TCP/IP鏈接有效,只針對在Activity狀態下的線程,客戶端發送、接收、或者處理數據包的寫超時時間,默認是60,一般不用設置,除非你網絡狀態很差
#net_write_timeout=300
#禁用域名DNS查找,不能在mysql的授權表中使用主機名或域名,只能使用IP或localhost
#skip_name_resolve = 1
#跳過外部鎖定,防止文件目錄不可用
skip-external-locking
#使用InnoDB引擎獨立表空間,每個表獨立表空間,不使用共享表空間ibdata,但是表的元數據、undo log、插入緩沖等等還是保存在共享表空間里,新版本默認為開
innodb_file_per_table = 1
#在MySQL暫時停止響應新請求之前的短時間內多少個請求可以被存在堆棧中,默認值為50.并發高需要調高這個值,但是太高會占內存,不能超過系統設定值.
back_log = 103
#安全參數,與性能無關,阻止設定值次數嘗試登陸失敗后的客戶端登陸,以防止暴力破解密碼,內網安全要求低的可以適當增加.可以用flush hosts命令刷新這個登錄失敗的主機記錄.
max_connect_errors = 100000
#控制innodb自增長鎖模式參數,全局變量,只讀變量,整型值,有0,1,2三個值(分別代表traditional(“傳統”), consecutive(“連續”)和interleaved(“交叉”)),默認為1,表示自增ID是連續的,一般也不用修改.
#innodb_autoinc_lock_mode = 2
#線程處理模式,no-threads(單線程處理),one-thread-per-connection(每個請求對應一個線程),dynamically-loaded/pool-of-threads(線程池模式,官方mysql和Percona參數名字不一樣)官方版本需要購買服務開啟
#thread_handling = pool-of-threads
#線程池中連接超時的時間,默認500ms,調低可以提高連接池的利用
#thread_pool_stall_limit = 200
#索引緩沖區的大小,只對MyISAM表起作用,但是系統表和臨時磁盤表都還是MyISAM表,要想加快這些的運行速度,這個值還是不能太低
key_buffer_size = 32M
#批量插入緩存大小,只對MyISAM表起作用,適用于在一次性插入100-1000+條記錄時,提高效率.默認值是8M
#bulk_insert_buffer_size = 64M
#網絡數據包容量限制,較大的插入/更新/導出/導入會受此參數限制,超過限制則導致sql執行失敗.默認4M(太小),最大限制為1GB(太大),現在網絡設備帶寬都比較大,建議最少設置到32M以上,千兆以上網絡建議64M/128M比較好.該值應為1024的倍數;否則四舍五入取最接近的倍數,mha等集群架構最好設定成64M以上.
max_allowed_packet = 128M
#全局最大打開文件數,不可以超過系統設定的最大文件數,不然無效
open_files_limit = 65535
#innodb引擎限制一次打開表空間文件.ibd的文件描述符數量,只在使用獨立表空間時才有用,范圍10~4294967295,不可以超過全局最大文件數.新版默認為-1,表示根據實際打開數量自動設置(自動計算規則：如果innodb_file_per_table沒有打開,則默認值為300,如果打開了,則以innodb_open_files與table_open_cache中的較大值為準),僅用于限制innodb表空間.ibd文件的描述符數量,不影響table cache的數量,增大這個變量需要注意同時修改server層的變量open_files_limit=num(只讀變量),這個參數為系統級別的文件句柄限制參數,由于每個session操作數據庫表時都要占用文件描述符,數據庫連接本身也要占用文件描述符,因此如果手動指定innodb_open_files參數時,還需要注意并發連接數量open_files_limit的大小設置
innodb_open_files = 65530
#允許存到緩存里的表的句柄數量,增大可改善頻繁打開和關閉表的操作,新版本默認是2000.參考值為max_connections*表的個數,如果在show processlist里經常發現opening table那么可能就是這兩個變量設置小了,也受open_files_limit的設置和innodb_open_files變量影響.
table_open_cache = 65530
#與table_open_cache作用類似,但是是緩存表定義文件.frm的數量.默認值是-1,表示自動計算,計算公式(400 + (table_open_cache / 2)),如果打開的表實例的數量超過這個參數設置,則LRU機制標記表實例并最終從數據字典緩存中刪除,有助于解決大量內存被用于緩存極少使用的表實例的情況.
#table_definition_cache = 4096
#5.6.6版本新增,每一個線程可以使用緩存表的句柄的并發數(分區數),不能超過table_open_cache,默認是1,最大值為64,一般夠用,除非庫的并發實在太多且頻繁打開表.
table_open_cache_instances = 16
#排序緩存,會話級別內存緩存,一般OLTP超過512K性能增加不明顯,建議值為56K-1M,如果是數據倉庫(OLAP)設置8M以上能進一步提升效果.如果通過show global status看到Sort_merge_passes的值很大,可以考慮通過適當調整這個參數的值來增大排序緩存區,但最好不要全局設置,只對當前session設置較大的值就可以了
sort_buffer_size = 512K
#TCP/IP和套接字通信緩沖區大小,創建長度達net_buffer_length的行,主要影響導入導出
net_buffer_length = 8K
#以順序讀掃描的方式掃描表數據的時候使用緩沖區的大小,會話級別內存緩存,默認為128K,最大為2G,只對MyISAM表起作用,要注意系統臨時表也是myisam
read_buffer_size = 1M
#會話級別內存緩存,以隨機讀掃描的方式掃描表數據的時候使用緩沖區的大小,默認為256K,最大為2G,原本只對MyISAM表起作用,但是新版本也會用做新特性mrr的buffer,
read_rnd_buffer_size = 16M
#MyISAM表發生變化時重新排序所需的緩沖,臨時磁盤表都是MyISAM表,要加快臨時表排序,這個值不能低,特別是數據倉庫(OLAP)
myisam_sort_buffer_size = 128M
#MySQL重建索引時所允許的最大臨時文件的最大大小
myisam_max_sort_file_size = 10G
#自動檢查和修復沒有適當關閉的 MyISAM 表
myisam_repair_threads = 1
#MyISAM表在打開的時候被自動檢查,如果表被不恰當地關閉,就修復表.
#myisam_recover
#啟用binlog,并指定存儲位置,默認存到數據目錄,主庫一定要開,從庫可適當關閉
log-bin=/data/mysql/data/mysql-bin
#默認是1,開啟記錄sql語句到binlog中,改成0則是禁止sql語句記入二進制日志文件binlog中,當然也就不會更新到備庫中
#sql_log_bin = 0
#binlog類型格式,建議用mixed或row,但現在大多數情況會出現mixed格式轉換成row格式的事情(特別是RC隔離級別),而STATEMENT只在個別情況使用了.
binlog_format=mixed
#binlog校驗規則,一般不用設置.當binlog event發送回來最后獲取event內容的時候,會增加4個額外字節做校驗用.在5.6.5以后的版本默認是CRC32,低版本都是NONE.在使用MGR集群時需要設置為NONE
#binlog_checksum=NONE
#數據庫ID號,主要用于主從復制相關,盡量設置成全網唯一值
server-id = 18650
#共享表空間容量大小,建議至少1G以上,5.7之后才實現真正大小縮放(以前只會變大)
innodb_data_file_path = ibdata1:1G:autoextend
#硬盤上單個redolog文件的容量大小,建議1-2G或以上,默認值為48M,最小值為1M,5.6.3版本開始支持最大512G.設置越大則數據庫I/O越少,但是故障恢復的代價就越大,時間越長.
innodb_log_file_size = 1024M
#redolog內存參數,8-32M即可,設置越大則數據庫I/O越少
innodb_log_buffer_size = 16M
#設置有幾組redolog,設置多一些也可以減少I/O壓力,特別是大事務的情況,最大值為100,最小值且也是默認值是2.
innodb_log_files_in_group = 3
#redolog存放目錄,建議不設置,ib_logfile默認存在數據文件目錄下,設置它主要是分散磁盤壓力
#innodb_log_group_home_dir = /tmp
#控制innodb數據文件及redo log的打開、刷寫模式,有三個值：fdatasync(默認且速度最快),O_DSYNC(san存儲和讀較多推薦使用),O_DIRECT(禁用了系統緩存,速度慢但最安全,raid卡+wb或原子寫FIO上效果好),現在大多數 新環境中,都用上了固態硬盤和raid卡,所以建議設置成O_DIRECT,避免使用固態硬盤而造成的雙緩存現象,減輕存儲數據的代價
innodb_flush_method = O_DIRECT
#開啟獨立的purge線程并設定有多少個,有助于提高DML多表操作的效率.以及減少innodb內部的資源爭用.新版默認值為4,最大值為32,
#innodb_purge_threads = 8
#表示一次完成多少個undolog page,但這個值的副作用是會影響到undolog的釋放,因為總是在128輪purge后釋放undolog page.默認300,可以設置1-5000,設置少一些會加快undo回收.
#innodb_purge_batch_size = 1
#控制innodb內部寫線程數量參數,全局變量,只讀變量,新版默認值為4,最小值為1,最大值為64,受操作系統的aio-max-nr設置限制
#innodb_write_io_threads = 8
#控制innodb內部讀線程數量參數,全局變量,只讀變量,新版默認值為4,最小值為1,最大值為64
#innodb_read_io_threads = 8
#事務隔離級別,總共四種:READ-UNCOMMITTED,READ-COMMITTED,REPEATABLE-READ,SERIALIZABLE,默認是RR,一般而言,用RC也不會有大問題,畢竟有問題的幻讀也都是已經提交的數據,反而減輕鎖粒度.
transaction_isolation = READ-COMMITTED
#默認為0,即關閉,意為禁止使用非安全鎖,也即啟用間隙鎖功能.類似于將MySQL的事務隔離級別設定為READ-COMMITTED,但是這個參數必須重啟mysql程序來加載配置才生效,一般也不用設置,除非有特殊需求.不過也有如下特性:(1)對UPDATE或DELETE語句來說,InnoDB僅鎖定需要更新或刪除的行,對不能夠被WHERE條件匹配的行施加的鎖會在條件檢查后予以釋放.這可以有效地降低死鎖出現的概率；(2)執行UPDATE語句時,如果某行已經被其它語句鎖定,InnoDB會啟動一個“半一致性(semi-consistent)”讀操作從MySQL最近一次提交版本中獲得此行,并以之判定其是否能夠并當前UPDATE的WHERE條件所匹配.如果能夠匹配,MySQL會再次對其進行鎖定,而如果仍有其它鎖存在,則需要先等待它們退出.
#innodb_locks_unsafe_for_binlog = 1
#事務提交后通過fsync()寫到硬盤redo文件ib_logfile的頻率,0是存在內存中到一定量再寫入(一般是1秒),1是立刻寫入(速度最慢但最安全,最多只丟一個事務),2是存到操作系統緩存由操作系統決定寫入時間(5.6.6之前是每隔1s刷盤,之后的版本是通過參數innodb_flush_log_at_timeout設置,默認也是1s.,但避免了mysql進程掛了就完全丟數據的問題),由于直接影響了磁盤IO,所以對數據庫性能影響較大.
innodb_flush_log_at_trx_commit = 1
#5.6.6引入,參數innodb_flush_log_at_trx_commit=1時,此超時參數不起作用,當innodb_flush_log_at_trx_commit=0/2時才起作用.表示每隔幾秒一次的頻率刷新redo log(在5.6.6之前是固定每秒一次刷新一次,5.6.6之后刷新頻率可以通過這個參數設置.)默認值是1S.如果你覺得磁盤壓力太大,那可以適當調大,但請注意數據安全性就降低了.
#innodb_flush_log_at_timeout = 5
#刷新binlog的頻率,0是由操作系統決定刷新時間,1每次事務都刷新一次(最慢但最安全,最多只丟一個事件),N(除0和1的其他數量,非負數)每N個事務刷新一次,如果不支持group commit那么就表示n個event(所有事件,包含一般事務和alter/create等的隱式事務)刷新一次,如果是支持group commit,那么就表示攢夠n個event后binlog group才刷新一次,即支持group commit的性能更好,減少IO次數,安全性也比0提高了一些,但是和1的選項相比,安全性還是略差,會丟n-1個事務,mysql5.6后基本完美支持了group commit.
sync_binlog = 1
#行格式定義,有Antelope類型(支持冗余,緊湊行格式)可以使用redundant和compact兩種行格式,Barracuda類型(支持數據壓縮,動態行格式,改善blob,text字段存儲布局)可以使用舊的redundant和compact,還可以用compressed和dynamic四種行格式,要想支持壓縮類型,必須先定義Barracuda.盡量不要用compressed行格式,因為innodb_buffer中的pages無法壓縮,只能壓縮保存到磁盤中的page,表定義中需要添加ROW_FORMAT=xxx行格式配合使用.默認值為antelope,建議使用默認的antelope文件格式和compact的行格式,適用于絕大多數場景,需要用到壓縮和不需要經常修改和備份的表則選擇Barracuda更好一些.
#innodb_file_format = Barracuda
#允許索引使用動態壓縮,但是表的row_format必須是compressed或者dynamic.可以使索引列長度大于767bytes,但是總長度不能大于3072 bytes.
#innodb_large_prefix = 1
#開啟與關閉刷新鄰接頁功能,機械磁盤建議設置為1即啟用,對于SSD則建議設置為0即關閉,還可以設置為2,表示同樣刷新鄰接頁,但不要求在同一個區中的臟頁是連續的.
#innodb_flush_neighbors = 0
#實例級別內存參數,innodb緩存總量(包含一些其他緩存),默認值為128M,設置越大,innodb性能越高,一般單實例建議50%-70%,多實例建議20%左右,5.6版本前是只讀變量,只能重啟實例生效,5.7.x后可動態修改.
innodb_buffer_pool_size = 830M
#緩存池實例并發數量,減少內部對緩存池數據結構的爭用(緩存池的訪問在某些階段是互斥的)而提高并發性能,只在innodb_buffer_pool_size大于1G時生效,默認值在小于1G時為1,大于1G時為8,要保證innodb_buffer_pool_size/innodb_buffer_pool_instances大于128M,不然可能會有反效果,建議大于512M以上.在5.7最大值是64.
#innodb_buffer_pool_instances = 16
#設置 InnoDB 存儲的數據目錄信息和其它內部數據結構的內存池大小.表越多需要分配越多的內存.如果超出設定值,會在錯誤日志寫警告信息.默認值是 1MB.
#innodb_additional_mem_pool_size = 2M
#默認為關閉OFF.如果開啟該參數,啟動MySQL服務時,MySQL將本地熱數據加載到InnoDB緩沖池中,按需求開啟
#innodb_buffer_pool_load_at_startup = 1
#默認為關閉OFF.在關閉時把熱數據dump到本地磁盤,和上面參數配合,按需求開啟.
#innodb_buffer_pool_dump_at_shutdown = 1
#當臟頁占了多少百分比后就開始刷新臟頁,寫得越多,應該設置越低(減少占用緩存),根據LSN來參考log sequence number,Log flushed up to的差距來設定,相差越遠就應該設的越低,默認值為75表示75%,最小值為0,最大值為99
#innodb_max_dirty_pages_pct = 50
#innodb_buffer_pool中old_block_sublist(舊數據鏈)最大占比,默認是8分3,也就是37%,取值范圍是5~95,如果讀多寫少可適當增加,反之減少.
#innodb_old_blocks_pct = 50
#將緩存池刷新到磁盤的臟頁數量,默認值為200,根據硬盤性能判定,機械硬盤1千左右,SSD硬盤4-5千左右,PCIE-SSD可以1-2萬,但不建議超過2萬
#innodb_io_capacity = 1000
#最大刷新到磁盤的臟頁數量,根據上面參數設定,但一定要比他多
#innodb_io_capacity_max = 2000
#控制Innodb雙寫緩沖的開關(雙寫緩沖是對連續磁盤空間的順序寫,減少隨機I/O),默認開啟,0是開啟,1是關閉,如果存儲設備支持原子寫,可以關閉,寫入速度增加明顯,其他情況建議開啟
#innodb_doublewrite = 1
#限制并發增刪改查的操作數量,當并發達到這個限制,額外的線程將被放置到隊列中,等待并發連接釋放,會睡眠數微秒,可以通過設定參數innodb_thread_sleep_delay來配置睡眠時間,期間不會占用cpu和io資源.默認為0(無限制),最大值為1000.大多數情況下不需要設置,但當并發太高,服務器支撐不起負載的時候,可以設置64~128,建議為CPU核心數X磁盤數量X2,實際上，在線程進入鎖等待以后，并發線程的計數也會減一，也就是說等行鎖（也包括間隙鎖）的線程是不算在 128 里面的。
#innodb_thread_concurrency = 128
#以前叫insert buffer,現在叫change buffer,因為現在設置的值有：inserts、deletes、purges、changes(inserts和deletes)、all(默認)、none.只能用在非唯一的索引上,嵌套在innodb_buffer_pool里面,一般不需要動
#innodb_change_buffering = all
#設置innodb_change_buffering占用innodb_buffer_pool的大小,默認是25%,最高只能是50%,5.6后支持,數據修改比較多的話,看需求設置
#innodb_change_buffer_max_size = 25
#在新版本mysql中,query_cache已失去意義,并且偶爾會造成query_cache鎖,增加額外開銷,建議直接關閉
query_cache_size=0
query_cache_type=0
#MDL元數據鎖的超時時間,類似(不只是)數據結構ddl或dml等操作的鎖的等待時間,默認值為31536000秒(一年),設置這個參數主要是避免一些大表的DDL或DML操作長期鎖住表,如果表的數據量小默認就可以.
lock_wait_timeout = 600
#innodb事務請求行鎖的超時時間限制參數,太多鎖等待是很糟糕的,而innodb有自旋鎖,等待時間太長也會耗費資源,單位是秒,最小可設置為1s,最大可設置1073741824秒(34年),默認是50s.
innodb_lock_wait_timeout = 300
#默認值為OFF,如果事務因為加鎖超時,會回滾上一條語句執行的操作.如果設置ON,則整個事務都會回滾.注重事務一致性的需要開啟
innodb_rollback_on_timeout = 1
#當innodb線程獲取mutex資源失敗后,自旋鎖嘗試的次數,默認是30.因為自旋鎖是占用cpu和內存資源的,所以適當降低可以降低負載,但是也會提高查詢失敗的次數.提高該值則相反,最好測試后再考慮修改.
#innodb_sync_spin_loops = 30
#定義InnoDB自旋操作的空閑循環轉數,默認為6轉.只要不為0,對性能變化影響不大,一般是配合innodb_sync_spin_loops來使用.
#innodb_spin_wait_delay = 6
#用來配置從服務器的更新是否寫入二進制日志,如果有層級從庫就必須開(注意server-id),沒有的話建議關閉,能有效提高性能.在GTID復制模式下,所有數據庫都要開啟log_slave_updates參數,是因為為了方便快速同步數據,如果涉及到切換,那么可以快速地把二進制日志進行同步(因為所有服務器都有記錄完整的binlog)
log_slave_updates = 1
#當slave從庫宕機后,假如relay-log損壞了,導致一部分中繼日志沒有處理,則自動放棄所有未執行的relay-log,并且重新從master上獲取日志,這樣就保證了relay-log的完整性.默認情況下該功能是關閉的,建議開啟,可以有效保證數據一致性.
relay_log_recovery = 1
#設置數據庫為只讀狀態,普通用戶不可以寫入/更改數據(管理員還是可以寫),默認關閉(即0),一般從庫最好設置,避免不必要的誤操作,主庫就當然不要動他
#read_only=1
#5.7引入的新參數,以前的read_only不能限制super用戶(例如root),而這個參數可以.
#super_read_only=1
#5.7引入的新參數,開啟后之后,執行的SQL只能是只讀
#tx_read_only=1
#從庫relay log執行完后的自動清除開關,默認開啟(即1),某些情況適用關閉(即0),只作用于從庫
#relay_log_purge=0
#臨時表容量緩存,會話級別內存緩存,超過之后臨時表會轉到磁盤上,磁盤上的臨時表引擎默認是myisam,內存的臨時表引擎是memory,均不可讀寫.由于復雜查詢往往都有用到臨時表并且這個參數是會話級別的,如果設置太大可能容易造成OOM,如果有大的查詢,可以對當前session設置較大的值就可以了,如果通過show global status看到Created_tmp_disk_tables的值很大,可以套用這個公式:Created_tmp_disk_tables/(Created_tmp_disk_tables+Created_tmp_tables)*100% > 10%,確認是否需要增加.
tmp_table_size=64M
#臨時表行數緩存,會話級別內存緩存,和tmp_table_size一起限制臨時表大小,可以創建的內存表(memory table)的最大行數,一般默認16M就足夠.如果有大的查詢,可以對當前session設置較大的值就可以了,如果數據量很大,最好調大.
#max_heap_table_size=96M
#如果臨時表實在太大,磁盤空間壓力增大,則需要考慮設置臨時表存儲位置,默認是/tmp
#tmpdir=/tmp
#數據庫允許的最大連接數限制,建議設置成最高可負載連接數的80%,不然可能卡死,默認是151.如果status變量Connection_errors_max_connections不為零并一直在增長,就說明被這個參數限制而連接失敗的用戶很多,應該考慮增大這個參數的值,另外這個參數也受操作系統的open-files-limit設置限制,而mysql的最大連接數其實是：max_connections+1
max_connections=1024
#每個獨立數據庫用戶的最大連接數,默認是0,表示沒限制,設置這個可以防止最大連接數max_connections滿了,導致管理用戶(root等)都連不上的情況
max_user_connections=256
#可以重新利用保存在緩存中線程的數量,當線程斷開后不會立刻關閉,會保存到緩存中等待被再利用,默認值為-1,表示自動計算:8+(max_connections/100).并發低此參數效果不明顯,并發高調大效果明顯,當公式Threads_created/Connections狀態變量比值越接近1,說明線程的命中率越低,就應該考慮增加這個參數的值
#thread_cache_size = 51
#會話級別內存緩存,聯結查詢緩存,默認值為256K,最小值為128字節,join比較多就調大,也是要注意oom,如果有大的查詢,可以對當前session設置較大的值就可以了.
join_buffer_size = 128M
#開啟慢查詢參數,0關閉,1開啟,默認關閉,可動態修改.
#slow_query_log = 1
#設置慢查詢存儲路徑
slow_query_log_file = /tmp/slow_querys.log
#慢查詢記錄時間,5.5之后可以設置到少于1秒,建議設置到0.1秒
long_query_time = 0.1
#用來設置是否記錄沒有使用索引的查詢到慢查詢記錄,默認關閉,看需求開啟,會產生很多日志,可動態修改.
#log_queries_not_using_indexes = 1
#管理指令也會被記錄到慢查詢.比如OPTIMEZE TABLE, ALTER TABLE,默認關閉,看需求開啟,會產生很多日志,可動態修改.
#log_slow_admin_statements = 1
#是否把從庫執行慢的語句也計入從庫的慢查詢日志中,看需求開啟,會產生很多日志,可動態修改.
#log_slow_slave_statements? = 1
#binlog會話級別內存緩存,事務沒有提交的時候,產生的日志記錄到Cache中；等到事務需要提交的時候,則把日志持久化到磁盤.
binlog_cache_size = 4M
#最大binlog日志的緩存區大小,所有會話的binlog_cache_size總數不能超過這個值,默認就是最大值,在32位的系統中是4G,64位的是16P,設置它是防止mysql內存溢出
#max_binlog_cache_size = 4G
#binlog二進制日志寫入給定值,默認值是1GB,最大不能設置大于1G,根據實際產生情況設置,文件太大不利于打開,但是如果你正使用大的事務,二進制日志還是會超過限制
max_binlog_size = 500M
#限制binlog空間大小,默認為0(即無限制),如果你正使用大的事務,二進制日志還是會超過max_binlog_size限制,而最大值則受這個參數限制,可以防止磁盤被撐爆.
#relay_log_space_limit=16G
#binlog保存天數,過期自動刪除,默認值為0(代表不會自動清理binlog)
expire_logs_days=7
#當binlog_format=row時控制是否記錄用戶原生SQL的參數到binlog file中,默認關閉
#binlog_rows_query_log_events = 1
#打開、關閉普通查詢日志功能,默認關閉,如果設置為1或者ON,則數據庫所有的SQL都會被記錄下來,一般會增長很快,也會浪費數據庫5%-10%的性能.
#general_log = 1
#設置查詢日志路徑和文件名,如果不設置,則默認在datadir目錄下命名為hostname.log,和上面參數結合使用.
#general_log_file = /tmp/general_querys.log
#指定查詢日志general_log和慢查詢日志slow_log存到數據庫的表中,分別是mysql.general_log和mysql.slow_log,默認是FILE,即文件中,按實際情況設置.
#log_output=TABLE
#5.6新功能,從ibdata1分離undo文件,開啟并指定有多少個undo log文件,默認為0,即不開啟該功能.mysql8.0默認開啟并設置成2
innodb_undo_tablespaces = 4
#指定存放undo的目錄,默認是當前數據目錄(datadir參數指定的目錄),出于減輕磁盤壓力的思維下,可以考慮獨立出去
#innodb_undo_directory = /tmp/
#指定undo回滾段大小,至少大于等于35,默認128,一般情況下也夠了,mysql8.0取消了這個參數
#innodb_undo_logs = 256
#開啟在線回收(收縮)undo log日志文件,支持動態設置.
innodb_undo_log_truncate =1
#當undo超過這個閥值(默認是1G),會觸發truncate回收(收縮)動作,truncate后空間縮小到10M,視乎你硬盤容量而定.
#innodb_max_undo_log_size = 2G
#控制回收(收縮)undo log的頻率.undo log空間在它的回滾段沒有得到釋放之前不會收縮,想要增加釋放回滾區間的頻率,就得降低這個參數值,默認128.
#innodb_purge_rseg_truncate_frequency = 64
#mysql5.6后參數,開啟后,會將所有deadlock記錄在error_log中,作為dba,你應該需要知道有多少死鎖信息
innodb_print_all_deadlocks = 1
#在5.6.2之前,slave記錄的master信息以及slave應用binlog的信息存放在文件中,即master.info與relay-log.info.在5.6.2版本之后,允許記錄到table中,多源復制,這個必須是table
master_info_repository = TABLE
#同上信息,多源復制,這個必須是table
relay_log_info_repository = TABLE
#GTID模式,5.6新功能,新的復制方式,需要就打開,建議binlog改成row
gtid_mode = on
#強制GTID的一致性,一般和上面參數一起使用,但是開啟后只允許能夠保障事務安全,并且能夠被日志記錄的SQL語句被執行,像create table ... select 和 create temporarytable語句,以及同時更新事務表和非事務表的SQL語句或事務都不允許執行.
enforce_gtid_consistency = 1
#關閉numa功能,5.6.27新參數,默認為0,即開啟,我們要設成1,即關閉這個功能.numa是linux系統為了合理分配多核環境下內存空間而存在的功能,但是對于大內存應用反而造成了性能瓶頸,所以對于數據庫類別應用來說,應當要關閉.
#innodb_numa_interleave = 1
#用于從flush隊列中取事務的超時時間,這主要是防止并發事務過高,導致某些事務的RT上升,單位微秒,默認是0.
#binlog_max_flush_queue_time = 0
#默認為開啟on(即1),事務將以二進制日志的順序相同的順序進行寫入,當設置為關閉off(即0)時,事務可能以和binlog不相同的順序被提交,高并發下會有一些性能的提升
#binlog_order_commits = 1
#innodb存儲引擎恢復級別,可選0-6,默認是0,當數據庫或表不能正常使用時才可能需要用到
#innodb_force_recovery = 0
#5.7.15新功能,關閉死鎖監測,正常情況下死鎖監測只占少部分資源,所以默認開啟也不需要理會.在高并發情況下則相反,會造成額外的開銷,關閉他就能提高并發,但是出現死鎖就不會回滾,會一直鎖等待,所以關閉之后就需要調小innodb_lock_wait_timeout的值來減少鎖等待時間,避免鎖等待太長時間,代碼層面也需要做好這個報錯的返回規則
#innodb_deadlock_detect=off
#percona特有功能,其他版本暫時沒有,限制所有事務的空閑時間,例如大量JDBC的長連接,特別有用,設置秒數即啟用,默認不設置即沒限制.
#innodb_kill_idle_transaction = 30
#數據導入/導出到文件的限制(load data和SELECT...into outfile),屬于安全限制的一種,新版本默認是null,即禁止.可以輸入文件夾名字,表示限制導入/導出文件夾,舊版本默認值為空,即完全不限制出導入/導出
#secure_file_priv = /tmp
#影響mysql選擇排序的算法,適當調大有助于優化sql執行效率,mysql有兩種文件排序算法(雙路排序和單路排序),如果需要排序的列的總大小加上orderby列的大小超過了這個參數定義的字節,mysql就會使用雙路排序,當包含text、blob列時,也會使用雙路排序,雙路排序的開銷可能會非常巨大,因為他會讀取表兩次,第二次讀取會引發大量的隨機IO,對于myisam來說這個代價尤其昂貴,myisam表利用系統調用去提取每行的數據.單路排序效率更快一些,避免了第二次讀取數據.并且把隨機IO變成了順序IO,但是它會使用更多的內存空間,因為它把排序需要的所有列都一次性導出來保存在內存中
#max_length_for_sort_data = 4096
#設置從庫SQL線程并行重放events(事務)的worker線程數量,默認值為0,最大值為1024.在5.6.x版本中設置這個參數大于0時,則SQL線程充當worker線程的協調者,在多個worker線程之間分發基于庫級別的events,也就是庫級別并行復制,5.7.x版本的并行復制可以設置基于組提交事務的并行復制,更加好用.
#slave_parallel_workers = 8
#5.7新參數,指定并行復制方式,但為了兼容5.6只支持庫模式,默認的并行復制方式是基于庫級別,即默認值是：DATABASE,我們要設置成5.7支持的基于組提交事務的并行復制方式,則需要設置成：LOGICAL_CLOCK,速度更快.
#slave_parallel_type = LOGICAL_CLOCK
#默認是0即關閉,當slave_parallel_type設置為LOGICAL_CLOCK的時候使用,1為開啟,控制Slave上的binlog提交順序和Master上的binlog的提交順序一樣,保證GTID的順序.因為在slave上應用事務的順序是無序的,和relay log中記錄的事務順序不一樣,這樣數據一致性是無法保證的.5.7.19前即使設成1也有可能有問題,所以需要使用這個參數的話請更新到5.7.19.
#slave_preserve_commit_order = 1
#binlog提交后等待延遲多少時間再同步到磁盤,單位是微秒,默認0,不延遲.設置延遲可以讓多個事務在用一時刻提交,提高binlog組提交的并發數和效率,從而提高slave的吞吐量.建議默認
#binlog_group_commit_sync_delay = 100
#在等待上面參數超時之前,如果有足夠多的事務,則停止等待直接提交.單位是事務數,默認0.建議默認
#binlog_group_commit_sync_no_delay_count = 100
#5.6.13之后引入的參數,控制stop slave 的執行時間,假設重放大事務的時候,突然執行stop slave,會執行很久,甚至可能產生死鎖或阻塞,嚴重影響性能.默認值是31536000秒=1年.
#rpl_stop_slave_timeout=300
#判斷主庫是否掛掉的超時設置,在設定時間內依然沒有獲取到Master的回應就認為Master已經掛掉了,后續根據超時重連參數設置進行重連主庫的操作.默認值：3600S
#slave_net_timeout=300
#自動處理同步復制錯誤,默認是STRICT嚴格模式,IDEMPOTENT是冪等模式,它跳過了duplicate-key(1062)和no-key-found(1032)等錯誤,該模式只有在ROW的binlog模式下生效,在STATEMENT的binlog模式下無效,和slave_skip_errors的作用是一樣的,但是slave_skip_errors不支持動態修改,必須重啟mysql才能生效,因此建議使用slave_exec_mode.正常情況下,應該是從庫遇到錯誤就停止復制,然后人工去處理數據一致性問題,第一時間去想怎么滿足這個復制,而不是去跳過這個事務.
#slave_exec_mode=IDEMPOTENT
#半同步主庫開關,使用前需要先確認有沒有加載半同步插件
#rpl_semi_sync_master_enabled = 1
#當確認半同步開啟之后,控制主庫使用半同步復制機制把binlog發送到slave之后,等待從庫ACK接收確認包的時間,超過這個時間會自動轉為異步,直到從庫重新上線
#rpl_semi_sync_master_timeout = 3000
#5.7.3新加的半同步參數,至少有N個slave接收到日志,然后返回ack,這個半同步事務才能提交,默認是1.當這個值設置到和從庫數量相等的話,則效果會等同于全同步復制.
#rpl_semi_sync_master_wait_for_slave_count = 2
#5.7新參數,控制半同步模式下 主庫在返回給會話事務成功之前提交事務的方式.舊模式是AFTER_COMMIT,新模式是AFTER_SYNC,默認值：AFTER_SYNC .master 將每個事務寫入binlog ,傳遞到slave,并且刷新到磁盤.master等待slave 反饋接收到事務并刷新到磁盤.一旦接到slave反饋,master在主庫提交事務并且返回結果給會話. 在AFTER_SYNC模式下,所有的客戶端在同一時刻查看已經提交的數據.假如發生主庫crash,所有在主庫上已經提交的事務已經同步到slave并記錄到relay log.此時切換到從庫,可以保障最小的數據損失.
#rpl_semi_sync_master_wait_point = AFTER_SYNC
#默認為on,即當從庫降到0個的時候,主庫依然等待從庫發送ack確認包.改為off之后,即從庫降到0個后,主庫不再等待從庫,直接轉為異步.
#rpl_semi_sync_master_wait_no_slave=OFF
#半同步從庫開關,使用前需要先確認有沒有加載半同步插件
#rpl_semi_sync_slave_enabled = 1
#5.7.5新參數,定義磁盤臨時表的引擎類型,默認是InnoDB.之前的版本中只能使用MyISAM(就算是新版的5.6也是)
#internal_tmp_disk_storage_engine = InnoDB
#5.6.3新參數,控制CREATE TEMPORARY TABLE 創建的臨時表的引擎類型,在以前默認是MEMORY,
#default_tmp_storage_engine = InnoDB
#控制主從復制的同步進程是否隨mysql程序啟動而啟動,默認是0(即隨mysql進程一起啟動),1為關閉.關閉之后從庫重啟后會出現雙no狀態,對于集群有一定意義.
#skip_slave_start=1
#只記錄參數指定的數據庫的binlog,通常用于主從復制環境的主庫設置,有一定隱患,通常不會這樣用
#binlog_do_db=work,test
#忽略參數指定的數據庫的binlog,通常用于主從復制環境的主庫設置,有一定隱患,通常不會這樣用
#binlog_ignore_db=mysql,test
#從庫同步選擇參數,只執行某個庫或某個表的同步語句,其他庫和表都不處理,不能單行多選,有多一個庫的需求就要多寫一行配置
#replicate_wild_do_table=test.%
#從庫同步選擇參數,忽略掉某個庫或某個表的同步語句,其他庫和表的都會執行,不能單行多選,有多一個庫的需求就要多寫一行配置
#replicate_wild_ignore_table=mysql.%
#5.7.8被添加,單位為ms,動態參數,默認為0.控制查詢的最長時間,默認是0,即無限制,但暫時也只能控制select,看你對數據庫性能要求多高而定,設置較短時間等同于禁用復雜查詢.
#max_execution_time = 300000
#在多線程復制時,在隊列中Pending的事件所占用的最大內存,默認為16M,如果內存富余,或者延遲較大時,可以適當調大;這個值要比主庫的max_allowed_packet大
#slave_pending_jobs_size_max = 128M
#5.6.6新參數,默認為0即關閉,1為開啟,舊的時間格式timestamp有非標準行為,未聲明為null的話,會被分配為notnull屬性,而向此列插入null時將自動轉換為時間戳,或者是"0000-00-00 00:00:00"這樣的數據,這個參數的作用就是關閉這種非標準行為,讓他可以插入null,當徹底放棄timestamp后,這個參數也會被放棄,建議多用datatime
#explicit_defaults_for_timestamp=1
#限制了同一時間在mysqld上所有session中prepared 語句的上限.它的取值范圍為“0 - 1048576”,默認為16382.通常這個值夠用,因為沒有那么多這種需求,但是超出這個值的prepare語句就會報Can't create more than max_prepared_stmt_count statements (current value: 16382)錯誤.改大一些就好
#max_prepared_stmt_count=100000
#MySQL5.6新參數,可以設置Innodb數據頁為8K,4K,32K,64K來適應更多的場景,一般默認16K即可.這個參數在一開始初始化時就要加入my.cnf里,如果已經創建了表,再修改,啟動MySQL會報錯.一般情況下我們會建議一頁最好存儲兩行數據,但是有些情況數據很大或很小,就會浪費或者是溢出數據頁,造成行遷移,更改這個參數可以適當避免.
#innodb_page_size = 8k
#認證加密方式,一般不用設置,默認是:mysql_native_password,在5.7.2之后可以設置sha256_password來使得更加安全,在mysql8.0之后默認是:caching_sha2_password,會讓舊版本的mysql連不上.修改之后只對新授權用戶生效.
#default_authentication_plugin=mysql_native_password
#開啟performance_schema性能統計庫功能,5.7之后默認開啟,5.6之前默認關閉,注意：該參數為只讀參數,需要在實例啟動之前設置才生效.如果開啟失敗就要去錯誤日志進行排查.
#performance_schema = ON

[mysqldump]
quick
#同上,sql太長用mysqldump受此參數影響
max_allowed_packet = 128M
[mysql]
no-auto-rehash
prompt="\u@\h \R:\m:\s [\d]> "
[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout = 28800