Mysql常用配置及最佳化

標籤：緩衝   選項   資料庫   從伺服器   10g   主機名稱   資料庫操作   eth   重啟   

[client]
# 該目錄下的內容常用來進行localhost登陸，一般不需要修改
port = 3306# 連接埠號碼
socket = /var/lib/mysql/mysql.sock# 通訊端檔案(localhost登陸會自動產生)

[mysql]
# 包含一些用戶端mysql命令列的配置
no-auto-rehash # 預設不自動補全auto-rehash自動補全

[mysqld]
# mysql最佳化的配置目錄，除硬體和環境配置外，全部最佳化在此配置，
# 一般伺服器安裝只有此配置目錄
user = mysql#預設啟動使用者，一般不需要修改，可能出現啟動不成功
port = 3306#連接埠號碼
socket = /var/lib/mysql/mysql.sock#通訊端檔案 （通訊端方式登陸比TCP/IP方式串連快）
character-set-server = utf8#設定資料庫伺服器預設編碼 utf-8
basedir = /usr/local/mysql#資料庫安裝目錄--指定此參數可解決相對路徑造成的問題
datadir = /data/mysql#資料庫目錄，資料庫目錄切換時需要用到
pid-file = /var/run/mysqld/mysqld.pid#mysql進程檔案，可指定自己的進程檔案
external-locking = FALSE#外部鎖定(非多伺服器可不設定該選項，預設skip-external-locking)
skip-external-locking#跳過外部鎖定 （避免多進程環境下的external locking--鎖機制）
skip-name-resolve#跳過主機名稱解析，直接IP訪問，可提升訪問速度
log-error = /data/log/mysqld.log#錯誤記錄檔檔案
log-warnings#預設為1,表示啟用警告資訊記錄日誌,不需要置0即可,大於1時表示將錯誤或者失敗串連記錄日誌
open_files_limit = 10240#全域唯讀變數,檔案描述符限制 註：上限其實為OS檔案描述符上限，小於OS上限時生效 可用lsof查看限制並修改相應配置

#以下配置較為重要
back_log = 600 #在MYSQL暫時停止回應新請求之前，短時間內的多少個請求可以被存在堆棧中。不超過TCP/IP監聽隊列，建議512倍數
#如果系統在短時間內有很多串連，則需要增大該參數的值，該參數值指定到來的TCP/IP串連的監聽隊列的大小。預設值50。
max_connections = 5000#MySQL允許最大的進程串連數，如果經常出現Too Many Connections的錯誤提示，則需要增大此值
max_connect_errors = 6000#設定每個主機的串連請求異常中斷的最大次數，當超過該次數，MYSQL伺服器將禁止host的串連請求，
#直到mysql伺服器重啟或通過flush hosts命令清空此host的相關資訊
max_allowed_packet = 32M#限制接收資料包的大小,單條資料超過該值時插入或更新失敗,
#show VARIABLES like ‘%max_allowed_packet%‘; 查看當前限制大小
sort_buffer_size = 8M#每個串連獨享，用於最佳化不能通過sql或者索引最佳化的group和order等，設定的值應適中
#比如：500個串連，500*8 = 4G,將消耗4G記憶體
join_buffer_size = 8M#用於表間關聯緩衝的大小,每個串連獨享
#thread_concurrency = 16#應設為CPU核心數的2倍.
thread_cache_size = 600#線程緩衝變數，減少串連的建立，一般設定規則
#1G->8;2G->16;3G->32;根據實際情況可適當加大(個人：2^(n+2))
thread_stack = 1024K#設定MYSQL每個線程的堆棧大小，可設定範圍為128K至4GB,預設足夠大，一般不用修改
query_cache_size = 128M#查詢快取，設定不宜過大，主要是因為緩衝越大，設定失效時間越長。如果高並發寫，可以禁用該功能
query_cache_limit = 2M#指定單個查詢能夠使用的緩衝區大小，預設為1M
query_cache_min_res_unit = 4k#大資料查詢4k,小資料查詢2k,目的是提高快取命中率

