MySQL5.7配置檔案詳細說明

# 用戶端設定
[client]
port = 3306
# 預設情況下，socket檔案應為/usr/local/mysql/mysql.socket,是以可以ln -s xx  /tmp/mysql.sock
socket = /tmp/mysql.sock 

# 服務端設定
[mysqld]

##########################################################################################################
# 基礎資訊
#Mysql服務的唯一編号 每個mysql服務Id需唯一
server-id = 1

#服務端口号 預設3306
port = 3306

# 啟動mysql服務程序的使用者
user = mysql

##########################################################################################################
# 安裝目錄相關
# mysql安裝根目錄
basedir = /usr/local/mysql-5.7.21

# mysql資料檔案所在位置
datadir = /usr/local/mysql-5.7.21/data

# 臨時目錄 比如load data infile會用到,一般都是使用/tmp
tmpdir  = /tmp

# 設定socke檔案位址
socket  = /tmp/mysql.sock


##########################################################################################################
# 事務隔離級别，預設為可重複讀（REPEATABLE-READ）。（此級别下可能參數很多間隙鎖，影響性能，但是修改又影響主從複制及災難恢複，建議還是修改代碼邏輯吧）
# 隔離級别可選項目：READ-UNCOMMITTED  READ-COMMITTED  REPEATABLE-READ  SERIALIZABLE
# transaction_isolation = READ-COMMITTED
transaction_isolation = REPEATABLE-READ

##########################################################################################################
# 資料庫引擎與字元集相關設定

# mysql 5.1 之後，預設引擎就是InnoDB了
default_storage_engine = InnoDB
# 記憶體臨時表預設引擎，預設InnoDB
default_tmp_storage_engine = InnoDB
# mysql 5.7新增特性，磁盤臨時表預設引擎，預設InnoDB
internal_tmp_disk_storage_engine = InnoDB

#資料庫預設字元集,主流字元集支援一些特殊表情符号（特殊表情符占用4個位元組）
character-set-server = utf8

#資料庫字元集對應一些排序等規則，注意要和character-set-server對應
collation-server = utf8_general_ci

# 設定client連接配接mysql時的字元集,防止亂碼
# init_connect='SET NAMES utf8'

# 是否對sql語句大小寫敏感，預設值為0，1表示不敏感
lower_case_table_names = 1


##########################################################################################################
# 資料庫連接配接相關設定
# 最大連接配接數，可設最大值16384，一般考慮根據同時線上人數設定一個比較綜合的數字，鑒于該數值增大并不太消耗系統資源，建議直接設10000
# 如果在通路時經常出現Too Many Connections的錯誤提示，則需要增大該參數值
max_connections = 10000

# 預設值100，最大錯誤連接配接數，如果有超出該參數值個數的中斷錯誤連接配接，則該主機将被禁止連接配接。如需對該主機進行解禁，執行：FLUSH HOST
# 考慮高并發場景下的容錯，建議加大。
max_connect_errors = 10000

# MySQL打開的檔案描述符限制，預設最小1024;
# 當open_files_limit沒有被配置的時候，比較max_connections*5和ulimit -n的值，哪個大用哪個，
# 當open_file_limit被配置的時候，比較open_files_limit和max_connections*5的值，哪個大用哪個。
open_files_limit = 65535

# 注意：仍然可能出現報錯資訊Can't create a new thread；此時觀察系統cat /proc/mysql程序号/limits，觀察程序ulimit限制情況
# 過小的話，考慮修改系統配置表，/etc/security/limits.conf和/etc/security/limits.d/90-nproc.conf

# MySQL預設的wait_timeout  值為8個小時, interactive_timeout參數需要同時配置才能生效
# MySQL連接配接閑置超過一定時間後(機關：秒，此處為1800秒)将會被強行關閉
interactive_timeout = 1800 
wait_timeout = 1800 

# 在MySQL暫時停止響應新請求之前的短時間内多少個請求可以被存在堆棧中 
# 官方建議back_log = 50 + (max_connections / 5),封頂數為900
back_log = 900

##########################################################################################################
# 資料庫資料交換設定
# 該參數限制伺服器端，接受的資料包大小，如果有BLOB子段，建議增大此值，避免寫入或者更新出錯。有BLOB子段，建議改為1024M
max_allowed_packet = 128M

##########################################################################################################
# 記憶體，cache與buffer設定


