MySQL配置檔案

MySQL的配置檔案需要根據版本及實際情況進行相應配置，本人使用的是Percona版本，主要是用到執行緒池等功能，所以選擇Percona版本，配置檔案內容如下，大部分引數資訊我參考了相關資料做了說明，如有不當之處歡迎大家來指正。

 [mysqld]

######################################################################################################################3
#file config
######################################################################################################################3
pid-file=/data/mysql/mysql3307/tmp/mysqld.pid
basedir=/usr/local/mysql
datadir=/data/mysql/mysql3307/data
socket=/data/mysql/mysql3307/tmp/mysql.sock
user=mysql
port=3306

#Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
back_log = 500     
######################################################################################################################
#back_log值指出在mysql暫時停止回答新請求之前的短時間內多少個請求可以被存在堆疊中。也就是說，如果MySql的連線數
#達到max_connections時，新來的請求將會被存在堆疊中，以等待某一連線釋放資源，該堆疊的數量即back_log，如果等待連線的數量
#超過back_log，將不被授予連線資源。將會報：unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL
#的待連執行緒式時.back_log值不能超過TCP/IP連線的偵聽佇列的大小。若超過則無效，檢視當前系統的TCP/IP連線的偵聽佇列的大小
#命令：cat /proc/sys/net/ipv4/tcp_max_syn_backlog，目前系統為1024。對於Linux系統推薦設定為大於512的整數
#######################################################################################################################
server-id=394406
skip-name-resolve=1  # 跳過域名解析
character_set_server=utf8
max_connections = 1000
max_connect_errors = 100 ##max_connect_errors是一個MySQL中與安全有關的計數器值，它負責阻止過多嘗試失敗的客戶端以防止暴力-                                          破-解-密碼的情況。max_connect_errors的值與效能並無太大關係
interactive_timeout=600 #伺服器關閉互動式連線前等待活動的秒數,同時設定interactive_timeout和wait_timeout才會生效
wait_timeout=600         # 伺服器關閉非互動連線之前等待活動的秒數。
                                      #長時間的執行批次的MYSQL語句。最常見的就是採集或者新舊資料轉化
event_scheduler=1 #開啟事件排程器,0 off 1 on
explicit_defaults_for_timestamp #顯示指定預設值為timestamp型別的欄位 
log_timestamps=SYSTEM ##5.7.2新增引數log_timestamps 引數預設使用 UTC 時區，這樣會使得日誌中記錄的時間比中國這邊的慢了 8                                                 個    小時，導致檢視日誌不方便。修改為 SYSTEM 就能解決問題
sql_mode='' #sql_mode是個很容易被忽視的變數，預設值是空值，在這種設定下是可以允許一些非法操作的，比如允許一些非法資料的插入。在生產環境必須將這個值設定為嚴格模式，所以開發、測試環境的資料庫也必須要設定，這樣在開發測試階段就可以發現問題
######################################################################################################################
#如果使用mysql，為了繼續保留大家使用oracle的習慣，可以對mysql的sql_mode設定如下：
#在my.cnf新增如下配置
#[mysqld]
#sql_mode='ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,
#ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,PIPES_AS_CONCAT,ANSI_QUOTES'
######################################################################################################################
default_storage_engine=innodb

slow_query_log=1;#透過使用--slow_query_log[={0|1}]選項來啟用慢查詢日誌。所有執行時間超過long_query_time秒(預設值為10s)的

                             SQL語句都會被記錄到慢查詢日誌

long_query_time = 600 #slow_query_log 這句是開啟記錄慢查詢功能，slow_query_log=0關閉；slow_query_log=1開啟

                            （這個1可以不寫）
                       #long_query_time = 1 這句是記錄超過1秒的SQL執行語句