#資料庫引擎相關參數
default-storage-engine = InnoDB#預設資料庫引擎
innodb_data_file_path = ibdata1:1024M:autoextend#指定資料檔案，初始大小，指定擴充大小 注意與資料庫中初始檔案大小保持一致
#格式：size-allocation[:autoextend[:max-size-allocation]]
innodb_read_io_threads = 16#read IO線程,根據cpu核心線程數量設定
innodb_write_io_threads = 16#write IO線程,根據cpu核心線程數量設定
innodb_thread_concurrency = 48#伺服器有幾個CPU就設定為幾，建議用預設設定，一般為8
innodb_lock_wait_timeout = 120#事務擷取鎖的最長等待時間，逾時請求失敗
innodb_buffer_pool_size = 80G#類似於myisam的key_pool_size 適當增加可提高命中率，專用伺服器可設定為70-80%
#innodb_flush_log_at_trx_commit = 2#預設值1 每次提交日誌記錄磁碟 2 日誌寫入系統緩衝 0 不提交也記錄，不安全，不推薦
innodb_flush_method = O_DIRECT#控制著innodb資料檔案及redo log的開啟、刷寫入模式
#fdatasync(預設)，調用fsync()去刷資料檔案與redo log的buffer
#O_DSYNC，innodb會使用O_SYNC方式開啟和刷寫redo log,使用fsync()刷寫資料檔案
#O_DIRECT，innodb使用O_DIRECT開啟資料檔案，使用fsync()刷寫資料檔案跟redo log
#fsync() 作用：數作用是flush時將與fd檔案描述符所指檔案有關的buffer刷寫到磁碟
innodb_log_files_in_group = 2#以迴圈方式將記錄檔寫到多個檔案，預設2
innodb_log_file_size = 4G#資料記錄檔大小，較大可提升效能，
innodb_log_buffer_size = 512M#記錄檔所用的記憶體大小，以M為單位。緩衝區更大能提高效能，但意外的故障將會遺失資料(開發人員推薦1-8M---不知道為什麼)
#innodb_file_per_table = 1#獨立資料表空間 innodb 預設一個資料表空間
#
innodb_autoinc_lock_mode = 2#主要作用於自增列
#0 這個表示tradition 傳統 得到語句層級的鎖，具有連續性和重複性，但影響並發的插入
　　　　#1 這個表示consecutive 連續 根據一次性插入的數量產生連續的值，
#auto_inc鎖不需要一直保持到語句的結束，語句得到了相應的值後就可以提前釋放鎖
　　　　#2 這個表示interleaved 交錯 沒有auto_inc鎖，auto_incremant值可能不是連續的

#Percona XtraDB Cluster 5.7. 基於同步複製的多主MySQL叢集解決方案，只支援Innodb引擎
#需要安裝wsrep打過補丁的版本
#：https://www.percona.com/downloads/

pxc_strict_mode = ENFORCING
wsrep_cluster_address = gcomm://192.168.66.242,192.168.66.241 #叢集中的節點地址，可以使用主機名稱或IP
wsrep_node_address = 192.168.66.241#本機節點地址，可以使用主機名稱或IP
wsrep_provider = /usr/lib64/galera3/libgalera_smm.so
wsrep_sst_method = xtrabackup-v2#指定SST方式，支援rsync(最快，需要鎖表)，
#mysqldump和xtrabackup，從5.5.33-23.7.6起支援xtrabackup-v2
wsrep_slave_threads = 8#線程數量
wsrep_cluster_name = Cluster#叢集名字，必須統一
wsrep_node_name = Node1#叢集中節點名字，必須唯一
wsrep_sst_auth = "sstuser:8jUthGnAUfWEfJ9"#xtrabackup使用的使用者名稱密碼，第一台節點啟動時建立，詳見安裝文檔
#wsrep_sst_donor=‘node3,‘#一個逗號分割的節點串作為狀態轉移源，
#比如wsrep_sst_donor=node5,node3, 如果node5可用，用node5,不可用用node3,
#如果node3不可用，最後的逗號表明讓供應商自己選擇一個最優的。