# 記憶體臨時表的最大值,預設16M，此處設定成128M
tmp_table_size = 64M
# 使用者建立的記憶體表的大小，預設16M，往往和tmp_table_size一起設定，限制使用者臨師表大小。
# 超限的話，MySQL就會自動地把它轉化為基于磁盤的MyISAM表，存儲在指定的tmpdir目錄下，增大IO壓力，建議記憶體大，增大該數值。
max_heap_table_size = 64M



# 表示這個mysql版本是否支援查詢緩存。ps：SHOW STATUS LIKE 'qcache%'，與緩存相關的狀态變量。
# have_query_cache

# 這個系統變量控制着查詢緩存工能的開啟的關閉，0時表示關閉，1時表示打開，2表示隻要select 中明确指定SQL_CACHE才緩存。
# 看業務場景決定是否使用緩存，不使用，下面就不用配置了。
query_cache_type = 0 

# 預設值1M，優點是查詢緩沖可以極大的提高伺服器速度, 如果你有大量的相同的查詢并且很少修改表。
# 缺點：在你表經常變化的情況下或者如果你的查詢原文每次都不同,查詢緩沖也許引起性能下降而不是性能提升。
query_cache_size = 64M 

# 隻有小于此設定值的結果才會被緩沖，保護查詢緩沖,防止一個極大的結果集将其他所有的查詢結果都覆寫。
query_cache_limit = 2M

# 每個被緩存的結果集要占用的最小記憶體,預設值4kb，一般不怎麼調整。
# 如果Qcache_free_blocks值過大，可能是query_cache_min_res_unit值過大，應該調小些
# query_cache_min_res_unit的估計值：(query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache
query_cache_min_res_unit = 4kb

# 在一個事務中binlog為了記錄SQL狀态所持有的cache大小
# 如果你經常使用大的,多聲明的事務,你可以增加此值來擷取更大的性能.
# 所有從事務來的狀态都将被緩沖在binlog緩沖中然後在送出後一次性寫入到binlog中
# 如果事務比此值大, 會使用磁盤上的臨時檔案來替代.
# 此緩沖在每個連接配接的事務第一次更新狀态時被建立
binlog_cache_size = 1M



#*** MyISAM 相關選項
# 指定索引緩沖區的大小, 為MYISAM資料表開啟供線程共享的索引緩存,對INNODB引擎無效。相當影響MyISAM的性能。
# 不要将其設定大于你可用記憶體的30%,因為一部分記憶體同樣被OS用來緩沖行資料
# 甚至在你并不使用MyISAM 表的情況下, 你也需要仍舊設定起 8-64M 記憶體由于它同樣會被内部臨時磁盤表使用.
# 預設值 8M，建議值：對于記憶體在4GB左右的伺服器該參數可設定為256M或384M。注意：該參數值設定的過大反而會是伺服器整體效率降低！
key_buffer_size = 64M

# 為每個掃描MyISAM的線程配置設定參數設定的記憶體大小緩沖區。 
# 預設值128kb，建議值：16G記憶體建議1M，4G：128kb或者256kb吧
# 注意，該緩沖區是每個連接配接獨占的，是以總緩沖區大小為 128kb*連接配接數；極端情況128kb*maxconnectiosns，會超級大，是以要考慮日常平均連接配接數。
# 一般不需要太關心該數值，稍微增大就可以了，
read_buffer_size = 262144 

# 支援任何存儲引擎
# MySQL的随機讀緩沖區大小，适當增大，可以提高性能。
# 預設值256kb；建議值：得參考連接配接數，16G記憶體，有人推薦8M
# 注意，該緩沖區是每個連接配接獨占的，是以總緩沖區大小為128kb*連接配接數；極端情況128kb*maxconnectiosns，會超級大，是以要考慮日常平均連接配接數。
read_rnd_buffer_size = 1M

# order by或group by時用到 
# 支援所有引擎，innodb和myisam有自己的innodb_sort_buffer_size和myisam_sort_buffer_size設定
# 預設值256kb；建議值：得參考連接配接數，16G記憶體，有人推薦8M.
# 注意，該緩沖區是每個連接配接獨占的，是以總緩沖區大小為 1M*連接配接數；極端情況1M*maxconnectiosns，會超級大。是以要考慮日常平均連接配接數。
sort_buffer_size = 1M

