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屋!