专栏首页数据和云MySQL 8.0.14版本新功能详解

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 Notesl Audit Log Notesl Compilation Notesl Component Notesl Configuration Notesl Deprecation and Removal Notesl Function Notesl Logging Notesl Optimizer Notesl Packaging Notesl Performance Schema Notes

l Pluggable Authenticationl Security Notesl Spatial Data Supportl SQL Syntax Notesl Thread Pool Notesl X Plugin Notesl Functionality Added or Changedl 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)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-02-17

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • Oracle 12c: arraysize会影响结果集么?

    SQL*Plus中ArraySize的设置会影响结果集的数量么?先不要轻易说“NO”,我们来看看Jonathan Lewis最近一篇文章中披露的一个案例。 当确...

    数据和云
  • 详述一则DB2 Error Code 1639和SQL State 08001案例诊断和解决方案

    导读:在 db2inst1/sqllib/security 目录下,检查 db2ckpw 和 db2chpw 文件的权限。

    数据和云
  • 史上最通俗分布式锁解读

    首先,分布式锁和我们平常讲到的锁原理基本一样,目的就是确保在多个线程并发时,只有一个线程在同一刻操作这个业务或者说方法、变量。

    数据和云
  • 图像拼接--A multiresolution spline with application to image mosaics

    A multiresolution spline with application to image mosaics 《Acm Trans on Graphi...

    用户1148525
  • Optimizing the number of centroids最优化形心数量

    Centroids are difficult to interpret, and it can also be very difficult to deter...

    到不了的都叫做远方
  • ZOJ 3713 In 7-bit

    Very often, especially in programming contests, we treat a sequence of non-white...

    ShenduCC
  • Three Paper Thursday: What’s Intel SGX Good For?

    Software Guard eXtensions (SGX) represents Intel’s latest foray into trusted com...

    仇诺伊
  • 重设3COM-3C857 ADSL密码的

    公司有台3COM-3C857 的ADSL ,现在忘记了管理登陆密码,无法登陆管理了,想要重新Reset下,找了半天都没有发现重设按钮,原本这种老古董应该换新的了...

    py3study
  • 如何将Ubuntu升级到18.04最新版

    Ubuntu操作系统的最新长期支持(LTS)版本Ubuntu 18.04(Bionic Beaver)于2018年4月26日发布。本教程将讲解如何将16.04或...

    挺问中原
  • Linux 搭建 maven

    Maven, 是基于项目对象模型(Project Object Model, POM),通过一小段描述信息来管理项目的构建,报告,文档的软件项目管理工具。

    阳光岛主

扫码关注云+社区

领取腾讯云代金券