# 此緩沖被使用來優化全聯合(full JOINs 不帶索引的聯合)
# 類似的聯合在極大多數情況下有非常糟糕的性能表現,但是将此值設大能夠減輕性能影響.
# 通過 “Select_full_join” 狀态變量檢視全聯合的數量
# 注意，該緩沖區是每個連接配接獨占的，是以總緩沖區大小為 1M*連接配接數；極端情況1M*maxconnectiosns，會超級大。是以要考慮日常平均連接配接數。
# 預設值256kb;建議值：16G記憶體，設定8M.
join_buffer_size = 1M

# 緩存linux檔案描述符資訊，加快資料檔案打開速度
# 它影響myisam表的打開關閉，但是不影響innodb表的打開關閉。
# 預設值2000，建議值：根據狀态變量Opened_tables去設定
table_open_cache = 2000

# 緩存表定義的相關資訊，加快讀取表資訊速度
# 預設值1400，最大值2000，建議值：基本不改。
table_definition_cache = 1400
# 該參數是myssql 5.6後引入的，目的是提高并發。
# 預設值1，建議值：cpu核數，并且<=16
table_open_cache_instances = 2

# 當用戶端斷開之後，伺服器處理此客戶的線程将會緩存起來以響應下一個客戶而不是銷毀。可重用，減小了系統開銷。
# 預設值為9，建議值：兩種取值方式，方式一，根據實體記憶體，1G  —> 8；2G  —> 16； 3G  —> 32； >3G  —> 64；
# 方式二，根據show status like  'threads%'，檢視Threads_connected值。
thread_cache_size = 16

# 預設值256k,建議值：16/32G記憶體，512kb，其他一般不改變，如果報錯：Thread stack overrun，就增大看看,
# 注意，每個線程配置設定記憶體空間，是以總記憶體空間。。。你懂得。
thread_stack = 512k


##########################################################################################################
# 日志檔案相關設定，一般隻開啟三種日志，錯誤日志，慢查詢日志，二進制日志。普通查詢日志不開啟。

# 普通查詢日志，預設值off，不開啟
general_log = 0
# 普通查詢日志存放位址
general_log_file = /usr/local/mysql-5.7.21/log/mysql-general.log

# 全局動态變量，預設3，範圍：1～3
# 表示錯誤日志記錄的資訊，1：隻記錄error資訊；2：記錄error和warnings資訊；3：記錄error、warnings和普通的notes資訊。
log_error_verbosity = 2
# 錯誤日志檔案位址
log_error = /usr/local/mysql-5.7.21/log/mysql-error.log


# 開啟慢查詢
slow_query_log = 1

# 開啟慢查詢時間，此處為1秒，達到此值才記錄資料
long_query_time = 3

# 檢索行數達到此數值，才記錄慢查詢日志中
min_examined_row_limit = 100

# mysql 5.6.5新增，用來表示每分鐘允許記錄到slow log的且未使用索引的SQL語句次數，預設值為0，不限制。
log_throttle_queries_not_using_indexes = 0

# 慢查詢日志檔案位址
slow_query_log_file = /usr/local/mysql-5.7.21/log/mysql-slow.log

# 開啟記錄沒有使用索引查詢語句
log-queries-not-using-indexes = 1


# 開啟二進制日志
log_bin = /usr/local/mysql-5.7.21/log/mysql-bin.log
# mysql清除過期日志的時間，預設值0，不自動清理，而是使用滾動循環的方式。
expire_logs_days = 0
# 如果二進制日志寫入的内容超出給定值，日志就會發生滾動。你不能将該變量設定為大于1GB或小于4096位元組。 預設值是1GB。
max_binlog_size = 1000M
# binlog的格式也有三種：STATEMENT，ROW，MIXED。mysql 5.7.7後，預設值從 MIXED 改為 ROW
# 關于binlog日志格式問題，請查閱網絡資料
binlog_format = row
# 預設值N=1，使binlog在每N次binlog寫入後與硬碟同步，ps：1最慢
# sync_binlog = 1 

##########################################################################################################
# innodb選項

# 說明：該參數可以提升擴充性和刷髒頁性能。
# 預設值1，建議值：4-8；并且必須小于innodb_buffer_pool_instances
innodb_page_cleaners = 4

# 說明：一般8k和16k中選擇，8k的話，cpu消耗小些，selcet效率高一點，一般不用改
# 預設值：16k；建議值：不改，
innodb_page_size = 16384