slow_query_log_file='/data/mysql/mysql3307/data/DBDWARE01-slow.log'
log_error_verbosity=1 #全域性動態變數，預設3，範圍：1～3。表示錯誤日誌記錄的資訊，1：只記錄error資訊；2：記錄error和warnings資訊；3：記錄error、warnings和普通的notes資訊。
#slow_launch_time=2 #slow_launch_time的設定跟慢查詢日誌的查詢閥值設定不同，表示了thread create的一個閥值，如果thread create的時間超過了這個值，這變數slow_launch_time的值加1

myisam_repair_threads=2 # 如果該值大於1，在Repair by sorting過程中並行建立MyISAM表索引(每個索引在自己的執行緒內)  
myisam_recover_options=FORCE #myisam_recover_options=force，那麼即使此時key cache不存在了也會進行強制修復，此時做的就是對比資料檔案和索引檔案，然後刪除資料檔案中多餘的行，因此這樣可能會丟資料
                 #配置了引數myisam_recover_options=default，這個配置表示每次訪問MyISAM表之前都會先檢測表是否需要修復，如果需要則自動進行，這也就是前面看到資訊last (automatic?) repair failed。而修復失敗是因為這個引數帶來的修復行為預設是從key cache裡面找需要修復的資料，而我當時是shutdown例項，rsync到新環境中起例項，此時已沒有當時的現場(key cache環境)，加上default不會強制進行修復（強制修復表如果索引檔案和資料檔案資料不一致則自動進行刪除或者增加行），（如果是myisam_recover_options=force，那麼即使此時key cache不存在了也會進行強制修復，此時做的就是對比資料檔案和索引檔案，然後刪除資料檔案中多餘的行，因此這樣可能會丟資料）

######################################################################################################################3
#memory config
######################################################################################################################3
table_open_cache = 10000 #MYSQL預設的table_open_cache為64，這個數值是偏小的，如果max_connections較大，則容易引起效能問題。
             #表現：資料庫查詢效率慢，show processlist 發現比較多的查詢正在opening table。
max_allowed_packet = 16M #用來控制其通訊緩衝區的最大長度，解決執行一個SQL，但SQL語句過大或者語句中含有BLOB或者longblob欄位。比如，圖片資料的處理
max_heap_table_size = 64M #這個變數定義了使用者可以建立的記憶體表的大小，這個值用來計算記憶體表的最大行數值
tmp_table_size = 1073741824    #copy to tmp talbe 語句產生的原因是查詢需要Order By 或者Group By等需要用到結果集時，
                                #引數中設定的臨時表的大小小於結果集的大小時，就會將該表放在磁碟上，這個時候在硬碟上的IO
                #要比內銷差很多。所耗費的時間也多很多。另外Mysql的另外一個引數max_heap_table_size
                #比tmp_table_size小時，則系統會把max_heap_table_size的值作為最大的記憶體臨時表的上限，
                #大於這個時，改寫硬碟
sort_buffer_size = 50M #你可以考慮增加sort_buffer_size 來加速ORDER BY 或者GROUP BY 操作,不能透過查詢或者索引最佳化的。
                       #在任何情況下, 設定它大於需要的全域性會減慢很多的查詢。最後是作為一個會話設定來增加,
                       #只有對需要大量的記憶體的會話, 在Linux上,有閥值為256KB 和2MB ,大的值可能顯著的減慢記憶體分配
join_buffer_size = 50M #如果應用中，很少出現join語句，則可以不用太在乎join_buffer_size引數的設定大小。
                       #如果join語句不是很少的話，個人建議可以適當增大join_buffer_size到1MB左右，如果記憶體充足可以設定為2MB
thread_cache_size = 8 #根據實體記憶體設定規則如下1G  —> 8, 2G  —> 16 ,3G  —> 32 ,>3G  —> 64

