MySQL5.7 的新特点

1、安全性

MySQL 5.7 的目标是成为发布以来最安全的 MySQL 服务器，其在 SSL/TLS 和全面安全开发方面有一些重要的改变。

mysql.user表结构升级

MySQL5.7用户表mysql.user的plugin字段不允许为空，默认值是mysql_native_password，而不是mysql_old_password，不再支持旧密码格式。

从旧版本升级，直接将原有的SQL导入到MySQL5.7，需要进行user表结构的升级：

shell> /usr/local/mysql/bin/mysql_upgrade -uroot -p
重启MySQL

mysql.user表中已经没有了password字段，取而代之的是“authentication_string”

# 修改密码(或者忘记密码的处理方式)：

# 运行：

shell> mysqld_safe --skip-grant-tables &

# 如果此时不想被远程连接：

shell> mysqld_safe --skip-grant-tables --skip-networking &

# 更改密码：

mysql> update mysql.user set authentication_string=password('123AAAbbb2323#') where user='root' and Host = 'localhost';

mysql> flush privileges;

或者：

mysql> alter user 'root'@'localhost' identified by '123AAAbbb2323#';

mysql> flush privileges;

账号安全

用户长度最大为32字节，之前最大长度为16字节，并且CREATE USER 和 DROP USER 命令里实现了 IF [NOT] EXISTS 条件判断。

创建用户分2步:先通过create user 创建用户,再通过grant来授权。

# 先创建用户

mysql> CREATE USER 'dba'@'localhost' IDENTIFIED BY '123456';

# 或者

mysql> CREATE USER 'dba'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY '123456';

# 或者

mysql> create user ‘test1’@’%’ identified with mysql_native_password as ‘23AE809DDACAF96AF0FD78ED04B6A265E05AA257’

# 再授权

mysql> grant select,insert,update,delete on *.* to dba@localhost;

直接使用grant命令创建用户将会报如下警告：

Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement.

密码过期策略

增加密码过期机制，过期后需要修改密码，否则可能会被禁用，或者进入沙箱模式。

为用户设置密码过期时间，一定时间以后，强制用户修改密码。可以直接在create user的时候设置，也可以alter user设置：

PASSWORD EXPIRE DEFAULT　　 # 默认，过期时间受全局变量 default_password_lifetime 控制
- PASSWORD EXPIRE NEVER #永不过期
PASSWORD EXPIRE INTERVAL N DAY　 #N天后过期
- PASSWORD EXPIRE #过期

举例：

# 直接创建用户的时候设置 10天后过期：

mysql> create user dba@localhost identified by '123456' password expire interval 10 day;  

# 对已有用户设置 永不过期：

mysql> alter user dba@localhost password expire never;

默认密码过期时间受变量default_password_lifetime控制，为0表示默认永不过期

mysql> set global  default_password_lifetime=0;

Query OK, 0 rows affected (0.00 sec)

密码安全策略

为防止用户设置过简单的密码，mysql在5.6开始就已经支持了密码安全策略的插件。

开启密码安全策略，2种方法：

① 在配置文件里修改：

[mysqld]

plugin-load=validate_password.so

validate-password=FORCE_PLUS_PERMANENT

② 在线修改

mysql> INSTALL PLUGIN validate_password SONAME 'validate_password.so';

Query OK, 0 rows affected (0.01 sec)

开启安全策略的时候，不能为用户设置一个简单的密码：

mysql> grant all on *.* to dba@localhost identified by '123';

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

密码要求的强度手参数：validate_password_policy 的影响。validate_password_policy的值有：

0 or LOW：仅需需符合密码长度（由参数validate_password_length指定，默认为8）
1 or MEDIUM：满足LOW策略，同时还需满足至少有1个数字，小写字母，大写字母和特殊字符，默认。
2 or STRONG：满足MEDIUM策略，同时密码不能存在字典文件（dictionary file）中

对普通用户进行lock

锁定用户：

