mysql从5.7升级到8.0的注意事项
####################################
获取非innodb的表及更改语句
mysql> select concat(table_schema,'.',table_name) as table_name,engine,concat('alter table ',table_schema,'.',table_name,' engine=innodb;') from information_schema.tables where engine != 'innodb'and table_schema not in('mysql','sys','information_schema','performance_schema'); +------------+--------+----------------------------------------------------------------------+ | table_name | engine | concat('alter table ',table_schema,'.',table_name,' engine=innodb;') | +------------+--------+----------------------------------------------------------------------+ | glc.Test1 | MyISAM | alter table glc.Test1 engine=innodb; | | glc.user | MyISAM | alter table glc.user engine=innodb; | | glc.user_1 | CSV | alter table glc.user_1 engine=innodb; | +------------+--------+----------------------------------------------------------------------+ 3 rows in set (0.00 sec) mysql>
(1)sql_mode全局变量:在升级MySQL版本到8.0的过程中,需要关注sql_mode参数默认值的变化,8.0版本sql_mode不支持 NO_AUTO_CREATE_USER,要避免配置的sql_mode中带有 NO_AUTO_CREATE_USER
(2)mysql_native_password插件:MySQL8.0.4开始,默认身份认证开始改变。因为之前,MySQL的密码认证插件是“mysql_native_password”,而现在使用的是“caching_sha2_password”,在[mysqld]项中添加配置:default_authentication_plugin = mysql_native_password
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root';
MySQL 8相较于之前的版本,有许多新特性。以下是一些具体的新特性及详细说明:
- 支持窗口函数
MySQL 8中新增支持窗口函数,可以通过OVER子句来执行一些高级分析功能,如排名、行号和累加等。它允许在单个查询中处理和聚合数据,简化了复杂查询的编写和维护。
- 公用表表达式
MySQL 8中支持公用表表达式(CTE),也被称为WITH子句,允许在一个SQL语句中定义一个临时表,然后在后续的查询中引用它。这个功能可以使复杂的查询更加可读和易于维护。
- 多语句事务
MySQL 8中引入了多语句事务(Atomic DDL),允许将多个数据定义语句(DDL)作为一个事务原子地执行。这意味着如果DDL语句中的任何一个失败,整个事务都会被回滚,避免了数据不一致的问题。
- 加强JSON支持
MySQL 8增强了对JSON数据类型的支持,包括更快的JSON解析速度、新的JSON函数和操作符。JSON数据可以存储和查询非结构化数据,如文本、图像和视频等。
- 数据字典
MySQL 8引入了数据字典,用于存储元数据信息,包括表、列、索引等信息,以提高性能和可靠性。数据字典存储在系统表中,可以通过SQL查询访问。
- 安全性增强
MySQL 8增加了许多安全性特性,包括密码过期策略、角色和权限管理、密码掩盖等。其中最重要的是默认使用caching_sha2_password插件作为身份验证插件,提供更加安全的身份验证机制。
- 更好的性能和扩展性
MySQL 8改进了查询优化器和执行引擎,提高了性能和扩展性。其中包括更好的索引使用和优化、更好的查询计划生成和执行、更好的查询并发性和更好的存储引擎。
- 支持空间数据类型
MySQL 8增加了对空间数据类型的支持,包括几何类型和地理位置类型。这些类型支持空间数据的存储、查询和分析,如计算两点之间的距离等。
这些是MySQL 8的一些主要新特性,可以提高数据处理和管理的效率和安全性。
MySQL 8增加了许多安全性特性,以下是一些详细说明及举例:
- 密码过期策略
MySQL 8引入了密码过期策略,可以强制用户在一定时间内更改密码,以增加账户安全性。管理员可以使用ALTER USER语句设置密码过期时间和最长使用期限。
举例:设置一个用户的密码必须在90天后过期并且最长使用期限为365天:
ALTER USER 'user'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY MAXIMUM PASSWORD LIFETIME 365 DAY;
- 角色和权限管理
MySQL 8新增了角色和权限管理,可以将权限授予角色,然后将角色授予用户,以简化权限管理。管理员可以使用CREATE ROLE、GRANT和REVOKE语句来创建和管理角色。
举例:创建一个名为'admin'的角色,然后将SELECT和UPDATE权限授予该角色:
CREATE ROLE 'admin'; GRANT SELECT, UPDATE ON database.* TO 'admin';
- 密码掩盖
MySQL 8新增了密码掩盖,可以在SHOW PROCESSLIST和INFORMATION_SCHEMA.PROCESSLIST中隐藏密码,以增加安全性。管理员可以使用SET PERSIST command_display_internal_commands=OFF语句设置密码掩盖。
举例:设置密码掩盖,以隐藏SHOW PROCESSLIST中的密码:
SET PERSIST command_display_internal_commands=OFF;
- TLS加密
MySQL 8默认使用TLS协议加密所有网络连接,以提供更加安全的通信。管理员可以使用--ssl选项启用TLS加密,或者使用ALTER INSTANCE语句设置SSL选项。
举例:启用TLS加密:
mysql --ssl-mode=REQUIRED -u user -p
- 安全审计日志
MySQL 8引入
MySQL 8增加了许多安全性特性,其中一些包括:
- 密码过期策略
MySQL 8引入了密码过期策略,可以配置密码过期时间和密码复杂性要求。当用户的密码过期时,必须更改密码才能继续访问数据库。这提高了数据库的安全性,防止密码被长时间滥用。
- 角色和权限管理
MySQL 8中新增了角色和权限管理功能。通过角色,可以将一组权限分配给多个用户,简化了权限管理和维护。此外,MySQL 8还提供了更多的系统级角色,如SYS_ADMIN和SYS_REPL。
- 密码掩盖
MySQL 8中的密码掩盖功能可以隐藏客户端命令行中的敏感信息,如密码和证书。它可以防止未经授权的用户从客户端屏幕或日志中窃取敏感信息。
- 加密通信
MySQL 8支持SSL/TLS协议,可以加密客户端和服务器之间的通信,确保数据的保密性和完整性。MySQL 8还支持X.509证书验证,可以确保连接的安全性。
举例说明:
假设一个用户需要访问MySQL数据库并执行一些操作,但是管理员不想将所有权限授予该用户。在MySQL 8中,管理员可以为该用户创建一个角色,并将所需的权限分配给该角色。然后,管理员可以将该用户添加到该角色中,以便该用户可以使用该角色的权限。这可以简化权限管理,并防止权限滥用。例如:
CREATE ROLE `readonly`; GRANT SELECT ON `database`.* TO `readonly`; CREATE USER `user1`@`localhost` IDENTIFIED BY 'password'; GRANT `readonly` TO `user1`@`localhost`;
在上面的示例中,创建了一个名为readonly的角色,它被授予对database数据库中所有表的SELECT权限。然后,创建了一个名为user1的用户,并将readonly角色授予该用户。现在,user1可以使用readonly角色的SELECT权限,但不能修改或删除表中的数据。
MySQL 8增强了对JSON的支持,主要包括以下几个方面:
- JSON数据类型
MySQL 8引入了JSON数据类型,允许用户将JSON数据存储在数据库中。JSON数据类型支持索引和查询,可以更方便地进行数据检索和处理。用户可以使用JSON_EXTRACT和JSON_SET等函数来操作JSON数据。
- 新增JSON函数和运算符
MySQL 8新增了很多JSON函数和运算符,用于处理和操作JSON数据,包括JSON_ARRAY、JSON_OBJECT、JSON_SEARCH、JSON_KEYS等。这些函数和运算符可以更方便地对JSON数据进行操作。
- 对JSON数据的索引支持
MySQL 8对JSON数据的索引支持也有所增强,可以通过普通索引和全文索引来加速JSON数据的检索。使用普通索引可以加速具有指定值的JSON属性的查找,而全文索引可以加速JSON属性值的模糊匹配。
- 更好的JSON性能
MySQL 8的JSON处理性能也得到了提升,可以更快地进行JSON数据的解析和查询。在处理大量的JSON数据时,MySQL 8的性能比MySQL 5.7有了明显的提升。
总的来说,MySQL 8对JSON的支持得到了大幅度增强,使得MySQL 8更适合存储和处理半结构化数据,例如日志数据、NoSQL数据等。这个变化也使得MySQL 8可以更好地与现代Web应用程序和数据处理工具集成。
##################################