新安装的mysql版本(8.0.11),在跑一个demo时,需要修改root账号的原始密码。于是上网搜索了一下,又如下几种方法:
方法1) SET PASSWORD FOR 'root'@'localhost' = PASSWORD('admin');
方法2) UPDATE user SET Password = PASSWORD('admin') WHERE user = 'root';
方法3) mysqladmin -u root password oldpass "newpass"
以上几种方法都不能执行成功,其中方法1)和方法2)直接报语法错误,Error Code: 1064. You have an error in your SQL syntax; 方法3)报Access denied for use 'root'@'localhost' '(using password: YES)'。没办法了,于是发帖求助(见https://bbs.csdn.net/topics/392391222),了解到是因为mysql的版本升级修改了语法所致,mysql从5.7版本以后把修改root账号密码改为了
update user set authentication_string=password("newpasswd") where user="root";
可是执行时还是报错,“Client does not support authentication protocol requested by server;”,继续上网搜索,发现可能是我安装mysql的时候选择了默认的强加密方式所致,旧版本的加密方式是'mysql_native_password',新版本的mysql加密方式变了,见https://blog.csdn.net/da_learner/article/details/80202190。于是执行了以下命令,将加密方式改为和旧版本兼容
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';
结果还是报错,'23:22:23 update user set authentication_string=password(`admin`) where user='root' Error Code: 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 '(`admin`) where user='root'' at line 1 0.140 sec'
至此我已无力深究了,希望有哪位大神能指点一二。最后,只好用了如下方式来修改密码才改成功
ALTER USER 'root'@'localhost' IDENTIFIED BY 'admin';
期间,因为要把密码改为'admin',开始误以为admin是mysql的关键字,尝试用了反引号、双引号引起来,都没有用。