早先数据文件的.frm , .opt , .par , .TRN , .TRG , .isl 文件都移除了，将所有原先存放于数据字典文件中的信息，全部存放到数据库系统表中，不再通过文件的方式存储数据字典信息。
原先使用MyISAM存储引擎的数据字典表都改为使用InnoDB存储引擎存放。从不支持事务的MyISAM存储引擎转变到支持事务的InnoDB存储引擎，为原子DDL的实现，提供了可能性。

旧版回顾
数据字典信息内容大致包括表结构、数据库名或表名、字段的数据类型、视图、索引、表字段信息、存储过程、触发器等内容。INFORMATION_SCHEMA库提供了对数据局元数据、统计信息、以及有关MySQL server的访问信息（例如：数据库名或表名，字段的数据类型和访问权限等）。
在MySQL8.0之前，MySQL的数据字典信息，并没有全部存放在系统数据库表中，部分数据库数据字典信息存放于文件中，其余的数据字典信息存放于数据字典库中（INFORMATION_SCHEMA,mysql,sys）。
例如表结构信息存放在.frm文件中,数据库表字段信息存放于INFORMATION_SCHEMA下的COLUMNS表中。早期，5.6版本之前，MyISAM是MySQL的默认存储引擎，而作为MyISAM存储引擎，它是没有数据字典的。只有表结构信息记录在.frm文件中。MySQL5.6版本之后，将InnoDB存储引擎作为默认的存储引擎。在InnoDB存储引擎中，添加了一些数据字典文件用于存放数据字典元信息，例如：.opt文件，记录了每个库的一些基本信息，包括库的字符集等信息，.TRN，.TRG文件用于存放触发器的信息内容。

新旧对比总结：
（1）INFORMATION_SCHEMA性能提升
　　数据库在查询INFORMATION_SCHEMA的表时，不再一定需要创建一张临时表，可以直接查询数据字典表。
　　在之前版本中，数据字典信息不一定是存放于表中，所以在获取数据字典信息时候，不仅仅是查表操作。例如读取数据库表结构信息，底层其实是读取.frm文件来获得，是一个文件打开读取的操作。
　　在新版本中，数据字典信息都可以通过直接查表的方式获取，替代那些获取信息慢的方式。在查询INFORMATIONS_SCHEMA表时，如果表上有索引，优化器会合理的利用索引。
　　对于INFORMATION_SCHEMA下的STATISTICS表和TABLES表中的信息，8.0中通过缓存的方式，以提高查询的性能。可以通过设置information_schema_stats_expiry参数设置缓存数据的过期时间，默认是86400秒。
查询这两张表的数据的时候，首先是到缓存中进行查询，缓存中没有缓存数据，或者缓存数据过期了，查询会从存储引擎中获取最新的数据。如果需要获取最新的数据，可以通过设置information_schema_stats_expiry参数为0或者ANALYZE TABLE操作。
（2）原子DDL
MySQL8.0开始支持原子DDL操作，一个原子DDL操作，具体的操作内容包括：数据字典更新，存储引擎层的操作，在binlog中记录DDL操作。并且这些操作都是原子性的，表示中间过程出现错误的时候，是可以完整回退的。这在之前版本的DDL操作中是不支持的。之前数据库版本中一直没有支持原子DDL的特性，是有原因的，因为在早期的数据库版本中，数据库元信息存放于元信息文件中、非事务性表中以及特定存储引擎的数据字典中。这些都无法保证DDL操作内容在一个事务当中，无法保证原子性。
（3）innodb_read_only对所有存储引擎生效
在8.0之前版本中，innodb_read_only参数可以阻止对InnoDB存储引擎表的create和drop等更新操作。但是在MySQL8.0中，开启innodb_read_only参数阻止了所有存储引擎的这些操作。create或者drop表的操作都需要更新数据字典表，8.0中这个数据字典表都改为了InnoDB存储引擎，所以对于数据字典表的更新会失败，从而导致各存储引擎create和drop表失败。同样的像ANALYZE TABLE和ALTER TABLE tbl_name ENGINE=engine_name这种操作也会失败，因为这些操作都要去更新数据字典表。
（4）mysqldump mysqlpump导出的内容影响
MySQL8.0之后，在使用mysqldump和mysqlpump导出数据时候，与之前有了一些不同，主要是以下几点：
　　之前版本的mysqldump和mysqlpump可以导出mysql系统库中的所有表的内容，8.0之后，只能导出mysql系统库中没有数据的数据字典表。
　　　　之前版本当使用 --all-databases 参数导出数据的时候，不加 --routines 和 --events 选项也可以导出触发器、存储过程等信息，因为这些信息都存放于proc和event表中，导出所有表即可导出这些信息。但是在8.0中，proc表和event表都不再使用，并且定义触发器、　　　　存储过程的数据字典表不会被导出，所以在8.0中使用mysqldump、mysqlpump导出数据的时候，如果需要导出触发器、存储过程等内容，一定需要加上 --routines 和 --events 选项。
　　　　之前版本中 --routines 选项导出的时候，备份账户需要有proc表的SELECT权限，在8.0中需要对所有表的SELECT权限
　　　　之前版本中，导出触发器、存储过程可以同时导出触发器、存储过程的创建和修改的时间戳，8.0中不再支持。
（5）新数据字典的局限性
　　MySQL8.0数据字典的改进有很多方便的特性，例如带来了原子DDL，提升了INFORMATION_SCHEMA的查询性能等，但是它并不是完美的，新版数据字典还是存在一些局限性：
　　　　通过手动mkdir的方式在数据目录下创建库目录，这种方式是不会被数据库所识别到。
　　　　DDL操作会花费更长的时间，因为之前的DDL操作是直接对.frm文件进行更改操作，只要写一个文件，现在是需要更新数据字典表，代表着需要将数据写到存储引擎、read log、undo log中。

