但行好事
莫论前程❤

MySQL数据库系列(5)—–密码管理(忘记密码)

在MySQL中,可以使用如下几种方法修改或设置mysql用户的密码.

方法一:

​ 管理员可以在系统命令行中使用mysqladmin命令设置mysql用户的密码.

使用mysqladmin设置密码的命令语法如下:

mysqladmin -u用户名   -p旧密码  password 新密码

比如将root用户的密码从123123改为123456

mysqladmin -uroot -p123123  password 123456

如果数据库刚刚被初始化,root用户还没有密码,这种情况下,可以不指定旧密码,所以-p可以省略,直接指定新密码即可,示例如下.

mysqladmin -uroot password 123123

方法二:

​ 使用root用户登录以后,使用SET PASSWORD命令设置用户密码,命令如下:

mysql> set PASSWORD for 'user'@'IPorHost' = PASSWORD('newpassword');
# 例如
mysql> set PASSWORD for liupenggc@liupeng.mobi = password('123123');

忘记密码–MySQL5.7.9, Ubuntu16.4:

如果忘记了mysql的root用户的密码,可以使用如下方法.重置root密码.

  1. 编辑mysql的配置文件/etc/mysql/mysql.conf.d/mysqld.cnf

    在[mysqld]段下加入一行“skip-grant-tables”

[mysqld]
#
# * Basic Settings
#
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
skip-grant-tables
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
  1. 重启mysql服务
service mysql restart
  1. 用空密码进入mysql管理命令行。
ubuntu@VM-0-5-ubuntu:/etc/mysql/mysql.conf.d$ mysql 
 Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.21-0ubuntu0.16.04.1 (Ubuntu)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 
  1. 切换到mysql库。
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed 
mysql> 
  1. 执行如下命令把密码重置为123456。退出数据库管理。
  • MySQL 5.7.6 以及最新版本:
mysql> update mysql.user set authentication_string=password('123456') where user='root' and Host ='localhost';
Query OK, 0 rows affected (0.00 sec)    
Rows matched: 4  Changed: 0  Warnings: 0   
  • MySQL 5.7.5 或更早之前的版本r:
 mysql> update mysql.user set password=PASSWORD('newpass') where User='root';

注:如果报错

ERROR 2059 (HY000): Authentication plugin 'sha256_password' cannot be loaded: No such file or directory
# 则
mysql>update user set plugin="mysql_native_password";  
mysql>flush privileges;
mysql>quit;
mysql> select user,plugin from mysql.user;
+------------------+-----------------------+
| user             | plugin                |
+------------------+-----------------------+
| root             | mysql_native_password |
| mysql.session    | mysql_native_password |
| mysql.sys        | mysql_native_password |
| debian-sys-maint | mysql_native_password |
| phpmyadmin       | mysql_native_password |
| liupenggc        | mysql_native_password |
+------------------+-----------------------+
6 rows in set (0.00 sec)
  1. 刷新mysql的系统权限相关表
mysql>flush privileges;
mysql>quit;
  1. 回到/etc/mysql/mysql.conf.d/mysqld.cnf,把刚才加入的那一行“skip-grant-tables”注释或删除掉。
  2. 再次重启mysql服务sudo service mysql restart,使用新的密码登陆,修改成功。
赞(1) 打赏
未经允许不得转载:刘鹏博客 » MySQL数据库系列(5)—–密码管理(忘记密码)
分享到: 更多 (0)

评论 抢沙发

评论前必须登录!

 

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