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（或者点击“阅读原文”）

下面来了解一下。

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

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: 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也已发布,欢迎关注。