Mysql 5.7憂化配置詳解

一、客戶端引數（mysql）

no-auto-rehash      -- 互動式終端命令自動補全

prompt= "\\u@\\h : \\d\\r:\\m:\\s>"      \u是登陸使用者名稱，\h是系統主機名，\d是當前資料庫，\r是空格。

default_character_set=utf8     -- 設定客戶端的字符集

user=root      -- 設定登陸使用者名稱，如果指定本地登陸就不用輸入使用者名稱了。

password=密碼     -- 設定登陸密碼，如果指定本地登陸就不用輸入密碼。（不安全）

二、服務端引數（mysqld）

1.innodb憂化引數

server_id=10          --server id值。

default_storage_engine=InnoDB             --預設儲存引擎

innodb_buffer_pool_size=系統記憶體X75%

innodb_buffer_pool_instances=1     --一般是CPU的個數，總的快取也除以這個值就是每個快取池的大小。（預設是1）

innodb_buffer_pool_dump_at_shutdown=ON     --資料庫關閉時備份快取池的資料。（預設ON）

innodb_buffer_pool_load_at_startup=ON      --資料庫啟動時載入快取池的資料。（預設ON）

innodb_buffer_pool_dump_pct=25                --將快取池前25%的資料備份到磁碟。（預設是25）

innodb_lru_scan_depth=1024                        --如果快取池有1024個髒頁立即重新整理到磁碟。（預設是1024）

innodb_flush_method=O_DIRECT                  --控制innodb資料檔案，redo檔案開啟及刷寫模式。（建議值是 O_DIRECT）

innodb_flush_neighbors=0                 --0表示不重新整理鄰近頁，1表示不重新整理鄰近頁。（預設值）

innodb_use_native_aio=ON                --開啟非同步IO功能

innodb_io_capacity =200                       --每秒寫入多少iops。預設是200，一般機械盤可以是2000，ssd可以是4000。

innodb_io_capacity_max=200                --最大寫入多少iops。

innodb_doublewrite=ON                     --開啟兩次寫功能。

innodb_flush_log_at_trx_commit=1    --事物提交redo log重新整理到磁碟。0  日誌重新整理到快取，1   日誌直接重新整理到磁碟，2  日誌重新整理到作業系統快取。

innodb_support_xa=ON                 --開啟分散式事物。

innodb_file_per_table=ON             --每張表單獨的表空間。

innodb_numa_interleave=OFF       --開啟numa功能。

2.sql最佳化引數

show global status like '%sort%';        --可以在資料庫狀態值檢視當前設定的值是否合理。

show global status  like '%tmp%'; 

innodb_sort_buffer_size=1048576     --sql排序快取大小，預設是1M。（order by語句使用）

tmp_table_size=16777216     --臨時表大小。 （group by開啟的表）

table_open_cache=2000        --開啟臨時表快取。

max_tmp_tables=32               --開啟臨時表個數

join_buffer_size=262144        --sql連線查詢快取

query_cache_size=1048576   --查詢快取大小

read_buffer_size=131072      --讀快取區，將表順序掃描讀到一個快取區。

3.重做日誌最佳化

innodb_log_file_size=50331648     -- 預設值是50M。一般可以設定為4G以上，如果值太小會導致頻繁日誌檔案替換影響效能。

innodb_log_files_in_group=2         --重做日誌個數。

innodb_log_buffer_size=16777216    --重做日誌快取，預設16M。

innodb_log_group_home_dir=./        --重做日誌路徑。

innodb_log_compressed_pages=ON    --重做日誌開啟壓縮。

4.二進位制日誌最佳化

log_bin=OFF          -- 預設值是OFF（關閉），是否寫入binlog檔案。

binlog_format=ROW     -- 二進位制複製的格式，預設值是statement（語句複製）。statement是記錄sql語句，從庫直接回放sql就可以。row是記錄修改的資料，從庫直接同步資料就可以。mixed混合複製預設是語句複製，比如random，now動態資料（函式）採用行復制。

binlog_rows_query_log_events=OFF    --預設是OFF，在binlog中記錄sql語句。建議值是ON。

log_bin_basename=路徑        --二進位制檔案存放路徑。

log_bin_index=路徑                 --二進位制日誌檔案產生的索引檔案，內容是binlog的檔名。

max_binlog_cache_size=1024    --預設值是1M。寫入二進位制檔案時快取，參考值檢視狀態資訊（Binlog_cache_disk_use，Binlog_cache_use）

max_binlog_size=1048576     --預設大小是1G。可以適當調大。

expire_logs_days=0                --預設值是0。表示不自動刪除，生產環境需要設定其值。

sync_binlog=1                         --事物提交立即重新整理到binlog檔案。

log_slave_updates=ON           --預設值是OFF，在從庫也寫binlog日誌。（用於從庫）

read_only=OFF                 --預設值是OFF，從庫是隻讀不能寫入，建議值是OFF。（用於從庫）

relay_log_recovery=ON    --預設值是OFF，IO執行緒高可用，建議值是ON。（用於從庫）

relay_log_info_repository=FILE    --預設值是FILE，sql執行緒高可用，建議值是TABLE。（用於從庫）

slave_parallel_type=DATABASE    --預設是基於資料庫並行複製，建議值是logical_clock基於事物組並行複製。

slave_parallel_workers=0     --預設值是0，建議值是CPU的個數，有多少個sql執行緒回放sql語句。

thread_handling=one-thread-per-connection    --建議值是pool-of-threads

thread_cache_size=9           --執行緒池快取大小。

gtid_mode=on      --預設是關閉，開啟GTID事物全域性id。一般用於主從複製

enforce-gtid-consistency=1

innodb_purge_threads=4      --purge執行緒個數，預設值是4。purge執行緒主要是刪除記錄和undo log資料。

innodb_read_io_threads=4   --讀執行緒個數。

innodb_write_io_threads=4   --寫入執行緒個數。

5.錯誤和通用日誌

log_error=路徑            --記錄資料庫啟動關閉和執行的狀態。

log_output=FILE         --日誌輸出格式。

log_queries_not_using_indexes=OFF    --沒有使用索引的SQL記錄到日誌。

slow_query_log=OFF     --慢查詢日誌，記錄比較慢的SQL語句。

slow_query_log_file=路徑         --慢查詢日誌記錄路徑。

long_query_time=10.000000    --慢查詢日誌記錄時間。

general_log=OFF             --通用日誌。

general_log_file=路徑      --通用日誌記錄路徑。

6.其他引數最佳化

open_files_limit=5000         --檔案開啟數。

connect_timeout=10           --客戶端連線超時。

max_connections=151        --最大連線數

innodb_encrypt_online_alter_logs=OFF      -- 開啟DDL線上定義。

innodb_online_alter_log_max_size=134217728     --此引數預設為關閉，大小為128M。DDL線上定義所產生的日誌大小。

innodb_change_buffering=all     --提高輔助索引的插入效能。

innodb_adaptive_hash_index=ON     --開啟自適應雜湊索引。