The mysql.session exists but is not correctly configured. The mysql.session needs SELECT privileges

最新推荐文章于 2022-12-29 22:50:05 发布

Dreamy_Lois

最新推荐文章于 2022-12-29 22:50:05 发布

阅读量1.1k

点赞数

分类专栏： mysql 文章标签： mysql mysql_upgrade 解决方法

本文链接：https://blog.youkuaiyun.com/qq_42120178/article/details/110553005

版权

mysql 专栏收录该内容

6 篇文章

订阅专栏

本文介绍在Centos7系统将MySQL从5.7.28升级到5.7.31时，执行mysql_upgrade出现的问题及解决办法。包括‘mysql_upgrade: command not found’和‘The mysql.session exists but is not correctly configured’错误，还提及了mysql中user表和db表的区别。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.环境

系统：Centos7
mysql5.7.28 -> mysql5.7.31

2.问题及解决办法

执行升级mysql的最后一步mysql_upgrade时出错

2.1 mysql_upgrade: command not found

错误信息：

mysql_upgrade -u root -p
-bash: mysql_upgrade: command not found

解决：
进入mysql文件夹的bin文件夹下执行 ./mysql_upgrade

cd /usr/local/mysql/bin/

2.2 The mysql.session exists but is not correctly configured.

错误信息

./mysql_upgrade -uroot -p
Enter password: 
Checking if update is needed.
Checking server version.
Error occurred: The mysql.session exists but is not correctly configured. The mysql.session needs SELECT privileges in the performance_schema database and the mysql.db table and also SUPER privileges.

原因：mysql_upgrade 之前会执行下面的sql，检查mysql.session用户的权限

 SELECT SUM(count)=3 FROM ( SELECT COUNT(*) as count FROM
    mysql.tables_priv WHERE Table_priv='Select' and User='mysql.session' and
    Db='mysql' and Table_name='user' UNION ALL SELECT COUNT(*) as count FROM
    mysql.db WHERE Select_priv='Y' and User='mysql.session' and
    Db='performance_schema' UNION ALL SELECT COUNT(*) as count FROM
    mysql.user WHERE Super_priv='Y' and User='mysql.session') as user_priv;

解决：

查询mysql.user表中是否存在多个mysql.session用户

SELECT * FROM `mysql`.`user` WHERE User='mysql.session'

发现多个用户时，保留Host列为localhost的用户，其余删除，即可再次执行mysql_upgrade

如果不存在1中的情况，请查看mysql.db与mysql.user表中的mysql.session用户是否拥有Select_priv权限

SELECT * FROM `mysql`.`user` WHERE User='mysql.session'

在这里插入图片描述

SELECT * FROM `mysql`.`db` WHERE User='mysql.session' AND Db='performance_schema'

在这里插入图片描述
将权限都修改好了后，再次执行mysql_upgrade即可成功。

博主遇见的是2.2中的第2中情况，db表中没有该信息，直接执行插入语句即可

INSERT INTO `mysql`.`db`(`Host`, `Db`, `User`, `Select_priv`, `Insert_priv`, `Update_priv`, `Delete_priv`, `Create_priv`, `Drop_priv`, `Grant_priv`, `References_priv`, `Index_priv`, `Alter_priv`, `Create_tmp_table_priv`, `Lock_tables_priv`, `Create_view_priv`, `Show_view_priv`, `Create_routine_priv`, `Alter_routine_priv`, `Execute_priv`, `Event_priv`, `Trigger_priv`) VALUES ('localhost', 'performance_schema', 'mysql.session', 'Y', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N');