mysql基礎配置及優化

[client]

#該目錄下的内容常用來進行localhost登陸，一般不需要修改

port = 3306 # 端口号

socket = /var/lib/mysql/mysql.sock # 套接字檔案(localhost登陸會自動生成)

[mysql]

# 包含一些用戶端mysql指令行的配置

no-auto-rehash # 預設不自動補全 auto-rehash自動補全

[mysqld]

lower_case_table_names=1 #取消區分大小寫

sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

【sq_mode解釋：https://blog.csdn.net/lhn_hpu/article/details/52200792】

# 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 #資料庫操作二進制記錄，資料庫備份，複制所需

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