mysql> alter user admin@localhost account lock;

登陆报错：

ERROR 3118 (HY000): Access denied for user 'admin'@'localhost'. Account is locked.

解锁用户：

mysql> alter user admin@localhost account unlock;

初始化方式

高版本的MySQL，逐步废弃mysql_install_db的初始化方式

在5.7中，推荐使用bin/mysqld –-initialize对数据库进行初始化，初始化过程会创建带随机密码的 root@localhost 账号并不再创建test 库，初始化输出如下：

[Note] A temporary password is generated for root@localhost: KJ1foE6BFX;3

在初始化时如果使用–initial-insecure，则会创建空密码的 root@localhost 账号

新初始化的 root@localhost 账号，第一次登录时，需要修改新的密码才能正常使用

SSL 特性

MySQL 5.7版本提供了更为简单SSL安全访问配置，并且默认连接就采用SSL的加密方式

姜承尧文章中的测试案例显示开启SSL 性能开销在25% 左右

2、新支持

原生支持JSON

5.7版本之前，只能在varchar或是text等字符类型的列中存储json类型的字符串，并通过程序解析使用json字符串。这种做法当然有其缺陷：必须自行确认/解析数据、解决更新中的困难、或在执行插入操作时忍受较慢的速度。

5.7版本：增加了json列类型以及json_开头的函数，如json_type(),json_object(),json_merge()等。

从MySQL5.7.8之后，由于原生支持JSON，处理JSON文件就非常简单。现在执行插入与更新操作时可以自动确认了，而且效率很高；使用新定制的一系列功能访问对象与数组成员的速度也更快了。

举个栗子：

创建表json_test:

CREATE TABLE json_test(

  id INT(11) AUTO_INCREMENT PRIMARY KEY,

  person_desc JSON

)ENGINE INNODB;

插入一条记录：

INSERT INTO json_test(person_desc) VALUES ('{

    "programmers": [{

        "firstName": "Brett",

        "lastName": "McLaughlin",

        "email": "aaaa"

    }, {

        "firstName": "Jason",

        "lastName": "Hunter",

        "email": "bbbb"

    }, {

        "firstName": "Elliotte",

        "lastName": "Harold",

        "email": "cccc"

    }],

    "authors": [{

        "firstName": "Isaac",

        "lastName": "Asimov",

        "genre": "sciencefiction"

    }, {

        "firstName": "Tad",

        "lastName": "Williams",

        "genre": "fantasy"

    }, {

        "firstName": "Frank",

        "lastName": "Peretti",

        "genre": "christianfiction"

    }],

    "musicians": [{

        "firstName": "Eric",

        "lastName": "Clapton",

        "instrument": "guitar"

    }, {

        "firstName": "Sergei",

        "lastName": "Rachmaninoff",

        "instrument": "piano"

    }]

}');

查看插入的这行JSON数据有哪些KEY：

mysql> SELECT id,json_keys(person_desc) as "keys" FROM json_test\G

*************************** 1. row ***************************

  id: 1

keys: ["authors", "musicians", "programmers"]

1 row in set (0.00 sec)

可以看到里面有三个KEY，分别为authors，musicians，programmers。

又例如：

# 转换为json对象

mysql> select json_object('name','tom','age',20) ;

+------------------------------------+

| json_object('name','tom','age',20) |

+------------------------------------+

| {"age": 20, "name": "tom"}         |

+------------------------------------+

1 row in set (0.00 sec)

# 转换为json数组

mysql> select json_array('a','b',now()) ;

+------------------------------------------+

| json_array('a','b',now())                |

+------------------------------------------+

| ["a", "b", "2019-01-08 13:40:21.000000"] |

+------------------------------------------+

1 row in set (0.00 sec)

支持空间数据类型

空间数据类型常用于处理地理空间信息，它们描述了几何对象的真实坐标与形状。在MySQL中，可以使用像Point、LineString或Polygon之类的几何对象代表，还有一些很有用的空间函数k。

