.jpg)
下面小編就為大家?guī)硪黄獪\談mysql密碼遺忘和登陸報(bào)錯(cuò)的問題。小編覺得挺不錯(cuò)的，現(xiàn)在就分享給大家，也給大家做個(gè)參考。一起跟隨小編過來看看吧
mysql登錄密碼忘記，其實(shí)解決辦法很簡單，只需要在mysql的主mysqlmy.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
登錄后重置密碼
[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，然后重啟mysql
[root@test-huanqiu?~]#?vim?/etc/my.cnf........#skip-grant-tables [root@test-huanqiu?~]#?/etc/init.d/mysqld?restart [root@test-huanqiu?~]#?mysql?-p123456mysql>
———————————————————————————————————————–
發(fā)現(xiàn)的一個(gè)坑：
mysql之前進(jìn)行了全量備份，在恢復(fù)后，發(fā)現(xiàn)用之前的密碼登陸不進(jìn)去了！
使用上面的方法，無密碼登陸后再重置密碼，但是重置密碼后發(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)
解決：
mysql，再重置密碼
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:?0 mysql>?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:?0 mysql>?select?host,user,password?from?user; +-----------+------+-------------------------------------------+ |?host?|?user?|?password?| +-----------+------+-------------------------------------------+ |?localhost?|?root?|?*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9?| |?127.0.0.1?|?root?|?*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9?| +-----------+------+-------------------------------------------+
然后使用重置后的密碼就能正常登陸了！
——————————————————————————————————————
mysql登錄報(bào)錯(cuò)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>
—————————————————————————————————-
啟動(dòng)mysql的時(shí)候報(bào)錯(cuò)：
Starting?MySQL....?ERROR!?The?server?quit?without?updating?PID?file?(/data/mysql/data/mysql.pid).
嘗試的解決辦法：
（1）權(quán)限問題
可能是mysql.pid文件沒有寫的權(quán)限，將mysql的mysql目錄和數(shù)據(jù)目錄的權(quán)限都設(shè)置成mysql啟動(dòng)用戶權(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ù)的啟動(dòng)。
去mysql的數(shù)據(jù)目錄看看，如果存在mysql-bin.index，就立刻刪掉它，它就是罪魁禍?zhǔn)琢耍?/p>
（4）mysql在啟動(dòng)時(shí)沒有指定配置文件時(shí)會(huì)使用/etc/my.cnf配置文件，請打開這個(gè)文件查看在[mysqld]節(jié)下有沒有指定數(shù)據(jù)目錄。
在[mysqld]下添加設(shè)置，如datadir = /data/mysql/data
（5）skip-federated字段問題
檢查一下my.cnf文件中有沒有沒被注釋掉的skip-federated字段，如果有就立即注釋掉。
（6）錯(cuò)誤日志目錄不存在
去my.cnf文件下是否有l(wèi)og日志配置路徑，如果有，查看下日志目錄是否存在，日志目錄權(quán)限要確保是mysql啟動(dòng)用戶權(quán)限。
（7）selinux惹的禍，如果是centos系統(tǒng)，默認(rèn)會(huì)開啟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，報(bào)錯(cuò)如下：
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)方式），放棄對mysql的監(jiān)聽；
5）排查DNS解析問題,檢查mysql服務(wù)端192.168.1.14的my.cnf文件里是否設(shè)置了skip_name_resolve。這個(gè)參數(shù)加上后，不支持主機(jī)名的連接方式。
6）排查–port問題，有可能mysql服務(wù)端192.168.1.14的MySQL port不是默認(rèn)3306，比如是3307端口，這樣，遠(yuǎn)程連接的時(shí)候要加上–port=3307
7）排查用戶和密碼問題， 其實(shí)用戶和密碼的錯(cuò)誤，不會(huì)出現(xiàn)111的，所以排除用戶密碼問題
ERROR 1045 (28000): Access denied for user ‘root’@’XXXX’ (using password: YES)
.png)
