CentOS not allowing remote MySQL connections

Question

When assigning a user from a remote IP to connect to a database it is saying that it's failing to connect. It is also failing to connect with root so something is wrong. Bind IP is off and I have also tried disabling iptables, still no dice. Port 3306 is forwarded.

I'm running on Centos 5.6, using phpmyadmin, but I have also tried to assign the user via the commandline and create a new database, still not working.

Been googling and troubleshooting for hours now, no dice.

Whats the FW in front of of your server? i have a feeling it could be there... ItS setup to allow and forward 3306 to your DB? What do you get when you run "telenet youriphere 3306? service iptables status show no chains being loaded correct? — RomeNYRR, Apr 06 '12 at 10:46
telnet 108.170.15.197 3306 Trying 108.170.15.197... Connected to 108.170.15.197 (108.170.15.197). Escape character is '^]'. N 5.5.22-cllaVk:zyoGu8W,r{42:m7mysql_native_password — nd8ad, Apr 06 '12 at 11:09
good youre connected. are you restricting access on the firewall by source IP or opening it to the world? can you test from another external connection to confirm that the issue is on the remote users end. also mask your IP in your post above ;) — RomeNYRR, Apr 06 '12 at 11:29
I've turned off iptables on the CentOS and then also turned off the firewall from the main machine (it's virtualized), still having issues. It's not even returning an error, access denied or anything, just timing out. — nd8ad, Apr 06 '12 at 12:26
Have you commented skip-networking Also, bind-address=YOUR-SERVER-IP — Abhishek Anand Amralkar, Feb 15 '13 at 11:40
What is the remote user, using to connect to the database and what happens if he trys to telnet? — 300c, Mar 17 '13 at 17:55

score 1 · Answer 1 · answered Apr 06 '12 at 11:39

1

Your problem description is not clear. You need to differentiate between problems caused by network related issues (firewall blocking port 3306, incorrect bind address, etc..), or caused by invalid credentials.

If you can telnet to your server IP/port and get connected:

$ telnet your_server_ip 3306

This means that mysql server is running and accepting connections on the specified IP. The next step is to try to connect to mysql using mysql client:

$ mysql -u user -p -h your_server_ip

If you get access denied error, you need to make sure you are connecting with the correct username/password.

For root user, AFAIK it is not allowed (by default) to connect to mysql DB remotely as root.

answered Apr 06 '12 at 11:39

Khaled

35,688
8
69
98

The MySQL connection doesn't load, it times out remotely, im connecting via SSH so the server is online, and iptables is off. It is returning no error message, no access denied, none of that, – nd8ad Apr 06 '12 at 12:55
By default root is allowed to connect remotely, it's only if you run a command something like `mysql_secure_installation`, then it will remove the privilege of root to connect remotely. – Napster_X Dec 15 '12 at 18:15

score 0 · Answer 2 · answered Apr 06 '12 at 10:48

0

You should bind to an IP. If you want to bind to all IP's on the machine you need to bind to:

0.0.0.0

answered Apr 06 '12 at 10:48

Lucas Kauffman

16,818
9
57
92

The server only has one IP assigned to it, so. – nd8ad Apr 06 '12 at 10:56
Yes but then it will not be accessible to localhost if you set it to that IP. – Lucas Kauffman Apr 06 '12 at 11:12
I just got rid of the ip bind completely and it's not working – nd8ad Apr 06 '12 at 13:13
Well if you don't bind to anything, that doesn't really surprise me, if it doesn't bind to anything how are you supposed to communicate with it? – Lucas Kauffman Apr 06 '12 at 20:48
I believe if you remove the ip bind completely, it will bind to all the available IP's or interfaces ... more like *:3306 – Napster_X Dec 15 '12 at 18:12
actually this is how you should do it, per the mysql documentation iirc. – Lucas Kauffman Dec 15 '12 at 21:56

score 0 · Answer 3 · answered Jun 22 '12 at 10:27

0

To allow remote mysql connections, you have to edit your server's /etc/my.cnf

locate [mysqld] and add the following line:

bind-address = <server's ip address>

save and restart mysql server.

/etc/init.d/mysqld restart

answered Jun 22 '12 at 10:27

vvens

173
2
9

score 0 · Answer 4 · answered Apr 17 '13 at 08:52

You can try the following things,

SSH in to you server where MySQl is running, Make sure if you are able to log in to the Mysql as "root" from the server locally.
Double check if the Mysql port 3306 is listening.
See if IPTABLEs is set to allow the Mysql inbound connections else you can stop the IPTABLES service and try connecting the MySQL server from a remote system.
Connecting as a "root" user is not a good practice, create a Mysql user and grant all privileges to the new user to connect the database from a remote system identified by a password.

Example:-

GRANT ALL ON *. * TO user@'Your IP Address' IDENTIFIED BY 'PASSWORD';

FLUSH PRIVILEGES;

Hope this will Help you.

Thanks!

CentOS not allowing remote MySQL connections

4 Answers4