# 說明：InnoDB使用一個緩沖池來儲存索引和原始資料, 不像MyISAM.這裡你設定越大,你在存取表裡面資料時所需要的磁盤I/O越少.
# 在一個獨立使用的資料庫伺服器上,你可以設定這個變量到伺服器實體記憶體大小的60%-80%
# 注意别設定的過大，會導緻system的swap空間被占用，導緻作業系統變慢，進而減低sql查詢的效率
# 預設值：128M，建議值：實體記憶體的60%-80%
innodb_buffer_pool_size = 512M

# 說明:隻有當設定 innodb_buffer_pool_size 值大于1G時才有意義，小于1G，instances預設為1，大于1G，instances預設為8
# 但是網絡上有評價，最佳性能，每個執行個體至少1G大小。
# 預設值：1或8，建議值：innodb_buffer_pool_size/innodb_buffer_pool_instances >= 1G
innodb_buffer_pool_instances = 1

# 說明：mysql 5.7 新特性，defines the chunk size for online InnoDB buffer pool resizing operations.
# 實際緩沖區大小必須為innodb_buffer_pool_chunk_size*innodb_buffer_pool_instances*倍數，取略大于innodb_buffer_pool_size
# 預設值128M，建議值：預設值就好，亂改反而容易出問題，它會影響實際buffer pool大小。
innodb_buffer_pool_chunk_size = 128M 

# 在啟動時把熱資料加載到記憶體。預設值為on，不修改
innodb_buffer_pool_load_at_startup = 1
# 在關閉時把熱資料dump到本地磁盤。預設值為on，不修改
innodb_buffer_pool_dump_at_shutdown = 1

# 說明：影響Innodb緩沖區的重新整理算法，建議從小到大配置，直到zero free pages；innodb_lru_scan_depth * innodb_buffer_pool_instances defines the amount of work performed by the page cleaner thread each second.
# 預設值1024，建議值: 未知
innodb_lru_scan_depth = 1024

# 說明：事務等待擷取資源等待的最長時間，機關為秒，看具體業務情況，一般預設值就好
# 預設值：50，建議值：看業務。
innodb_lock_wait_timeout = 60

# 說明：設定了Mysql背景任務（例如頁重新整理和merge dadta from buffer pool）每秒io操作的上限。
# 預設值：200，建議值：方法一，單盤sata設100，sas10，raid10設200，ssd設2000，fushion-io設50000；方法二，通過測試工具獲得磁盤io性能後，設定IOPS數值/2。
innodb_io_capacity = 2000
# 說明：該參數是所有緩沖區線程io操作的總上限。
# 預設值：innodb_io_capacity的兩倍。建議值：例如用iometer測試後的iops數值就好
innodb_io_capacity_max = 4000

# 說明：控制着innodb資料檔案及redo log的打開、刷寫模式，三種模式：fdatasync(預設)，O_DSYNC，O_DIRECT
# fdatasync：資料檔案，buffer pool->os buffer->磁盤；日志檔案，buffer pool->os buffer->磁盤；
# O_DSYNC：  資料檔案，buffer pool->os buffer->磁盤；日志檔案，buffer pool->磁盤；
# O_DIRECT： 資料檔案，buffer pool->磁盤；           日志檔案，buffer pool->os buffer->磁盤；
# 預設值為空，建議值：使用SAN或者raid，建議用O_DIRECT，不懂測試的話，預設生産上使用O_DIRECT
innodb_flush_method = O_DIRECT


# 說明：mysql5.7之後預設開啟，意思是，每張表一個獨立表空間。
# 預設值1，開啟
innodb_file_per_table = 1

# 說明：The path where InnoDB creates undo tablespaces.通常等于undo log檔案的存放目錄。
# 預設值./;自行設定
innodb_undo_directory = /usr/local/mysql-5.7.21/log
# 說明：The number of undo tablespaces used by InnoDB.等于undo log檔案數量。5.7.21後開始棄用
# 預設值為0，建議預設值就好，不用調整了。
innodb_undo_tablespaces = 0
# 說明：定義undo使用的復原段數量。5.7.19後棄用
# 預設值128，建議不動，以後棄用了。
innodb_undo_logs = 128
# 說明：5.7.5後開始使用，線上收縮undo log使用的空間。
# 預設值：關閉，建議值：開啟
innodb_undo_log_truncate = 1
# 說明：結合innodb_undo_log_truncate，實作undo空間收縮功能
# 預設值：1G，建議值，不改。
innodb_max_undo_log_size = 1G

