3

My problem is similar to this question but since I don't have enough reputation to write a comment AND the answer to that question dindn’t help, I am starting a new question.

I have an GCE VM instance with LEMP with MySQL Ver 15.1 Distrib 10.1.18-MariaDB and I'm trying to connect remotely to it from my local machine.

I already tried all the suggestions in the question link that I mentioned before.

This is my firewall configuration:

enter image description here

In my.cnf file I have:

bind-address = 0.0.0.0

And about MySQL users privileges I have the following:

enter image description here enter image description here

When I try to connect remotely with wkreport user I get the following result:

enter image description here

My question is, what am I missing ?!

Community
  • 1
  • 1
dsilva
  • 131
  • 2
  • 9
  • 2
    do you have any firewall/iptables on the SQL machine? try looking at /var/log/messages as root while trying to login to mysql. you may find hints. – Slava Feb 08 '17 at 15:55
  • @Slava In my.cnf file i have the following setup: 
        general_log_file     = /var/log/mysql/mysql.log
    general_log            = 1
    log_warnings         = 2

And when tailing the log while trying to connect remotely i get no logs
     – dsilva Feb 08 '17 at 16:26
  • 1
    @Slava In the iptables INPUT rules im accepting tcp connections on 3306 like this: **ACCEPT tcp -- anywhere anywhere tcp dpt:mysql** – dsilva Feb 08 '17 at 16:54
  • try tailing /var/log/messages after (or during) an attempt to login to mysql. maybe something else is blocking you, you might see it there. – Slava Feb 09 '17 at 12:07
  • @Slave I don't seem to have that /var/log/messages on my machine !? I'm looking to /var/log/mysql/mysq.log and i can see connection logs from localhost (connected via ssh on GCE VM) but when i try from my local machine to the GCE VM external IP no logs are written. Can't understand this behavior since i have configured firewall to accept tcp on 3306 – dsilva Feb 10 '17 at 16:14
  • @Slava Sorry, misspelled your name in the last comment – dsilva Feb 10 '17 at 16:23

2 Answers2

2

I just found the solution to my problem,

Special thanks to @Slava for pointing me the way, after all it was iptables.

So, I kept receiving a "MySQL connection refused" message when trying to connect remotely so I searched for a way to see TCP connection logs and I found the tcpdump command.

By running sudo tcpdump port 3306 -vvv -n I saw the following output every time I tried to connect remotely:

enter image description here

I searched the tcpdump man page and saw that R means for TCP RST (RESET) flag.

Searched a little bit and found this question and its accepted answer led me again into IPTABLES that @Slava suggested since the first comment.

That's when I looked closely and saw that my INPUT ACCEPT tcp:3306 was defined after the REJECT TCP reject-with tcp-reset rule hence the log was showing.

enter image description here

After this I just removed the rule to accept tcp:3306 and prepended it to the reject tcp rules and voila!

iptables -D INPUT -p tcp -m tcp --dport 3306 -j ACCEPT
iptables -I INPUT {line number from the first reject tcp rule} -p tcp -m tcp --dport 3306 -j ACCEPT

IPTABLES now looks like this and finally I can connect to MySQL remotely: enter image description here

To list the iptables with line numbers type:

sudo iptables -nL --line-numbers

Final toughts:

  • This can be improved by whitelisting the source IP address from where you're making the remote connection for security matters.
Community
  • 1
  • 1
dsilva
  • 131
  • 2
  • 9
0

I had similar problem with a vm instance. I tested everything and it was solved by creating a new user on mysql.

I used this post to solve it.

Rafael Snor
  • 11
  • 1