# thread_concurrency = 8
ft_min_word_len = 2 #中文分詞，ft_min_word_len設定為2，呼叫'repair table your_table quick',修復索引。相比方案2，此方案較節省空間。對於主要使用中文的系統而言，此方案更佳
memlock  #伺服器是否鎖定在記憶體中
default-storage-engine = MyISAM
thread_stack = 192K    #每個連線執行緒被建立時,MySQL給它分配的記憶體大小.當MySQL建立一個新的連線執行緒時,需要給它分配一定大小的
            #記憶體堆疊空間，以便存放客戶端的請求的Query及自身的各種狀態和處理資訊。
transaction_isolation = REPEATABLE-READ ##事務隔離級別，全域性預設是REPEATABLE-READ,其實MySQL本來預設也是這個級別

key_buffer_size = 1024M    #這個引數是用來設定索引塊(index blocks)快取的大小,它被所有執行緒共享,嚴格說是它決定了資料庫索引處理的速度
                        #尤其是索引讀的速度。那我們怎麼才能知道key_buffer_size的設定是否合理呢，一般可以
            #檢查狀態值Key_read_requests和Key_reads，比例key_reads / key_read_requests應該儘可能的低，
            #比如1:100，1:1000 ，1:10000。其值可以用以i下命令查得：mysql> show status like 'key_read%';
read_buffer_size = 2M    # MySQL讀入緩衝區大小。對錶進行順序掃描的請求將分配一個讀入緩衝區，MySQL會為它分配一段記憶體緩衝區。read_buffer_size變數控制這一緩衝區的大小。
                        # 如果對錶的順序掃描請求非常頻繁，並且你認為頻繁掃描進行得太慢，可以透過增加該變數值以及記憶體緩衝區大小提高其效能
read_rnd_buffer_size = 16M #MySql的隨機讀（查詢操作）緩衝區大小.當按任意順序讀取行時(例如,按照排序順序),將分配一個隨機讀快取區。
                           #進行排序查詢時，MySql會首先掃描一遍該緩衝，以避免磁碟搜尋，提高查詢速度，如果需要排序大量資料，
               #可適當調高該值。但MySql會為每個客戶連線發放該緩衝空間，所以應儘量適當設定該值，以避免記憶體開銷過大。
bulk_insert_buffer_size = 4294967296 #批次插入資料快取大小，可以有效提高插入效率，預設為8M
myisam_sort_buffer_size = 128M  #MyISAM表發生變化時重新排序所需的緩衝
myisam_max_sort_file_size = 10G    # MySQL重建索引時所允許的最大臨時檔案的大小 (當 REPAIR, ALTER TABLE 或者 LOAD DATA INFILE).
                                # 如果檔案大小比此值更大,索引會透過鍵值緩衝建立(更慢)
myisam_repair_threads = 1    # 如果一個表擁有超過一個索引, MyISAM 可以透過並行排序使用超過一個執行緒去修復他們.
                                # 這對於擁有多個CPU以及大量記憶體情況的使用者,是一個很好的選擇
#myisam_recover

######################################################################################################################3
#innodb config
######################################################################################################################3
innodb_buffer_pool_size = 40G    #這對Innodb表來說非常重要。Innodb相比MyISAM表對緩衝更為敏感。MyISAM可以在預設的 key_buffer_size 設定下執行的可以，
  #然而Innodb在預設的 innodb_buffer_pool_size 設定下卻跟蝸牛似的。由於Innodb把資料和索引都快取
                #起來，無需留給作業系統太多的記憶體，因此如果只需要用Innodb的話則可以設定它高達 70-80% 的可用記憶體。
                #一些應用於 key_buffer 的規則有 — 如果你的資料量不大，並且不會暴增，
                #那麼無需把 innodb_buffer_pool_size 設定的太大了