# 說明：重作日志檔案的存放目錄
innodb_log_group_home_dir = /usr/local/mysql-5.7.21/log
# 說明：日志檔案的大小
# 預設值:48M,建議值：根據你系統的磁盤空間和日志增長情況調整大小
innodb_log_file_size = 128M
# 說明：日志組中的檔案數量，mysql以循環方式寫入日志
# 預設值2，建議值：根據你系統的磁盤空間和日志增長情況調整大小
innodb_log_files_in_group = 3
# 此參數确定些日志檔案所用的記憶體大小，以M為機關。緩沖區更大能提高性能，但意外的故障将會丢失資料。MySQL開發人員建議設定為1－8M之間
innodb_log_buffer_size = 16M



# 說明：可以控制log從系統buffer刷入磁盤檔案的重新整理頻率，增大可減輕系統負荷
# 預設值是1；建議值不改。系統性能一般夠用。
innodb_flush_log_at_timeout = 1
# 說明：參數可設為0，1，2；
# 參數0：表示每秒将log buffer内容重新整理到系統buffer中，再調用系統flush操作寫入磁盤檔案。
# 參數1：表示每次事物送出，将log buffer内容重新整理到系統buffer中，再調用系統flush操作寫入磁盤檔案。
# 參數2：表示每次事物送出，将log buffer内容重新整理到系統buffer中，隔1秒後再調用系統flush操作寫入磁盤檔案。
innodb_flush_log_at_trx_commit = 1


# 說明：限制Innodb能打開的表的資料，如果庫裡的表特别多的情況，請增加這個。
# 值預設是2000，建議值：參考資料庫表總數再進行調整，一般夠用不用調整。
innodb_open_files = 8192

# innodb處理io讀寫的背景并發線程數量，根據cpu核來确認，取值範圍：1-64
# 預設值：4，建議值：與邏輯cpu數量的一半保持一緻。
innodb_read_io_threads = 4
innodb_write_io_threads = 4
# 預設設定為 0,表示不限制并發數，這裡推薦設定為0，更好去發揮CPU多核處理能力，提高并發量
innodb_thread_concurrency = 0
# 預設值為4，建議不變。InnoDB中的清除操作是一類定期回收無用資料的操作。mysql 5.5之後，支援多線程清除操作。
innodb_purge_threads = 4 

# 說明：mysql緩沖區分為new blocks和old blocks；此參數表示old blocks占比；
# 預設值：37，建議值，一般不動
innodb_old_blocks_pct = 37
# 說明：新資料被載入緩沖池，進入old pages鍊區，當1秒後再次通路，則提升進入new pages鍊區。
# 預設值：1000
innodb_old_blocks_time=1000
# 說明：開啟異步io，可以提高并發性，預設開啟。
# 預設值為1，建議不動
innodb_use_native_aio = 1

# 說明：預設為空，使用data目錄，一般不改。
innodb_data_home_dir=/usr/local/mysql-5.7.21/data
# 說明：Defines the name, size, and attributes of InnoDB system tablespace data files.
# 預設值，不指定，預設為ibdata1:12M:autoextend
innodb_data_file_path = ibdata1:12M:autoextend


# 說明:設定了InnoDB存儲引擎用來存放資料字典資訊以及一些内部資料結構的記憶體空間大小,除非你的資料對象及其多，否則一般預設不改。
# innodb_additional_mem_pool_size = 16M


# 說明：The crash recovery mode。隻有緊急情況需要恢複資料的時候，才改為大于1-6之間數值，含義查下官網。
# 預設值為0；
#innodb_force_recovery = 0

##########################################################################################################
# 其他。。。。
# 參考http://www.kuqin.com/database/20120815/328905.html
# skip-external-locking

# 禁止MySQL對外部連接配接進行DNS解析，使用這一選項可以消除MySQL進行DNS解析的時間。
# 缺點：所有遠端主機連接配接授權都要使用IP位址方式，因為隻認得ip位址了。
# skip_name_resolve = 0

# 預設值為off,timestamp列會自動更新為目前時間，設定為on|1，timestamp列的值就要顯式更新
explicit_defaults_for_timestamp = 1




[mysqldump]
# quick選項強制 mysqldump 從伺服器查詢取得記錄直接輸出而不是取得所有記錄後将它們緩存到記憶體中
quick
max_allowed_packet = 16M

[mysql]
# mysql指令行工具不使用自動補全功能，建議還是改為
# no-auto-rehash
auto-rehash
socket = /tmp/mysql.sock