支持为表计算列

所谓计算列，就是通过其他列计算得到的值。

5.7之前版本实现计算列一般通过触发器实现。如下：

mysql> create trigger insr_tig before insert on t1 for each row set new.c3=new.c1+new.c2;

Query OK, 0 rows affected (0.09 sec)

mysql> create trigger upd_tig before update on t1 for each row set new.c3=new.c1+new.c2;

Query OK, 0 rows affected (0.01 sec)

5.7版本实现计算列只需要在建表/修改表时添加列的as参数就可以了，如下：

mysql> create table t2(id int auto_increment not null,c1 int ,c2 int ,c3 int as(c1+c2),primary key(id));

Query OK, 0 rows affected (0.03 sec)

mysql> insert into t2(c1,c2) values(2,3);

Query OK, 1 row affected (0.01 sec)

mysql> select * from t2;

+----+------+------+------+

| id | c1   | c2   | c3   |

+----+------+------+------+

|  1 |    2 |    3 |    5 |

+----+------+------+------+

1 row in set (0.00 sec)

mysql> update t2 set c1=10 where id=1;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t2;

+----+------+------+------+

| id | c1   | c2   | c3   |

+----+------+------+------+

|  1 |   10 |    3 |   13 |

+----+------+------+------+

1 row in set (0.00 sec)

Online rename index name

alter table ttt1 rename index idx to idxx;

mysql> show create table ttt1 ;

+-------+--------------------------------------------------------------------------------------+

| Table | Create Table                                                                         |

+-------+--------------------------------------------------------------------------------------+

