MySQL remote connection [not as usual]

问题:

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).

回答1:

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.

回答2:

Your netstat output shows that it is not open to external connections, it should show 0.0.0.0 in there instead of 127.0.0.1 (which is loopback, not local as such).

You probably need to add a different bind-address to your mysql config (and don't forget to restart the service).

Try

bind-address = *

or your actual NIC ip (66.123.173.170) perhaps.

回答3:

Your need to set your bind-address in my.cnf to 0.0.0.0 not just comment out the line.

As a side note, you should probably not allow root access from all IP addresses as you say you have configured it. I would limit root to local machine only.

回答4:

By default MySQL listen on localhost. You need to configure it to run on your actual interface or you can use 0.0.0.0 address in bind.