MySQL5.7: my.cnf參數速查小冊子
MySQL配置檔案
在
MySQL文檔包含了描述各種配置檔案
-
/etc/my.cnf
-
/etc/mysql/my.cnf
-
SYSCONFDIR/my.cnf
-
$MYSQL_HOME/my.cnf
-
defaults-extra-file
-
~/.my.cnf
-
~/.mylogin.cnf
但它沒有提到mysql.cnf, 下文我們将會為大家解釋
MySQL配置檔案加載順序
root@a609a3ac9400:/# which mysqld
/usr/sbin/mysqld
root@a609a3ac9400:/# /usr/sbin/mysqld --verbose --help | grep -A 5 'Default options'
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf
/etc/my.cnf 、/etc/mysql/my.cnf 、~/.my.cnf 是 MySQL 的預設配置檔案，有先後的讀取順序
/etc/mysql/mysql.cnf是什麼情況呢？
root@a609a3ac9400:~# ls -lrt /etc/alternatives/my.cnf
lrwxrwxrwx 1 root root 20 Sep 28 16:28 /etc/alternatives/my.cnf -> /etc/mysql/mysql.cnf
從上面資訊可以看出，/etc/alternatives/my.cnf 其實是一個軟連接配接，指向參數檔案/etc/mysql/mysql.cnf
/etc/mysql/conf.d/ 和 /etc/mysql/mysql.conf.d/的作用
# Copyright (c) 2016, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License, version 2.0,
# as published by the Free Software Foundation.
# This program is also distributed with certain software (including
# but not limited to OpenSSL) that is licensed under separate terms,
# as designated in a particular file or component or in included license
# documentation. The authors of MySQL hereby grant you an additional
# permission to link the program and your derivative works with the
# separately licensed software that they have included with MySQL.
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License, version 2.0, for more details.
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
但是/etc/mysql/my.cnf參數檔案下面沒有任何參數設定，隻看到下面兩行設定，表示導入這兩個目錄裡面的配置檔案。
#表示包含/etc/mysql/conf.d/這個路徑下面的配置檔案
#表示包含/etc/mysql/mysql.conf.d/這個路徑下面的配置檔案
/etc/mysql/conf.d/ 目錄下的 my.cnf 和 mysql.cnf、mysqld.cnf 差別僅僅在于後者專門用于服務端和用戶端的配置，即[mysqld]節點和[mysql]節點
MySQL配置檔案參數
[mysqld]
server-id=513306 # Mysql唯一辨別，一個叢集中唯一；
port=3306 # 服務端口，預設3306
user = mysql # 啟動使用者，建議使用者mysql
bind_address= 0.0.0.0 # 綁定的IP位址，建議使用具體位址
basedir=/mysql/app/mysql # mysql安裝路徑，建議使用絕對路徑
datadir=/mysql/data/3306/data # 資料目錄
socket=/mysql/data/3306/mysql.sock # 指定套接字檔案
pid-file=/mysql/data/3306/mysql.pid # 指定pid檔案
character-set-server=utf8 # 指定預設編碼格式
skip-character-set-client-handshake=1 # 跳過mysql程式起動時的字元參數設定 ，使用伺服器端字元集設定 0:不跳過 1：跳過
autocommit = 0 # 是否開啟自動送出， 0:不開啟 1：開啟
skip_name_resolve = 1 # 禁止域名解析 建議開啟
max_connections = 800 # 最大連接配接數
max_connect_errors = 1000 # 最大連接配接錯誤
default-storage-engine=INNODB # 設定預設引擎，常用引擎INNODB，MYISAN，建議使用INNODB
transaction_isolation = READ-COMMITTED # 事務隔離級别，可選參數有：READ-UNCOMMITTED(讀取未送出内容), READ-COMMITTED（讀取送出内容）, REPEATABLE-READ(可重讀), SERIALIZABLE(可串行化).
explicit_defaults_for_timestamp = 1 # 參數是否初始化
sort_buffer_size = 32M # 排序使用的緩存大小，MySQL5.7中，預設為1M(優化參數之一，一般情況下預設數值就夠用了)
join_buffer_size = 128M # join操作所用用的緩存大小
tmp_table_size = 72M # 臨時表大小
max_allowed_packet = 16M # 服務端最大允許接收的資料包大小
sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER" # mysql支援的基本文法及校驗規則
interactive_timeout = 1800 # 是MySQL在等待一個活動連接配接關閉連接配接前等待的秒數。預設28800秒，8小時
wait_timeout = 1800 # 是MySQL在等待一個非活動連接配接關閉連接配接前等待的秒數。預設28800秒，8小時
read_buffer_size = 16M # （資料檔案存儲順序）是MySQL讀入緩沖區的大小，将對表進行順序掃描的請求将配置設定一個讀入緩沖區，MySQL會為它配置設定一段記憶體緩沖區，read_buffer_size變量控制這一緩沖區的大小，如果對表的順序掃描非常頻繁，并你認為頻繁掃描進行的太慢，可以通過增加該變量值以及記憶體緩沖區大小提高其性能，read_buffer_size變量控制這一提高表的順序掃描的效率 資料檔案順序。
read_rnd_buffer_size = 32M # 是MySQL的随機讀緩沖區大小，當按任意順序讀取行時（列如按照排序順序）将配置設定一個随機讀取緩沖區，進行排序查詢時，MySQL會首先掃描一遍該緩沖，以避免磁盤搜尋，提高查詢速度，如果需要大量資料可适當的調整該值，但MySQL會為每個客戶連接配接配置設定該緩沖區是以盡量适當設定該值，以免記憶體開銷過大。表的随機的順序緩沖 提高讀取的效率。
#event_scheduler =1 # 事件排程器 1：開啟 0:不開啟
query_cache_type = 1 # 控制着查詢緩存工能的開啟的關閉。0時表示關閉，1時表示打開，2表示隻要select 中明确指定SQL_CACHE才緩存。
query_cache_size=1M # 查詢緩存大小， 一般 1M很夠用了
table_open_cache=2048 # 檔案描述符的緩存大小，4G記憶體的機器，建議設定為2048，
thread_cache_size=768 # 線程池緩存大小，當用戶端斷開連接配接後 将目前線程緩存起來 當在接到新的連接配接請求時快速響應 無需建立新的線程
myisam_max_sort_file_size=10G # mysql重建索引時允許使用的臨時檔案最大大小
myisam_sort_buffer_size=64M # MyISAM表發生變化時重新排序所需的緩沖。一般64M足矣。
key_buffer_size=32M # Key Buffer大小，用于緩存MyISAM表的索引塊。決定資料庫索引處理的速度（尤其是索引讀），對于記憶體在4GB左右的伺服器該參數可設定為256M或384M。注意：該參數值設定的過大反而會是伺服器整體效率降低！
read_buffer_size=8M # 用于對MyISAM表全表掃描時使用的緩沖區大小。針對每個線程進行配置設定（前提是進行了全表掃描）。進行排序查詢時，MySql會首先掃描一遍該緩沖，以避免磁盤搜尋，提高查詢速度，如果需要排序大量資料，可适當調高該值。但MySql會為每個客戶連接配接發放該緩沖空間，是以應盡量适當設定該值，以避免記憶體開銷過大。
read_rnd_buffer_size=4M # 是MySQL的随機讀緩沖區大小，當按任意順序讀取行時（列如按照排序順序）将配置設定一個随機讀取緩沖區，進行排序查詢時，MySQL會首先掃描一遍該緩沖，以避免磁盤搜尋，提高查詢速度，如果需要大量資料可适當的調整該值，但MySQL會為每個客戶連接配接配置設定該緩沖區是以盡量适當設定該值，以免記憶體開銷過大。表的随機的順序緩沖 提高讀取的效率。
back_log=1024 # 值指出在MySQL暫時停止回答新請求之前的短時間内多少個請求可以被存在堆棧中。由預設的50，每個連接配接256kb
#flush_time=0
open_files_limit=65536 # MySQL打開了多少個檔案描述符，預設最小1024
table_definition_cache=1400 # 表定義檔案緩存相比表檔案描述符緩存所消耗的記憶體更小，其預設值是400
#binlog_row_event_max_size=8K
# 有時候為了避免master.info和中繼日志崩潰，在容忍額外的fsync()帶來的開銷，推薦設定
#sync_master_info=10000 # 預設為10000，每間隔多少事務重新整理master.info，如果是table（innodb）設定無效，每個事務都會更新，建議 設定為1
#sync_relay_log=10000 # 預設為10000，即每10000次sync_relay_log事件會重新整理到磁盤。為0則表示不重新整理，交由OS的cache控制，建議設定為1
#sync_relay_log_info=10000 # 預設為10000，每間隔多少事務重新整理relay-log.info，建議設定為1
########log settings########
log-output=FILE # 日志存儲方式，TABLE、FILE，建議設定為FILE，預設為FILE
general_log = 0 # 所有到達MySQL Server的SQL語句記錄下來。通用日志，不建議開啟，這個很消耗磁盤空間，用于優化及故障排查
general_log_file=/mysql/log/3306/itpuxdb-general.err # 指定通用日志檔案
slow_query_log = ON # ON 為開啟慢查詢日志，off表示關閉慢查詢日志，用于優化SQL語句
slow_query_log_file=/mysql/log/3306/itpuxdb-query.err #指定慢查詢日志檔案
long_query_time=10 # 指定多少秒傳回查詢的結果為慢查詢
log-error=/mysql/log/3306/itpuxdb-error.err # 指定錯誤日志
log_queries_not_using_indexes = 1 # 開啟 記錄沒有使用索引查詢語句，1或者ON開啟，記錄至慢日志中
log_slow_admin_statements = 1 #記錄那些慢的optimize table，analyze table和alter table語句，1或者ON開啟，記錄至慢日志中
log_slow_slave_statements = 1 # 記錄由Slave所産生的慢查詢
log_throttle_queries_not_using_indexes = 10 # 設定每分鐘記錄到日志的未使用索引的語句數目，超過這個數目後隻記錄語句數量和花費的總時間
expire_logs_days = 90 # 保留多少天
min_examined_row_limit = 100 # 對于查詢掃描行數小于此參數的SQL，将不會記錄到慢查詢日志中
#log_bin = "/log/bin_log/binlog" # bin 日志路徑設定
########replication settings######## # 主從複制設定
#master_info_repository = TABLE # 值如果為FILE，建議将其修改為TABLE
#relay_log_info_repository = TABLE
#log_bin = bin.log
#sync_binlog = 1 # 預設，sync_binlog=0，表示MySQL不控制binlog的重新整理，如果sync_binlog>0，表示每sync_binlog次事務送出，# sync_binlog=1了，表示每次事務送出，MySQL都會把binlog刷下去，是最安全但是性能損耗最大的設定。sync_binlog=1，多個事務同時送出，同樣很大的影響MySQL和IO性能。# MySQL DBA設定的sync_binlog并不是最安全的1，而是100或者是0。這樣犧牲一定的一緻性，可以獲得更高的并發和性能。
#gtid_mode = on # 是否開啟開啟 基于 gtid 的複制， 5.7之後才出現的新特性
#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########
# 根據您的伺服器IOPS能力适當調整,隻有當你在頻繁寫操作的時候才有意義
# 一般配普通SSD盤的話，可以調整到 10000 - 20000
# 配置高端PCIe SSD卡的話，則可以調整的更高，比如 50000 - 80000
innodb_io_capacity = 4000 # 動态調整重新整理髒頁的數量，一般設定最大值的1/2
innodb_io_capacity_max = 8000 #動态調整重新整理髒頁的最大數量
innodb_buffer_pool_size = 200M # 緩存池大小，預設128M，建議設定為總記憶體大小的，設定為實體記憶體的80%
innodb_buffer_pool_instances = 8 # 可以開啟多個記憶體緩沖池，把需要緩沖的資料hash到不同的緩沖池中，這樣可以并行的記憶體讀寫。
innodb_buffer_pool_load_at_startup = 1 # 預設為關閉OFF。如果開啟該參數，啟動MySQL服務時，MySQL将本地熱資料加載到InnoDB緩沖池中。
innodb_buffer_pool_dump_at_shutdown = 1 # 預設啟用。指定在MySQL伺服器關閉時是否記錄在InnoDB緩沖池中緩存的頁面，以便在下次重新啟動時縮短預熱過程。
innodb_lru_scan_depth = 2000 # LRU清單中可用頁的數量，預設值為1024。
innodb_lock_wait_timeout = 5 # 事務鎖逾時時間
#innodb_flush_method = O_DIRECT
innodb_log_file_size = 200M # mysql事務日志檔案（ib_logfile0）的大小；
innodb_log_files_in_group = 2 # 指定你有幾個日志組。一般２-３個日值組。預設為兩個。
innodb_log_buffer_size = 16M # 事務在記憶體中的緩沖大小。
innodb_undo_logs = 128 # InnoDB使用的復原段個數，必須設定35個以上；，預設128
innodb_undo_tablespaces = 3 # 是控制undo是否開啟獨立的表空間的參數，為0表示：undo使用系統表空間，即ibdata1，不為0表示：使用獨立的表空間，一般名稱為 undo001 undo002，存放位址的配置項為：innodb_undo_directory，預設配置為0，參數必須大于或等于2，即回收（收縮）一個undo log日志檔案時，要保證另一個undo log是可用的。
innodb_undo_log_truncate = 1 # 參數設定為1，即開啟線上回收（收縮）undo log日志檔案，支援動态設定。
innodb_max_undo_log_size = 2G # 每一個undo日志檔案大小，預設1G
innodb_flush_neighbors = 1 # 刷髒頁的控制政策，參數就是用來控制這個行為的，值為 1 的時候會有上述的“連坐”機制，值為 0 時表示不找鄰居，自己刷自己的。建議設定為0
innodb_purge_threads = 4 # 負責回收已經使用并配置設定的undo頁，可以指定多個innodb_purge_threads來進一步加快和提高undo回收速度。
innodb_large_prefix = 1 # 單索引限制，是否開啟允許列索引最大達到3072，不開啟隻有767
innodb_thread_concurrency = 64 # 來限制并發線程的數量
innodb_print_all_deadlocks = 1 # 是否開啟儲存死鎖日志，死鎖日志存放到error_log配置的檔案裡面
innodb_strict_mode = 1 # InnoDB嚴格檢查模式，尤其采用了頁資料壓縮功能後，最好是開啟該功能。開啟此功能後，當建立表（CREATE TABLE）、更改表（ALTER TABLE）和建立索引（CREATE INDEX）語句時，如果寫法有錯誤，不會有警告資訊，而是直接抛出錯誤，這樣就可直接将問題扼殺在搖籃
innodb_sort_buffer_size = 64M
innodb_flush_log_at_trx_commit=1 # sync_binlog 兩個參數是控制MySQL 磁盤寫入政策以及資料安全性的關鍵參數，當兩個參數都設定為1的時候寫入性能最差，但安全性最高，
# 設定為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_autoextend_increment=64 # 預設 8M ,機關為M，配置表空間自動擴充，每次擴充多大M
innodb_concurrency_tickets=5000 # 這個參數設定為一種tickets,預設是5000，
innodb_old_blocks_time=1000 # 頁讀取到mid位置後，需要等待多久才會被加入到LRU清單的熱端。預設1000ms
innodb_open_files=65536 # 限制Innodb能打開的表的資料。
innodb_stats_on_metadata=0 # 是否自動更新統計資訊,預設為0關閉，
innodb_file_per_table=1 # MySQL InnoDB引擎 預設會将所有的資料庫InnoDB引擎的表資料存儲在一個共享空間中：ibdata1，當增删資料庫的時候，ibdata1檔案不會自動收縮，單個資料庫的備份也将成為問題。通常隻能将資料使用mysqldump 導出，然後再導入解決這個問題。如果啟用了innodb_file_per_talbe參數，需要注意的是每張表的表空間記憶體放的隻是資料、索引和插入緩沖Bitmap頁，其他資料如：復原資訊、插入緩沖索引頁、系統事物資訊、二次寫緩沖（Double write buffer）等還是放在原來的共享表空間内。同時說明了一個問題：即使啟用了innodb_file_per_table參數共享表空間還是會不斷的增加其大小的。
#innodb_checksum_algorithm=0 # 是否開啟checksum算法
innodb_data_file_path=ibdata1:200M;ibdata2:200M;ibdata3:200M:autoextend:max:5G #可配置表空間相關參數。
innodb_temp_data_file_path = ibtmp1:200M:autoextend:max:20G # 可配置臨時表空間相關參數。
innodb_buffer_pool_dump_pct = 40 # 指定每個緩沖池最近使用的頁面讀取和轉儲的百分比，1-100，預設25
innodb_page_cleaners = 4 # 多個頁面清理線程刷髒頁，用于指定頁面清理線程的數量。其預設值1，維持了之前單個頁面清理線程的配置
innodb_purge_rseg_truncate_frequency = 128 # 指定purge操作被喚起多少次之後才釋放rollback segments。當undo表空間裡面的rollback segments被釋放時，undo表空間才會被truncate。由此可見，該參數越小，undo表空間被嘗試truncate的頻率越高。
binlog_gtid_simple_recovery=1 #這個變量用于在MySQL重新開機或啟動的時候尋找GTIDs過程中，控制binlog 如何周遊的算法？
#2. 當binlog_gtid_simple_recovery=FALSE 時：
# 為了初始化 gtid_executed，算法是： 從newest_binlog -> oldest_binlog 方向周遊讀取，如果發現有Previous_gtids_log_event ， 那麼就停止周遊
# 為了初始化 gtid_purged，算法是： 從oldest_binlog -> newest_binlog 方向周遊讀取, 如果發現有Previous_gtids_log_event（not empty）或者 #至少有一個Gtid_log_event的檔案，那麼就停止周遊
#3. 當binlog_gtid_simple_recovery=TRUE 時：
# 為了初始化 gtid_executed ， 算法是： 隻需要讀取newest_binlog
# 為了初始化 gtid_purged， 算法是： 隻需要讀取oldest_binlog
#4. 當設定binlog_gtid_simple_recovery=TRUE ， 如果MySQL版本低于5.7.7 ， 可能會有gitd計算出錯的可能，具體參考官方文檔較長的描述
log_timestamps=system # 主要是控制 error log、slow_log、genera log，等等記錄日志的顯示時間參數，但不會影響 general log 和 slow log 寫到表 (mysql.general_log, mysql.slow_log) 中的顯示時間
#transaction_write_set_extraction=MURMUR32 # 基于WRITESET的并行複制方式
show_compatibility_56=on # mysql相容性是否相容mysql5.6，這是是開啟相容
lower_case_table_names=1 # 是否區分大小寫 說明 0：區分大小寫，1：不區分大小寫
read_only=1 # 普通是否可讀， 0：關閉可讀， 1：開啟可讀
super_read_only=1 # 管理者（super）使用者是否可讀，超級可讀 ，0：關閉可讀， 1：開啟可讀 ![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)