MySQL8.0新增配置引數詳解

MySQL8.0新增或改進了資料字典、原子DDL、安全和賬戶、資源管理、InnoDB增強、字符集支援、JSON增強、欄位型別支援、最佳化器、通用表示式、視窗函式、正規表示式支援、內部臨時表、日誌、備份鎖等特性，MySQL本身是一個配置比較多比較複雜的資料庫，那麼新的版本中有哪些引數項是新增的用來控制這些新特性的使用那？接下來我們對新增的配置項進行逐一分析。

• activate_all_roles_on_login

此引數在版本8.0.2引入，是一個可以動態調整的global級引數，預設值為OFF。此引數用於控制在賬戶登入時是否啟用已經授予的角色，如果為ON則授予的角色會被啟用，設定為OFF時只能透過SET DEFAULT ROLE顯式啟用使用者角色。activate_all_roles_on_login設定只在賬戶登入或者開始執行儲存過程時生效，如果想更改session的role需要執行SET ROLE語句。

• binlog_expire_logs_seconds

引數在版本8.0.1中引入，是可以動態調整的global級引數，8.0.4之前預設值為0，8.0.11之後為2592000也就是30天。之前版本binlog自動清理時間以引數expire_logs_days也就是以天為單位，當前兩個引數並存並且有一個非0時則以非0的引數為binlog自動清理時間，如果兩個都為非0值則以binlog_expire_logs_seconds為binlog清理時間忽略expire_logs_days引數設定。

• binlog_row_metadata

此引數在8.0.1版本引入，是可以動態調整的global級引數，預設值為MINIMAL,可以設定為FULL。此引數用於控制row格式下binlog中表的後設資料數量，設定為MINMAL時記錄符號標記、列字符集和空間型別，設定為FULL時會記錄表所有的後設資料，例如列名、列舉或集合所有的值、主鍵資訊等等。

• binlog_row_value_options

此引數在8.0.3版本引入，是可以動態調整global級引數，預設值為’’，可以設定為PARTIAL_JSON。

當設定為PARTIAL_JSON並且binlog格式為ROW或者MIXED模式時，使用JSON_SET(), JSON_REPLACE()和 JSON_REMOVE()對JSON的列進行操作時，binlog只記錄更新的部分，而不是記錄整個JSON的變更，進而減少binlog大小。不過當對JSON文件的更改需要比當前文件更大的空間或者SERVER不能夠進行部分更新時，binlog還是會記錄整個JSON文件。

• binlog_transaction_dependency_history_size

此引數在版本8.0.1中引入，是可以動態調整的global級引數，預設值為25000，可以設定為0-1000000之間的任意整數。8.0基於WriteSet進行並行複製時，WriteSet是一個hash陣列，binlog_transaction_dependency_history_size值就是這個hash陣列的最大值。

• binlog_transaction_dependency_tracking

此引數在8.0.1版本引入，是可以動態調整的global級列舉型別引數，預設值為COMMIT_ORDER，也可以設定為WRITESET、WRITESET_SESSION。此引數用於主庫決定事務間在從庫進行多執行緒複製的依賴模式。

         COMMIT_ORDERE：根據主庫事務提交時間戳進行並行，也就5.7的GroupCommit；

         WRITESET：根據WriteSet進行並行，只要是不在同一個佇列裡的都可以並行；

         WRITESET_SESSION: 根據WriteSet進行並行，但相同session的事務不會並行。

• caching_sha2_password_auto_generate_rsa_keys

在版本8.0.4引入，是不可動態調整的global級引數，預設值為ON，當OpenSSL被編譯進MySQL時，根據此引數判斷是否在啟動時自動生成公鑰和私鑰檔案。

• caching_sha2_password_private_key_path

同上，用於指定私鑰檔案路徑及檔名，當設定為相對路徑時儲存在資料檔案路徑下。

• caching_sha2_password_public_key_path

同上，用於存放公鑰檔案路徑及檔名稱。

• cte_max_recursion_depth

在8.0.3版本引入，是可以動態調整的global、session級的整數型別引數，預設值為1000，可以設定為0 – 4294967295之間的任意整數。該引數用於設定公共表表示式的最大遞迴深度。如果SQL執行過程中遞迴深度超過該值則會中斷執行。

