MySQL忘记root密码后可以使用下面的方法修改。
1、登录MySQL所在的服务器，手工kill掉MySQL进程
kill `cat $mysql_data_dir/hostname.pid`
$mysql_data_dir/hostname.pid为MySQL数据目录，它记录了MySQL服务的进程号。
[root@rhel6 ~]# ps -ef |grep mysql
root 6602 1 0 21:39 ? 00:00:00 /bin/sh ./mysqld_safe --skip-grant-tables --user=mysql
mysql 6754 6602 0 21:39 ? 00:00:01 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --skip-grant-tables --log-error=/var/lib/mysql/rhel6.er
r --pid-file=/var/lib/mysql/rhel6.pidroot 6851 6830 0 21:47 pts/3 00:00:00 grep mysql
[root@rhel6 ~]# cat /var/lib/mysql/rhel6.pid
6754
[root@rhel6 ~]# kill `cat /var/lib/mysql/rhel6.pid `
[root@rhel6 ~]# ps -ef |grep mysql
root 6859 6830 0 21:48 pts/3 00:00:00 grep mysql
2、使用--skip-grant-tables选项重启MySQL服务
--skip-grant-tables意思是启动MySQL服务时跳过权限表认证。启动后，连接到MySQL的root将不需要口令。或者在/etc/my.cnf中添加skip-grant-tables参数，启动mysql，service mysql start/systemctl start mysql
[root@rhel6 bin]# ./mysqld_safe --skip_grant-tables --user=mysql &
[1] 6900
[root@rhel6 bin]# 161122 21:52:03 mysqld_safe Logging to '/var/lib/mysql/rhel6.err'.
161122 21:52:03 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
[root@rhel6 bin]# ps -ef |grep mysql
root 6900 6830 0 21:52 pts/3 00:00:00 /bin/sh ./mysqld_safe --skip_grant-tables --user=mysql
mysql 7052 6900 1 21:52 pts/3 00:00:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --skip-grant-tables --log-error=/var/lib/mysql/rhel6.er
r --pid-file=/var/lib/mysql/rhel6.pidroot 7075 6830 0 21:52 pts/3 00:00:00 grep mysql
[root@rhel6 bin]# mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.34-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, 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>
3、修改root密码
mysql> set password=password('123456');
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this 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> update user set password=password('123456') where user='root' and host='localhost';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
由于使用了--skip-grant-tables选项启动，使用"set password"命令更改密码失败，直接更新user表的password字段后更改密码成功。
在5.7版本中password有规则策略，123456不符合。
4、刷新权限表
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
5、重新用root登录时，必须输入新密码
[root@rhel6 bin]# mysql -uroot
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@rhel6 bin]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.34-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, 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>
参考：《深入浅出MySQL》
本文转自hbxztc 51CTO博客，原文链接：http://blog.51cto.com/hbxztc/1875966，如需转载请自行联系原作者
