MySQL5.7.21 执行mysql_upgrade时遇到如下错误:
$mysql_upgrade -uroot -p123456 -S /data/3316/logs/mysql.sock
mysql_upgrade: [Warning] Using a password on the command line interface can be insecure.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 会执行一个比较弱智的校验。如果结果不等于1则,不能执行mysql_upgrade。
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.session用户,只保留'mysql.session'@'localhost' 即可。
drop user 'mysql.session'@'127.0.0.1';
flush privileges;
mysql> select user ,host from mysql.user where user='mysql.session';
+---------------+-----------+
| user | host |
+---------------+-----------+
| mysql.session | 127.0.0.1 |
| mysql.session | localhost |
+---------------+-----------+
2 rows in set (0.00 sec)
mysql> select user ,host from mysql.db where user='mysql.session';
+---------------+-----------+
| user | host |
+---------------+-----------+
| mysql.session | 127.0.0.1 |
| mysql.session | localhost |
+---------------+-----------+
2 rows in set (0.00 sec)
mysql> drop user 'mysql.session'@'127.0.0.1';
Query OK, 0 rows affected (10.00 sec)
mysql> select user ,host from mysql.db where user='mysql.session';
+---------------+-----------+
| user | host |
+---------------+-----------+
| mysql.session | localhost |
+---------------+-----------+
1 row in set (0.00 sec)
mysql> select user ,host from mysql.user where user='mysql.session';
+---------------+-----------+
| user | host |
+---------------+-----------+
| mysql.session | localhost |
+---------------+-----------+
1 row in set (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
Upgrade process completed successfully.
Checking if update is needed.