• default_collation_for_utf8mb4

在8.0.11版本中引入，是可以動態調整的global、session級列舉型別引數。此引數值會從主庫傳遞到從庫，主要用於主從同步或者MGR中從8.0之前版本向8.0同步資料時來保持主從間以一致的排序規則來處理資料。

• histogram_generation_max_mem_size

8.0.2版本引入，是可以動態設定的global、session級引數，預設值為20000000，可以設定為1000000到18446744073709551615（32位系統為4294967295）之間任意值。當建立直方圖時，需要將表的資料讀入記憶體，但是當表過大時可能會將將大量資料讀入記憶體，為規避這個風險，server層根據histogram_generation_max_mem_size設定進行計算，計算出讀出多少行進入記憶體，進行取樣。

• information_schema_stats_expiry

引數由8.03版本引入，是可以動態設定的global、session級引數。預設值為86400，可以設定為0到315360000之間熱任意整數。

Information_schema庫中有一些列提供表的統計資料，統計資料會隨著表中內容的變化進行更新。預設情況下MySQL檢索mysql.index_stats 和mysql.table_stats兩個字典表進行表資訊檢索，這樣遠比從儲存引擎層檢索相關資訊高效的多。如果需要的表統計資訊沒有被快取或者已經過期，則會從儲存引擎獲取相關統計資訊並快取到mysql.index_stats 和mysql.table_stats兩個表中，在表統計資訊過期之前，後來的查詢都會使用此快取資訊。每個session可以獨立設定information_schema_stats_expiry，預設值是86400秒也就是24小時，此數值最大可以設定為一年；如果想要更新某張表的統計資訊執行ANALYZE TABLE即可；如果每次查詢都想獲取最新的統計資訊，將此值設定成0即可，MySQL每次都會跳過mysql.index_stats 和mysql.table_stats兩個字典表直接透過儲存引擎獲取統計資訊。

• innodb_dedicated_server

引數在8.03版本中引入，是一個只能在啟動時設定的global級引數。此引數設定為ON時，MySQL會根據檢測到的記憶體大小設定innodb_buffer_pool_size、innodb_log_file_size、innodb_flush_method三個引數。有了這個引數我們就不用再寫指令碼根據記憶體大小去修改配置檔案的這三個引數了，運維自動化又省了一步。當伺服器MySQL與其他應用共享伺服器記憶體時建議設定為OFF。那麼MySQL具體的設定策略是什麼那？

innodb_buffer_pool_size：

innodb_log_file_size：

innodb_flush_method

當開啟 innodb_dedicated_server時，刷盤方式會採用O_DIRECT_NO_FSYNC ，O_DIRECT_NO_FSYNC 不可用時將會採用預設的刷盤方式。需要注意的是，目前在linux中當檔案大小發生變化時，O_DIRECT_NO_FSYNC 可能會導致系統hung住，因此不建議在linux中採用該刷盤方式。

• innodb_directories

引數在版本8.0.4中引入，只能在啟動時指定，不能夠動態調整。資料庫啟動時會根據此引數掃描innodb 表空間檔案。在資料庫離線時，我們可以透過指定該引數用於移動或者恢復表空間檔案到指定的路徑。

• innodb_log_spin_cpu_abs_lwm

引數在8.0.11版本中引入，是可以動態調整的全域性引數，預設值為80，可以設定為0到4294967295之間的任意整數。此數值表示innodb使用者執行緒刷redo進行自旋等待時CPU的低水位，當cpu使用率低於此值時不進行自旋等待。如果此值設定超過100例如150時，則表示在多核CPU時第一個核使用率為100%，第二個核使用率為50%。

• innodb_log_spin_cpu_pct_hwm

同上一個引數，預設值為50，可以設定為0到100之間的任意整數值。此引數表示innodb使用者執行緒刷redo進行自旋等待的高水位，表示CPU總處理能力百分比，例如4核設定為50時，表示個2個核為100%。該引數核數計算以實際使用CPU為準，例如將mysqld程式繫結到48核中的4核，則該引數忽略其他44核。

• innodb_log_wait_for_flush_spin_hwm

引數在8.0.11版本中引入，是可以動態調整的全域性整型引數，單位為毫秒，預設值為400可以設定為0到2^64-1（32位系統為2^32-1）之間的整數值，當使用者執行緒等待刷redo超過innodb_log_wait_for_flush_spin_hwm毫秒後不再進行自旋。

