mysql登錄密碼忘記，其實(shí)解決辦法很簡(jiǎn)單，只需要在mysql的主配置文件my.cnf里添加一行“跳過(guò)授權(quán)表”的參數(shù)選擇即可！
在my.cnf中添加下面一行：
[root@test-huanqiu ~]# vim /etc/my.cnf //在[mysqld]區(qū)域里添加
........
skip-grant-tables //跳過(guò)授權(quán)表
然后重啟mysql服務(wù)，即可無(wú)密碼登錄
[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: 0
mysql> 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>
-----------------------------------------------------------------------------------------------------------------------
發(fā)現(xiàn)的一個(gè)坑：
mysql之前進(jìn)行了全量備份，在恢復(fù)后，發(fā)現(xiàn)用之前的密碼登陸不進(jìn)去了！
使用上面的方法，無(wú)密碼登陸后再重置密碼，但是重置密碼后發(fā)現(xiàn)仍然登陸不進(jìn)去。
最后發(fā)現(xiàn)是因?yàn)閙ysql.user表內(nèi)容被清空了！
mysql> select host,user,password from user;
Empty set (0.00 sec)
解決：
插入數(shù)據(jù)，再重置密碼
| 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 |+-----------+------+-------------------------------------------+ |
