适用范围
MySQL 8.0
问题概述
客户MySQL8.0环境中，主库参数文件配置innodb_buffer_pool_size为48G，经过后期运维重启后，巡检时发现MySQL内存只分配了8G，和参数/etc/my.cnf配置中显示指定的48G相差甚远，故怀疑运维人员后期在线设置过该参数（MySQL 5.7版本以后 innodb_buffer_pool_size可以在线动态调整），经过再三确认，客户肯定并没有任何运维人员进行过参数调整。
问题分析
1. 确认参数文件中内存设置是否正确？
[root@iZf8zcal7db1wnlk0w6a8lZ ~]#
[root@iZf8zcal7db1wnlk0w6a8lZ ~]# cat /etc/my.cnf|grep -i innodb_buffer_pool_size
innodb_buffer_pool_size = 48G
[root@iZf8zcal7db1wnlk0w6a8lZ ~]#
2. 确认引用的参数文件是否还有其他选择，是否还存在其他的参数文件？
[root@iZf8zcal7db1wnlk0w6a8lZ ~]# find / -name my.cnf
/etc/my.cnf
[root@iZf8zcal7db1wnlk0w6a8lZ ~]#
[root@iZf8zcal7db1wnlk0w6a8lZ ~]# find / -name my.*
/usr/lib/python3.6/site-packages/babel/locale-data/my.dat
/etc/my.cnf
说明：通过OS中查找参数文件，并未发现有其他参数文件引用。
3. 在线查询数据库中内存参数大小进行确认。
[root@iZf8zcal7db1wnlk0w6a8lZ ~]#
[root@iZf8zcal7db1wnlk0w6a8lZ ~]# /usr/local/mysql/bin/mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 193
Server version: 8.0.36 MySQL Community Server - GPL
Copyright (c) 2000, 2024, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
root@localhost:(none) 02:47:39 >show global variables like '%innodb_buffer_pool_size%';
+-------------------------+------------+
| Variable_name | Value |
+-------------------------+------------+
| innodb_buffer_pool_size | 8589934592 |
+-------------------------+------------+
1 row in set (0.01 sec)
root@localhost:(none) 02:58:19 >select 8589934592/1024/1024/1024;
+---------------------------+
| 8589934592/1024/1024/1024 |
+---------------------------+
| 8.000000000000 |
+---------------------------+
1 row in set (0.00 sec)
说明：在MySQL数据库中进行查询发现，innodb_buffer_pool_size只有8G大小，对比参数文件中的48G，不匹配。
4. 经过重启MySQL验证后，问题依然存在。
root@localhost:(none) 03:05:06 >shutdown;
Query OK, 0 rows affected (0.08 sec)
root@localhost:(none) 03:05:09 >exit
Bye
[root@iZf8zcal7db1wnlk0w6a8lZ ~]# /usr/local/mysql/bin/mysqld_safe --user=mysql &
[1] 117682
[root@iZf8zcal7db1wnlk0w6a8lZ ~]# 2024-04-01T07:05:33.358753Z mysqld_safe Logging to '/data/mysql8036/3306/logs/error.log'.
2024-04-01T07:05:33.399030Z mysqld_safe Starting mysqld daemon with databases from /data/mysql8036/3306/data
[root@iZf8zcal7db1wnlk0w6a8lZ ~]#
[root@iZf8zcal7db1wnlk0w6a8lZ ~]# /usr/local/mysql/bin/mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 193
Server version: 8.0.36 MySQL Community Server - GPL
Copyright (c) 2000, 2024, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
root@localhost:(none) 03:07:00 >s
--------------
/usr/local/mysql/bin/mysql Ver 8.0.36 for Linux on x86_64 (MySQL Community Server - GPL)
Connection id: 8
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 8.0.36 MySQL Community Server - GPL
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
UNIX socket: /data/mysql8036/3306/run/mysql.sock
Binary data as: Hexadecimal
Uptime: 1 min 28 sec
Threads: 2 Questions: 8 Slow queries: 0 Opens: 136 Flush tables: 3 Open tables: 55 Queries per second avg: 0.090
--------------
root@localhost:(none) 03:07:01 >show global variables like '%innodb_buffer_pool_size%';
+-------------------------+------------+
| Variable_name | Value |
+-------------------------+------------+
| innodb_buffer_pool_size | 8589934592 |
+-------------------------+------------+
1 row in set (0.00 sec)
说明：MySQL实例重启后，innodb_buffer_pool_size大小依然为8G，和参数文件中指定的48G不匹配！
5. 发现问题：mysqld-auto.cnf文件
由于没有其他异常发现，但仍然随机去找可能得问题点（进程，目录结构等）看是否有其他异常!
[root@iZf8zcal7db1wnlk0w6a8lZ ~]# ps -ef|grep mysqld
root 117682 1 0 15:05 ? 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --user=mysql
mysql 118351 117682 0 15:05 ? 00:00:06 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql8036/3306/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql8036/3306/logs/error.log --pid-file=/data/mysql8036/3306/run/mysql.pid --socket=/data/mysql8036/3306/run/mysql.sock --port=3306
root 119976 119229 0 15:44 pts/1 00:00:00 grep --color=auto mysqld
[root@iZf8zcal7db1wnlk0w6a8lZ ~]#
[root@iZf8zcal7db1wnlk0w6a8lZ ~]# ls -lhrt /data/mysql8036/3306/data/
total 883M
-rw-r----- 1 mysql mysql 56 Mar 4 17:01 auto.cnf
-rw-r----- 1 mysql mysql 200M Mar 4 17:01 ibdata2
drwxr-x--- 2 mysql mysql 4.0K Mar 4 17:01 performance_schema
-rw------- 1 mysql mysql 1.7K Mar 4 17:01 ca-key.pem
-rw-r--r-- 1 mysql mysql 1.1K Mar 4 17:01 ca.pem
-rw------- 1 mysql mysql 1.7K Mar 4 17:01 server-key.pem
-rw-r--r-- 1 mysql mysql 1.1K Mar 4 17:01 server-cert.pem
-rw------- 1 mysql mysql 1.7K Mar 4 17:01 client-key.pem
-rw-r--r-- 1 mysql mysql 1.1K Mar 4 17:01 client-cert.pem
-rw------- 1 mysql mysql 1.7K Mar 4 17:01 private_key.pem
-rw-r--r-- 1 mysql mysql 452 Mar 4 17:01 public_key.pem
drwxr-x--- 2 mysql mysql 4.0K Mar 4 17:01 mysql
drwxr-x--- 2 mysql mysql 4.0K Mar 4 17:01 sys
-rw-r----- 1 mysql mysql 8.6M Mar 30 10:51 '#ib_16384_1.dblwr'
drwxr-x--- 2 mysql mysql 4.0K Mar 30 10:51 rockdb
-rw-r----- 1 mysql mysql 338 Apr 1 15:03 mysqld-auto.cnf
-rw-r----- 1 mysql mysql 11K Apr 1 15:05 ib_buffer_pool
drwxr-x--- 2 mysql mysql 4.0K Apr 1 15:05 '#innodb_redo'
-rw-r----- 1 mysql mysql 200M Apr 1 15:05 ibtmp2
drwxr-x--- 2 mysql mysql 4.0K Apr 1 15:05 '#innodb_temp'
-rw-r----- 1 mysql mysql 200M Apr 1 15:05 ibtmp1
-rw-r----- 1 mysql mysql 25M Apr 1 15:05 mysql.ibd
-rw-r----- 1 mysql mysql 32M Apr 1 15:07 undo_001
-rw-r----- 1 mysql mysql 16M Apr 1 15:07 undo_002
-rw-r----- 1 mysql mysql 200M Apr 1 15:17 ibdata1
-rw-r----- 1 mysql mysql 576K Apr 1 15:17 '#ib_16384_0.dblwr'
[root@iZf8zcal7db1wnlk0w6a8lZ ~]#
[root@iZf8zcal7db1wnlk0w6a8lZ ~]# find / -name *.cnf
/data/mysql8036/3306/data/auto.cnf
/data/mysql8036/3306/data/mysqld-auto.cnf
/etc/pki/tls/openssl.cnf
/etc/pki/tls/ct_log_list.cnf
/etc/my.cnf
[root@iZf8zcal7db1wnlk0w6a8lZ ~]#
说明：从以上随机查找中，发现了异常文件mysqld-auto.cnf 终于恍然大悟，想起了MySQL8.0的持久化参数的特性。
MySQL8.0的持久化参数相关知识点：
SET PERSIST命令：
MySQL 8.0版本有一个新特性，在数据库中，引入了一个新命令 SET PERSIST，它允许用户将会话基本的配置选项永久保持在配置文件中，以便在数据库重启后仍然生效。
SET PERSIST 语法会在datadir目录下创建一个json格式的mysqld-auto.cnf文件，在数据库重启时，首先会加载常规的参数文件my.cnf或者指定的参数文件abc.cnf，最后才读取mysqld-auto.cnf文件，故这样会导致一个现象：
如果 mysqld-auto.cnf文件中的参数与my.cnf文件中参数值重复，会覆盖my.cnf中参数的值！
持久化参数查询：
root@localhost:(none) 04:12:14 >
root@localhost:(none) 04:12:15 >select * from performance_schema.persisted_variables;
+---------------------------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+---------------------------------+----------------+
| information_schema_stats_expiry | 0 |
| innodb_buffer_pool_size | 8589934592 |
+---------------------------------+----------------+
2 rows in set (0.00 sec)
root@localhost:(none) 04:12:17 >exit
Bye
[root@iZf8zcal7db1wnlk0w6a8lZ ~]# cat /data/mysql8036/3306/data/mysqld-auto.cnf
{"Version": 2, "mysql_static_variables": {"innodb_buffer_pool_size": {"Value": "8589934592", "Metadata": {"Host": "localhost", "User": "root", "Timestamp": 1711955005068940}}}, "mysql_dynamic_variables": {"information_schema_stats_expiry": {"Value": "0", "Metadata": {"Host": "localhost", "User": "root", "Timestamp": 1711766956975656}}}}[root@iZf8zcal7db1wnlk0w6a8lZ ~]#
[root@iZf8zcal7db1wnlk0w6a8lZ ~]#
解决方案
通过RESET命令将持久化参数文件中的设置清空！
[root@iZf8zcal7db1wnlk0w6a8lZ ~]#
[root@iZf8zcal7db1wnlk0w6a8lZ ~]# /usr/local/mysql/bin/mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 11
Server version: 8.0.36 MySQL Community Server - GPL
Copyright (c) 2000, 2024, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
root@localhost:(none) 04:16:16 >reset persist innodb_buffer_pool_size;
Query OK, 0 rows affected (0.00 sec)
root@localhost:(none) 04:16:38 >
root@localhost:(none) 04:16:38 >select * from performance_schema.persisted_variables;
+---------------------------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+---------------------------------+----------------+
| information_schema_stats_expiry | 0 |
+---------------------------------+----------------+
1 row in set (0.00 sec)
root@localhost:(none) 04:16:41 >system cat /etc/my.cnf|grep -i pool_size
innodb_buffer_pool_size = 48G
root@localhost:(none) 04:17:35 >
root@localhost:(none) 04:17:36 >
说明：
reset persist ; 将清空mysqld-auto.cnf指定参数的配置，my.cnf文件中的配置不变！
reset persist; 将清空mysqld-auto.cnf文件中所有参数的配置，my.cnf文件中的配置不变！
root@localhost:(none) 04:38:21 >select * from performance_schema.persisted_variables;
+---------------------------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+---------------------------------+----------------+
| information_schema_stats_expiry | 0 |
| max_connections | 500 |
+---------------------------------+----------------+
2 rows in set (0.00 sec)
root@localhost:(none) 04:38:36 >reset persist;
Query OK, 0 rows affected (0.00 sec)
root@localhost:(none) 04:39:09 >select * from performance_schema.persisted_variables;
Empty set (0.00 sec)
root@localhost:(none) 04:39:12 >
root@localhost:(none) 04:39:13 >system cat /data/mysql8036/3306/data/mysqld-auto.cnf
{"Version": 2}root@localhost:(none) 04:39:48 >
root@localhost:(none) 04:39:49 >
总结
1. MySQL参数可以在运行时通过 SET GLOBAL 命令来更改，但是这种更改只会临时生效直到下次启动时数据库又会从配置文件（my.cnf）中读取。
2. MySQL8.0新增了 SET PERSIST命令，参数生效的同时，MySQL会将该命令的配置保存到数据目录下的 mysqld-auto.cnf 文件中，下次启动时会读取该文件用其中的配置来覆盖缺省的配置文件(my.cnf)。
3. 对于已经持久化了的变量（set persist），可以通过 reset persist 命令清除掉，这里只是清空 mysqld-auto.cnf 和 performance_schema.persisted_variables 中的内容，对于已经修改了的变量的值，不会产生影响。
参考文档
https://dev.mysql.com/doc/refman/8.0/en/persisted-system-variables.html