•  innodb_print_ddl_logsz

這個引數在8.03版本中引入是可以動態設定的global級引數。預設值為OFF，設定為ON時能夠在標準錯誤輸出，根據系統和配置的不同可能是error log、Linux終端或windows控制檯。8.0引入了原子DDL的特性，InnoDB透過寫DDL日誌到mysql.innodb_ddl_log的方式來實現DDL的重做和回滾，但除非在debug模式下，mysql.innodb_ddl_log對使用者是不可見的，可以透過設定innodb_print_ddl_logsz為ON來在錯誤輸出中檢視DDL的重做和回滾資訊。

• innodb_redo_log_encrypt

這個引數在8.0.1版本中引入，是可以動態設定的global級引數，預設值為OFF。此引數用於控制redo log的加密，將記憶體中的redo寫入磁碟時進行加密，從磁碟中讀取redo log進記憶體時進行解密。Redo的後設資料和加密秘鑰存放第一個redo檔案頭部，如果啟動前將此檔案刪除則會自動取消加密，如果有redo log已經加密儲存，則會啟動失敗。

• innodb_stats_include_delete_marked

引數在8.0.1版本中引入，時可以動態設定的全域性布林型別引數，預設值為No。預設情況下，MySQL進行表統計資訊計算時，會採用Repeatable-Read隔離級別，因此其他程式在採用Repeatable-Read及以上隔離級別查詢時，資料和索引的統計資訊已經排除掉了其他未提交事務中刪除的資料，這樣會導致執行計劃不是最優。當開啟innodb_stats_include_delete_marked時，最佳化器在計算統計資料時會將其他未提交事務中已刪除的資料計算在內。

• innodb_undo_log_encrypt

這個引數在8.0.1版本引入，是可以動態設定的global級引數，預設值為OFF。只有在獨立開啟undo時此引數設定為ON才會生效。和redo一樣，undo在寫入磁碟時進行加密，從磁碟讀取到記憶體時進行解密。Undo的加密和解密使用表空間的加密key。Undo的後設資料和加密的密碼存放於undo表空間檔案頭部。

• internal_tmp_mem_storage_engine

引數在8.0.2版本引入，是可以動態調整的全域性、session級列舉型別引數，預設值為TempTable，也可以設定為Memory。最佳化器根據此引數選擇記憶體中內部臨時表的引擎型別。

• keyring_operations

引數在8.0.4版本引入，是可以動態設定的全域性布林型引數，預設值為ON。此引數設定是否允許對innodb表空間加密所用的秘鑰進行遷移等操作。

• log_error_services

這個引數在8.0.2版本引入，是可以動態設定的global級引數，預設值為”log_filter_internal; log_sink_internal”。

• mandatory_roles

這個引數在8.0.2版本引入，是可以動態設定的global級引數，預設值為空字串。

• original_commit_timestamp

在8.0.1版本引入，是可以動態調整的session級整型引數。從庫在進行主從複製重放binlog時，該引數設定為事務在主庫提交時的時間戳，從公元紀元開始的毫秒數值。

• password_history

在版本8.0.3引入，是可以動態調整的全域性整型引數，預設值為0，可以設定為0- 4294967295之間的任意整數。該引數數值用於設定歷史密碼可以再次使用之前需要進行密碼修改的次數。設定為0則不會對歷史密碼是否可以重用進行限制。

• password_reuse_interval

在版本8.0.3版本引入，是可以動態調整的整型引數，預設值為0，可以設定為0- 4294967295之間的任意整數。該引數用於設定歷史密碼可以重用的間隔天數，設定為0則不會對歷史密碼重用進行時間間隔設定。

• performance_schema_max_digest_sample_age

在8.0.3版本引入，是可以動態調整的全域性整型引數，預設值為60，可以設定為0- 1048576之間任意整數。該引數用於設定events_statements_summary_by_digest表中語句重新採用時間，如果有新的相同摘要值得語句被採集到並且當前表中的資料執行時間為performance_schema_max_digest_sample_age秒之前，則會使用當前語句替換events_statements_summary_by_digest中取樣的語句。

• persisted_globals_load

