mysql忘记root密码

1.centos服务器忘记root密码，登录服务器，打开/etc/my.cnf，添加skip_grant_tables

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
#在my.ini，[mysqld]下添加一行，使其登录时跳过权限检查
skip_grant_tables
user=mysql

保存后退出。

2.重新启动mysql，如果提示“mysqld: unrecognized service”，则需要安装mysql-server服务：yum -y install mysql-server,没有提示直接重启。

[root@cuishougt bin]# service mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]

3.这时再进入mysql是跳过密码验证的，提示输入密码时直接回车，然后更新root密码，刷新权限后退出。

[root@cuishougt bin]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.73 Source distribution

Copyright (c) 2000, 2013, 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> 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> updates 
Display all 1105 possibilities? (y or n)
mysql> UPDATE user SET Password = password ( 'root' ) WHERE User = 'root' ;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> quit;

update mysql.user  set password=password('root') where user='root'；

（此时提示ERROR 1054 (42S22): Unknown column 'password' in 'field list'）

（这是怎么回事？）原来是mysql数据库下已经没有password这个字段了，password字段改成了authentication_string，改用

UPDATE mysql.user SET Host='%' WHERE User='root' AND Host='localhost';
#mysql8 默认密码要求是：包含大小写字母、数字以及特殊字符，位数要求八位及以上。
ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'Wo123456';

 

当你登陆mysql之后你会发现，当你执行命令时会出现

ERROR 1820 (HY000): You must reset your password using ALTER USER statement；

当你执行了SET PASSWORD = PASSWORD('123456')；

出现：ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

你需要执行两个参数来把mysql默认的密码强度的取消了才行

set global validate_password_policy=LOW;
set global validate_password_length=6;
然后你在执行
SET PASSWORD = PASSWORD('123456')；

 

4.再次修改/etc/my.conf文件，去掉或者注释skip_grant_tables代码，然后重启mysql服务，再次登录就可以用新密码登录了。

[root@cuishougt bin]# service mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]
[root@cuishougt bin]# mysql -u root -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@cuishougt bin]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.73 Source distribution

5.windows下mysql8.0忘记root密码重置方法如下

1）停止mysql服务

net stop mysql

2)输入 mysqld --skip-grant-tables --shared-memory （第一个参数是跳过权限检查，第二个似乎是 MySQL 8 需要的）

mysqld --skip-grant-tables --shared-memory

3）重新打开一个命名窗口登录mysql，这时不需要输入密码，直接回车进入mysql，选择mysql库，然后重置密码

UPDATE mysql.user SET authentication_string=null WHERE User='root';
FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY '123456';
FLUSH PRIVILEGES;
exit;

4）重启mysql服务，这时就可以用新的密码登录root账户了。