Cant login to mysql on another droplet

June 1, 2017 3.4k views
MySQL Nginx DigitalOcean CentOS
cetetek
By:
cetetek

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.

Log In to Comment
5 Answers
hansen June 1, 2017

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

Reply
  • kamaln7 MOD 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.

    • 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
    • cetetek June 2, 2017
      [deleted]
    • cetetek June 2, 2017
      [deleted]
    • cetetek June 2, 2017
      [deleted]
  • cetetek June 2, 2017
    [deleted]
  • cetetek June 2, 2017
    [deleted]
  • 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

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
  • 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！

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
  • cetetek June 3, 2017

    tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 5056/mysqld
    This is mysql

    • hansen June 3, 2017

      Overlooked that when you posted three posts at the same time and none of them using code tags.
      But your firewall is still blocking MySQL, so either remove that or disable your firewall.

  • cetetek June 3, 2017

    skip-networking # This line has been commented

    maxconnections = 500
    max    connecterrors = 100
    open    files_limit = 65535

cetetek June 3, 2017
[deleted]
Reply
golfunir September 3, 2017
[deleted]
Reply
Have another answer? Share your knowledge.

Related Questions