MySQL 8.0 用户管理 更改密码 创建用户登录 遇到很迷的问题

Question

1、MySQL版本8.0根据官方文档来创建用户，设置密码。然后测试登录以及更改密码。1.1、创建用户并登陆测试在MySQL服务器上创建用户。mysql>createuser'aplan'@'%'identifiedby'Aplan123@';QueryOK,0rowsaffected(0.00sec)mysql>flushprivileges;QueryOK,0rowsaffected(0.00sec)mysql>grantallonaplan.*to'aplan'@'%';QueryOK,0rowsaffected(0.15sec)mysql>mysql>mysql>flushprivileges;QueryOK,0rowsaffected(0.00sec)然后使用用户登录MySQL测试[root@testzhiwei]#/usr/local/mysql/bin/mysql-uaplan-pAplan123@Warning:Usingapasswordonthecommandlineinterfacecanbeinsecure.ERROR1045(28000):Accessdeniedforuser'aplan'@'localhost'(usingpassword:YES)[root@testzhiwei]#[root@testzhiwei]#[root@testzhiwei]#[root@testzhiwei]#/usr/local/mysql/bin/mysql-uaplan-pEnterpassword:ERROR1045(28000):Accessdeniedforuser'aplan'@'localhost'(usingpassword:YES)无法登录。1.2、更改用户密码mysql>ALTERUSER'aplan'@'%'IDENTIFIEDBY'password';ERROR1064(42000):YouhaveanerrorinyourSQLsyntax;checkthemanualthatcorrespondstoyourMySQLserverversionfortherightsyntaxtousenear'IDENTIFIEDBY'password''atline1?????我这哪里的语法错了？？？

Answer 1

Thecaching_sha2_passwordandsha256_passwordauthenticationpluginsprovidemoresecurepasswordencryptionthanthemysql_native_passwordplugin,andcaching_sha2_passwordprovidesbetterperformancethansha256_password.Duetothesesuperiorsecurityandperformancecharacteristicsofcaching_sha2_password,itisasofMySQL8.0thepreferredauthenticationplugin,andisalsothedefaultauthenticationpluginratherthanmysql_native_password.
CREATEUSER'aplan'@'%'IDENTIFIEDWITHmysql_native_passwordBY'Aplan123@';
                            

Answer 2

注意文本格式，有时候在其他地方直接复制粘贴执行会有问题，例如从word复制执行。另外密码如果有特殊符号，可以用单引号括起来。以下是测试，没发现有什么问题：mysql>createuser'aplan'@'%'identifiedby'Aplan123@';QueryOK,0rowsaffected(0.02sec)
mysql>selectuser,hostfrommysql.user;
user
host
aplan
%
mysql.infoschema
localhost
mysql.session
localhost
mysql.sys
localhost
root
localhost
5rowsinset(0.00sec)
mysql>flushprivileges;QueryOK,0rowsaffected(0.04sec)
mysql>exitBye[root@linux-basemysql]#mysql-uaplan-p'Aplan123@'mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.WelcometotheMySQLmonitor.Commandsendwith;org.YourMySQLconnectionidis10Serverversion:8.0.12MySQLCommunityServer-GPL
Copyright(c)2000,2018,Oracleand/oritsaffiliates.Allrightsreserved.
OracleisaregisteredtrademarkofOracleCorporationand/oritsaffiliates.Othernamesmaybetrademarksoftheirrespectiveowners.
Type'help;'or'h'forhelp.Type'c'toclearthecurrentinputstatement.
mysql>selectuser();
user()
aplan@localhost
1rowinset(0.00sec)mysql>alteruseraplan@'%'identifiedby'password';QueryOK,0rowsaffected(0.04sec)
mysql>exitBye[root@linux-basemysql]#mysql-uaplan-p'password'mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.WelcometotheMySQLmonitor.Commandsendwith;org.YourMySQLconnectionidis11Serverversion:8.0.12MySQLCommunityServer-GPL
Copyright(c)2000,2018,Oracleand/oritsaffiliates.Allrightsreserved.
OracleisaregisteredtrademarkofOracleCorporationand/oritsaffiliates.Othernamesmaybetrademarksoftheirrespectiveowners.
Type'help;'or'h'forhelp.Type'c'toclearthecurrentinputstatement.
mysql>selectuser();
user()
aplan@localhost
1rowinset(0.00sec)