MySQL远程连接[不像往常一样] [英] MySQL remote connection [not as usual]
问题描述
我无法访问mysql外部。
我认为它是mysql或防火墙的东西或mysql中的一些特权。
我已经尝试过在互联网上的步骤。我会把这个过程一步一步地举例说明我在做什么，并作为一个教程给另一个人有同样的问题：
m使用：
-ubuntu服务器12.04
-mysql-server5.5
- 防火墙只是软件一
1-首先我安装mysql： p>
sudo apt-get install mysql-server
2-我更改了root密码：
sudo /etc/init.d/mysql stop
sudo mysqld --skip-grant-tables&
mysql -u root mysql
UPDATE user SET密码= PASSWORD（'MYPASSWORD'）WHERE User ='root'; FLUSH特权;出口;
3-我授予所有PRIVILEGES根目录到任何ip： p>
mysql>授予所有权限*。*至'root'@'％'IDENTIFIED BY'password'WITH GRANT OPTION;
mysql> FLUSH特权;
4-编辑my.cnf
sudo nano /etc/mysql/my.cnf
我评论了行，如下：
＃skip-external-locking
＃bind-address = 127.0.0.1
我编辑iptables以允许MySql 3306 ：
iptables -A INPUT -i eth0 -p tcp -m tcp --dport 3306 -j ACCEPT
现在输入netstat -ant：
活动的Internet连接（服务器和已建立）
Proto Recv-Q Send-Q本地地址外地址状态
tcp 0 0 127.0.0.1:3306 0.0.0.0:*监听
tcp 0 0 0.0.0.0:110 0.0.0.0:* LISTEN
tcp 0 0 0.0.0.0:143 0.0.0.0:* LISTEN
tcp 0 0 0.0.0.0:10000 0.0。 0.0：* LISTEN
tcp 0 0 66.123.173.170:53 0.0.0.0:* LISTEN
tcp 0 0 127.0.0.1:53 0.0.0.0:* LISTEN
tcp 0 0 0.0.0.0 ：22 0.0.0.0:* LISTEN
tcp 0 0 127.0.0.1:953 0.0.0.0:* LISTEN
tcp 0 0 0.0.0.0:25 0.0.0.0:* LISTEN
tcp 0 0 0.0.0.0:993 0.0.0.0:* LISTEN
tcp 0 0 0.0.0.0:995 0.0.0.0:* LISTEN
tcp 0 0 66.123.173.170:22 189.32.2.232:49167 ESTABLISHED
tcp 0 336 66.123.173.170:22 189.32.2.232:49654 ESTABLISHED
tcp6 0 0 ::: 110 ::: * LISTEN
tcp6 0 0 ::: 143 ::: * LISTEN
tcp6 0 0 ::: 8080 ::: * LISTEN
tcp6 0 0 ::: 21 ::: * LISTEN
tcp6 0 0 ::: 53 ::: * LISTEN
tcp6 0 0 ::: 22 ::: * LISTEN
tcp6 0 0 :: 1：953 ::: * LISTEN
tcp6 0 0 ::: 25 ::: * LISTEN
tcp6 0 0 ::: 993 ::: * LISTEN
tcp6 0 0 ::: 995 ::: * LISTEN
tcp6 0 0 127.0.0.1:8005 ::: * LISTEN
看到端口3306已打开！对不对？
6-我重新启动了mysql：
sudo service mysql start
我键入：
服务mysql status
结果：
mysql启动/运行，进程20757
7-我尝试连接到服务器：
mysql -h 66.123.173.170 -u root -p
我遇到这个错误：
错误2003（HY000）：无法连接到'66 .123.173.170'（111）上的MySQL服务器
当我这样做：
mysql -h 127.0.0.1 -u root -p
它可以打开MySQL> terminal
8- DOUBT：这个程序有什么问题吗？
OBS：在第4步，绑定地址到
bid-address = 0.0.0.0
，但它没有解决问题。
9- DOUBT：If我关闭MySQL与：服务mysql stop 然后，我可以访问防火墙本地与mysql -h 127.0.0.1 -u root -p
我做了，我可以连接甚至与mysql停止/等待（这是在停止和检索MySQL状态后给出的状态）。
恢复解决方案：
我杀了mysqld，端口并重新启动它。
我认为这是一个错误或一些相关的检查逐步我做了什么：
1-在3306上创建要连接的程序
首先，我使用Java创建一个简单的程序，任何问题，我的防火墙。程序只是打开一个端口3306与TCP连接2分钟只是为了测试，程序：
import java.io。 IOException;
import java.net.ServerSocket;
public class PortMysqlTest {
public static void main（String [] args）{
int port = 3306;
ServerSocket ss = null;
try
{
ss = new ServerSocket（port）;
ss.setReuseAddress（true）;
}
catch（IOException e）
{
System.out.println（e.getMessage（））;
}
long futureTime = System.currentTimeMillis（）+ 1000 * 60 * 2;
while（System.currentTimeMillis（）< futureTime）
{
try {
Thread.sleep（1000）;
} catch（InterruptedException e）{
e.printStackTrace（）;
}
}
if（ss！= null）
{
try
{
ss.close（）;
}
catch（IOException e）
{
System.out.println（e.getMessage（））;
}
}
}
2-已停止mysql服务
在执行程序之前，我停止了mysql服务：
sudo service mysql stop
或者可以使用：
/etc/init.d/mysql stop
3-使用java PortMysqlTest启动我的程序
我的程序（PortMysqlTest）抛出一个异常， port已被mysqld使用！
惊喜？据我所知，用mysql服务停止端口应该是免费的。我对吗？ （我不确定如果有人可以回答这个...）
4-我搜索的应用程序PID使用地址/端口with：
sudo netstat -lpn | grep 3306
答案：
tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN 6736 / mysqld
$ b b
5-我用以下方式杀死了这个过程：
kill -9 6736
OBS，如果你不知道kill是什么：
6736是持有端口，我得到这个号码与步骤4，6736 / mysqld
6-现在我再次运行我的程序：
$ b
java PortMysqlTest并与telnet连接（telnet 66.123.173.170 3306）externaly和 工作！
所以问题不是防火墙，因为我可以从外部连接到机器66.123.173.170。
strong> 7-再次启动mysql服务：
（我等待2分钟，我的程序停止并释放3306端口）并启动mysql服务再次测试，具有：
sudo服务mysql start
或
sudo /etc/init.d/mysql start
8-我使用telnet与外部连接：
telnet 66.123.173.170 3306
在尝试连接到mysql之后：
$ b $ b mysql -h 66.123.173.170 -u root -p
我认为我的MySql安装有一个错误;
或者东西，（我不知道是什么），当重新启动mysql时，mysql没有得到配置：
bind-address = 0.0.0.0
或
b $ b
bind-address = *
希望这帮助别人。
I'm not getting to get access to mysql externally. I think it's mysql or firewall stuff or some privilege within the mysql.
I already tried doing steps that are on internet. I'll put the process step-by-step to exemplify what I'm doing and to serve as a tutorial for another people that has this same problem:
I'm using:
-ubuntu server 12.04
-mysql-server5.5
-there is NO hardware firewall just software one
1- First I installed mysql with:
sudo apt-get install mysql-server
2- I changed the root password by:
sudo /etc/init.d/mysql stop
sudo mysqld --skip-grant-tables &
mysql -u root mysql
UPDATE user SET Password=PASSWORD('MYPASSWORD') WHERE User='root'; FLUSH PRIVILEGES; exit;
3- I give ALL PRIVILEGES to root to any ip:
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;
4- After I edited the my.cnf
sudo nano /etc/mysql/my.cnf
I commented the lines, as bellow:
#skip-external-locking
#bind-address = 127.0.0.1
5- I edited the iptables to allow MySql 3306:
iptables -A INPUT -i eth0 -p tcp -m tcp --dport 3306 -j ACCEPT
now typing netstat -ant:
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address Foreign Address State
tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN
tcp 0 0 0.0.0.0:110 0.0.0.0:* LISTEN
tcp 0 0 0.0.0.0:143 0.0.0.0:* LISTEN
tcp 0 0 0.0.0.0:10000 0.0.0.0:* LISTEN
tcp 0 0 66.123.173.170:53 0.0.0.0:* LISTEN
tcp 0 0 127.0.0.1:53 0.0.0.0:* LISTEN
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN
tcp 0 0 127.0.0.1:953 0.0.0.0:* LISTEN
tcp 0 0 0.0.0.0:25 0.0.0.0:* LISTEN
tcp 0 0 0.0.0.0:993 0.0.0.0:* LISTEN
tcp 0 0 0.0.0.0:995 0.0.0.0:* LISTEN
tcp 0 0 66.123.173.170:22 189.32.2.232:49167 ESTABLISHED
tcp 0 336 66.123.173.170:22 189.32.2.232:49654 ESTABLISHED
tcp6 0 0 :::110 :::* LISTEN
tcp6 0 0 :::143 :::* LISTEN
tcp6 0 0 :::8080 :::* LISTEN
tcp6 0 0 :::21 :::* LISTEN
tcp6 0 0 :::53 :::* LISTEN
tcp6 0 0 :::22 :::* LISTEN
tcp6 0 0 ::1:953 :::* LISTEN
tcp6 0 0 :::25 :::* LISTEN
tcp6 0 0 :::993 :::* LISTEN
tcp6 0 0 :::995 :::* LISTEN
tcp6 0 0 127.0.0.1:8005 :::* LISTEN
See that port 3306 is open! Am I right?
6- I restarted the mysql:
sudo service mysql start
I typed:
service mysql status
result:
mysql start/running, process 20757
7- I tried to connect to the server:
mysql -h 66.123.173.170 -u root -p
I got this error:
ERROR 2003 (HY000): Can't connect to MySQL server on '66.123.173.170' (111)
When I do:
mysql -h 127.0.0.1 -u root -p
It works opened MySQL> terminal
8- DOUBT: Do you see anything wrong with this process?
OBS: on step 4, I also tried to put the bind address to bid-address = 0.0.0.0 but it didn't solve the problem.
9- DOUBT: If I turn off the MySQL with: service mysql stop then, can I access the firewall locally with mysql -h 127.0.0.1 -u root -p ?
I did it and I could connect even with mysql stopped/waiting (this was the status given after stop and retrieveing status of MySQL).
Resumed solution:
I killed the mysqld that was holding the 3306 port and restarted it.
I think this is a bug or something related check step-by-step what I did:
1- Created a program to connect on 3306
First I create a simple program in Java to make sure there wasn't any problem with my firewall. The program just open a port on 3306 with TCP connection by 2 minutes just for testing, the program:
import java.io.IOException;
import java.net.ServerSocket;
public class PortMysqlTest {
public static void main(String[] args) {
int port = 3306;
ServerSocket ss = null;
try
{
ss = new ServerSocket(port);
ss.setReuseAddress(true);
}
catch (IOException e)
{
System.out.println(e.getMessage());
}
long futureTime = System.currentTimeMillis() + 1000 * 60 * 2;
while (System.currentTimeMillis() < futureTime)
{
try {
Thread.sleep(1000);
} catch (InterruptedException e) {
e.printStackTrace();
}
}
if (ss != null)
{
try
{
ss.close();
}
catch (IOException e)
{
System.out.println(e.getMessage());
}
}
}
2- Stopped the mysql service
Before executing the program I stopped the mysql service:
sudo service mysql stop
or it can be done with:
/etc/init.d/mysql stop
3- Started My program with java PortMysqlTest
My program (PortMysqlTest) throw an exception saying the address/port as already in use by mysqld!
Surprise? As far as I know the with mysql service stopped the port should be free. Am I right? (I'm not sure of it if someone can answer this...)
4- I searched for the application PID that was using the address/port with:
sudo netstat -lpn | grep 3306
the answer:
tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN 6736/mysqld
5- I killed the process with:
kill -9 6736
OBS, if you don't know what kill do: 6736 is the process that was holding the port, I got this number with step 4, 6736/mysqld
6- Now I run my program again:
java PortMysqlTest and connected with telnet (telnet 66.123.173.170 3306) externaly and it worked!
So the problem wasn't the firewall, because I could connect externally to the machine 66.123.173.170.
7- Started the mysql service again:
(I waited 2 minutes for my program do stop and free the 3306 port) and started the mysql service again to test, with:
sudo service mysql start
or
sudo /etc/init.d/mysql start
8- I did connected externally with telnet:
telnet 66.123.173.170 3306
and worked!!!
After I tried connect to mysql with:
mysql -h 66.123.173.170 -u root -p
and worked!!!
Conclusion: I think there was a bug with my MySql installation; or something, (I don't know what is), when restarting mysql the mysql doesn't get the configuration of:
bind-address = 0.0.0.0
or
bind-address = *
I hope this help someone else.
这篇关于MySQL远程连接[不像往常一样]的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
