使用SQLyog误删用户导致无法连接,错误信息: ERROR 1449 (HY000): The user specified as a definer (‘mysql.infoschema’@‘localhost’) does not exist
在网上找到的解决办法mysql_upgrade -u root -p
此方法在低版本下可用,
我的mysql版本是8.018,提示信息:he mysql_upgrade client is now deprecated. The actions executed by the upgrade client are now done by the server. To upgrade, please start the new MySQL binary with the older data directory. Repairing user tables is done automatically. Restart is not required after upgrade. The upgrade process automatically starts on running a new MySQL binary with an older data directory. To avoid accidental upgrades, please use the --upgrade=NONE option with the MySQL binary. The option --upgrade=FORCE is also provided to run the server upgrade sequence on demand. It may be possible that the server upgrade fails due to a number of reasons. In that case, the upgrade sequence will run again during the next MySQL server start. If the server upgrade fails repeatedly, the server can be started with the --upgrade=MINIMAL option to start the server without executing the upgrade sequence, thus allowing users to manually rectify the problem.
根据提示 使用mysqld --upgrade=FORCE 没提示信息,问题依旧。
最后回到开始,根据提示信息Mysql Error:The user specified as a definer (‘mysql.infoschema’@’localhost’) does not exist’
既然说是用户不存在,那就创建mysql.infoschema用户
mysql -uroot -p;
CREATE USER 'mysql.infoschema'@'localhost' ; #无需密码
GRANT ALL ON *.* TO 'mysql.infoschema'@'localhost'; #授于本地所有权限
到此 问题解决,仅仅是简单的创建用户授权操作,不用升级用户等操作也可以。
总结:当出现问题后,根据提示信息是不错的解决问题途径。