科普文：软件架构数据库系列之【MySQL8.0新特性--全局参数持久化和自增主键持久化】

-无-为-

于 2024-09-30 08:35:20 发布

阅读量1.4k

点赞数 40

分类专栏：软件架构业务场景 MySQL 文章标签：数据库 mysql MySQL8新特性自增主键持久化全局参数持久化

本文链接：https://blog.csdn.net/Rookie_CEO/article/details/142647478

版权

软件架构同时被 3 个专栏收录

678 篇文章 12 订阅

订阅专栏

业务场景

386 篇文章 1 订阅

订阅专栏

MySQL

124 篇文章 2 订阅

订阅专栏

概叙

科普文：软件架构数据库系列之【MySQL前世今生及其体系结构概叙】_mysql 的几个分支-CSDN博客

前面在梳理MySQL5.0、5.5、5.6、5.7、8.0、8.4、9.0等主要版本时，有提到MySQL8.0的这两个持久化特性：全局参数持久化、自增主键的持久化。今天我们就详细说说这两个新特性，当然本地环境还是MySQL 8.4。

全局参数持久化：

类似于Oracle的SPFILE，实时修改的数据库参数可以被更新到SPFILE文件，在发生故障或者需要重启数据库时，可以通过SPFILE文件来启动数据库，复刻重启前的数据库参数。

MySQL 8.0 版本支持在线修改全局参数并持久化，通过加上 PERSIST 关键字，可以将修改的参数持久化到新的配置文件（mysqld-auto.cnf）中，重启 MySQL 时，可以自动从该配置文件（mysqld-auto.cnf）获取到最新的配置参数。而在MySQL 8.0 版本中如果在线修改了全局参数，没有将其更新到my.cnf数据库配置参数文件中，则下次重启后，该参数又会恢复到修改前的值。

这个特性可以给dba带来一定的维护方便。

自增主键持久化：

这个特性只是为了修复20年前的老bug“Bug #199Innodb autoincrement stats los on restart” ，将自增主键的计数器持久化到redo log中。

备注：SPFILE是oracle持久化的二进制文件，只能通过ALTER SYSTEM SET命令修改，而PFILE是静态的文本文件，通常用于初始化数据库。

怎样查看一个参数修改是否需要重启数据库

select name,value ,ISSYS_MODIFIABLE from v$parameter

如果ISSYS_MODIFIABLE 返回的是false，说明该参数无法用alter system语句动态修改,需要重启数据库


用命令修改参数

alter system set java_pool_size=2 scope=spfile ;

scope=spfile 的修改是需要重启数据库

scope=both 实时生效和写入spfile，不需要重启数据库

scope=memory 实时生效，但不写spfile，这样如果重启数据库，则恢复原来的参数值

1)创建pfileSQL>create pfile from spfile

这样就在d:/oracle/product/10.1.0/db_1/database目录下面多1个文件INITorcl.ORA

或者copy d:/oracle/product/10.1.0/admin/orcl/pfile/init.ora.XXXXXXXX到上述目录, 名字改成INITorcl.ORA

init.ora.XXXX也是个pfile文件, 不妨试着用这个文件启动你的数据库

SQL>startup pfile='d:/oracle/product/10.1.0/admin/orcl/pfile/init.ora.XXXXXXXX'

特别是你改动参数导致数据库无法启动的情况下, 用这个文件恢复你的spfile将非常有用

SQL>create spfile from pfile='d:/oracle/product/10.1.0/admin/orcl/pfile/init.ora.XXXXXXXX'

2)修改pfile的内容修改后主要内容为

sga_target=1700000000(1.7G左右)

lock_sga=true

pre_aggregate_tagert=250000000(250M左右)

workarea_size_policy=auto

pre_page_sga=true

sga_max_size=1720000000(1.72G左右)

3)根据pfile启动数据库SQL>startup pfile='d:/oracle/product/10.1.0/db_1/database/INITorcl.ORA'

如果不能启动, 可能是某些参数的原因, 那么就修改INIToracl.ORA的配置文件, 直到能正常启动为止.

全局参数持久化

MySQL :: WL#8688: Support ability to persist SET GLOBAL settings

全局参数：MySQL :: MySQL 8.4 Reference Manual :: 7.1.9.1 System Variable Privileges

MySQL :: MySQL 8.4 Reference Manual :: 7.1.9.4 Nonpersistible and Persist-Restricted System Variables

