Question

Cant login to mysql on another droplet

Posted June 1, 2017 3.7k views
Nginx CentOS MySQL DigitalOcean

Hi, there,
I have two droplet (vlife-portal & vlife-data ) with the same configuration.
vlife-portal have a SSL.

Within both mysql.user, I have set up root@‘private IP’ and grant them related privileges.

And I followed the tutorial in the community to set up master & slave mysql, I tried to make vlife-portal as master and the other as slave. But after I start the slave, I find that I cant connect to vlife-portal.

Then I tested on vlife-portal in my terminal, I can connect to vlife-data’s Mysql directly.
However, when I on the vlife-data, I cant connect to vlife-portal.

iptables / firewalls are not enabled.
No bind-address in my.cnf. I tried bind-address to 0.0.0.0 still not work.

*Error msg: *
ERROR 2003 (HY000): Can’t connect to MySQL server on 'private IP’ (110)

Could you please give me an hand on this?
Thanks.

Add a comment
cetetek
By:
cetetek
Add a comment

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

5 answers
hansen June 1, 2017

Hi @cetetek
Can you run sudo lsof -iTCP -sTCP:LISTEN -P on the data server?

Reply Report
  • kamaln7 June 1, 2017

    In addition to that, can you also paste the output of both sudo iptables -L -n and sudo iptables-save? These will print any firewall rules that you have enabled.

    Reply Report
    • cetetek June 2, 2017

      [root@vlife-portal ~]# iptables -L -n
      Chain INPUT (policy ACCEPT)
      target prot opt source destination

      ACCEPT all – 0.0.0.0/0 0.0.0.0/0

      ACCEPT all – 0.0.0.0/0 0.0.0.0/0 state RELATED,ESTABLISHED
      ACCEPT tcp – 0.0.0.0/0 0.0.0.0/0 tcp dpt:22
      ACCEPT tcp – 0.0.0.0/0 0.0.0.0/0 tcp dpt:80
      DROP tcp – 0.0.0.0/0 0.0.0.0/0 tcp dpt:3306
      ACCEPT icmp – 0.0.0.0/0 0.0.0.0/0 icmptype 8

      Chain FORWARD (policy ACCEPT)
      target prot opt source destination

      Chain OUTPUT (policy ACCEPT)
      target prot opt source destination

      [root@vlife-portal ~]# iptables-save

      Generated by iptables-save v1.4.21 on Fri Jun 2 23:50:48 2017

      *filter
      :INPUT ACCEPT [79935:4658370]
      :FORWARD ACCEPT [0:0]
      :OUTPUT ACCEPT [43636440:35733086523]
      -A INPUT -i lo -j ACCEPT
      -A INPUT -m state –state RELATED,ESTABLISHED -j ACCEPT
      -A INPUT -p tcp -m tcp –dport 22 -j ACCEPT
      -A INPUT -p tcp -m tcp –dport 80 -j ACCEPT
      -A INPUT -p tcp -m tcp –dport 3306 -j DROP
      -A INPUT -p icmp -m icmp –icmp-type 8 -j ACCEPT
      COMMIT

      Completed on Fri Jun 2 23:50:48 2017

      Reply Report
    • cetetek June 2, 2017
      [deleted]
      Reply Report
    • cetetek June 2, 2017
      [deleted]
      Reply Report
    • cetetek June 2, 2017
      [deleted]
      Reply Report
  • cetetek June 2, 2017
    [deleted]
    Reply Report
  • cetetek June 2, 2017
    [deleted]
    Reply Report
  • cetetek June 2, 2017

    Command not found
    But I use netstat -nlp |grep LISTEN

    [root@vlife-portal ~]# netstat -nlp |grep LISTEN
    tcp 0 0 127.0.0.1:6379 0.0.0.0:* LISTEN 2203/redis-server 1
    tcp 0 0 0.0.0.0:80 0.0.0.0:* LISTEN 4399/nginx: worker

    tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 2499/sshd

    tcp 0 0 0.0.0.0:443 0.0.0.0:* LISTEN 4399/nginx: worker

    tcp 0 0 127.0.0.1:9000 0.0.0.0:* LISTEN 1627/php-fpm: maste
    tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 5056/mysqld

    tcp6 0 0 :::22 :::* LISTEN 2499/sshd

    unix 2 [ ACC ] STREAM LISTENING 2961566 5056/mysqld /tmp/mysql.sock
    unix 2 [ ACC ] STREAM LISTENING 11943 1/systemd /var/run/dbus/systembussocket
    unix 2 [ ACC ] STREAM LISTENING 1451 1/systemd /run/systemd/journal/stdout
    unix 2 [ ACC ] STREAM LISTENING 8630 1/systemd /run/systemd/private
    unix 2 [ ACC ] SEQPACKET LISTENING 8694 1/systemd /run/udev/control

    Reply Report
jtittle1 June 1, 2017

@cetetek

If you’re unable to connect on the private IP, it’s most likely due to MySQL not being bound to it. You may need to explicitly bind to it using:

bind-address = PRIVATE_IP

Where PRIVATE_IP is the private network IP for your Droplet. Once you bind MySQL to a private IP, you need to create a new user that uses the connecting servers private IP.

…

For example, let’s say that the MySQL servers private IP is 11.22.33.44 and the connecting servers IP is 22.33.44.55.

You’d set:

bind-address = 11.22.33.44

and then create a new user using the connecting servers private IP:

grant all on dbname.* to 'dbuser'@'22.33.44.55' identified by 'dbpassword';

You’d sub out dbname, dbuser, and dbpassword with your own values.

Reply Report
  • cetetek June 2, 2017

    I would try this later, but I think this may not be the problem. Because I can link from A to B directly, and B has not set the bind-address.
    Thanks all the same！

    Reply Report
hansen June 2, 2017

Hi @cetetek

Okay, so you actually have a firewall rule that specifically blocks MySQL:

DROP tcp -- 0.0.0.0/0 0.0.0.0/0 tcp dpt:3306

And I don’t see MySQL listening on the port, but only on a socket, so maybe you have the skip-networking defined somewhere in the configuration.

Reply Report
cetetek June 3, 2017
[deleted]
Reply Report
golfunir September 3, 2017
[deleted]
Reply Report
Submit an Answer

Related

Looking for something else?

Ask a new question Search for more help