淺談mysql密碼遺忘和登陸報錯的問題

2024-07-24 13:12:49

字體：大中小

來源：轉載

供稿：網友

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

登錄后重置密碼

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>

再次將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 |+-----------+------+-------------------------------------------+

然后使用重置后的密碼就能正常登陸了！

------------------------------------------------------------------------------------------------------------------

mysql登錄報錯1：

[root@test-huanqiu ~]# mysql -p123456
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)

[root@test-huanqiu ~]# ps -ef|grep mysql
root 28279 1 0 12:55 ? 00:00:00 /bin/sh /usr/local/mysql//bin/mysqld_safe --datadir=/data/mysql/data --pid-file=/data/mysql/data/mysql.pid
mysql 29059 28279 0 12:55 ? 00:00:01 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql/ --datadir=/data/mysql/data --plugin-dir=/usr/local/mysql//lib/plugin --user=mysql --log-error=/data/mysql/data/mysql-error.log --pid-file=/data/mysql/data/mysql.pid --socket=/usr/local/mysql/var/mysql.sock --port=3306
root 30726 11268 0 12:58 pts/2 00:00:00 grep mysql

可知，當前mysql.sock文件路徑是/usr/local/mysql/var/mysql.sock，

解決辦法：做軟鏈接

[root@test-huanqiu ~]# ll /usr/local/mysql/var/mysql.sock
rwxrwxrwx. 1 mysql mysql 0 Nov 29 12:55 /usr/local/mysql/var/mysql.sock
[root@test-huanqiu ~]# rm -f /var/lib/mysql/mysql.sock
[root@test-huanqiu ~]# ln -s /usr/local/mysql/var/mysql.sock /var/lib/mysql/mysql.sock

這樣就沒問題了

[root@test-huanqiu ~]# mysql -p123456
mysql>

----------------------------------------------------------------------------------------------------

啟動mysql的時候報錯：

Starting MySQL.... ERROR! The server quit without updating PID file (/data/mysql/data/mysql.pid).

嘗試的解決辦法：

（1）權限問題

可能是mysql.pid文件沒有寫的權限，將mysql的安裝目錄和數據目錄的權限都設置成mysql啟動用戶權限。比如都修改為mysql:mysql權限

（2）可能進程里已經存在mysql進程

ps -ef|grep mysql 查出要是有mysql進程存在，就kill掉，再嘗試重啟mysql

（3）可能是多次在機器上安裝mysql，有殘余數據影響了服務的啟動。

去mysql的數據目錄看看，如果存在mysql-bin.index，就立刻刪掉它，它就是罪魁禍首了！