启用这个功能，使用特定的语法 SET PERSIST 来设定任意可动态修改的全局变量！

SET PERSIST 语句可以修改内存中变量的值，并且将修改后的值写⼊数据⽬录中的 mysqld-auto.cnf 中。
SET PERSIST_ONLY 语句不会修改内存中变量的值，只是将修改后的值写⼊数据⽬录中的 mysqld-auto.cnf 中。
RESET PERSIST 移除所有的全局持久化参数
set persist max_connections=DEFAULT; 恢复到MySQL的默认值，与my.cnf无关

MySQL :: MySQL 8.4 Reference Manual :: 7.1.9.4 Nonpersistible and Persist-Restricted System Variables

mysql> select * from performance_schema.persisted_variables;
Empty set (0.03 sec)

mysql> show variables like '%max_connections%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| max_connections        | 2000  |
| mysqlx_max_connections | 100   |
+------------------------+-------+
2 rows in set, 1 warning (0.03 sec)

mysql> set persist max_connections=300;
Query OK, 0 rows affected (0.01 sec)

mysql> show variables like '%max_connections%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| max_connections        | 300   |
| mysqlx_max_connections | 100   |
+------------------------+-------+
2 rows in set, 1 warning (0.00 sec)

mysql> select * from performance_schema.persisted_variables;
+-----------------+----------------+
| VARIABLE_NAME   | VARIABLE_VALUE |
+-----------------+----------------+
| max_connections | 300            |
+-----------------+----------------+
1 row in set (0.00 sec)


恢复 max_connections 参数为初始默认值，这是默认值，不是my.cnf中的值。


mysql> set persist max_connections=DEFAULT;
Query OK, 0 rows affected (0.01 sec)

mysql> show variables like '%max_connections%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| max_connections        | 151   |
| mysqlx_max_connections | 100   |
+------------------------+-------+
2 rows in set, 1 warning (0.00 sec)

mysql> select * from performance_schema.persisted_variables;
+-----------------+----------------+
| VARIABLE_NAME   | VARIABLE_VALUE |
+-----------------+----------------+
| max_connections | 151            |
+-----------------+----------------+
1 row in set (0.00 sec)

mysql>

移除所有的全局持久化参数，则只需执行：
mysql> RESET PERSIST;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from performance_schema.persisted_variables;
Empty set (0.00 sec)

mysql> show variables like '%max_connections%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| max_connections        | 151   |
| mysqlx_max_connections | 100   |
+------------------------+-------+
2 rows in set, 1 warning (0.00 sec)

mysql>

全局变量的修改会保存在两处：mysqld-auto.cnf和performance_schema.persisted_variables

1. 数据目录下mysqld-auto.cnf文件，

注意，不是启动时--defaults-file指定的配置文件。

注意： 即使你通过 SET PERSIST 修改配置的值并没有任何变化，也会写入到 mysqld-auto.cnf 文件中。但你可以通过设置成 DEFAULT 值的方式来恢复初始默认值！

持久化信息以json格式保存，其中，Metadata记录了这次修改的用户及时间信息。

在数据库启动时，会首先读取其它配置文件，最后才读取mysqld-auto.cnf文件。不建议手动修改该文件，其有可能导致数据库在启动过程中因解析错误而失败。如果出现这种情况，可手动删除mysqld-auto.cnf文件或将persisted_globals_load变量设置为off来避免该文件的加载。

2. performance_schema.persisted_variables

全局变量的持久化除了SET PERSIST外，还有SET PERSIST_ONLY，与前者相比，其只持久化全局变量，而不修改其内存值。同时，在权限方面，前者只需要SYSTEM_VARIABLES_ADMIN，后者还需要PERSIST_RO_VARIABLES_ADMIN权限。

对于已经持久化了变量，可通过RESET PERSIST命令清除掉，注意，其只是清空mysqld-auto.cnf和performance_schema.persisted_variables中的内容，对于已经修改了的变量的值，不会产生任何影响。

除此之外，还可以通过下述方式将全局变量持久化为默认值。注意，是默认值，而不是修改前的值。

set persist max_connections=default;这个命令同“set global max_connections=default”类似，都会将变量的值设置为默认值，只不过前者还会将默认值持久化到配置文件中。

此时如果再重启数据库，则max_connections=2000，会以my.cnf为准。

示例如下：