数据字典变更影响

开启innodb_read_only会阻止所有对InnoDB存储引擎表create和drop等更新操作[数据字典表全为innodb],会导致各存储引擎create和drop表失败。同样的像ANALYZE TABLE和ALTER TABLE tbl_name ENGINE=engine_name这种操作也会失败，因为这些操作都要去更新数据字典表；
以前，mysql系统数据库中的表对 DML 和 DDL 语句可见。从 MySQL 8.0 开始，数据字典表是不可见的，不能直接修改或查询。

表统计信息变化
- 　　8.0以前，INFORMATION_SCHEMA查询 STATISTICS和表中的TABLES表统计信息直接从存储引擎检索统计信息，从 MySQL 8.0 开始，默认使用缓存表统计信息[数据文件结构变化带来的影响]。
- 　　系统变量定义缓存表统计信息过期之前的 information_schema_stats_expiry 时间段。默认值为 86400 秒（24 小时）。
- 　　要随时更新给定表的缓存值，请使用ANALYZE TABLE xxx
- 　　如果没有缓存统计信息或统计信息已过期，则在查询表统计信息列时从存储引擎中检索统计信息。
- 　　要始终直接从存储引擎检索最新统计信息，请设置 information_schema_stats_expiry 为0.
INFORMATION_SCHEMA 查询结果的行顺序变化排序
- 　　有几个INFORMATION_SCHEMA表是数据字典表的视图[待进一步验证具体指哪些]，它使优化器能够在这些基础表上使用索引。
- 　　因此，根据优化器的选择，INFORMATION_SCHEMA 查询结果的行顺序可能与之前的结果不同。如果查询结果必须具有特定的行排序特征，请包含一个ORDER BY子句

对INFORMATION_SCHEMA表查询返回列名大小写问题
- 　　对INFORMATION_SCHEMA表的查询可能会以与早期 MySQL 系列不同的字母大小写返回列名。应用程序应该以不区分大小写的方式测试结果集列名。
- 　　如果这不可行，一种解决方法是在选择列表中使用列别名，以所需的字母大小写返回列名。例如

MySQL5736
SELECT table_schema , table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'users';
列名 table_schema　　　　 table_name
performance_schema　　    users
sqle 　　　　　　　　　　   users
SELECT TABLE_SCHEMA , TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'users';
列名 TABLE_SCHEMA 　　 TABLE_NAME
performance_schema　　　users
sqle 　　　　　　　　　　 users

MySQL8027
SELECT TABLE_SCHEMA , TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'users';
列名 TABLE_SCHEMA 　　 TABLE_NAME
performance_schema 　　　users
sqle 　　　　　　　　　　   users

SELECT TABLE_SCHEMA AS table_schema, TABLE_NAME AS table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'users';
列名 table_schema 　　　　table_name
performance_schema 　　 users
sqle 　　　　　　　　　　 users

create .. like ..建表规则变化
- 　　CREATE TABLE dst_tbl LIKE src_tbl要求它 src_tbl是一个基表，如果它是一个INFORMATION_SCHEMA 表是数据字典表的视图，则失败。例：

mysql5736> CREATE TABLE events_test like INFORMATION_SCHEMA.TABLES;
Query OK, 0 rows affected (0.07 sec)

