CODEWITHSUNDEEP
mysqlgoogle-compute-enginemariadb
dsilva
dsilva

Reputation: 131

Remotely connect to MySQL on Google Compute Engine VM

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 ?!

Upvotes: 3

Views: 4916

Answers (2)

Rafael Snor
Rafael Snor

Reputation: 11

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.

Upvotes: 0

dsilva
dsilva

Reputation: 131

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.

Upvotes: 2

Related Questions