mysql登錄密碼忘記，其實解決辦法很簡單，只需要在mysql的主配置文件my.cnf里添加一行“跳過授權(quán)表”的參數(shù)選擇即可！
在my.cnf中添加下面一行：
[root@test-huanqiu ~]# vim /etc/my.cnf //在[mysqld]區(qū)域里添加
........
skip-grant-tables //跳過授權(quán)表
然后重啟mysql服務(wù)，即可無密碼登錄
[root@test-huanqiu ~]# /etc/init.d/mysqld restart
登錄后重置密碼
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
|
[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>
-----------------------------------------------------------------------------------------------------------------------
發(fā)現(xiàn)的一個坑：
mysql之前進(jìn)行了全量備份，在恢復(fù)后，發(fā)現(xiàn)用之前的密碼登陸不進(jìn)去了！
使用上面的方法，無密碼登陸后再重置密碼，但是重置密碼后發(fā)現(xiàn)仍然登陸不進(jìn)去。
最后發(fā)現(xiàn)是因為mysql.user表內(nèi)容被清空了！
mysql> select host,user,password from user;
Empty set (0.00 sec)
解決：
插入數(shù)據(jù)，再重置密碼
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
|
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
可知，當(dāng)前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）權(quán)限問題
可能是mysql.pid文件沒有寫的權(quán)限，將mysql的安裝目錄和數(shù)據(jù)目錄的權(quán)限都設(shè)置成mysql啟動用戶權(quán)限。比如都修改為mysql:mysql權(quán)限
（2）可能進(jìn)程里已經(jīng)存在mysql進(jìn)程
ps -ef|grep mysql 查出要是有mysql進(jìn)程存在，就kill掉，再嘗試重啟mysql
（3）可能是多次在機(jī)器上安裝mysql，有殘余數(shù)據(jù)影響了服務(wù)的啟動。
去mysql的數(shù)據(jù)目錄看看，如果存在mysql-bin.index，就立刻刪掉它，它就是罪魁禍?zhǔn)琢耍?/p>
（4）mysql在啟動時沒有指定配置文件時會使用/etc/my.cnf配置文件，請打開這個文件查看在[mysqld]節(jié)下有沒有指定數(shù)據(jù)目錄。
在[mysqld]下添加設(shè)置，如datadir = /data/mysql/data
（5）skip-federated字段問題
檢查一下my.cnf文件中有沒有沒被注釋掉的skip-federated字段，如果有就立即注釋掉。
（6）錯誤日志目錄不存在
去my.cnf文件下是否有l(wèi)og日志配置路徑，如果有，查看下日志目錄是否存在，日志目錄權(quán)限要確保是mysql啟動用戶權(quán)限。
（7）selinux惹的禍，如果是centos系統(tǒng)，默認(rèn)會開啟selinux
閉它，打開/etc/selinux/config，把SELINUX=enforcing改為SELINUX=disabled后存盤退出重啟機(jī)器試試。
（8）重新初始化mysql數(shù)據(jù)試試
切換到mysql的安裝目錄下
./scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/data/mysql/data --user=mysql
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
使用mysql服務(wù)端授權(quán)的信息登錄mysql，報錯如下：
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.1.14' (111)
可能的原因有：
1）可能網(wǎng)絡(luò)連接問題，遠(yuǎn)程ping 192.168.1.14 ，能ping通，排除此情況；
2）mysql服務(wù)端192.168.1.14的iptables里做了3306端口連接的白名單限制；
3）mysql服務(wù)端192.168.1.14的my.cnf文件里配置了bind_address地址綁定，不允許本機(jī)連接；
4）mysql服務(wù)端192.168.1.14的my.cnf文件里配置了skip_networking,這使用MySQL只能通過本機(jī)Socket連接(socket連接也是
本地連接的默認(rèn)方式），放棄對TCP/IP的監(jiān)聽；
5）排查DNS解析問題,檢查mysql服務(wù)端192.168.1.14的my.cnf文件里是否設(shè)置了skip_name_resolve。這個參數(shù)加上后，不支
持主機(jī)名的連接方式。
6）排查--port問題，有可能mysql服務(wù)端192.168.1.14的MySQL port不是默認(rèn)3306，比如是3307端口，這樣，遠(yuǎn)程連接的時候要加上--port=3307
7）排查用戶和密碼問題， 其實用戶和密碼的錯誤，不會出現(xiàn)111的，所以排除用戶密碼問題
ERROR 1045 (28000): Access denied for user 'root'@'XXXX' (using password: YES)
以上這篇淺談mysql密碼遺忘和登陸報錯的問題就是小編分享給大家的全部內(nèi)容了，希望能給大家一個參考，也希望大家多多支持服務(wù)器之家。