　　　　　　　　 MySQL 10.0.0.11:8027 ssl TEST SQL > CREATE TABLE events_test like INFORMATION_SCHEMA.TABLES;
　　　　　　　　 ERROR: 1347 (HY000): 'information_schema.TABLES' is not BASE TABLE

二、cache_sha2_password 作为首选身份验证插件

升级MySQL8.0后默认身份验证插件由之前的mysql_native_password变更为cache_sha2_password 此更改同时影响服务器和客户端库
default_authentication_plugin 系统变量的默认值从 mysql_native_password变为 caching_sha2_password。
libmysqlclient库将 caching_sha2_password其视为默认身份验证插件，而不是 mysql_native_password.

cache_sha2_password 兼容性问题和解决方案

[mysqld]
default_authentication_plugin=mysql_native_password

caching_sha2_password-兼容的客户端和连接器

MySQL 8.0（8.0.4 或更高版本）中的libmysqlclient客户端库。
MySQL 5.7（5.7.23 或更高版本）中的libmysqlclient客户端库。
MySQL 连接器/C++ 1.1.11 或更高版本或 8.0.7 或更高版本。
MySQL 连接器/J 8.0.9 或更高版本。
MySQL Connector/NET 8.0.10 或更高版本（通过经典 MySQL 协议）。
MySQL 连接器/Node.js 8.0.9 或更高版本。
PHP：X DevAPI PHP 扩展 (mysql_xdevapi) 支持 caching_sha2_password.
PHP：PDO_MySQL 和 ext/mysqli 扩展不支持 caching_sha2_password. 另外，与7.1.16之前的PHP版本和7.2.4之前的PHP 7.2一起使用时， default_authentication_plugin=caching_sha2_password 即使caching_sha2_password不使用也无法连接。
不建议调回default_authentication_plugin=mysql_native_password兼容方式，建议启用cache_sha2_password 升级最新的连接器驱动版本

三、配置更改

分区表
MySQL 存储引擎现在负责提供自己的分区处理程序，并且 MySQL 服务器不再提供通用分区支持。 InnoDB并且 NDB是唯一提供 MySQL 8.0 支持的本机分区处理程序的存储引擎。必须在升级服务器之前InnoDB更改使用任何其他存储引擎的分区表 - 将其转换为或NDB，或删除其分区 - 否则之后无法使用。
将MyISAM 表转换为 InnoDB 基本语法
ALTER TABLE table_name ENGINE=InnoDB;
字符集
默认字符集已从更改 latin1为utf8mb4
如需保留原字符集可以设置
使用my.cnf文件中的这些行启动服务器：
[mysqld]
character_set_server=latin1
collation_server=latin1_swedish_ci
lower_case_table_names参数
从 MySQL 8.0.11 开始，禁止 lower_case_table_names 使用与初始化服务器时使用的设置不同的设置来启动服务器。该限制是必要的，因为各种数据字典表字段使用的排序规则基于 lower_case_table_names 服务器初始化时定义的设置，并且使用不同的设置重新启动服务器会在标识符的排序和比较方式方面引入不一致。
如果设置为 0，则按指定存储表名，并且比较区分大小写。如果设置为 1，则表名以小写形式存储在磁盘上，并且比较不区分大小写。如果设置为 2，则表名按给定方式存储，但以小写形式进行比较。

四、服务器更改

账户管理
在 MySQL 8.0.11 中，删除了与帐户管理相关的几个已弃用的功能，例如使用 GRANT语句修改用户帐户的非特权特性、 NO_AUTO_CREATE_USERSQL 模式、 PASSWORD()函数和 old_passwords系统变量。
SQL模式
在 MySQL 8.0.11 中，这些已弃用的兼容性 SQL 模式已被删除：DB2, MAXDB, MSSQL, MYSQL323, MYSQL40, ORACLE, POSTGRESQL, NO_FIELD_OPTIONS, NO_KEY_OPTIONS, NO_TABLE_OPTIONS. 它们不能再分配给sql_mode系统变量或用作 mysqldump --compatible选项的允许值
空间数据类型
从 MySQL 8.0.3 开始，空间数据类型允许一个 SRID属性，以显式指示存储在列中的值的空间参考系统 (SRS)。
验证列中的所有值是否具有相同的 SRID。要确定几何列中包含的 SRID col_name，请使用以下查询：
SELECT DISTINCT ST_SRID(col_name) FROM tbl_name;
空间函数
MySQL 8.0.0 中删除了几个空间函数，因为空间函数命名空间更改 ST_为执行精确操作的函数实现了前缀，或者MBR为基于最小边界矩形执行操作的函数实现了前缀。在生成的列定义中使用已删除的空间函数可能会导致升级失败。在升级之前，运行mysqlcheck --check-upgradeST_删除空间函数，并用它们或MBR命名的替换替换任何你找到的。有关已删除空间函数的列表，请参阅 MySQL 8.0 中已删除的功能.
BACKUP_ADMIN权限
当执行就地升级到 MySQL 8.0.3 或更高版本时，该BACKUP_ADMIN权限会自动授予具有RELOAD该权限的用户。
切换二进制日志记录格式
从 MySQL 8.0.13 开始，由于基于行或混合的复制模式与基于语句的复制模式在处理临时表的方式上存在差异，因此在运行时切换二进制日志记录格式有了新的限制。