| ttt1  | CREATE TABLE `ttt1` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `person_desc` json DEFAULT NULL,

  PRIMARY KEY (`id`),

  KEY `idx` (`id`)

) /*!50100 TABLESPACE `tb_space1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8 |

+-------+--------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

mysql> alter table ttt1 rename index idx to idxx;

Query OK, 0 rows affected (0.05 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table ttt1 ;

+-------+--------------------------------------------------------------------------------------+

| Table | Create Table                                                                         |

+-------+--------------------------------------------------------------------------------------+

| ttt1  | CREATE TABLE `ttt1` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `person_desc` json DEFAULT NULL,

  PRIMARY KEY (`id`),

  KEY `idxx` (`id`)

) /*!50100 TABLESPACE `tb_space1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8 |

+-------+--------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

动态修改varchar 长度大小

可以通过ALTER TABLE 语句以in place方式修改varchar的大小且无需table-copy

但存在限制：表示 varchar 长度的字节数不能变化(如果变更前使用1个字节表示长度，变更后也必须使用1个字节表示)，即只支持0~255内的或者255以上的范围变更(增大)，如果字段的长度从254增到256时就不能使用in-place算法，必须使用copy算法，否侧报错，这个原理就是varchar会在头部存储一个长度，如果小于255就是一个BYTES，8位；如果大于255当然就需要两个BYTES了。头部都变了，自然要重新copy table了。

需要注意的是减小 varchar(N)长度的大小必须使用copy类型

举个栗子：

# varchar(20) -- varchar(300)，只能使用copy算法

mysql> create table testtb2 (id int , vvv varchar(20)) ;

Query OK, 0 rows affected (0.02 sec)

mysql> alter table testtb2 change column vvv vvv varchar(300),algorithm=inplace;

ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

mysql> alter table testtb2 change column vvv vvv varchar(300),algorithm=copy;

Query OK, 0 rows affected (0.32 sec)

Records: 0  Duplicates: 0  Warnings: 0

新增临时表空间

为所有非压缩的innodb临时表提供一个独立的表空间，默认的临时表空间文件为ibtmp1，位于数据目录。我们可通过innodb_temp_data_file_path参数指定临时表空间的路径和大小

mysql> show global variables like 'innodb_temp_data_file_path';

+----------------------------+-----------------------+

| Variable_name              | Value                 |

+----------------------------+-----------------------+

| innodb_temp_data_file_path | ibtmp1:12M:autoextend |

+----------------------------+-----------------------+

1 row in set (0.01 sec)

MySQL每次重新启动时，会重新创建临时表空间

**注意 **:

从5.7.5开始，新增一个系统选项 internal_tmp_disk_storage_engine 可定义OnDisk临时表的引擎类型为 InnoDB，而在这以前，只能使用 MyISAM。(HEAP临时表，内部临时表有两种类型：HEAP临时表和OnDisk临时表)

而在5.6.3以后新增的参数default_tmp_storage_engine是控制create temporary table创建的外部临时表的存储引擎，在以前默认是MEMORY。(外部临时表：临时表只对当前用户可见，当前会话结束的时候，该临时表会自动关闭)

mysql> show global variables like '%_storage_engine';

+----------------------------------+--------+

| Variable_name                    | Value  |

+----------------------------------+--------+

| default_storage_engine           | InnoDB |

| default_tmp_storage_engine       | InnoDB |

| enforce_storage_engine           |        |

| internal_tmp_disk_storage_engine | InnoDB |

+----------------------------------+--------+

4 rows in set (0.01 sec)

sys schema

MySQL 5.7 版本新增了sys 数据库,该库通过视图的形式把information_schema 和performance_schema结合起来，查询出更加令人容易理解的数据，帮助DBA快速获取数据库系统的各种纬度的元数据信息，帮助DBA和开发快速定位性能瓶颈。

mysql client

旧版本的mysql客户端在执行Control+C，如果有SQL在运行会中断SQL，没有会退出mysql客户端，新版本也会中断SQL，但是不会退出。

3、InnoDB增强

在线调整InnoDB缓冲池大小

MySql5.7之前：要变更innodb_buffer_pool大小必须更改my.cnf文件后重启数据库服务器方生效

MySql5.7之后：变为动态参数，可以在线调整大小

nnodb_buffer_pool_size 缓池区大小
innodb_buffer_pool_chunk_size 缓池区块的大小默认128M
innodb_buffer_pool_instances 缓池区实例的个数

innodb_buffer_pool_size 必须是 innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances 倍数，如果不是mysql也会自动取整为倍数。

修改缓冲池的大小，会对运行的事务有影响，尽量在少操作的情况下修改。

mysql> show global status where variable_name='InnoDB_buffer_pool_resize_status';

+----------------------------------+----------------------------------------------------------------------+

| Variable_name                    | Value                                                                |

+----------------------------------+----------------------------------------------------------------------+

| Innodb_buffer_pool_resize_status | Size did not change (old size = new size = 134217728. Nothing to do. |

+----------------------------------+----------------------------------------------------------------------+

1 row in set (0.01 sec)

mysql> set global innodb_buffer_pool_size=268435456 ;

Query OK, 0 rows affected (0.00 sec)

mysql> SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status';

+----------------------------------+----------------------------------------------------+

| Variable_name                    | Value                                              |

+----------------------------------+----------------------------------------------------+

| Innodb_buffer_pool_resize_status | Completed resizing buffer pool at 190108 16:24:05. |

+----------------------------------+----------------------------------------------------+

1 row in set (0.00 sec)

支持为Innodb建立表空间

MySql5.7之前：具有系统共享表空间和为每个表建立的独立表空间

MySql5.7之后：支持create tablespace语法为一个表或者多个表建立公用表空间

举个栗子：

mysql> create tablespace tb_space1 add datafile 'tb_space11.ibd' engine=innodb;

Query OK, 0 rows affected (0.30 sec)

mysql> create table ttt1 (id INT(11) AUTO_INCREMENT PRIMARY KEY,person_desc JSON) tablespace tb_space1;

Query OK, 0 rows affected (0.06 sec)

mysql> create table ttt2 (id INT(11) AUTO_INCREMENT PRIMARY KEY,person_desc JSON) tablespace tb_space1;

Query OK, 0 rows affected (0.06 sec)

mysql> show create table ttt3 ;

+-------+-----------------------------------------------------------------------------------+

| Table | Create Table                                                                      |

+-------+-----------------------------------------------------------------------------------+

| ttt2  | CREATE TABLE `ttt2` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `person_desc` json DEFAULT NULL,

  PRIMARY KEY (`id`)

) /*!50100 TABLESPACE `tb_space1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8                     |

+-------+-----------------------------------------------------------------------------------+

1 row in set (0.00 sec)

tb_space11.ibd表空间文件被创建在数据目录下

InnoDB缓冲池导入导出

增加以下参数控制InnoDB缓冲池的导入导出:

innodb_buffer_pool_filename 如果开启InnoDB预热功能，停服务时，MySQL将InnoDB缓冲池中的热数据保存到数据目录中，默认文件名为ib_buffer_pool
innodb_buffer_pool_dump_pct 导出缓存池的百分比，默认25%
innodb_buffer_pool_dump_now 用手工方式立刻做一次把热数据dump到本地磁盘。
innodb_buffer_pool_dump_at_shutdown 正常停止服务时，InnoDB将InnoDB缓冲池中的热数据保存到本地硬盘，默认为OFF
innodb_buffer_pool_load_now 停止MySQL服务时，以手动方式立刻做一次将InnoDB缓存池中的热数据保存到本地硬盘，默认为OFF
innodb_buffer_pool_load_abort 如果开启该参数，即便开启InnoDB预热功能，启动服务时，MySQL也不会将本地硬盘的热数据加载到InnoDB缓冲池中，默认为OFF
innodb_buffer_pool_load_at_startup 如果开启该参数，启动MySQL服务时，MySQL将本地热数据加载到InnoDB缓冲池中，默认为OFF

支持多线程刷脏页

5.6.2版本中，MySQL将刷脏页的线程从master线程独立出来，5.7.4版本之后，MySQL系统支持多线程刷脏页，进程的数量由innodb_page_cleaners参数控制，该参数不能动态修改，最小值为1 ，最大值支持64，5.7.7以及之前默认值是1 ，5.7.8版本之后修改默认参数为4。

当启用多线程刷脏页，系统将innodb buffer instance脏页分配到各个空闲的刷脏页线程上，如果设置的innodb_page_cleaners>innodb_buffer_pool_instances,系统会自动重置为innodb_buffer_pool_instances大小。

4、复制增强

MySQL5.7在主从复制上面相对之前版本多了一些新特性，包括多源复制、基于组提交的并行复制、在线修改Replication Filter、GTID增强、半同步复制增强等

多源复制、基于组提交的并行复制、在线修改Replication Filter 的内容这里先略去

MySql5.7之前，要把基于日志点的复制方式变为基于GTID的复制方式或者把基于GTID的复制方式变为基于日志点的复制方式必须要重启master服务器。MySql5.7之后可以在线变更，不需要重启服务器。

5、弃用

以下特性在5.7里已经弃用，在后续的版本可能会移除。

–skip-innodb，–innodb=OFF, –disable-innodb 弃用了

因为5.7innodb是不能禁用的。

系统变量storage_engine 用 default_storage_engine代替

mysql.user表中的password字段被弃用，改为authentication_string

巴特西

MySQL5.7 的新特点

1、安全性

mysql.user表结构升级

账号安全

密码过期策略

密码安全策略

对普通用户进行lock

初始化方式

SSL 特性

2、新支持

原生支持JSON

支持空间数据类型

支持为表计算列

Online rename index name

动态修改varchar 长度大小

新增临时表空间

sys schema

mysql client

3、InnoDB增强

在线调整InnoDB缓冲池大小

支持为Innodb建立表空间

InnoDB缓冲池导入导出

支持多线程刷脏页

4、复制增强

5、弃用

最新文章

热门文章