I updated the mysql from 5.0 to mysql 8.0 today, everything seemed smooth before I restarted the server, I changed the “nginx 301 https”, then restarted the service, I got this error:
Then I login the mysql, I got this error:
Error 1449 - The user specified as a definer ('mysql.infoschema'@'localhost') does not exist Error #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') ORDER BY `User` ASC, `Host` ASC' at line 1
I checked all the solutions on the internet, and I used the following solution, but this idea wasn’t work on me,
you can check the original page here.
mysql -u root -p drop user `mysql.infoschema`@"localhost"; flush privileges; create user `mysql.infoschema`@"localhost" identified by '123456'; flush privileges; use mysql; update user set Select_priv = 'Y' where User = 'mysql.infoschema'; flush privileges;
And I got another error:
ERROR 1726 (HY000): Storage engine 'MyISAM' does not support system tables. in Mysql 8.0 after CREATE USER
Finally, I changed the my.cnf to got both errors solved.
vim /etc/my.cnf skip-grant-tables service mysqld restart
You can open your Winscp, open and edit the my.cnf here: /etc/my.cnf
Restart the service or server, it works!