歡迎光臨
每天分享高質量文章

MySQL 8.0.14版本新功能詳解

點擊▲關註 “資料和雲”   給公眾號標星置頂

更多精彩 第一時間直達

作者:崔虎龍,雲和恩墨-開源架構部-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

 

 

下麵來瞭解一下。

 

1

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)

 

實踐:

 

總結下來,補助作用,個人覺得但意義不大。

 

2

Audit Log Notes

 

審計API現在允許應用程式使用新的audit_api_message_emit組件將自己的訊息事件添加到審計日誌

INSTALL COMPONENT “file://component_audit_api_message_emit”;

UNINSTALL COMPONENT “file://component_audit_api_message_emit”;

 

總結下來,視覺效果好,不需要重新排版資料顯示。

 

3

Compilation Component Configuration Deprecation Notes

 

在使用原始碼 編譯的時候 修複的一些bug 問題,包含boost編譯,組件,Cmake配置

棄用resolveip和resolve_stack_dump函式

 

4

Function Notes

 

JSON_ARRAYAGG()和JSON_OBJECTAGG()聚合函式現在可以用作視窗函式。

GROUP 實現 Jason格式聚合

 

5

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日誌記錄更詳細

 

6

Optimizer Notes

 

之前版本,派生表和公共表運算式不能包含外部取用。現在允許外部取用。

 

7

Packaging Notes

 

Ubuntu 18.10和Fedora 29預設安裝OpenSSL 1.1.1,但MySQL不完全支持OpenSSL 1.1.1。要安裝MySQL,必須安裝OpenSSL 1.0.2兼容性包。

 

8

Performance Schema Notes

 

性能樣式陳述句事件表(events_statements_current、events_statements_history和events_statements_history_long)現在有一個STATEMENT_ID列,指示服務器在SQL級別維護的查詢ID。列值對於服務器實體是惟一的,因為它們是使用原子遞增的全域性計數器生成的。

 

9

Pluggable Authentication Notes

 

LDAP端口號配置為636或3269,那麼插件現在使用LDAPS (SSL上的LDAP)而不是LDAP。端口號可以使用authentication_ldap_sasl_server_port或authentication_ldap_simple_server_port系統變數設置。

 

10

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

11

Spatial  Data Support

 

ST_Distance()函式現在接受第三個可選引數,允許指定傳回值的單位。允許的單元是新INFORMATION_SCHEMA ST_UNITS_OF_MEASURE表中列出的單元。

 

12

SQL Syntax Notes

 

現在,派生表的前面可以加上LATERAL關鍵字,以指定在同一個FROM子句中允許取用(依賴於)前面表的列。用側接指定的派生表只能出現在FROM子句中,要麼出現在以逗號分隔的表串列中,要麼出現在聯接規範中(聯接、行內接、交叉聯接、左[外]聯接或右[外]聯接)。橫向派生表使某些SQL操作成為可能,而這些操作不能使用非橫向派生表,或者需要效率較低的變通方法。

 

13

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;

 

14

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功能。

 

15

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. 列上沒有索引。

 

16

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需要多多磨練。

 


資源下載

關註公眾號:資料和雲(OraNews)回覆關鍵字獲取

2018DTCC , 資料庫大會PPT

2018DTC,2018 DTC 大會 PPT

DBALIFE ,“DBA 的一天”海報

DBA04 ,DBA 手記4 電子書

122ARCH ,Oracle 12.2體系結構圖

2018OOW ,Oracle OpenWorld 資料

產品推薦

 

雲和恩墨Bethune Pro企業版,集監控,巡檢,安全於一身,你的專屬資料庫實時監控和智慧巡檢平臺,漂亮的不像實力派,你值得擁有!

雲和恩墨zData一體機現已發佈超融合版本和精簡版,支持各種簡化場景部署,零資料丟失備份一體機ZDBM也已發佈,歡迎關註。

    閱讀原文

    赞(0)

    分享創造快樂