Mysql5.7升级8.0登录后查询数据库报1499错误

登录后执行以下代码报错

mysql>show databases;
ERROR 1449 (HY000): The user specified as a definer ('mysql.infoschema'@'localhost') does not exist

mysql> use wodeshuju;
Database changed
mysql>show tables;
ERROR 1449 (HY000): The user specified as a definer ('mysql.infoschema'@'localhost') does not exist

原因：用户mysql.infoschema 权限问题

解决方案：

 1、在Mysql 8.0.16 之前，我们用下面的方案可以解决

mysql -uroot -p
Enter password:密码
SET GLOBAL innodb_fast_shutdown = 1;
mysql_upgrade -uroot -p
Enter password:密码

2、MySQL 8.0.16开始 mysql_upgrade 升级程序已经废弃

--创建用户:
mysql> create user 'mysql.infoschema'@'%' identified by '密码';
Query OK, 0 rows affected (0.01 sec)
--用户授权:
mysql> grant all privileges on *.* to 'mysql.infoschema'@'%';
--注意：'@'%'这里的含义如下,%表示：授权所有用户
Query OK, 0 rows affected (0.01 sec)
--刷新授权:
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

3、使用方法2创建用户报1726错误

ERROR 1726 (HY000): Storage engine ‘MyISAM’ does not support system tables. [mysql.user]

解决方法：ALTER TABLE mysql.user ENGINE = InnoDB;

修复之后，继续创建用户，发现哪个表报上面的错，就用上面的语句更改引擎。但，最终还是报错：mysql8 ERROR 1726 (HY000): Storage engine ‘MyISAM’ does not support system tables. [mysql.user]

 4、终极方案

以上方法都不行就试试这个方案，亲测有效

mysql -uroot -p"密码"

use mysql;

INSERT INTO mysql.user(`Host`,`User`,`plugin`,`authentication_string`,ssl_cipher,x509_issuer,x509_subject) VALUES ('localhost','mysql.infoschema','mysql_native_password','*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE','','','');

FLUSH PRIVILEGES;

update mysql.user set Select_priv = 'Y' where User = 'mysql.infoschema';

FLUSH PRIVILEGES;