您的浏览器不支持CSS3,建议使用Firfox、Chrome等浏览器,以取得最佳显示效果

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

Linux, Web开发 448℃ 0 5个月前 (11-25)

现象: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

来自为知笔记(Wiz)
0

暂无评论

评论前:需填写以下信息,或 登录

ERROR: si-captcha.php plugin: GD image support not detected in PHP!

Contact your web host and ask them to enable GD image support for PHP.

ERROR: si-captcha.php plugin: imagepng function not detected in PHP!

Contact your web host and ask them to enable imagepng for PHP.

用户登录

忘记密码?