SET @@SESSION.binlog_format 如果会话有任何打开的临时表，则不能使用。
SET @@global.binlog_format如果任何复制通道有任何打开的临时表， SET @@persist.binlog_format则不能使用。SET @@persist_only.binlog_format如果复制通道具有打开的临时表，则允许，因为与不同PERSIST， PERSIST_ONLY不会修改运行时全局系统变量值。
SET @@global.binlog_format如果任何复制通道应用程序正在运行， SET @@persist.binlog_format则不能使用。这是因为该更改仅在其应用程序重新启动时才在复制通道上生效，此时复制通道可能已打开临时表。这种行为比以前更严格。SET @@persist_only.binlog_format如果任何复制通道应用程序正在运行，则允许。
从 MySQL 8.0.27 开始，配置会话设置 internal_tmp_mem_storage_engine 需要 SESSION_VARIABLES_ADMIN or SYSTEM_VARIABLES_ADMIN 权限。
从 MySQL 8.0.27 开始，克隆插件允许在进行克隆操作时对供体 MySQL 服务器实例进行并发 DDL 操作。以前，在克隆操作期间会持有一个备份锁，以防止供体上的并发 DDL。要恢复到先前在克隆操作期间阻塞供体上的并发 DDL 的行为，请启用该 clone_block_ddl 变量。请参阅第 5.6.7.4 节，“克隆和并发 DDL”。

五、InnoDB 更改

InnoDB 信息模式视图名称变更
INFORMATION_SCHEMA基于InnoDB系统表的视图被数据字典表的内部系统视图所取代。受影响 InnoDB INFORMATION_SCHEMA的视图已重命名：
表 2.16 重命名的 InnoDB 信息模式视图
旧名称新名字

✳升级到 MySQL 8.0.3 或更高版本后，更新任何引用先前InnoDB INFORMATION_SCHEMA视图名称的脚本。
zlib库版本从版本 1.2.3 提升到版本 1.2.11
zlib 1.2.11 中的 zlibcompressBound()函数返回的对压缩给定字节长度所需的缓冲区大小的估计值比 zlib 1.2.3 版中的稍高。该函数由确定创建压缩表或在压缩表中插入和更新行时允许的最大行大小的compressBound() 函数调用。因此，在早期版本中成功的行大小非常接近最大行大小的操作现在可能会失败。
为避免此问题，请测试压缩的语句 CREATE TABLE ... ROW_FORMAT=COMPRESSED, INSERT, and UPDATE 升级前在 MySQL 8.0 测试实例上具有大行的表。
innodb-directories
将使用绝对路径或在数据目录之外的位置创建的每个表文件和通用表空间文件的位置添加到innodb_directories 参数值中。
否则，InnoDB在恢复过程中无法找到这些文件。要查看表空间文件位置，请查询 INFORMATION_SCHEMA.FILES表：
SELECT TABLESPACE_NAME, FILE_NAME FROM INFORMATION_SCHEMA.FILES \G
不允许循环目录引用
从 MySQL 8.0.17 开始，该 CREATE TABLESPACE ... ADD DATAFILE子句不允许循环目录引用。例如，/../以下语句中的循环目录引用 ( ) 是不允许的：
CREATE TABLESPACE ts1 ADD DATAFILE ts1.ibd 'any_directory/../ts1.ibd';
为避免升级问题，请在升级到 MySQL 8.0.17 或更高版本之前从表空间数据文件路径中删除任何循环目录引用。
要检查表空间路径，请查询 INFORMATION_SCHEMA.INNODB_DATAFILES 表。
分区表与lower_case_table_names=1升级失败问题
在将二进制文件或软件包升级到 MySQL 8.0.17 后启动服务器时，在区分大小写的文件系统上从 MySQL 8.0.14、8.0.15 或 8.0.16 到 MySQL 8.0.17 的就地升级失败并出现以下错误，如果分区表存在并且 lower_case_table_names=1：
Upgrading from server version version_number with
partitioned tables and lower_case_table_names == 1 on a case sensitive file
system may cause issues, and is therefore prohibited. To upgrade anyway, restart
the new server version with the command line option 'upgrade=FORCE'. When
upgrade is completed, please execute 'RENAME TABLE part_table_name
TO new_table_name; RENAME TABLE new_table_name
TO part_table_name;' for each of the partitioned tables.
Please see the documentation for further information.
如果升级到 MySQL 8.0.17 时遇到此错误，请执行以下解决方法：

