Ubuntu Server中LAMP下MySQL無法遠程連接問題1130
Ubuntu Server中LAMP下MySQL無法遠程連接該怎么辦：
很常見的問題發生了，我們怎么處理它?
環境：ubuntu Server (版本8.04以上對于LAMP應用都大同小異), cl@ubuntu.
目的：安裝LAMP重新配置RT系統。
軟件：服務器端已安裝LAMP組件，MySQL5.1版本;客戶端使用Navicat進行遠程連接。
問題② 提示”is not allowed to connect to this MySQL server” ,如圖2
對于數據庫的遠程連接不外乎兩方面入手：1、客戶端至服務器端的連通性。2、服務器端沒有合理配置。當然非要加上客戶端也沒有配置之類的，也尚可，比如ODBC的方式連接MySQL。
②當MySQL 連接服務器時發生”is not allowed to connect to this MySQL server”錯誤，我們要注意在MySQL的user表中修改host列的localhost為%，即可以遠程連接。
做如下操作：
mysql> use mysql --切換數據庫上下文
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select host,user,password from user; --查詢USER 表
+-----------+------------------+-------------------------------------------+
- | host | user | password |
+-----------+------------------+-------------------------------------------+
- | localhost | root | *MD5加密 |
- | ubuntu | root | *MD5加密 |
- | 127.0.0.1 | root | *MD5加密 |
- | localhost | debian-sys-maint | * MD5加密|
+-----------+------------------+-------------------------------------------+
- mysql> delete from user where user='root' and host <> '%'; --刪除多余用戶
- Query OK, 2 rows affected (0.00 sec)
- mysql> update user set host ='%' where host='localhost' and user='root'; --更新host
- Query OK, 1 row affected (0.00 sec)
- Rows matched: 1 Changed: 1 Warnings: 0
- mysql> select host,user,password from user;
+-----------+------------------+-------------------------------------------+
| host | user | password |
+-----------+------------------+-------------------------------------------+
| % | root | *MD5加密|
| localhost | debian-sys-maint | * MD5加密|
+-----------+------------------+-------------------------------------------+
2 rows in set (0.00 sec)
注：如果在修改User時失誤出現以下結果，怎么辦?
mysql> select host,user,password from user;
+-----------+------------------+-------------------------------------------+
| host | user | password |
+-----------+------------------+-------------------------------------------+
| ubuntu | root | * MD5加密|
| localhost | debian-sys-maint | * MD5加密|
+-----------+------------------+-------------------------------------------+
2 rows in set (0.00 sec)
此時沒有127.0.0.1和localhost主機，所以無法用root用戶進行連接，會提示以下錯誤
- ~$ mysql -u root -p
- Enter password:
- ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
則此時只能用系統自帶的debian-sys-maint用戶登錄，修改host。該用戶的的登錄密碼在/etc/mysql/debian.cnf中明文顯示。
比如：
- host = localhost
- user = debian-sys-maint
- password = 明文密碼
- socket = /var/run/mysqld/mysqld.sock
- basedir = /usr
做如下操作：
- ~$ mysql -u debian-sys-maint -p
- Enter password:
- mysql> use mysql;
- mysql> update user set host ='%' where host='ubuntu';
- mysql> exit
- ~$ sudo /etc/init.d/mysql restart
清理思路，注意操作后需重啟服務。
【編輯推薦】