引數只能在啟動時指定，不可動態調整，預設值為ON。當設定為ON時，在讀取完其他配置檔案後會讀取mysqld-auto.cnf檔案，mysqld-auto.conf中配置項會覆蓋其他配置檔案的設定，引數設定以json形式儲存於mysqld-auto.conf檔案中。

8.0中支援透過set persist 語法改變當前執行例項和配置檔案mysqld-auto.conf檔案中的配置項，set persist_only語法只更改mysqld-auto.conf中的配置項，不改變當前例項的配置項。可以透過RESET PERSIST、RESET PERSIST system_var_name、RESET PERSIST IF EXISTS system_var_name語法取消全部或者某一個配置項。

• regexp_stack_limit

引數在8.0.4版本中引入，是可以動態設定的全域性引數，預設值為8000000，可以設定為0到2147483647之間的任意整數。此數值表示MySQL用於正規表示式匹配函式REGEXP_LIKE()以及相關函式的最大堆疊記憶體可用位元組數。

• regexp_time_limit

引數在8.0.4版本中引入，是可以動態設定的全域性引數，預設值32。此引數表示匹配引擎進行正規表示式匹配函式REGEXP_LIKE()以及相關函式的最大步數，因而間接影響時間限制。

• rpl_read_size

引數在8.0.11版本中引入，是可以動態設定的全域性整型引數，預設值為8192，可以設定為8192-4294967295之間的任意4k的倍數。該引數設定server從binlog和relaylog中一次讀取的最小位元組數。如果binlog或relaylog的IO壓力較大，影響系統效能，可適當增大該引數，減少IO次數，進而減小磁碟IO壓力。 master的dump執行緒和slave的coordinator 執行緒等所有讀取binlog和relaylog的執行緒都會分配rpl_read_size大小的buffer，因此設定過大會對伺服器記憶體有一定影響。

• resultset_metadata

引數在8.0.3版本中引入，是可以動態設定的session級列舉型別引數，預設值為FULL,可以設定為NONE。該引數控制傳輸後設資料可選的連結server端是否傳輸後設資料，設定為FULL時返回所有後設資料，設定為NONE時不返回後設資料。

• schema_definition_cache

是可以動態設定的global級引數，預設值為256，可以設定為256到524288之間的任意整數。此數值表示可以在字典物件快取中儲存的schema定義物件的最大數量。

• sha256_password_auto_generate_rsa_keys

此引數需要在MySQL啟動時指定，不可以動態設定。預設值為ON。當MySQL用OpenSSL編譯時，此引數可用。當data目錄下沒有RSA私有/公共金鑰對時，此引數用於控制是否在啟動時自動生成生成RSA私有/公共金鑰對。

• sha256_password_private_key_path

此引數需要在MySQL啟動時指定，不可以動態設定。預設值為private_key.pem。此引數用於設定私鑰檔案，設定為相對路徑時儲存在data目錄下。

• sha256_password_public_key_path

此引數用於設定公鑰檔案。

ssl_fips_mode引數在8.0.11版本中引入，是可動態設定的global列舉型別引數。預設值OFF，可以設定為OFF (0) 、ON (1)、STRICT (2)。

• show_create_table_verbosity

引數在8.0.11版本引入，是可以動態設定的全域性和session級布林型引數，預設值為OFF。設定為OFF並且錶行格式為預設值時，執行show create table顯示的建表語句不會顯示row_format值；啟用該引數時，show create table的create table語句會一直顯示row_format值。

• ssl_fips_mode

引數在8.0.11版本中引入，是可以動態設定global級列舉型別引數。預設值為OFF，可以設定為OFF (0) 、ON (1)、STRICT (2)。當將OpenSSL編譯進MySQL並且FIPS模組可用時，用於設定FIPS模組是否在服務端可用。

• temptable_max_ram

引數在8.0.2版本中引入，是可以動態設定的global級整型引數，預設值為1073741824（1GB）,可以設定為2097152-2^64-1的任意整數。當TempTable 儲存引擎的表小於temptable_max_ram時，臨時表不會存入磁碟。

MySQL8.0新增的的配置項較多，在配置線上應用時還是需要多注意引數的不同配置對資料庫整體執行的影響，結合自己的業務的實際情況設定最符合自己生產需要的配置檔案。