innodb_data_file_path = ibdata1:10M:autoextend    #資料檔案配置,共享表空間地址及初始化大小，自動擴充套件屬性
innodb_read_io_threads=8
innodb_write_io_threads=8        # innodb_read_io_threads innodb_write_io_threads 多核cpu可以透過這兩個引數更有效的利用cpu效能
innodb_thread_concurrency = 16   # 調節  併發執行緒數的限制值
innodb_flush_log_at_trx_commit = 2  
## innodb_flush_log_at_trx_commit ##
# 0：log buffer將每秒一次地寫入log file中，並且log file的flush(刷到磁碟)操作同時進行。該模式下在事務提交的時候，不會主動觸發寫入磁碟的操作。
#1：每次事務提交時MySQL都會把log buffer的資料寫入log file，並且flush(刷到磁碟)中去，該模式為系統預設。
#2：每次事務提交時MySQL都會把log buffer的資料寫入log file，但是flush(刷到磁碟)操作並不會同時進行。該模式下，MySQL會每秒執行一次 flush(刷到磁碟)操作
innodb_log_buffer_size = 8M   # InnoDB的寫操作，將資料寫入到記憶體中的日誌快取中，由於InnoDB在事務提交前，並不將改變的日誌寫入到磁碟中，因此在大事務中，可以減輕磁碟I/O的壓力。通常情況下，如果不是寫入大量的超大二進位制資料（a lot of huge blobs），4MB-8MB已經足夠了
innodb_max_dirty_pages_pct = 75 # 關於innodb_max_dirty_pages_pct值的爭議，如果值過大，記憶體也很大或者伺服器壓力很大，那麼效率很降低，如果設定的值過小，那麼硬碟的壓力會增加，建議是在75-80.並且innodb plugin引進了innodb_adaptive_flushng(自適應的重新整理)，該值影響每秒重新整理髒頁的數量
innodb_flush_method = O_DIRECT  
##innodb_flush_method這個引數控制著innodb資料檔案及redo log的開啟、刷寫模式，對於這個引數，檔案上是這樣描述的：
#有三個值：fdatasync(預設)，O_DSYNC，O_DIRECT
#預設是fdatasync，呼叫fsync()去刷資料檔案與redo log的buffer
#為O_DSYNC時，innodb會使用O_SYNC方式開啟和刷寫redo log,使用fsync()刷寫資料檔案
#為O_DIRECT時，innodb使用O_DIRECT開啟資料檔案，使用fsync()刷寫資料檔案跟redo log

innodb_lock_wait_timeout = 120 #  innodb_lock_wait_timeout指的是事務等待獲取資源等待的最長時間，超過這個時間還未分配到資源則會返回應用失敗；引數的時間單位是秒，最小可設定為1s(此時需要考慮應用端的頻繁異常處理會消耗效能，不能設定過小)，最大可設定1073741824秒以上
innodb_buffer_pool_instances=8  #當 innodb_buffer_pool_size 設定的 大於 1GB 以後  那麼此引數設定就尤為重要了,   MySQL 5.6.6開始 此引數預設為 8,  主要目的是為瞭解決 互斥鎖, 每個緩衝池管理其自己的空閒列表,提高查詢併發性, 對於互斥鎖 可以自行補腦吧,如果innodb_buffer_pool_size大於1.3GB，則innodb_buffer_pool_instances的預設值為innodb_buffer_pool_size/ 128MB   即大致為 10 左右.每個例項 具有獨立的快取區塊
innodb_page_cleaners=8  # 為了提升擴充套件性和刷髒效率，在5.7.4版本里引入了多個page cleaner執行緒。從而達到並行刷髒的效果。
                        # 在該版本中，Page cleaner並未和buffer pool繫結，其模型為一個協調執行緒 + 多個工作執行緒，協調執行緒本身也是工作執行緒。因此如果innodb_page_cleaners設定為8，那麼就是一個協調執行緒，加7個工作執行緒
#innodb_force_recovery=1

######################################################################################################################3
#log file config
######################################################################################################################3
expire_logs_days=30
innodb_log_files_in_group=5
innodb_log_file_size=500m
binlog_format=row
log_bin=on
log_bin=/data/mysql/mysql3307/logs/mysql-bin
log_bin_index=/data/mysql/mysql3307/logs/mysql-bin.index

