知識星球點選▲關註 “資料和雲” 給公眾號標星置頂
更多精彩 第一時間直達
作者：崔虎龍，雲和恩墨-開源架構部-MySQL技術顧問，長期服務於資料中心（金融，遊戲，物流）行業，熟悉資料中心運營管理的流程及規範，自動化運維 等方面。擅長MySQL，Redis，MongoDB 資料庫高可用設計 和 運維故障處理，備份恢復，升級遷移，效能最佳化 。
MySQL已進入8.0的時代，臨近春節 ，MySQL突然搞個突襲，釋出了8.0.14版本，節後嘗試，分享給大家。
有添加了那些 新功能修複了那些bug，算算一共19個專案，其中我本人感興趣的Account Management ,Function ,Logging ，Security , Functionality 要是使用8.0。14版本實際應用中，會起到相對應的幫助。
相關連結：https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-14.html（或者點選“閱讀原文”）
| l Account Management Notes
l Audit Log Notes l Compilation Notes l Component Notes l Configuration Notes l Deprecation and Removal Notes l Function Notes l Logging Notes l Optimizer Notes l Packaging Notes l Performance Schema Notes
|
l Pluggable Authentication
l Security Notes l Spatial Data Support l SQL Syntax Notes l Thread Pool Notes l X Plugin Notes l Functionality Added or Changed l Bugs Fixed
|
下麵來瞭解一下。
Account Management Notes
也算是補助功能功能就是MySQL現在允許一個帳戶有雙重密碼，指定為主密碼和輔助密碼。
命令新增如下：RETAIN CURRENT PASSWORD
mysql> CREATE USER ‘testuser’@’localhost’ IDENTIFIED BY ‘123456’;
Query OK, 0 rows affected (0.01 sec)
mysql> ALTER USER ‘testuser’@’localhost’ IDENTIFIED BY ‘test123’
RETAIN CURRENT PASSWORD;
Query OK, 0 rows affected (0.01 sec)
實踐：
總結下來，補助作用，個人覺得但意義不大。
Audit Log Notes
審計API現在允許應用程式使用新的audit_api_message_emit元件將自己的訊息事件新增到審計日誌
INSTALL COMPONENT “file://component_audit_api_message_emit”;
UNINSTALL COMPONENT “file://component_audit_api_message_emit”;
總結下來，視覺效果好，不需要重新排版資料顯示。
Compilation Component Configuration Deprecation Notes
在使用原始碼 編譯的時候 修複的一些bug 問題，包含boost編譯，元件，Cmake配置
棄用resolveip和resolve_stack_dump函式
Function Notes
JSON_ARRAYAGG()和JSON_OBJECTAGG()聚合函式現在可以用作視窗函式。
GROUP 實現 Jason格式聚合
Logging Notes
1）log_slow_extra 慢日誌引數，提供了更詳細的內容
mysql> SET GLOBAL log_slow_extra=1;
Query OK, 0 rows affected (0.01 sec)
參考連結：https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_log_slow_extra
2）Binary log 加密機制，屬於安全機制方面
[mysqld]
early-plugin-load=keyring_file.so
keyring_file_data=/opt/idc/mysql/mysql-keyring/keyring
I ran the following command to install the plugin.
mysql> install plugin keyring_file soname ‘ keyring_file.so’;
mysql>set global keyring_file_data=’/opt/idc/mysql/mysql-keyring/keyring’
mysql>SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS
WHERE PLUGIN_NAME LIKE ‘keyring%’;
mysql> alter table test encryption=’Y’;
binlog_encryption
| Property | Value |
| Command-Line Format | –binlog-encryption |
| Introduced | 8.0.14 |
| System Variable | binlog_encryption |
| Scope | Global |
| Dynamic | Yes |
| SET_VAR Hint Applies | No |
| Type | Boolean |
| Default Value | OFF |
3）mysql_error日誌記錄更詳細
Optimizer Notes
之前版本，派生表和公共表運算式不能包含外部取用。現在允許外部取用。
Packaging Notes
Ubuntu 18.10和Fedora 29預設安裝OpenSSL 1.1.1，但MySQL不完全支援OpenSSL 1.1.1。要安裝MySQL，必須安裝OpenSSL 1.0.2相容性包。
Performance Schema Notes
效能樣式陳述句事件表(events_statements_current、events_statements_history和events_statements_history_long)現在有一個STATEMENT_ID列，指示伺服器在SQL級別維護的查詢ID。列值對於伺服器實體是惟一的，因為它們是使用原子遞增的全域性計數器生成的。
Pluggable Authentication Notes
LDAP埠號配置為636或3269，那麼外掛現在使用LDAPS (SSL上的LDAP)而不是LDAP。埠號可以使用authentication_ldap_sasl_server_port或authentication_ldap_simple_server_port系統變數設定。
Security Notes
全域性變數受限制的會話變數需要SYSTEM_VARIABLES_ADMIN或SUPER，但現在也可以用SESSION_VARIABLES_ADMIN設定:
binlog_format
binlog_row_image
binlog_row_value_options
binlog_rows_query_log_events
debug
debug_sync
default_collation_for_utf8mb4
explicit_defaults_for_timestamp
gtid_next
histogram_generation_max_mem_size
original_commit_timestamp
sql_log_bin
sql_log_off
sql_require_primary_key
auto_increment_increment
auto_increment_offset
binlog_direct_non_transactional_updates
bulk_insert_buffer_size
character_set_filesystem
character_set_database
collation_database
pseudo_slave_mode
pseudo_thread_id
rbr_exec_mode
transaction_write_set_extraction
Spatial Data Support
ST_Distance()函式現在接受第三個可選引數，允許指定傳回值的單位。允許的單元是新INFORMATION_SCHEMA ST_UNITS_OF_MEASURE表中列出的單元。
SQL Syntax Notes
現在，派生表的前面可以加上LATERAL關鍵字，以指定在同一個FROM子句中允許取用(依賴於)前面表的列。用側接指定的派生表只能出現在FROM子句中，要麼出現在以逗號分隔的表串列中，要麼出現在聯接規範中(聯接、行內接、交叉聯接、左[外]聯接或右[外]聯接)。橫向派生表使某些SQL操作成為可能，而這些操作不能使用非橫向派生表，或者需要效率較低的變通方法。
Thread Pool Notes
執行緒池外掛附帶的INFORMATION_SCHEMA表已被遷移為效能樣式表。INFORMATION_SCHEMA表現在已被棄用，將在未來的MySQL版本中刪除。應用程式應該從舊表過渡到新表。例如，如果一個應用程式使用這個查詢:
[mysqld]
plugin-load=thread_pool.so
SELECT * FROM INFORMATION_SCHEMA.TP_THREAD_STATE;
↓
SELECT * FROM performance_schema.tp_thread_state;
X plugin Notes
X plugin是mysql新發版本5.7.12中新增的外掛，利用它實現mysql作為檔案儲存資料庫，也就是利用mysql 5.7版本json支援的特性完成，完全模仿mongodb做的 ，有時間嘗試一下。
修改內容：
1.X Plugin現在在其錯誤處理類中包含5位SQLSTATE錯誤程式碼。以前，SQL錯誤的SQLSTATE錯誤程式碼傳回給客戶機，但是隻公開特定於mysql的錯誤編號。(錯誤# 28735058)
2.在查詢檔案集合時，如果在SQL查詢中將布林值用作佔位符的引數，則會傳回意外的結果。現在為布林值添加了一個新的翻譯專門化，以便在這種情況下正確處理它們。(錯誤# 28227037)
3.在傳回資料之前，X協議現在總是將檢索到的資料轉換為utf8mb4字符集(使用utf8mb4_general_ci排序規則)。(錯誤# 28180155)
4.X協議現在支援SQL prepare功能。
Functionality Added or Changed
- l InnoDB: By default, undo logs reside in two undo tablespaces that are created when the MySQL instance is initialized.預設兩個undo 日誌
CREATE UNDO TABLESPACE tablespace_name ADD DATAFILE ‘file_name.ibu’;
DROP UNDO TABLESPACE tablespace_name;
ALTER UNDO TABLESPACE tablespace_name SET {ACTIVE|INACTIVE};
- l InnoDB: InnoDB now supports parallel clustered index reads, which can improve CHECK TABLE performance. This feature does not apply to secondary index scans. The innodb_parallel_read_threads session variable must be set to a value greater than 1 for parallel clustered index reads to occur. The default value is 4. The actual number of threads used to perform a parallel clustered index read is determined by the innodb_parallel_read_threads setting or the number of index subtrees to scan, whichever is smaller.InnoDB: InnoDB now supports parallel clustered index reads, which can improve CHECK TABLE performance. This feature does not apply to secondary index scans. The innodb_parallel_read_threads session variable must be set to a value greater than 1 for parallel clustered index reads to occur. The default value is 4. The actual number of threads used to perform a parallel clustered index read is determined by the innodb_parallel_read_threads setting or the number of index subtrees to scan, whichever is smaller.
innoDB現在支援並行聚集索引讀取，這可以提高檢查表的效能。此特性不適用於輔助索引掃描。innodb_parallel_read_threads會話變數必須設定為大於1的值，以便進行並行群集索引讀取。預設值是4
- l InnoDB: When the innodb_dedicated_server variable is enabled, the size and number of log files are now configured according to the automatically configured buffer pool size. Previously, log file size was configured according to the amount of memory detected on the server, and the number of log files was not configured automatically.
InnoDB:在啟用innodb_dedicated_server變數時，現在根據自動配置的緩衝池大小配置日誌檔案的大小和數量。
- l Replication：When running a group in single-primary mode, in the event of a new primary being elected while there were transactions held in the backlog to be applied, there was a chance that a read operation against the new primary could return a stale value. Now, you can use the group_replication_consistency variable to control how a group behaves in this situation. When group_replication_consistency is set to EVENTUAL, a new primary responds to read requests even when there is a backlog which has not yet been applied, which matches the previous behavior and comes with the risk that a client could read old values while any backlog is being applied. Writes to the new primary fail during this period because it is has super_read_only mode enabled. When group_replication_consistency is set to BEFORE_ON_PRIMARY_FAILOVER, any new read or write queries against a newly elected primary that is applying backlog from the old primary are held until the backlog is applied. This ensures that clients always read the newest value which they have written, but also means that clients might have to wait until the backlog has been applied before they can read from the new primary.
複製:mgr 單主樣式下
group_replication_consistency= EVENTUAL & 故障導致選擇新主的情況下，新主未應用的backlog下，客戶端請求可能會讀取舊的資料。
同樣條件group_replication_consistency= BEFORE_ON_PRIMARY_FAILOVER 時，新主未應用的backlog下，客戶端請求會等待，應用backlog為止。
確保資料原子性。
- l ALTER TABLE now can be used to change a column character set in place (without a table rebuild), when these conditions apply:
1.The column data type is CHAR, VARCHAR, a TEXT type, or ENUM.
2.The character set change is from utf8mb3 to utf8mb4, or any character set to binary.
3.There is no index on the column.
ALTER TABLE現在可以用來改變一個列的字符集(不需要重建表)，當這些條件適用:
1.column資料型別是CHAR、VARCHAR、文字型別或ENUM。
2.字符集的變化是從utf8mb3到utf8mb4，或任何字符集到二進位制。
3. 列上沒有索引。
Bugs Fixed
整體bugs 修複大概如下：
| Important Change | 1 |
| InnoDB | 27 |
| Partitioning | 3 |
| Replication | 25 |
| Json | 6 |
| other | 112 |
下麵抽出重要的部分：
- Important Change: Importing a dump from a MySQL 5.7 server to a server running MySQL 8.0 often failed with ER_WRONG_VALUE_FOR_VAR when an SQL mode not supported by the 8.0 server was used. This could happen frequently due to the fact that NO_AUTO_CREATE_USER is enabled by default in MySQL 5.7 but not supported in MySQL 8.0.
The behavior of the server in such circumstances now depends on the setting of the pseudo_slave_mode system variable. If this is false, the server rejects the mode setting with ER_UNSUPPORTED_SQL_MODE. If pseudo_slave_mode is true, the server ignores the unsupported mode and gives a warning. Note that mysqlbinlog sets pseudo_slave_mode to true prior to executing any SQL. (Bug #90337, Bug #27828236)
從MySQL 5.7伺服器匯入轉儲到執行MySQL 8.0的伺服器時，當使用8.0伺服器不支援的SQL樣式時，ER_WRONG_VALUE_FOR_VAR常常失敗。這種情況經常發生，因為在MySQL 5.7中預設啟用NO_AUTO_CREATE_USER，但在MySQL 8.0中不支援。
- InnoDB: The TempTable storage engine incorrectly created temporary files in the system temporary directory instead of the directory defined by the tmpdir variable. (Bug #28598943)
TempTable儲存引擎錯誤地在系統臨時目錄中建立臨時檔案，而不是在tmpdir變數定義的目
錄中建立臨時檔案。(錯誤# 28598943)
- Replication: When stopping replication, any channels that had pending transactions could cause a deadlock in Group Replication. (Bug #92376, Bug #28636768, Bug #28365855)
複製:當停止複製時，任何具有掛起事務的通道都可能導致組複製中的死鎖。(Bug 92376, Bug 28636768, Bug 28365855)
- JSON: When trying to select from a JSON column of a FEDERATED table, the server returned ER_INVALID_JSON_PATH_CHARSET Cannot create a JSON value from a string with CHARACTER SET ‘binary’.
JSON:當試圖從聯邦表的JSON列中進行選擇時，伺服器傳回的
ER_INVALID_JSON_PATH_CHARSET無法從字符集為“binary”的字串中建立JSON值。
- Partitioning: ALTER TABLE … EXCHANGE PARTITION did not work when the partitioned table had one or more partition definitions using the DATA DIRECTORY option. This fix supports partitioned tables using the InnoDB storage engine only. (Bug #19730200)
分割槽:ALTER TABLE…當分割槽表使用DATA DIRECTORY選項具有一個或多個分割槽定義時，EXCHANGE分割槽無法工作。此修複僅支援使用InnoDB儲存引擎分割槽表。(錯誤# 19730200)
- Mysqldump： output could include SQL mode values that have been removed. (Bug #28373001, Bug #91714)
mysqldump輸出可以包括已刪除的SQL樣式值。(Bug #28373001, Bug #91714)
總結：透過對於MySQL8.0.14版本 新功能的瞭解和bug修複方面，個人認為重要的還是在於 Replication這部分，MySQL8.0需要多多磨練。
雲和恩墨Bethune Pro企業版，集監控，巡檢，安全於一身，你的專屬資料庫實時監控和智慧巡檢平臺，漂亮的不像實力派，你值得擁有！ 雲和恩墨zData一體機現已釋出超融合版本和精簡版，支援各種簡化場景部署，零資料丟失備份一體機ZDBM也已釋出,歡迎關註。
