- 論壇徽章:
- 0
|
5可用積分
本帖最后由 cenalulu 于 2013-04-28 13:54 編輯
環(huán)境：
mysql -V
mysql Ver 14.14 Distrib 5.1.62, for pc-linux-gnu (i686) using readline 5.1
uname -r
2.6.18-308.el5
lsb_release -a
LSB Version: :core-4.0-ia32:core-4.0-noarch:graphics-4.0-ia32:graphics-4.0-noarch:printing-4.0-ia32:printing-4.0-noarch
Distributor ID: CentOS
Description: CentOS release 5.8 (Final)
Release: 5.8
Codename: Final
建立數(shù)據(jù)庫(kù)，多實(shí)例，方式是每個(gè)實(shí)例一個(gè)單獨(dú)的配置文件
已經(jīng)給root用戶(hù)賦予數(shù)據(jù)庫(kù)密碼，并可以用該密碼登錄數(shù)據(jù)庫(kù)
進(jìn)入后，用GRANT 授權(quán)從庫(kù)
mysql> GRANT replication slave on *.* to 'rep'@'192.168.88.%' identified by '123456abc';
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
就停止數(shù)據(jù)庫(kù) /data/3306/mysql stop 并查看進(jìn)程，的確停止了
用mysqld_safe --skip-grant-tables --skip-networking 的方式進(jìn)入數(shù)據(jù)庫(kù)，重新給root用戶(hù)賦予密碼，并
FLUSH RPIVILEGES;
quit;
再重啟數(shù)據(jù)庫(kù)，用密碼可以進(jìn)入數(shù)據(jù)庫(kù)
可以有權(quán)限使用所有的庫(kù)，但是grant再次報(bào)錯(cuò)
GRANT replication slave on *.* to 'rep'@'192.168.88.%' identified by '123456abc';
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
嘗試給root授予所有權(quán)限
GRANT ALL on *.* to 'root'@'localhost';
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
原因？已經(jīng)登入數(shù)據(jù)庫(kù)了，并有權(quán)限使用所有的庫(kù)了（嘗試use 各個(gè)庫(kù)，均可以）
是否有什么權(quán)限錯(cuò)誤，把權(quán)限表詳情放在下面，大家?guī)兔纯矗?br />
個(gè)人初步確定問(wèn)題可能在下面，User有空用戶(hù)，但是嘗試 delete from user where user is NULL or user=''; 并FLUSH PRIVIEGES;問(wèn)題還是如上所述
mysql> select Host,User,Grant_priv from db;
+-----------+-----------+------------+
| Host | User | Grant_priv |
+-----------+-----------+------------+
| % | | N |
| % | | N |
| localhost | wordpress | N |
+-----------+-----------+------------+
3 rows in set (0.00 sec)
mysql> select Host,Grant_priv from host;
Empty set (0.00 sec)
mysql> select User,Grantor from procs_priv;
Empty set (0.00 sec)
mysql> select User,Host,Grant_priv from user;
+-----------+-----------+------------+
| User | Host | Grant_priv |
+-----------+-----------+------------+
| root | localhost | N |
| root | 127.0.0.1 | Y |
| rep | | N |
| wordpress | localhost | N |
+-----------+-----------+------------+
4 rows in set (0.00 sec)
這個(gè)地方是不是有問(wèn)題？看起來(lái)，root@localhost沒(méi)有g(shù)rant權(quán)限，怎么解決？
mysql> select User,Host,Grant_priv from user;
+-----------+-----------+------------+
| User | Host | Grant_priv |
+-----------+-----------+------------+
| root | localhost | N |
| root | 127.0.0.1 | Y |
| rep | | N |
| wordpress | localhost | N |
+-----------+-----------+------------+
4 rows in set (0.00 sec)
mysql> select GRANTEE,PRIVILEGE_TYPE,IS_GRANTABLE from COLUMN_PRIVILEGES;
Empty set (0.00 sec)
而且，類(lèi)似的密碼問(wèn)題反復(fù)出現(xiàn)，比如，已經(jīng)設(shè)置好密碼，再次登錄，總是報(bào)錯(cuò)，總是需要停庫(kù)，再mysqld_safe --skip-grant-table --skip-networking的方式重新啟動(dòng)，再重設(shè)密碼，有時(shí)候還要?jiǎng)h除空用戶(hù)，才能登錄到數(shù)據(jù)庫(kù)
重要信息：
在此次報(bào)錯(cuò)之前，曾成功GRANT ALL給root@localhost
可是這次，來(lái)給從庫(kù)授權(quán)總是報(bào)錯(cuò)，再次嘗試給root授權(quán)也把錯(cuò)如上述
如何解決？大家?guī)蛶兔纯?/td> |
最佳答案
查看完整內(nèi)容
GRANT ALL on *.* to 'root'@'localhost';和GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY '123456' WITH GRANT OPTION;這兩個(gè)是不一樣的啊。另外你執(zhí)行不了應(yīng)該就是你當(dāng)前登錄的用戶(hù)沒(méi)有WITH GRANT OPTION權(quán)限。根據(jù)你給的權(quán)限表來(lái)看：mysql> select User,Host,Grant_priv from user;+-----------+-----------+------------+| User | Host | Grant_priv |+-----------+-----------+------------+| ...
|
免費(fèi)注冊(cè)
發(fā)表于 2013-04-26 00:37
發(fā)表于 2013-04-26 16:09