MySQL8.0参数配置不生效问题排查诊断

原创闫建(Rock Yan) 2024-04-01

1772

适用范围

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 中的内容，对于已经修改了的变量的值，不会产生影响。