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 |+-----------+------+-------------------------------------------+ |
