mysql登录密码忘记，其实解决办法很简单，只需要在mysql的主配置文件my.cnf里添加一行“跳过授权表”的参数选择即可！
在my.cnf中添加下面一行：
[root@test-huanqiu ~]# vim /etc/my.cnf //在[mysqld]区域里添加
........
skip-grant-tables //跳过授权表
然后重启mysql服务，即可无密码登录
[root@test-huanqiu ~]# /etc/init.d/mysqld restart
登录后重置密码
| [root@test-huanqiu ~]# mysql mysql> select host,user,password from mysql.user;+--------------------+------+-------------------------------------------+| host | user | password |+--------------------+------+-------------------------------------------+| localhost | root | *481ACA1BD6D1E86221244904E9C0FABA33B40B84 || host-192-168-1-117 | root | || 127.0.0.1 | root | || ::1 | root | || localhost | | || host-192-168-1-117 | | |+--------------------+------+-------------------------------------------+6 rows in set (0.00 sec)mysql> update mysql.user set password=password("123456") where host="localhost" and user="root";Query OK, 1 row affected (0.02 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> flush privileges;Query OK, 0 rows affected (0.01 sec)mysql> select host,user,password from mysql.user;+--------------------+------+-------------------------------------------+| host | user | password |+--------------------+------+-------------------------------------------+| localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 || host-192-168-1-117 | root | || 127.0.0.1 | root | || ::1 | root | || localhost | | || host-192-168-1-117 | | |+--------------------+------+-------------------------------------------+6 rows in set (0.00 sec)mysql> |
再次将my.cnf里添加的那一行注释，然后重启mysql
[root@test-huanqiu ~]# vim /etc/my.cnf
........
#skip-grant-tables
[root@test-huanqiu ~]# /etc/init.d/mysqld restart
[root@test-huanqiu ~]# mysql -p123456
mysql>
-----------------------------------------------------------------------------------------------------------------------
发现的一个坑：
mysql之前进行了全量备份，在恢复后，发现用之前的密码登陆不进去了！
使用上面的方法，无密码登陆后再重置密码，但是重置密码后发现仍然登陆不进去。
最后发现是因为mysql.user表内容被清空了！
mysql> select host,user,password from user;
Empty set (0.00 sec)
解决：
插入数据，再重置密码
| mysql> insert into user(host,user,password) values("localhost","root","123456");Query OK, 1 row affected, 3 warnings (0.01 sec)mysql> select host,user,password from user;+-----------+------+----------+| host | user | password |+-----------+------+----------+| localhost | root | 123456 |+-----------+------+----------+1 row in set (0.00 sec)mysql> update mysql.user set password=password("123456") where host="localhost" and user="root";Query OK, 1 row affected (0.01 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> select host,user,password from user;+-----------+------+-------------------------------------------+| host | user | password |+-----------+------+-------------------------------------------+| localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |+-----------+------+-------------------------------------------+1 row in set (0.00 sec)mysql> insert into user(host,user,password) values("127.0.0.1","root","123456");Query OK, 1 row affected, 3 warnings (0.00 sec)mysql> select host,user,password from user;+-----------+------+-------------------------------------------+| host | user | password |+-----------+------+-------------------------------------------+| localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 || 127.0.0.1 | root | 123456 |+-----------+------+-------------------------------------------+2 rows in set (0.00 sec)mysql> update mysql.user set password=password("123456") where user="root";Query OK, 1 row affected (0.00 sec)Rows matched: 2 Changed: 1 Warnings: 0mysql> select host,user,password from user;+-----------+------+-------------------------------------------+| host | user | password |+-----------+------+-------------------------------------------+| localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 || 127.0.0.1 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |+-----------+------+-------------------------------------------+ |