transaction_isolation = READ-COMMITTED#交易隔離等級
# 1.READ-UNCOMMITTED-讀未提交 --髒讀(開發生產均不建議)
# 2.READ-COMMITTE-讀已提交 --非預設
# 3.REPEATABLE-READ -可重複讀--可能出現幻影行（Innodb 和 Falcon 通過並發解決幻讀問題）
# 4.SERIALIZABLE -串列--最進階別 可能出現大量逾時和鎖競爭
tmp_table_size = 10G#查詢產生的暫存資料表大小超過該值時會在硬碟產生MyISAM表，如果存在大量group by 等語句，可調整大小
max_heap_table_size = 5G#記憶體表最大空間限制 表在磁碟中，資料在記憶體中
explicit_defaults_for_timestamp=1#主要針對TIMESTAMP列，不指定預設會自動加上notnull屬性，第一列會自動添加current timestamp
table-definition-cache = 4096#表定義相關資訊緩衝 實際存放的是frm與記憶體的映射關係
table-open-cache = 4096#開啟表緩衝 存放已開啟的表控制代碼
#同時設定生效
interactive_timeout = 120#mysql在關閉一個互動的串連之前所要等待的秒數(用戶端串連)
wait_timeout = 864000#mysql在關閉一個非互動的串連之前所要等待的秒數 預設8小時(應用程式調用),
#根據應用實際情況決定 show processlist; sleep進程多則相應調小
long_query_time = 2#慢查詢逾時設定，預設10秒，記錄超過查詢時間的語句
slow-query-log=1#注意之前的版本5.6之前的版本為 log-slow-query 是否記錄慢查詢日誌---作為資料庫分析
slow-query-log-file=/data/log/query-slow.log#慢查詢日誌記錄檔案
#相關二進位檔案設定
log_bin#資料庫操作二進位記錄，Database Backup，複製所需
binlog_format = ROW#ROW(基於行的複製--安全，但是注意並發) STATEMENT(基於sql語句的複製)，MIXED(混合模式複製)
binlog_cache_size = 4M#二進位日誌緩衝，提高log-bin記錄效率
log_bin_trust_function_creators = 1#主從複製是需要注意，為了保證主從複製完全一致，需要開啟此選項，主從預設阻止函數建立
max_binlog_size = 1G#二進位記錄檔大小預設1G 要求大於4096 小於1G
expire_logs_days = 7#清除到期日誌
#binlog_do_db#此參數表示只記錄指定資料庫的二進位日誌
#binlog_ignore_db#忽略某資料庫記錄

key_buffer_size = 2048M#提高查詢命中率，最好不要超過4G,可根據快取命中率適當調整show global status like ‘key_read%‘;
read_buffer_size = 16M#順序讀緩衝，每個串連獨享
read_rnd_buffer_size = 64M#隨機讀緩衝，每個串連獨享
bulk_insert_buffer_size = 1G#批量插入並且只有向非空表添加資料時才使用該緩衝,只對myisam表使用。

#主從複製相關--必須開啟log-bin
server-id = 19911216#主從複製必須，並且各伺服器具有唯一性
log_slave_updates#配置從伺服器的更新是否寫入二進位日誌，預設是不開啟的
replicate-ignore-db = mysql#主從複製預設忽略的資料庫，可用","分隔或使用多條記錄
#replicate-do-db=qrs,login#主從複製指定資料庫,","號隔開或使用多條記錄

[mysqldump]#資料庫全量備份
quick#強制mysqldump從伺服器一次一行地檢索表中的行
max_allowed_packet = 32M#可接收資料包大小

[isamchk]#在mysqld伺服器不使用的情況下修複表或在崩潰狀態下恢複表
key_buffer = 1024M
sort_buff_size =1024M
read_buffer = 16M
write_buffer = 16M

[myisamchk]#在mysqld伺服器不使用的情況下修複表或在崩潰狀態下恢複表
key_buffer = 1024M
sort_buff_size = 1024M
read_buffer = 16M
write_buffer = 16M

[mysqld_safe]#safe方式啟動資料庫，相比於mysqld,會在服務啟動後繼續監控服務狀態，死機時重啟
open-files-limit = 8192

Mysql常用配置及最佳化