######################################################################################################################3
#replication config
######################################################################################################################3
#slave_parallel_type=LOGICAL_CLOCK
slave-parallel-workers=0   # MySQL 5.6版本也支援所謂的並行複製，但是其並行只是基於schema的，也就是基於庫的。如果使用者的MySQL資料庫例項中存在多個schema，對於從機複製的速度的確可以有比較大的幫助
master_info_repository=table # 從機把主的資訊存在主資訊倉庫裡。主資訊庫可以是檔案也可以上表，具體由—master-info-repository引數值決定。—master-info-repository＝file時 會生成master.info 和 relay-log.info2個檔案，如果—master-info-repository＝table，資訊就會存在mysql.master_slave_info表中。不管是設定的哪種值，都不要移動或者編輯相關的檔案和表。想要更改配置透過再次執行change master to …語句，變更會自動儲存到相關的檔案和表。這個配置對應的表或者檔案裡的內容會覆蓋某些命令列或者my.cnf中的配置
relay_log_info_repository=table # 建議將其修改為TABLE，因為1.relay.info明文儲存不安全，把relay.info中的資訊記錄在table中相對安全。
                                # 2.可以避免relay.info更新不及時，SLAVE 重啟後導致的主從複製出錯
relay_log_recovery=on # 當slave從庫當機後，假如relay-log損壞了，導致一部分中繼日誌沒有處理，則自動放棄所有未執行的relay-log，並且重新從master上獲取日誌，這樣就保證了relay-log的完整性。預設情況下該功能是關閉的，將relay_log_recovery的值設定為 1時，可在slave從庫上開啟該功能，建議開啟
relay_log_purge=on # 有時候，我們希望將 MySQL 的 relay log 多保留一段時間，比如用於高可用切換後的資料補齊，於是就會設定 relay_log_purge=0，禁止 SQL 執行緒在執行完一個 relay log 後自動將其刪除，但會有風險
##### 風險
## 首先，為了讓從庫是 crash safe 的，必須設定 relay_log_recovery=1，這個選項的作用是，在 MySQL 崩潰或人工重啟後，由於 IO 執行緒無法保證記錄的從主庫讀取的 binlog 位置的正確性，因此，就不管 master_info 中記錄的位置，而是根據  relay_log_info 中記錄的已執行的 binlog 位置從主庫下載，並讓 SQL 執行緒也從這個位置開始執行。MySQL 啟動時，相當於執行了 flush logs ，會新開一個 relay log 檔案，新的 relay log 會記錄在新的檔案中。如果預設情況 relay_log_purge=1 時，SQL 執行緒就會自動將之前的 relay log 全部刪除。而當 relay_log_purge=0 時，舊的 relay log 則會被保留。雖然這並不會影響從庫複製本身，但還是會有地--雷 ：
##    由於崩潰或停止 MySQL 時，SQL 執行緒可能沒有執行完全部的 relay log，最後一個 relay log 中的一部分資料會被重新下載到新的檔案中。也就是說，這部分資料重複了兩次。
##    如果 SQL 跟得很緊，則可能在 IO 執行緒寫入 relay log ，但還沒有將同步到磁碟時，就已經讀取執行了。這時，就會造成新的檔案和舊的檔案中少了一段資料。
##   如果我們讀取 relay log 來獲取資料，必須注意這一點，否則就會造成資料不一致。而保留 relay log 的目的也在於此。因此，在處理 relay log 時必須格外小心，透過其中 binlog 頭資訊來確保正確性。
#####
slave_preserve_commit_order=1 # 對於多執行緒slaves，來保障事務在slave上執行的順序與relay log中的順序嚴格一致，只有當“slave_parallel_workers”開啟時有效；此時“log_bin”、“log_slave_updates”必須開啟，而且“slave_parallel_type”值必須為“LOGICAL_CLOCK”（預設值為DATABASE）。即當多執行緒開啟時，且根據relay log中事務的邏輯順序執行statements，是否需要嚴格保持順序，預設值為0表示併發執行忽略順序

