自助建站网站,广州番禺区严格控制人员流动,企业网站模板用哪个,公司做网站比较好的文章目录 前言1. 运维管理 1.1 可持久化变量1.2 管理员端口1.3 资源组1.4 数据库粒度只读1.5 show processlist 实现方式1.6 加速索引创建速度1.7 控制连接的内存使用量1.8 克隆插件1.9 mysqldump 新增参数1.10 慢日志增强1.11 快速加列1.12 InnoDB 隐藏主键1.13 Redo 配置1.14…文章目录 前言1. 运维管理 1.1 可持久化变量1.2 管理员端口1.3 资源组1.4 数据库粒度只读1.5 show processlist 实现方式1.6 加速索引创建速度1.7 控制连接的内存使用量1.8 克隆插件1.9 mysqldump 新增参数1.10 慢日志增强1.11 快速加列1.12 InnoDB 隐藏主键1.13 Redo 配置1.14 undo 配置1.15 事务调度 CATS1.16 自增主键持久化1.17 在线回收临时表空间1.18 自适应参数 2. 开发相关 2.1 默认字符集2.2 GROUP BY ASC/DESC 语法2.3 公用表达式2.4 窗口函数2.5 check 约束2.6 隐藏列2.7 不可见索引2.8 降序索引2.9 函数索引2.10 VALUES 语法2.11 NOWAIT 和 SKIP LOCKED2.12 分区表支持 3. 复制相关 3.1 Binlog 中包含元数据3.2 Binlog 记录事务提交时间戳3.3 Binlog 过期时间3.4 ReplicaSet 复制方案 4. 安全相关 4.1 角色管理4.2 caching_sha2_password4.3 print_identified_with_as_hex4.4 更细粒度的权限控制4.5 生成随机密码4.6 双重密码支持4.7 密码错误锁定用户4.8 账号备注信息 5. 优化器相关 5.1 hash join5.2 直方图 6. 推荐升级流程总结
前言
MySQL 5.7 在 2023 年 10 月 31 日起已经终止软件生命周期了意味着 MySQL 官方将不再提供对 MySQL 5.7 版本的技术支持和更新。8.0 版本成为官方长期支持版本提供了很多新特性本文将详细解读 MySQL 8.0 版本的新特性为 MySQL 版本升级作参考和指导。
1. 运维管理
1.1 可持久化变量
MySQL 5.7 版本使用 SET 命令修改参数后需要再将参数写入到配置文件中否则重启后又恢复默认配置。MySQL 8.0 提供SET PERSIST 语法可以将参数持久化到配置文件中用户无需再对配置文件进行编辑是一个对云厂商比较友好的特性。
# 持久化变量同时修改变量内存值
SET PERSIST max_connections 1000;# 只持久化修改不修改内存中变量值适用于修改只读参数
SET PERSIST_ONLY back_log 100;# 清空 mysqld-auto.cnf 中的变量持久化修改参数的原理是 MySQL 维护了一个配置文件即 mysqld-auto.cnf
RESET PERSIST;# 删除 mysqld-auto.cnf 中的特定变量如果变量不存在会报错
RESET PERSIST system_var_name;# 删除 mysqld-auto.cnf 中的特定变量不存在则不会报错。
RESET PERSIST IF EXISTS system_var_name;当使用 SET PERSIST 命令时变量更改将更新到数据目录中的 mysqld-auto.cnf 选项文件。mysqld-auto.cnf 是仅在第一次执行 PERSIST 或 PERSIST_ONLY 语句时创建的 JSON 格式文件。内容如下 需要的授权
GRANT SYSTEM_VARIABLES_ADMIN, PERSIST_RO_VARIABLES_ADMIN, SHUTDOWN on *.* to adminlocalhost;参考资料Persisted System Variables 1.2 管理员端口
MySQL 8.0 提供了管理员的连接地址和端口默认的管理员端口是 33062相当于是为管理员连接提供了专用通道且没有连接数限制这样即使 MySQL 连接数满了也可以连接上去修改 max_connections 的大小。
[(none)]show variables like %admin%;
--------------------------------------------------
| Variable_name | Value |
--------------------------------------------------
| admin_address | |
| admin_port | 33062 |
--------------------------------------------------使用管理员端口的用户需要拥有 SERVICE_CONNECTION_ADMIN 权限的用户连接。 参考资料Administrative Connection Management 1.3 资源组
MySQL 是单进程多线程的程序在 8.0 之前所有的线程优先级都是相同的并且所有的线程资源都是共享的。8.0 之后推出 RESOURCE GROUP 特性DBA 可以通过资源组的方式修改线程优先级以及所使用的资源目前仅支持 CPU 资源。
业务上经常会有一些跑批操作这些跑批 SQL 往往较复杂且涉及数据量大执行起来非常消耗资源往往是业务低峰夜间执行避免影响其它线程运行避免 CPU 跑满堵塞其它线程导致请求进不去。有了 RESOURCE GROUP 特性我们可以创建一个资源组让跑批任务限制在固定的一个或多个 CPU 核上避免影响其它线程。
由于文章篇幅如果想使用或者详细了解该特性的朋友请参考下方文档。 详细解读8.0 新特性 - RESOURCE GROUP 1.4 数据库粒度只读
MySQL 8.0 可设置数据库粒度的只读模式禁止所有更新操作适用于数据迁移场景。
# 开启只读模式
ALTER DATABASE db_name READ ONLY 1;
# 关闭只读模式
ALTER DATABASE db_name READ ONLY 0;1.5 show processlist 实现方式
show processlist 默认是从全局的线程管理器中获取线程信息这种实现方式会持有全局互斥锁对数据库的性能有一定的影响官方推荐使用 performance_schema.processlist 的方式查询这种方式不会持有全局锁。
MySQL 8.0.22 中引入 performance_schema_show_processlist 参数设置为 ON 则表示使用 performance_schema.processlist 的方式实现 show processlist 默认为 OFF。 参考资料The processlist Table 1.6 加速索引创建速度
MySQL 8.0.27 中引入 innodb_ddl_threads 参数。该参数用于 MySQL 创建二级索引时在排序和构建阶段使用线程的个数一定程度上可以加快索引的创建速度默认为 4 个。
大表创建索引时可以给流量较低的备库开启该参数从而减小主备延迟。 详细解读8.0 新特性 - innodb_ddl_threads 1.7 控制连接的内存使用量
MySQL 8.0.28 开始引入 connection_memory_limit 变量可以限制单个连接内存的使用量如果超过最大值连接会被断开。
1.8 克隆插件
克隆插件Clone Plugin是 MySQL 8.0.17 引入的一个重大特性可以从本地或者远程克隆数据。
如果在 8.0.17 之前想要给 MySQL 复制拓扑中添加一个新节点只支持 Binlog 一种恢复方式如果新节点所需要的 Binlog 在集群中不存在就只能先借助备份工具进行全量备份恢复再配置增量同步。这种方式虽然能达到添加新节点的目的但总归是需要借助外部工具相对来说是有一定的使用门槛和工作量。
Clone 插件支持本地克隆和远程克隆可以很方便的帮助我们添加一个新的节点也可以作为 Innodb 引擎的物理备份工具。
克隆插件之前有文章详细介绍过感兴趣的可以阅读。 推荐阅读MySQL 8.0 Clone Plugin 详解 1.9 mysqldump 新增参数
在 MySQL 8.0.30 中mysqldump 新增了 mysqld-long-query-time 选项允许自定义 long_query_time 的会话值。这样可以避免将逻辑备份相关的语句记录在慢日志中。 参考资料mysqldump_mysqld-long-query-time 1.10 慢日志增强
在 MySQL 8.0.14 中引入 log_slow_extra 参数开启后在慢日志中会记录更加详细的信息例如语句异常终止的信号、语句返回数据的字节大小、排序的行数等等。
该参数默认是关闭的开启后记录的信息更多意味着慢日志将膨胀的更快额外的字段信息可参考下方资料。 参考资料log_slow_extra 1.11 快速加列
从 MySQL 8.0.12 开始OnlineDDL 开始支持 INSTANT 算法可以使用该算法进行加字段操作只需修改表的元数据信息操作瞬间就可以完成。不过在 8.0.29 之前列只能加在表的最后的位置。从 8.0.29 开始移除了该限制快速加列支持加到表的任何位置并且删除列也支持 INSTANT 算法。
通过只修改数据字典的方法来实现大表快速加列避免之前加列操作必须做的数据拷贝从而大幅缩小大表加列所需的时间减少对系统的影响。 推荐阅读8.0 新特性 - Instant Add Column 1.12 InnoDB 隐藏主键
MySQL Innodb 引擎采用的是 IOT索引组织表存储方式主键的重要性就不言而喻。在早期版本用户如果没有显式指定主键会自动生成隐藏主键 row_id 来组织 B 树隐藏主键 row_id 只会作用于 MVCC、Redo 和 Undo 等内部机制无法在复制模块中使用。一些大数据组件生成的表结构往往都没有主键设计会出现全表扫描回放问题带来非常大的主备延迟。
而且 MySQL 一些周边软件 gh-ost、DTS 服务等都有依赖主键设计没有主键会降低数据库的可维护性。
MySQL 8.0.30 引入 sql_generate_invisible_primary_key 参数可为没有显式设置主键的表创建一个隐式的主键。该特性之前的文章中有详细解读。 推荐阅读8.0 新特性 - Generated Invisible Primary Key 1.13 Redo 配置
在 MySQL 8.0.30也包含 5.7 版本 版本 Redo 的配置相关的参数主要有下面两个 innodb_log_files_in_groupRedo 文件的个数默认为 2。 innodb_log_file_sizeRedo 在磁盘上单个文件的大小。 du -sh ib_logfile* 128M ib_logfile0 128M ib_logfile1 cat /etc/my.cnf | grep innodb_log_file_size innodb_log_file_size 128M
这两个参数不支持在线修改必须重启生效。
MySQL 8.0.30 版本发布后提供新参数 innodb_redo_log_capacity 代替之前两个参数不过原来两个参数并没有取消会自动计算转换为 innodb_redo_log_capacity 抛出警告
[Warning] [MY-013907] [InnoDB] Deprecated configuration parameters innodb_log_file_size and/or innodb_log_files_in_group have been used to compute innodb_redo_log_capacity1073741824. Please use innodb_redo_log_capacity instead.支持在线修改
set global innodb_redo_log_capacity1073741824;推荐阅读8.0 新特性 - innodb_redo_log_capacity 1.14 undo 配置
MySQL 8.0 版本默认开启 innodb_undo_tablespaces 参数将 undo 日志从系统表空间 ibdata 文件中独立出来这样就可以动态调整 undo 大小便于收缩 undo 空间否则必须重新初始化实例。
除了默认会独立出 undo 空间外还提供了 SQL 语句可用于管理 undo 表空间。 参考资料Undo Tablespaces 1.15 事务调度 CATS
MySQL 8.0 中的 InnoDB 引擎使用了 Contention-Aware Transaction Scheduling (CATS) 竞争感知事务调度算法可以提升高并发场景下数据库的吞吐提升数据库整体性能。
当多个事务同时获取一把锁那么哪个事务先获得这把锁在 MySQL 8.0 版本之前使用的是 FIFO 先进先出算法该算法会将锁优先分配给最先进入等待队列中的事务。在 MySQL 8.0 中则会计算每个事务堵塞的事务数最后将锁分配给堵塞事务最多的事务。 推荐阅读Transaction Scheduling 1.16 自增主键持久化
MySQL 5.7 版本 auto_increment 是存储在内存中的这就导致每次重启 MySQL 都会重新计算该值计算逻辑是取该字段的 MAX VALUE 请看下方 case
-- 创建一张测试表id 为自增主键
create table t1(id bigint auto_increment primary key,c1 varchar(10) not null,c2 varchar(10) not null
);插入 8 行记录
insert into t1(c1, c2) value (a, b),(a, b),(a, b),(a, b),(a, b),(a, b),(a, b),(a, b);此时 AUTO_INCREMENT 的值为 9下一条写入会被分配自增 ID 为 9
select TABLE_NAME, AUTO_INCREMENT from information_schema.TABLES where TABLE_NAME t1;TABLE_NAME
AUTO_INCREMENT
t1
9
然后删除后 4 条记录
delete from t1 where id 4;重启数据库
$ service mysqld restart
Shutting down MySQL.... SUCCESS!
Starting MySQL... SUCCESS! 查询 t1 表的自增 ID
select TABLE_NAME, AUTO_INCREMENT from information_schema.TABLES where TABLE_NAME t1;TABLE_NAME
AUTO_INCREMENT
t1
5
可以看到 MySQL 在重启之后自增列的值被重置了。这个现象被称之为 BUG 主要是在现在的互联网业务中支撑业务数据的不仅仅只有 MySQL还可能会有 RedisRabbitMQ 等缓存和消息队列或者是单独的 MySQL 日志归档库自增列可能会被用来作为关联各个存储之间的“逻辑外键”当 MySQL 重启之后新写入的数据可能会用到已经被删除的值导致数据库中的数据和外部系统之间的数据出现错误的关联。另外一种问题场景就是 MySQL 自身各个表之间有外键关系但是没有建立外键约束也会遇到类似的问题。 该 BUG 已在 MySQL 8.0 版本修复推荐阅读。 Auto-Increment Counter Persistence in MySQL 8: Comparing the Evolution From MySQL 5.7 1.17 在线回收临时表空间
在 MySQL 5.7 版本中用户创建的临时表和磁盘临时表会存储在全局的临时表空间 ibtmp1 中会话结束后并不会释放临时空间只会打上删除的标记可复用如果要释放临时表空间需要重启实例。
MySQL 8.0 新增 innodb_temp_tablespaces_dir 参数默认在数据目录中 #innodb_tmp 命名的一个临时表空间池一个会话最多会分配两个临时表空间分别用来存储用户创建的临时表和优化器内部创建的临时表。当会话连接断开时会截断这两个临时表空间。 参考资料 Temporary Tablespaces 1.18 自适应参数
MySQL 8.0.14 推出了 innodb_dedicated_server 参数开启该参数后 MySQL 会根据服务器的配置自适应一些关键参数。比如 buffer pool 的大小和 redo log 的大小是一个对云厂商比较友好的特性。如果你的 MySQL 是部署在一个专属的数据库服务器中可以开启该参数。 参考资料innodb_dedicated_server 2. 开发相关
2.1 默认字符集
MySQL 8.0 默认字符由 latin1 调整为 utf8mb4值得注意的是在 MySQL 8.0 中utf8mb4 的默认排序规则是 utf8mb4_0900_ai_ci在 MySQL 5.7 中则是 utf8mb4_general_ci也就是如下两个参数的默认值。
[mysqld]
character_set_serverutf8mb4
collation_serverutf8mb4_0900_ai_ci从 MySQL 5.7 升级到 8.0 的话可以将参数设置为 5.7 相同的值数据迁移完成后可以改回或者一直沿用 5.7 的参数。 参考资料MySQL 8.0 Configuration Changes 2.2 GROUP BY ASC/DESC 语法
MySQL 8.0 版本不支持 GROUP BY ASC/DESC 语法如果需要对分组的列进行排序需要显式指定排序列。
-- MySQL 5.7
select name,count(*) from table_name GROUP BY name desc;-- MySQL 8.0
select name,count(*) from table_name GROUP BY name ORDER BY name desc;推荐阅读MySQL 8.0 SQL Change 2.3 公用表达式
公用表达式 简称WITH (Common Table Expressions)是 MySQL 8.0 新增的语法可以定义一个临时的结果集合全局可用。某些特殊场景下使用公用表达式可以让 SQL 更加简洁同时也提升了 SQL 的可读性。
WITH cte (col1, col2) AS
(SELECT 1, 2UNION ALLSELECT 3, 4
)
SELECT col1, col2 FROM cte;-- 输出
------------
| col1 | col2 |
------------
| 1 | 2 |
| 3 | 4 |
------------参考资料Common Table Expressions 2.4 窗口函数
窗口函数Window Function是一个提升 MySQL 数分能力的特性可针对一组进行计算并为每行返回一个结果这一点和聚合函数不同。聚合函数只能为每个分组返回一个结果窗口函数中的 over 子句定义了要计算行的行窗口。
sales 为销售表有每个城市和商品的利润
mysql SELECT * FROM sales ORDER BY country, year, product;
-----------------------------------
| year | country | product | profit |
-----------------------------------
| 2000 | Finland | Computer | 1500 |
| 2000 | Finland | Phone | 100 |
| 2001 | Finland | Phone | 10 |
| 2000 | India | Calculator | 75 |
| 2000 | India | Calculator | 75 |
| 2000 | India | Computer | 1200 |
| 2000 | USA | Calculator | 75 |
| 2000 | USA | Computer | 1500 |
| 2001 | USA | Calculator | 50 |
| 2001 | USA | Computer | 1500 |
| 2001 | USA | Computer | 1200 |
| 2001 | USA | TV | 150 |
| 2001 | USA | TV | 100 |
-----------------------------------如果想计算总利润可以使用 sum 函数如果想要知道每个国家的利润可以使用 group by 分组再使用 sum 函数。
mysql SELECT SUM(profit) AS total_profitFROM sales;
--------------
| total_profit |
--------------
| 7535 |
--------------
mysql SELECT country, SUM(profit) AS country_profitFROM salesGROUP BY countryORDER BY country;
-------------------------
| country | country_profit |
-------------------------
| Finland | 1610 |
| India | 1350 |
| USA | 4575 |
-------------------------聚合函数则可以使用聚会函数为每一行返回一个结果如下面的 case 可以把总利润和国家利润计算出来放在每条记录的后面便于对比。
mysql SELECTyear, country, product, profit,SUM(profit) OVER() AS total_profit,SUM(profit) OVER(PARTITION BY country) AS country_profitFROM salesORDER BY country, year, product, profit;
-----------------------------------------------------------------
| year | country | product | profit | total_profit | country_profit |
-----------------------------------------------------------------
| 2000 | Finland | Computer | 1500 | 7535 | 1610 |
| 2000 | Finland | Phone | 100 | 7535 | 1610 |
| 2001 | Finland | Phone | 10 | 7535 | 1610 |
| 2000 | India | Calculator | 75 | 7535 | 1350 |
| 2000 | India | Calculator | 75 | 7535 | 1350 |
| 2000 | India | Computer | 1200 | 7535 | 1350 |
| 2000 | USA | Calculator | 75 | 7535 | 4575 |
| 2000 | USA | Computer | 1500 | 7535 | 4575 |
| 2001 | USA | Calculator | 50 | 7535 | 4575 |
| 2001 | USA | Computer | 1200 | 7535 | 4575 |
| 2001 | USA | Computer | 1500 | 7535 | 4575 |
| 2001 | USA | TV | 100 | 7535 | 4575 |
| 2001 | USA | TV | 150 | 7535 | 4575 |
-----------------------------------------------------------------参考资料Window Function Concepts and Syntax 2.5 check 约束
MySQL 8.0 支持 check 约束请看下方示例在 age 字段可以创建一个约束年龄必须小于 150 岁。
CREATE TABLE user_test(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT 主键,name VARCHAR(10) not null COMMENT 姓名,age int UNSIGNED CHECK(age150) COMMENT 年龄
);插入 200 岁的张三会返回异常。
insert into user_test values (1, 张三, 200);
-- 异常
ERROR 3819 (HY000): Check constraint user_test_chk_1 is violated.参考资料CHECK Constraints 2.6 隐藏列
隐藏列是 MySQL 8.0.23 引入的新特性可以设置列的可见性除非显式指定隐藏属性的字段否则 select * from 不会显示。
CREATE TABLE t1 (i INT,j DATE default 2024-01-01 INVISIBLE
) ENGINE InnoDB;写入三条数据
insert into t1 values (1),(2),(3);select * from t1;
------
| i |
------
| 1 |
| 2 |
| 3 |
------select i, j from t1;
------------------
| i | j |
------------------
| 1 | 2024-01-01 |
| 2 | 2024-01-01 |
| 3 | 2024-01-01 |
------------------参考资料Invisible Columns 2.7 不可见索引
可以修改索引的可见性如果调整索引不可见那么优化器会默认忽略该索引。该特性在索引维护中非常有用大表中维护一个索引的代价还是比较大的。
讲一个笔者亲身经历过的故障吧研发人员要删除一张 20G 大表中的一个索引执行完后数据库 CPU 立马飙升原来还有 SQL 依赖刚才删除的索引那索引被删掉后这业务的 SQL 就全部全表扫描了数据库 CPU、IO 立马打满了。此时加索引也很难加上必须得停掉相关业务的 SQL 重新添加索引影响范围很大。
如果有不可见索引那么删除一个索引的流程就会安全很多可以先调整索引的可见性观测一段时间期间如果有任何问题则打开索引即可毕竟大表加一个索引也需要时间修改索引可见性几乎瞬间完成。
-- 查询元数据哪些索引不可见
select table_schema, table_name, index_name, column_name, is_visible
from information_schema.statistics
where is_visible no;-- 修改索引可见
alter table xx alter index idx_xx visible;
-- 修改索引不可见
alter table xx alter index idx_xx invisible;
-- 创建一个不可见索引
alter table xx add index idx_xx(column_name) invisible;推荐阅读Invisible Indexes 2.8 降序索引
MySQL 8.0 之前的索引排序规则之前只允许 ASC 存储创建时指定 DESC 也会被忽略现在创建索引时可以指定索引的排序方向便于应对一些排序场景避免文件排序。 推荐阅读Descending Indexes 2.9 函数索引
MySQL 8.0 提供了函数索引可应对使用函数导致索引失效的场景详细可参考下方案例。 推荐阅读MySQL 函数导致索引失效应对策略 2.10 VALUES 语法
values 是 MySQL 8.0.19 引入的一个 DML 语法属于一个种表值构造语法。
mysql VALUES ROW(1,-2,3), ROW(5,7,9), ROW(4,6,8) ORDER BY column_1;
------------------------------
| column_0 | column_1 | column_2 |
------------------------------
| 1 | -2 | 3 |
| 4 | 6 | 8 |
| 5 | 7 | 9 |
------------------------------
3 rows in set (0.00 sec)推荐阅读VALUES Statement 2.11 NOWAIT 和 SKIP LOCKED
MySQL 8.0 版本在 SELECT…FOR SHARE 和 SELECT…FOR UPDATE 语句中引入了 NOWAIT 和 SKIP LOCKED 选项用来解决电商场景中的热点行问题。
CREATE TABLE test_semi (a int NOT NULL,b int DEFAULT NULL,c int DEFAULT NULL,PRIMARY KEY (a)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci;insert into test_semi values(10, 1, 0),(11, 2, 0),(12,1,0),(13,2,0),(14,1,0);使用 Session 1 开启一个事务
begin;
select * from test_semi where b 1 for update;
----------------
| a | b | c |
----------------
| 10 | 1 | 0 |
| 12 | 1 | 0 |
| 14 | 1 | 0 |
----------------Session 2 尝试查询全表
-- 堵塞
select * from test_semi for update;-- 跳过了锁定的行
select * from test_semi for update SKIP LOCKED;
----------------
| a | b | c |
----------------
| 11 | 2 | 0 |
| 13 | 2 | 0 |
------------------ 需要加的锁被其他事务持有则直接返回异常不会等待锁超时
select * from test_semi for update NOWAIT;
ERROR 3572 (HY000): Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.推荐阅读Locking Reads 2.12 分区表支持
通用的分区接口已从代码中移除在 MySQL 8.0 中如果要使用分区表只能使用 InnoDB 存储引擎。
3. 复制相关
3.1 Binlog 中包含元数据
MySQL 5.7 中 Binlog 是不记录元数据的使用 mysqlbinlog 解析出来的 binlog 文本字段是用 1、2 按照顺序排列的没有字段名称。MysQL 8.0.1 版本提供了 binlog_row_metadata 参数设置为 full 的时候会在 Binlog Table_map event 中记录字段信息。
使用 mysqlbinlog 解析时需要指定 --print-table-metadat 选项。
mysqlbinlog -vv --base64-outputdecode-rows --print-table-metadata mysql-bin.000003 #240729 11:12:54 server id 553306 end_log_pos 431 CRC32 0x5478e143 Table_map: test.test_semi mapped to number 146
# has_generated_invisible_primary_key0
# Columns(a INT NOT NULL,
# b INT,
# c INT)
# Primary Key(a)
# at 431
#240729 11:12:54 server id 553306 end_log_pos 597 CRC32 0x05b1fd6a Update_rows: table id 146 flags: STMT_END_F
### UPDATE test.test_semi
### WHERE
### 110 /* INT meta0 nullable0 is_null0 */
### 21 /* INT meta0 nullable1 is_null0 */
### 30 /* INT meta0 nullable1 is_null0 */3.2 Binlog 记录事务提交时间戳
如果想从 Binlog 查一些问题比如业务 bug 造成的数据错乱需要秒级以内的粒度来分析问题MySQL 8.0 的 Binlog 中会记录事务提交的时间戳。
-- 源库事务提交的时间戳
# original_commit_timestamp1722222774134690 (2024-07-29 11:12:54.134690 CST)
-- 当前节点事务提交的时间戳
# immediate_commit_timestamp1722222774134690 (2024-07-29 11:12:54.134690 CST)还可以提升 MySQL 复制关系的可观测性详细参考下文。 推荐阅读MySQL 8 and Replication Observability 3.3 Binlog 过期时间
在 MySQL 8.0 之前Binlog 过期时间由 exprice_logs_days 参数控制单位是天。在 MySQL 8.0 中引入 binlog_expire_logs_seconds 参数可设置秒级别的过期时间默认是 2592000 秒既 30 天。
如果 expire_logs_days 和 binlog_expire_logs_seconds 参数都设置了非 0 的值那么 binlog_expire_logs_seconds 的优先级更高。
另外MySQL 8.0.29 新增 binlog_expire_logs_auto_purge 参数用来禁用 Binlog 自动清理。在此之前只能通过将 binlog_expire_logs_seconds 和 exprice_logs_days 设置为 0 来实现。 参考资料binlog_expire_logs_seconds 3.4 ReplicaSet 复制方案
MySQL Innodb ReplicaSet 是 MySQL 团队在 8.0 版本上添加的功能用来帮助用户快速部署和管理主从复制在数据库层仍然使用的是主从复制技术。
ReplicaSet 主要包含三个组件MySQL Router、MySQL Server 以及 MySQL Shell 高级客户端。MySQL Shell 负责管理 ReplicaSet 包括部署、切换、节点加入等都可以通过内置 AdminAPI 自动化完成。MySQL Router 是一款轻量级中间件可在应用程序和 ReplicaSet 之间提供透明路由和读写分离功能。 推荐阅读MySQL Innodb ReplicaSet 4. 安全相关
4.1 角色管理
MySQL Role 就是 8.0 新增加关于权限的功能 Role 角色 可以理解为是一个权限的集合比如在 SQLserver 中也有相关 Role 的功能下图是 SQLserver 的角色管理界面 与 SQLserver 不同的是 MySQL 目前没有系统默认系统的角色需要我们自行创建。 如何使用 role 本文就不展开介绍了如果一个实例账号比较多的情况下可通过 role 简化 DBA 的日常管理操作。另外一个账号可以属于多个角色并且支持在 session 中切换我觉得这是一个非常好的特性有什么用呢比如 DBA 日常排查问题的时候只需要只读权限即可在变更的时候才需要高权限那么 DBA 可以在两个角色中切换可以有效避免误操作。 推荐阅读Using Roles 4.2 caching_sha2_password
MySQL 8.0 版本默认的密码插件由 mysql_native_password 更改为 caching_sha2_password 插件。这项改动有什么影响遇到问题如何修改呢可参考下方文档。 推荐阅读Caching SHA-2 Pluggable Authentication 4.3 print_identified_with_as_hex
该特性是与 caching_sha2_password 配套的特性用于账号迁移场景。
set session print_identified_with_as_hex on;show create user t1%;
CREATE USER t1% IDENTIFIED WITH caching_sha2_password AS 0x2441243030352416564A3E2B11497D1D225702020B6A635920160378523571616436544E5A594B7273357142392E434D79796D424162485A365536317A63425349372E446A42 REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT;参考资料SHOW CREATE USER Statement 4.4 更细粒度的权限控制
MySQL 8.0 拥有更细粒度的权限控制如果授予 super 权限会提示 warning。
通过 show privileges 命令查询权限选项 5.7 有 31 个而 8.0 有 69 个权限选项另外授权相关的表引擎调整为了 InnoDB 引擎。
show privileges;参考资料Privileges Provided by MySQL 4.5 生成随机密码
MySQL 8.0 不能通过 grant 语法创建用户必须先 create user 然后再授权。在创建用户过程中可以让 MySQL 生成随机密码。
create user user1% identified by random password;-- 输出
------------------------------------------------
| user | host | generated password | auth_factor |
------------------------------------------------
| user1 | % | K[BnRT0qxSWd8zG]J7A0 | 1 |
------------------------------------------------修改密码的时候也可以指定随机密码
alter user user1% identified by random password;-- 输出
------------------------------------------------
| user | host | generated password | auth_factor |
------------------------------------------------
| user1 | % | TCiEI%8}cN8_eOY.m( | 1 |
------------------------------------------------set password for user1% to random;-- 输出
------------------------------------------------
| user | host | generated password | auth_factor |
------------------------------------------------
| user1 | % | YpS/oLgUY_GjO5/lE, | 1 |
------------------------------------------------随机密码的长度由参数 generated_random_password_length 来决定默认为 20 个字符。 参考资料Random Password Generation 4.6 双重密码支持
MySQL 8.0 支持一个用户同时设置两个密码新密码和旧密码都可以用于登陆数据库。
-- 给 user1 添加一个新的随机密码
alter user user1% identified by random password retain current password;-- 输出
------------------------------------------------
| user | host | generated password | auth_factor |
------------------------------------------------
| user1 | % | E8wLo{0]so],XImeeEH | 1 |
-------------------------------------------------- 删除旧密码
alter user user1% discard old password;可用于业务连接账号密码修改过渡阶段。 参考资料Dual Password Support 4.7 密码错误锁定用户
MySQL 8.0.19 开始可设置用户密码错误次数失败后锁定用户。
-- FAILED_LOGIN_ATTEMPTS密码失败次数
-- PASSWORD_LOCK_TIME锁定时间单位是天 设置为 UNBOUNDED 表示永久锁定alter user user1% FAILED_LOGIN_ATTEMPTS 4 PASSWORD_LOCK_TIME 3;密码输入失败 4 次锁定 3 天抛出如下异常。
ERROR 3955 (HY000): Access denied for user user1localhost. Account is blocked for 3 day(s) (3 day(s) remaining) due to 4 consecutive failed logins.对于锁定的账号在以下情况下会解锁
数据库重启。执行 FLUSH PRIVILEGES。锁定时间结束。通过 alter user 重置 FAILED_LOGIN_ATTEMPTS 或 PASSWORD_LOCK_TIME。通过 alter user user1‘%’ account unlock; 解锁。 参考资料Failed-Login Tracking and Temporary Account Locking 4.8 账号备注信息
创建用户时可以通过 COMMENT/ATTRIBUTE 子句对账号添加备注信息。设置的备注信息可以使用下面的方式查看。
-- 添加备注信息
alter user user1% ATTRIBUTE {username:张三, empno:10001, job:DBA};-- 查询
select * from user_attributes where user user1;-- 输出
------------------------------------------------------------------------------------------------
| USER | HOST | ATTRIBUTE |
------------------------------------------------------------------------------------------------
| user1 | % | {job: DBA, empno: 10001, comment: 测试用户, username: 张三} |
------------------------------------------------------------------------------------------------5. 优化器相关
5.1 hash join
MySQL 8.0 引入了 Hash join对于 BNL Join会基于驱动表的数据在 join buffer 中构建一个Hashmap。如果驱动表的数据可以一次性全部加载到 join buffer 中则只需要对被驱动表进行一次全表扫描就能完成 join。如果驱动表的数据无法一次性全部加载到 join buffer 中MySQL 会根据关联条件对驱动表和被驱动表进行分片一次 join 一对分片这种情况下一般只需要扫描两次数据就能完成 join 操作。
MySQL 8.0.20 之前 hash join 只适用于等值连接和笛卡尔积。
select * from a join b on a.id b.id;
select * from a join b;MySQL 8.0.20 开始hash join 支持非等值连接、半连接、反连接、左外连接、右外连接。
-- 非等值连接
select * from a join b where a.a1 b.b1;
-- 半连接
select * from a where a.a1 in (select b1 from b);
-- 反连接
select * from a where not exists (select * from b where a.a1 b.b1)
-- 左外连接
select * from a left join b on a.id b.id;
-- 右外连接
select * from a right join b on a.id b.id;推荐阅读MySQL优化器特性四表关联之BNLBlock Nested Loop和Hash Join 5.2 直方图
直方图Histogram是数据库提供的一种索引之外的基础统计信息用于描述列上的数据分布情况。它最典型的场景是估算查询谓词的选择性以便选择合适的执行计划。
例如下方的 SQL 需要先通过 where 条件过滤数据后选择结果集小的表作为驱动表这时优化器需要知道两表的总行数也需要知道每个表符合条件的行数也就是谓词的选取率。如果该字段没有二级索引和直方图的话MySQL 很难准确的选到小表因为无法知道表中的数据分布在多表关联的场景执行计划表关联的顺序对 SQL 执行效率影响很大。
select *
from customer join orders on customer.cust_id orders.customer_id
where customer.balance 1000 and orders.total 10000如果为两张表创建直方图表中的数据会被排序分为 100 个 bucket并记录每个桶中数据的最大值、最小值、出现频次占比等信息。这样的话就可以影响优化器在关联过程中选择合适的小表作为驱动表。
该特性适合多表关联由于数据分布不均匀选错索引的场景可以创建直方图引导优化器选择高效的访问路径。毕竟索引的维护是有代价的直方图只在创建和更新时才会有开销。 参考资料Optimizer Statistics 6. 推荐升级流程
基于当前业务的数据备份恢复一台实例将这台实例升级为 8.0。在新实例上进行充分的测试回归测试/性能测试通过业务测试识别出潜在问题。将测试发现的问题进行改造。MySQL 5.7 和 MySQL 8.0 配置双向同步用 read_only 保持只有一边写入便于割接后遇到问题回滚。割接切换到 MySQL 8.0。观测 7 天无异常后释放双向同步和 5.7 数据库资源。
总结
MySQL 8.0 相比 5.7 版本更新很多新特性很多改变没有直接删除便于用户过渡兼容性还是比较高的。不过到 8.1、8.2 的创新版本老的 5.7 的参数和语法就已经删除。
由于 5.7 已经结束软件生命周期意味着 Oracle 官方将不再为该版本提供更新和漏洞修复虽然数据库厂商保证原则性上 MySQL 是安全的保不齐有时会发现极其罕见的重大安全漏洞。如果用户坚持使用 5.7 版本将会面临安全风险增大、生态系统衰退、运维成本增高等问题。建议还在使用 5.7 的用户开始筹备规划未来的升级计划从而为业务提供持续可靠的数据库服务。
