MySQL8.0与MySQL5.7差异分析

前景

MySQL8.0与MySQL5.7有什么区别和坑？有必要升级MySQL8.0吗？MySQL 8.0是2018年4月20日发布的全球最受欢迎的开源数据库的一个非常令人兴奋的新版本，一些关键的增强包括：SQL窗口函数，公用表表达式，NOWAIT和SKIP LOCKED，降序索引，分组，正则表达式，字符集，成本模型和直方图。JSON扩展语法，新功能，改进排序和部分更新。

那么我们有必要及时升级到mysql8.0吗？我们先说说Mysql8.0的新特点：

Mysql8.0的新特点

使用JSON表函数，您可以使用JSON数据的SQL机制。
GIS地理支持。空间参考系统（SRS），以及SRS感知空间数据类型，空间索引和空间功能。
可靠性 DDL语句已变得原子性和崩溃安全，元数据存储在单个事务数据字典中。
由InnoDB提供支持！可观察性性能架构，信息架构，配置变量和错误记录的显着增强。
可管理性远程管理，撤消表空间管理和新的即时DDL。
安全 OpenSSL改进，新的默认身份验证，SQL角色，分解超级特权，密码强度等等。
性能 InnoDB在读/写工作负载，IO绑定工作负载和高争用“热点”工作负载方面明显更好。
增加了资源组功能，通过将用户线程映射到CPU，为用户提供一个选项，以针对特定硬件上的特定工作负载进行优化 。

然后我们分别从以下几点分别介绍MYSQL8.0和MYSQL5.7的区别：

1. 账户与安全

用户的创建与授权 在MySQL5.7的版本：

grant all privileges on *.* to 'bertram'@'localhost' identified by '1q2w3e4r';; #grant all privileges on *.* to '用户名'@'主机' identified by '密码'；
1

在MySQL8.0需要分开执行,否则使用以前的命令在8.0里面创建用户，会出现sql语法错误：

mysql> create user 'bertram'@'localhost' identified by '1q2w3e4r';
Query OK, 0 rows affected (0.04 sec) 
#create user '用户名'@'主机' identified by '密码'；

mysql> grant all privileges on *.* to 'bertram'@'localhost';
Query OK, 0 rows affected (0.04 sec) 
# grant all privileges on . to '用户名'@'主机'；
1
2
3
4
5
6
7

2. 认证插件更新

MySQL5.7 默认身份插件是 mysql_native_password
MySQL8.0 默认的身份插件是 caching_sha2_password
查看身份认证插件命令：show variables like 'default_authentication_plugin%';

mysql> show variables like 'default_authentication_plugin%';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| default_authentication_plugin | caching_sha2_password |
+-------------------------------+-----------------------+
1 row in set (0.01 sec)
1
2
3
4
5
6
7

身份认证插件可以通过以下2中方式改变：　
1）系统变量default_authentication_plugin去改变，在my.ini文件的[mysqld]下设置default_authentication_plugin=mysql_native_password
2）如果希望只是某一个用户通过mysql_native_password的方式认证，可以修改数据库mysql下面的user表的字段，执行以下命令：

mysql> alter user '用户名'@'主机' identified with mysql_native_password by '密码';
1

3. 密码管理

MySQL8.0的密码管理策略有3个变量

password_history #修改密码不允许与最近几次使用或的密码重复，默认是0，即不限制　　
password_reuse_interval #修改密码不允许与最近多少天的使用过的密码重复，默认是0,即不限制
password_require_current #修改密码是否需要提供当前的登录密码，默认是OFF,即不需要；如果需要，则设置成ON

查询当前MySQL密码管理策略相关变量，使用以下命令：

mysql> show variables like 'password%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| password_history         | 0     |
| password_require_current | OFF   |
| password_reuse_interval  | 0     |
+--------------------------+-------+
3 rows in set (0.00 sec)


1）设置全局的密码管理策略，在my.ini配置文件中，设置以上3个变量的值这种设置方式，需要重启mysql服务器；某些生产环境不允许重启，MySQL8.0提供了关键字persist持久化：
mysql> set persist password_history=6; #这条命令会在下次服务器重启的时候除了读取全局配置文件，还会读取这个配置文件,这条配置就会被读入从而达到持久化的目的

2）针对某一个用户单独设置密码管理策略
mysql> alter user '用户名'@'主机' password history 5; #这个用户的password_history 就被设置成了5

查看一下：
mysql> show user,host,Password_reuse_history from user;

查看某一张的字段的所有字段，使用以下命令:
mysql> desc 表名;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

对密码相关参数的修改

全局设置
第一种方式：修改 mysql 的配置文件，全局生效

## 找到mysql的配置文件 my.cnf, 我的在 /etc/my.cnf
vi /etc/my.cnf
# 新密码不能和前面三次的密码相同
password_history = 3 ; 
# 新密码不能和前面九十天内使用的密码相同
password_reuse_interval = 90 ; 
# 默认为off；为on 时 修改密码需要用户提供当前密码 (开启后修改密码需要验证旧密码，root 用户不需要)
password_require_current = on ;
1
2
3
4
5
6
7
8

第二种方式：或者使用 命令set persist password_history=6； 这个具体的实现是增加了一个配置文件

4. 角色管理

角色：一组权限的集合
一组权限赋予某个角色，再把某个角色赋予某个用户，那用户就拥有角色对应的权限
1）创建一个角色
mysql> create role '角色1';

2）为这个角色赋予相应权限
mysql> grant insert,update on . to '角色1';

3）创建一个用户
mysql> create user '用户1' identified by '用户1的密码';

4）为这个用户赋予角色的权限
mysql> grant '角色1' on . to '用户1'；

执行完上面4步，用户1就拥有了插入与更新的权限
　　
5）再创建1个用户
mysql> create user '用户2' identified by '用户2的密码';

6）为这个用户赋予同样的角色
mysql> grant '角色1' on . to '用户2';

执行完上面2步，用户2也用了角色1的权限，即插入与更新
查看用户权限，执行以下命令：
mysql> show grants for '用户名';

7）启用角色,设置了角色，如果不启用，用户登录的时候，依旧没有该角色的权限
mysql> set default role '角色名' to '用户名';

8）如果一个用户有多个角色，使用以下命令
mysql> set default role all to '用户名';

MySQL中与用户角色相关的表：mysql.default_roles、mysql.role_edges。
9）撤销权限
mysql> revoke insert,update on . from '角色名';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

参考：文章

先自我介绍一下，小编13年上师交大毕业，曾经在小公司待过，去过华为OPPO等大厂，18年进入阿里，直到现在。深知大多数初中级java工程师，想要升技能，往往是需要自己摸索成长或是报班学习，但对于培训机构动则近万元的学费，着实压力不小。自己不成体系的自学效率很低又漫长，而且容易碰到天花板技术停止不前。因此我收集了一份《java开发全套学习资料》送给大家，初衷也很简单，就是希望帮助到想自学又不知道该从何学起的朋友，同时减轻大家的负担。添加下方名片，即可获取全套学习资料哦