slave_skip_errors=1032,1062 ## 如有需要再新增，指跳過從庫部分錯誤，1032代表當delete資料是從庫不存在的報錯資訊 1062為重複

[mysqld_safe]
log-error=/data/mysql/mysql3307/logs/mysqld.log
pid-file=/data/mysql/mysql3307/tmp/mysqld.pid

[client]
socket = /data/mysql/mysql3307/tmp/mysql.sock

[mysql]
prompt=\\u@\\d \\r:\\m:\\s>

 

 

################################### 華麗的分割線 ############################################

下面奉上姜老師推薦的配置

[client]
user=david
password=88888888
[mysqld]########basic settings########server-id = 11 
port = 3306
user = mysql
bind_address = 10.166.224.32   #根據實際情況修改autocommit = 0   #5.6.X安裝時，需要註釋掉，安裝完成後再開啟character_set_server=utf8mb4
skip_name_resolve = 1
max_connections = 800
max_connect_errors = 1000
datadir = /data/mysql_data      #根據實際情況修改,建議和程式分離存放transaction_isolation = READ-COMMITTED
explicit_defaults_for_timestamp = 1
join_buffer_size = 134217728
tmp_table_size = 67108864
tmpdir = /tmp
max_allowed_packet = 16777216
sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"interactive_timeout = 1800
wait_timeout = 1800
read_buffer_size = 16777216
read_rnd_buffer_size = 33554432
sort_buffer_size = 33554432########log settings########log_error = error.log
slow_query_log = 1
slow_query_log_file = slow.log
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1
log_throttle_queries_not_using_indexes = 10
expire_logs_days = 90
long_query_time = 2
min_examined_row_limit = 100########replication settings########master_info_repository = TABLE
relay_log_info_repository = TABLE
log_bin = bin.log
sync_binlog = 1
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates
binlog_format = row 
relay_log = relay.log
relay_log_recovery = 1
binlog_gtid_simple_recovery = 1
slave_skip_errors = ddl_exist_errors########innodb settings########innodb_page_size = 8192
innodb_buffer_pool_size = 6G    #根據實際情況修改innodb_buffer_pool_instances = 8
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_lru_scan_depth = 2000
innodb_lock_wait_timeout = 5
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_flush_method = O_DIRECT
innodb_file_format = Barracuda
innodb_file_format_max = Barracuda
innodb_log_group_home_dir = /redolog/  #根據實際情況修改innodb_undo_directory = /undolog/      #根據實際情況修改innodb_undo_logs = 128
innodb_undo_tablespaces = 3
innodb_flush_neighbors = 1
innodb_log_file_size = 4G               #根據實際情況修改innodb_log_buffer_size = 16777216
innodb_purge_threads = 4
innodb_large_prefix = 1
innodb_thread_concurrency = 64
innodb_print_all_deadlocks = 1
innodb_strict_mode = 1
innodb_sort_buffer_size = 67108864 
########semi sync replication settings########plugin_dir=/usr/local/mysql/lib/plugin      #根據實際情況修改plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"loose_rpl_semi_sync_master_enabled = 1
loose_rpl_semi_sync_slave_enabled = 1
loose_rpl_semi_sync_master_timeout = 5000
[mysqld-5.7]
innodb_buffer_pool_dump_pct = 40
innodb_page_cleaners = 4
innodb_undo_log_truncate = 1
innodb_max_undo_log_size = 2G
innodb_purge_rseg_truncate_frequency = 128
binlog_gtid_simple_recovery=1
log_timestamps=system
transaction_write_set_extraction=MURMUR32
show_compatibility_56=on

source:https://www.cnblogs.com/gjc592/p/9199361.html