mysql8出现1045报错+常用的加密plugin汇总

痛定思痛。 2023-01-11 10:58 109阅读 0赞

# 一些准备工作 #

/etc/mysql/mysql.conf.d/mysqld.cnf

的\[mysqld\]下面加入skip-grant-tables

service mysql restart

然后输入mysql就可以登录客户端

# 操作列表 #

<table> 
 <tbody> 
  <tr> 
   <td style="width:310px;"> <h3><strong>操作</strong></h3> </td> 
   <td style="width:698px;"> <h3><strong>命令</strong></h3> </td> 
   <td style="width:510px;"> <h3><strong>使用备注(必须遵守,否则会碰到相关error)</strong></h3> </td> 
  </tr> 
  <tr> 
   <td style="width:310px;"><strong>查询使用的plugin</strong></td> 
   <td style="width:698px;"> <p>use mysql;</p> <p>select user,plugin from user where user='appleyuchi';</p> </td> 
   <td style="width:510px;">无</td> 
  </tr> 
  <tr> 
   <td style="width:310px;"><strong>更换plugin</strong></td> 
   <td style="width:698px;">update user set plugin='mysql_native_password' where user='root';</td> 
   <td style="width:510px;">无</td> 
  </tr> 
  <tr> 
   <td style="width:310px;"><strong>查询授权的/host/user/authentication_string</strong></td> 
   <td style="width:698px;">select host,user,authentication_string from mysql.user;</td> 
   <td style="width:510px;">无</td> 
  </tr> 
  <tr> 
   <td style="width:310px;"><strong>删除某个用户</strong></td> 
   <td style="width:698px;">drop user appleyuchi@'%'</td> 
   <td style="width:510px;">这里的%是上面一句命令中得到的host,也可能是其他域名或者IP</td> 
  </tr> 
  <tr> 
   <td style="width:310px;"><strong>查看某个用户被授予的权限[3]</strong></td> 
   <td style="width:698px;">select * from mysql.user where user='appleyuchi'\G;</td> 
   <td style="width:510px;">&nbsp;</td> 
  </tr> 
  <tr> 
   <td style="width:310px;"><strong>设置密码</strong></td> 
   <td style="width:698px;">ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'appleyuchi';</td> 
   <td style="width:510px;">如果运行不顺利参考[2]解决</td> 
  </tr> 
  <tr> 
   <td style="width:310px;"><strong>新建用户</strong></td> 
   <td style="width:698px;"> <p>flush privileges;</p> <p>create user root@localhost identified by 'appleyuchi';</p> <p>CREATE USER &nbsp;'root'@'%' IDENTIFIED BY &nbsp;'root';</p> <p>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;</p> <p>flush privileges;</p> </td> 
   <td style="width:510px;"> <p>由于是skip-grant-tables模式,</p> <p>所以有时候需要先执行flush privileges</p> <p>才能继续往下执行其他命令,</p> <p>所以左侧的flush privileges;执行了两次.</p> </td> 
  </tr> 
  <tr> 
   <td style="width:310px;"><strong>授予权限</strong></td> 
   <td style="width:698px;">GRANT ALL PRIVILEGES ON *.* TO 'appleyuchi'@'%' WITH GRANT OPTION;</td> 
   <td style="width:510px;">&nbsp;</td> 
  </tr> 
  <tr> 
   <td style="width:310px;"><strong>查看当前用户所拥有的权限</strong></td> 
   <td style="width:698px;"> <p>SHOW GRANTS FOR 'appleyuchi'@'%';</p> <p>&nbsp;</p> <p>select host,user,Grant_priv,Super_priv from mysql.user;</p> </td> 
   <td style="width:510px;">&nbsp;</td> 
  </tr> 
  <tr> 
   <td style="width:310px;"><strong>刷新权限</strong></td> 
   <td style="width:698px;">flush privileges;</td> 
   <td style="width:510px;">&nbsp;</td> 
  </tr> 
  <tr> 
   <td style="width:310px;"><strong>更换密码</strong>[5]</td> 
   <td style="width:698px;">ALTER USER appleyuchi IDENTIFIED WITH mysql_native_password BY 'appleyuchi';</td> 
   <td style="width:510px;">&nbsp;</td> 
  </tr> 
 </tbody> 
</table>

GRANT OPTION指的是把自己的权限赋予其他用户的能力.

all权限≠拥有grant option权限

\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

加密用的plugin

<table style="width:427px;"> 
 <tbody> 
  <tr> 
   <td style="width:207px;"><strong>plugin名字</strong></td> 
   <td style="width:216px;"><strong>备注</strong></td> 
  </tr> 
  <tr> 
   <td style="width:207px;">auth_socket</td> 
   <td style="width:216px;">无密码登录</td> 
  </tr> 
  <tr> 
   <td style="width:207px;">caching_sha2_password</td> 
   <td style="width:216px;">[1]中说不支持客户端使用</td> 
  </tr> 
  <tr> 
   <td style="width:207px;">mysql_native_password</td> 
   <td style="width:216px;">推荐使用</td> 
  </tr> 
 </tbody> 
</table>

# 总结 #

根据上面的2张表格,

你除了要检查plugin是不是mysql\_native\_password以外,

还要查看权限,

还要查看是否具有grant option.

缺一不可.

# 错误示范 #

update mysql.user set authentication\_string = "appleyuchi"  where user = "appleyuchi" ;

**注意上面的这个绝对是错误的,因为不可能使用明文保存密码**

select host,user,authentication\_string from mysql.user;

得到结果如下:

![20210127133811343.png][]

## ##

### **Reference:** ###

**\[1\][mysql错误：mysql\_native\_password][mysql_mysql_native_password]**

**\[2\][mysql8碰到ERROR 1396 (HY000)的解决方案][mysql8_ERROR 1396 _HY000]**

**\[3\][MySQL 查看用户授予的权限][MySQL]**

**\[4\][mysql root权限优化后没有grant权限][mysql root_grant]**

**\[5\][mysql8使用grant授权修改][mysql8_grant]**

[20210127133811343.png]: /images/20221119/8d5bfb254345451d836ba0205cec7b1f.png
[mysql_mysql_native_password]: https://blog.csdn.net/qq_43395428/article/details/104795256
[mysql8_ERROR 1396 _HY000]: https://blog.csdn.net/appleyuchi/article/details/113255257?spm=1001.2014.3001.5502
[MySQL]: https://www.cnblogs.com/wangchaoyuana/p/7545419.html
[mysql root_grant]: https://blog.csdn.net/weixin_34343308/article/details/92263792
[mysql8_grant]: https://blog.csdn.net/yunyexiangfeng/article/details/82876964