登录后执行以下代码报错
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;