数据库参数文件还是修改前的值

修改的新值，保存在数据目录下mysqld-auto.cnf文件，

自增主键持久化

这个特性只是为了修复“Bug #199Innodb autoincrement stats los on restart” ，将自增主键的计数器持久化到redo log中。

MySQL Bugs: #199: Innodb autoincrement stats los on restart

Peter Zaitsev 这个bug的提出者是Percona CEO；O(∩_∩)O

2003年Peter Zaitsev 提出，历史悠久且臭名昭著的bug。

自增主键未持久化导致的主键冲突

MySQL自增列导致主键重复案例分析 - 墨天轮

看出问题了吗？表自增初始值居然比max(id)小，了解自增ID的都知道，当有记录进行插入时，自增列产生的值就有可能与已有的记录主键冲突，导致出错，为了先恢复业务，于是笔者临时调整了自增初始值，只要确保比max(id)大即可，可是原因总要分析下不是吗？刚临时解决完这个表的问题，另外一个业务开发反馈另外一个实例也发生类似问题，也是报主键冲突，查看现象都是类似的，对于DBA来说，第一原则永远是先恢复业务后排查问题，于是笔者马上也调大报错表的自增值，先让表恢复使用。

3、对于DBA来说怎么快速troubleshooting，根据现场的记录和现象快速定位原因呢？对于使用TDSQL我们只能从我们使用层面上去排查问题，其实笔者之前也怀疑有可能是TDSQL的bug导致的，比如计数器没有及时更新，当然后面我们把这个原因排查了。也曾想过是不是因为自增ID没有持久化的原因，比如实例重启了，但是一想也不可能，因为首先我们没收到实例挂掉的告警，而且我们公司业务不依赖自增id，其次也不会导致数据插入失败的，于是这个也pass掉。

提示：MySQL8.0以前自增id是不持久化的，所以实例重启之后，自增初始值是max(id)+1开始算的，其实对于依赖自增id的业务或者归档数据可能会有一些问题，MySQL8.0之后将自增主键的计数器持久化到redo log中。每次计数器发生改变，都会将其写入到redo log中。如果数据库发生重启，InnoDB会根据redo log中的计数器信息来初始化其内存值。为了尽量减小对系统性能的影响，计数器写入到redo log中，并不会马上刷新。当然MySQL8.0之前的bug只针对InnoDB，Myiasm是没有这个问题的。

4、经过前面排除一系列的原因之后，笔者就想是不是开发去更新主键id呢？于是去找开发看了一部分代码，部分代码如下：

<update id="updateByExampleSelective" parameterType="map">


    update xxxx_env_version_history


    <set>


      <if test="record.id != null">


        id = #{record.id,jdbcType=INTEGER},


      </if>


      <if test="record.apiId != null">


        api_id = #{record.apiId,jdbcType=VARCHAR},


      </if>


      <if test="record.version != null">


        version = #{record.version,jdbcType=VARCHAR},


      </if>

这个明显是去更新主键id，可是开发说传的id和更新的id是一样的，好吧，笔者去拉取binlog分析一番，后面发现更新id和where条件id是不一致的，由于binlog过多，只展示部分binlog信息，如下图所示：

看到了吗，更新的id比原来的id大，这就会出现max(id)比自增初始值大，从而发生导致主键冲突的情况，既然知道原因，那么解决办法就简单多了。

bug复现：MySQL 5.7

mysql> \s;
--------------
mysql  Ver 14.14 Distrib 5.7.21, for Win64 (x86_64)

Connection id:          2
Current database:
Current user:           ODBC@
SSL:                    Not in use
Using delimiter:        ;
Server version:         5.7.21-log MySQL Community Server (GPL)
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
TCP port:               3307
Uptime:                 19 sec

Threads: 1  Questions: 5  Slow queries: 0  Opens: 99  Flush tables: 1  Open tables: 94  Queries per second avg: 0.263
--------------

ERROR:
No query specified

mysql> use testdb
Database changed
mysql> create table t1(id int auto_increment primary key);
Query OK, 0 rows affected (0.04 sec)

mysql> insert into t1 values(null),(null),(null);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

mysql> delete from t1 where id=3;
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)

mysql> insert into t1 values(null);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+----+
| id |
+----+
|  1 |
|  2 |
|  4 |
+----+
3 rows in set (0.00 sec)

mysql>

