Linux下mysql的root密码丢失重置

1、关闭mysql服务

[root@12c ~]# service mysqld stop

停止 mysqld：                                              [确定]

2、使用skip--grant-tables参数启动数据库

方法一，直接改配置文件修改/etc/my.cnf 增加skip-grant-tables参数启动如下。5.7版本下使用root 还需要j加入explicit_defaults_for_timestamp=true

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
skip-grant-tables
#explicit_defaults_for_timestamp=true

这种方法不推荐，修改完后需要改回来。
方法二，启动时用命令启动，记得保留这个窗口

[root@12c ~]# /etc/init.d/mysqld start --skip-grant-tables
正在启动 mysqld：                                          [确定]

3、测试登陆，并修改root密码

重启动一个命令窗口，为了区别，这里注意用户已经不是root了

[oracle@12c ~]$ mysql -uroot 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
| zebra              |
+--------------------+
4 rows in set (0.00 sec)

修改密码发两种方式

方式一5.6：

mysql> update mysql.user set password=password('mysql') where user='root';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 3  Changed: 0  Warnings: 0

5.7版本

mysql> update mysql.user set authentication_string=password('mysql') where user='root';
Query OK, 1 row affected, 1 warning (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 1

方式二：

mysql> set password for root@localhost=password('mysql');
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

第二种方式在此时是不能修改密码的。

4、刷新权限

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

5、关闭mysql服务，再次启动数据库

[root@12c ~]# service mysqld stop
停止 mysqld：                                              [确定]
[root@12c ~]# service mysqld start
正在启动 mysqld：                                          [确定]
[root@12c ~]# mysql -uroot -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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
| zebra              |
+--------------------+
4 rows in set (0.00 sec)

mysql> 

5.7使用SQL修改密码

mysql> alter user 'root'@'localhost' identified by 'youpassword';  

修改完毕。