Paincker

MySQL 5.7.16可登陆Shell但不能改root密码

发表于 分类于 阅读次数： Valine： 本文字数： 4.2k 阅读时长 ≈ 4 分钟

现象：Ubuntu系统，新安装的MySQL 5.7.16，在主机本地的SSH中输入任何密码都能登录root，而尝试了网上各种方法改密码包括官网资料，都不能用phpMyAdmin登录。

尝试新建了一个用户，发现新建的用户可以在phpMyAdmin登录。

具体查看user表，终于发现root中的plugin字段和其他用户不一样（其他都是mysql_native_password，root用户的是auth_socket），手动改成一样，没想到再改密码就好了。具体原因不怎么关心，所以也没有再去详细研究。

  1. mysql> select * from mysql.user;
  2. +-----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
  3. Host User Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv Create_tablespace_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections plugin authentication_string password_expired password_last_changed password_lifetime account_locked
  4. +-----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
  5. localhost root Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y 0 0 0 0 auth_socket N 2016-11-22 14:39:35 NULL N
  6. localhost mysql.sys N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0 mysql_native_password *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE N 2016-11-22 14:39:35 NULL Y
  7. localhost debian-sys-maint Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y 0 0 0 0 mysql_native_password *C39C7EF713969DE9B80483810249197BAEFE695C N 2016-11-22 14:39:36 NULL N
  8. localhost jzj N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0 mysql_native_password *EFF8F9D89394C61B877B5644F4035E47D4F37B70 N 2016-11-24 06:27:36 NULL N
  9. +-----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
  10. 4 rows in set (0.00 sec)

注意，MySQL 5.7版本用户相关的操作和老版本不一样，user表中的字段也不一样了，且对密码的复杂程度有较高要求（长度、字母大小写、数字、特殊符号等）。

创建用户

  1. mysql> CREATE USER 'jzj'@'localhost' IDENTIFIED BY 'Password@123';
  2. Query OK, 0 rows affected (0.00 sec)

查看当前用户

  1. mysql> select current_user();
  2. +----------------+
  3. current_user()
  4. +----------------+
  5. root@localhost
  6. +----------------+
  7. 1 row in set (0.00 sec)

设置用户密码：

  1. mysql> SET PASSWORD FOR 'jeffrey'@'localhost' = '新密码';

设置当前登录用户的密码：

  1. mysql> SET PASSWORD = '新密码';

改完用户信息后可能要调用下面的命令刷新才能生效

  1. mysql> FLUSH PRIVILEGES;

参考官方文档：

http://dev.mysql.com/doc/refman/5.7/en/set-password.html

相关文章