虽然id为3的记录删除了，但再次插入null值时，并没有重用被删除的3，而是分配了4。

删除id为4的记录，重启数据库，重新插入一个null值。

mysql> delete from t1 where id=4;
# service mysqld restart
mysql> insert into t1 values(null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
+----+
rows in set (0.00 sec)

可以看到，新插入的null值分配的是3，按照重启前的操作逻辑，此处应该分配5啊。

这就是自增主键没有持久化的bug。究其原因，在于自增主键的分配，是由InnoDB数据字典内部一个计数器来决定的，而该计数器只在内存中维护，并不会持久化到磁盘中。

当数据库重启时，该计数器会通过下面这种方式初始化SELECT MAX(ai_col) FROM table_name FOR UPDATE;

MySQL 8.0的解决思路

将自增主键的计数器持久化到redo log中。每次计数器发生改变，都会将其写入到redo log中。如果数据库发生重启，InnoDB会根据redo log中的计数器信息来初始化其内存值。为了尽量减小对系统性能的影响，计数器写入到redo log中，并不会马上刷新。

具体思路参考：MySQL :: WL#6204: InnoDB persistent max value for autoinc columns

因自增主键没有持久化而出现问题的常见场景：

1. 业务将自增主键作为业务主键，同时，业务上又要求主键不能重复。

2. 数据会被归档。在归档的过程中有可能会产生主键冲突。

所以，强烈建议不要使用自增主键作为业务主键。刨除这两个场景，其实，自增主键没有持久化的问题并不是很大，远没有想象中的”臭名昭著“。

最后，给出一个归档场景下的解决方案，

创建一个存储过程，根据table2（归档表）自增主键的最大值来初始化table1（在线表）。这个存储过程可放到init_file参数指定的文件中，该文件中的SQL会在数据库启动时执行。

DELIMITER ;;
CREATE PROCEDURE `auto_increment_fromtable2`(IN table1 VARCHAR(255), IN table2 VARCHAR(255))
BEGIN
set @qry = concat('SELECT @max1 := (`id` + 1) FROM `',table1,'` ORDER BY `id` DESC LIMIT 1;');
 prepare stmt from @qry;
execute stmt;
deallocate prepare stmt;
 set @qry = concat('SELECT @max2 := (`id` + 1) FROM `',table2,'` ORDER BY `id` DESC LIMIT 1;');
 prepare stmt from @qry;
execute stmt;
deallocate prepare stmt;
IF @max1 < @max2 THEN
 set @qry = concat('alter table `',table1,'` auto_increment=',@max2);prepare stmt from @qry;execute stmt;deallocate prepare stmt;
SELECT 'updated' as `status`;
else
SELECT 'no update needed' as `status`;
END IF;
END ;;
DELIMITER ;

bug验证：MySQL 8.4

mysql> \s;
--------------
mysql  Ver 8.4.2 for Win64 on x86_64 (MySQL Community Server - GPL)

Connection id:          8
Current database:       testdb
Current user:           root@localhost
SSL:                    Cipher in use is TLS_AES_128_GCM_SHA256
Using delimiter:        ;
Server version:         8.4.2 MySQL Community Server - GPL
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
TCP port:               3308
Binary data as:         Hexadecimal
Uptime:                 58 min 10 sec

Threads: 2  Questions: 21  Slow queries: 0  Opens: 143  Flush tables: 3  Open tables: 62  Queries per second avg: 0.006
--------------

ERROR:
No query specified

mysql> create table t12(id int auto_increment primary key);
Query OK, 0 rows affected (0.04 sec)

mysql> insert into t12 values(null),(null),(null);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t12;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

mysql> delete from t12 where id=3;
Query OK, 1 row affected (0.00 sec)

mysql> select * from t12;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)

mysql> insert into t12 values(null);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t12;
+----+
| id |
+----+
|  1 |
|  2 |
|  4 |
+----+
3 rows in set (0.00 sec)

mysql> delete from t12 where id=4;
Query OK, 1 row affected (0.01 sec)

mysql> select * from t12;
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
Connection id:    8
Current database: testdb

+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.05 sec)

mysql> insert into t12 values(null);   -- 即使重启了，也不会重新计数
Query OK, 1 row affected (0.01 sec)

mysql> select * from t12;
+----+
| id |
+----+
|  1 |
|  2 |
|  5 |
+----+
3 rows in set (0.00 sec)

mysql>