Geek asks and answers

MySQL remote connection [not as usual]

I am not accessing mysql from the outside. I think this is mysql or firewall stuff or some privileges in mysql.

I have already tried to take steps online. I will step by step put the process down to demonstrate what I'm doing and serve as a textbook for other people who have the same problem:

I use:

-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:

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 provide ALL PRIVILEGES for root for any ip:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;

4- After I edited my.cnf file

sudo nano /etc/mysql/my.cnf

I commented out the lines as below:

#skip-external-locking 
#bind-address           = 127.0.0.1

5- I edited iptables to enable MySql 3306:

iptables -A INPUT -i eth0 -p tcp -m tcp --dport 3306 -j ACCEPT

Now enter 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! I'm right?

6- I restarted 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:

2003 (HY000): MySQL '66.123.173.170 '(111)

:

mysql -h 127.0.0.1 -u root -p

MySQL >

8-DOUBT: - ?

OBS: 4 bid-address = 0.0.0.0 .

9-DOUBT: MySQL service mysql stop, mysql -h 127.0.0.1 -u root -p?

, mysql stop/waiting ( , MySQL).

+5
4

:

mysqld, 3306, .

, - , , :

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) , /, mysqld!

? , mysql , . ? ( , - ...)

4- PID , / :

sudo netstat -lpn | grep 3306

:

tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN      6736/mysqld

5-

kill -9 6736

OBS, , : 6736 - , , 4, 6736/mysqld

6- :

java PortMysqlTest telnet (telnet 66.123.173.170 3306) externaly !

, , 66.123.173.170.

7 - mysql:

( 2 , 3306) mysql :

sudo service mysql start 


sudo /etc/init.d/mysql start

8- telnet:

telnet 66.123.173.170 3306

!!!

, mysql

mysql -h 66.123.173.170 -u root -p

!!!

: , MySql; -, ( , ), mysql mysql :

bind-address = 0.0.0.0 


bind-address = *

, - .

+6

netstat , , 0.0.0.0 127.0.0.1 ( loopback, ).

, bind-address mysql ( ).

Try

bind-address = *

IP- NIC (66.123.173.170).

+2

my.cnf 0.0.0.0, .

, , root IP-, , . root .

0

By default, MySQL listens on localhost. You need to configure it to run on your real interface, or you can use the address 0.0.0.0 in bind.

0
source

More articles:


All Articles