MySQL5.7應當注意的參數

簡介： 本篇文章主要介紹 MySQL 初始化應當注意的參數，對于不同環境間實例遷移，這些參數同樣應當注意。

注： 本文介紹的參數都是在配置文件 [mysqld] 部分。

server_id 和 log_bin ，binlog_format

這幾個系統變量通常成對出現，當我們想指定log_bin 選項時，必須也要指定server_id 。

log_bin 是全局變量 不可動態修改 默認為OFF。當我們需要開啟binlog時，可將該參數設為binlog名字或絕對路徑加名字。

binlog_format 指定binlog格式 5.7.7版本以上默認是ROW模式

建議設置：

#server_id 各個實例建議設置不同 log_bin不指定路徑時默認在數據文件目錄
server_id = 213306
log_bin = mysqlbin
binlog_format = row
或者
server_id = 213306
log_bin = /data/mysql/logs/mysqlbin
binlog_format = row

sql_mode

該參數控制 MySQL server 在不同的SQL模式下運行，對于客戶端發送的請求不同的模式會有不同的應答。

sql_mode 參數分為全局和會話級別 可以動態修改

#sql_mode 默認為：
sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
#關于修改和各個模式的作用 可參考官方文檔：
 https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

該參數建議去掉ONLY_FULL_GROUP_BY，具體采用嚴格或非嚴格模式可以根據需求來修改。注意該參數在不同實例要保持一致，不然可能會出現一條sql在此環境下可以執行 在另外一個環境不能執行的情況。

max_connections

該參數指定 MySQL 的最大連接數，是全局變量 可動態修改 默認為151。建議設置大些 防止出現連接數用滿的錯誤。

character_set_server

該參數指定 MySQL server端字符集，分為全局和會話級別 可以動態修改 5.7版本默認值為latin1。

建議設置該參數為utf8或utf8mb4，不同實例間保持一致 特別是主從實例。

lower_case_table_names

該參數控制 MySQL 是否大小寫敏感，主要影響庫名及表名。

Linux下該參數默認為0 即對大小寫敏感，是全局變量 不可動態修改，建議設置為1。

transaction_isolation

該參數指定 MySQL server 采用哪種事務隔離級別，默認是REPEATABLE-READ 可動態修改。

采用哪種隔離級別要根據應用要求來選擇，一般可改為READ-COMMITTED，不同實例間建議保持一致。

innodb_buffer_pool_size

該參數控制InnoDB緩沖池大小，默認值為134217728字節（128MB）5.7.5版本以上可動態修改。

緩沖池是緩存數據和索引的地方，盡可能大的緩存池將確保使用內存而不是磁盤來進行大多數讀取操作。典型值為5-6GB（8GB RAM），20-25GB（32GB RAM），100-120GB（128GB RAM），在一個獨立使用的數據庫服務器上,你可以設置這個變量到服務器物理內存大小的80%。

innodb_log_file_size

該參數定義redo日志組中每個日志文件的大小（以字節為單位）,是全局變量 不可動態修改 默認為48M。

當MySQL server 讀寫比較頻繁時，建議增大該參數 可與 innodb_log_files_in_group 參數配合使用。

innodb_io_capacity 和 innodb_io_capacity_max

innodb_io_capacity參數設置InnoDB后臺任務每秒執行的IO操作數的上限，默認值為200 可動態修改。

此參數應設置為系統每秒大約可執行的IO操作數 即系統的IOPS。該值取決于你的系統配置。

當MySQL server 寫操作特多 刷新臟頁落后時 ， innodb_io_capacity_max 參數是后臺任務定義每秒執行的IO操作數的上限，innodb_io_capacity_max通常設置為innodb_io_capacity的2倍。

如果MySQL服務器是SSD高速磁盤，我們可以設置 innodb_io_capacity_max= 6000 和 innodb_io_capacity = 3000 （最大值的50％）。當然 運行sysbench或任何其他基準測試工具來對磁盤吞吐量進行基準測試是個好主意。

其他相關參數

除了上面列舉的參數 還有些其他參數需要注意 ，篇幅關系 我將其匯總如下：

#禁用所有DNS解析 建議開啟 唯一的限制是GRANT語句必須僅使用IP地址
skip_name_resolve = 1
#MySQL server關閉空閑連接等待的秒數 默認為28800
interactive_timeout = ？  
wait_timeout = ？
#日志記錄時間與系統保持一致
log_timestamps = SYSTEM
#一些日志相關參數
log_error = error.log
slow_query_log = 1
slow_query_log_file = slow.log
long_query_time = 3
#binlog日志刪除策略 單位為天 默認為0 及不自動清理
expire_logs_days = 30 
#允許master創建function并同步到slave，有潛在的數據安全問題
log_bin_trust_function_creators = 1
#導出文件安全目錄 默認為空
secure_file_priv = /tmp

總結：

本篇文章介紹了部分MySQL初始化應當注意的參數，給出了相關參數的默認值及是否可動態修改。對于不可動態修改的參數 建議啟動前設置合理，這樣可以減少后面維護重啟次數。

在大家修改參數之前 請記住以下幾點：

• 一次更改一個設置！這是估計變更是否有益的唯一方法。
• 不允許在配置文件中進行重復設置。如果要跟蹤更改，請使用版本控制。
• 更改前應該在測試環境演練。
• 確保參數位置正確，單位合理，不和其他參數沖突。
• 不要做天真的數學運算，比如“我的新服務器有2x內存，我只需要將所有值設置為以前的2倍”。