a.重新启动服务器 --upgrade=force以强制升级操作继续进行。
b.使用小写分区名称分隔符(#p#或 #sp#) 标识分区表文件名：
- 　　mysql> SELECT FILE_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_NAME LIKE '%#p#%' OR FILE_NAME LIKE '%#sp#%';
c.对于每个标识的文件，使用临时名称重命名关联的表，然后将表重命名回其原始名称。
- 　　mysql> RENAME TABLE table_name TO temporary_table_name;
- 　　mysql> RENAME TABLE temporary_table_name TO table_name;
d.验证没有分区表文件名小写分区名称分隔符（应返回空结果集）。
- 　　mysql> SELECT FILE_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_NAME LIKE '%#p#%' OR FILE_NAME LIKE '%#sp#%';
- 　　Empty set (0.00 sec)
e.在每个重命名的表上运行ANALYZE TABLE以更新 mysql.innodb_index_stats和 mysql.innodb_table_stats表中的优化器统计信息。

由于 MySQL 8.0.14、8.0.15 和 8.0.16 版本中仍然存在回归，因此不支持将分区表从 MySQL 8.0.14、8.0.15 或 8.0.16 导入 MySQL 8.0.17 - 敏感文件系统，其中 lower_case_table_names=1. 尝试这样做会导致“ Tablespace is missing for table ”错误。

构造表分区分隔符字符串
MySQL 在为表分区构造表空间名称和文件名时使用分隔符字符串。#p# 分隔符字符串在分区名称之前，“ #sp# ”分隔符字符串在子分区名称之前，如下所示：
schema_name.table_name#p#partition_name#sp#subpartition_name
table_name#p#partition_name#sp#subpartition_name.ibd

六、SQL 更改

ASC、DESC、GROUP BY 排序变更
从 MySQL 8.0.13 开始，已删除不推荐使用的子句ASC或 DESC限定符GROUP BY 以前依赖GROUP BY排序的查询可能会产生与以前的 MySQL 版本不同的结果。
要生成给定的排序顺序，请提供一个ORDER BY子句

FOREIGN KEY 定义
在 MySQL 5.7 中，子句表 FOREIGN KEY 定义不带指定关键字，会导致使用生成的约束名称。这种行为在 MySQL 8.0 中发生了变化，使用值而不是生成的名称。
述更改可能会导致依赖于先前外键约束命名行为的应用程序不兼容。

Changed Server Defaults
一些默认的参数值发生了变化

复制默认值
innodb_undo_tablespaces 系统变量的默认值由 0为 2。在 MySQL 8.0 中，最小值为innodb_undo_tablespaces 2，并且无法再在系统表空间中创建回滚段。因此，这是您无法恢复到 5.7 行为的情况。此更改的目的是能够自动截断撤消日志（请参阅下一项），回收（偶尔）长事务（例如mysqldump ）使用的磁盘空间。
innodb_autoinc_lock_mode 从1 （连续）更改为2（交错）
log_bin系统变量的默认值从更改OFF为 ON。
server_id系统变量的默认值从更改0为 1
expire_logs_days系统变量的默认值从更改0为 30。
master_info_repository和系统变量的默认值 relay_log_info_repository 从FILE变为 TABLE。
slave_pending_jobs_size_max 系统变量的默认值从更改16M为 128M。
gtid_executed_compression_period 系统变量的默认值从更改1000为 0。此更改确保mysql.gtid_executed表的压缩仅在需要时隐式发生。
组复制默认值
group_replication_autorejoin_tries 从 0 更改为 3，表示默认启用自动重新加入
group_replication_exit_state_action 更改ABORT_SERVER为 READ_ONLY。这意味着当成员退出组时，例如在网络故障后，实例变为只读，而不是被关闭。
group_replication_member_expel_timeout 从 0 变为 5，表示怀疑与该组失去联系的成员在 5 秒检测期后的 5 秒内承担开除责任。
对于生产环境，我们建议设置 innodb_dedicated_server为 ON.

posted on 2022-02-08 15:51 Jaylon 阅读(1237) 评论(0) 收藏举报

刷新页面返回顶部

导航

一、数据字典使用差异