Can't connect remotely to MySQL...
 

Hi!

Why can't I connect to MySQL from a remote computer?

I am using MySQL Control center to connect to the remote computer.

The remote computer is running Mandrake 10.2 with 2 ethernet card but i disabled eth0 and activated eth1.

It has no firewall. I even tried stopping the iptables service but still I can't connect.

I have performed the necessary grants to the 'root' user account with the host set to '%'. In Mandrake 10.1 I don't encounter this problem.

Can anyone help.

Thanks.

You might try looking for the answer in the mysql manual at
/usr/share/doc/packages/mysql/manual.pdf

Also, look in the server logs. It may indicate what the problem is.
sudo less /var/lib/mysql/mysqld.log
or
sudo bzcat /var/lib/mysql/mysql.log.1.bz2 | less
to read the log backup.

What error message are u getting?

Is the mysqld daemon running on the Mandrake box? And is it configured to listen to eth1 (ie not localhost)?

To jschiwal, yes I looked at the log files, in Mandrake its located at /var/log/mysqld/mysqld.log but I find no error entries there. I'm still looking for the mysql manual.

To jayakrishnan, the error I get is ERROR 2003: Can't connect to MySQL server on 'XXX.xxx.XXX.xxx' (10061)

To timmeke, yes the Mandrake daemon is running. I can connect to it locally. When i checked the my.cnf file there is no bind-address setting in it. Additionally when I peform a netstat -tap command I do not see mysql in list of daemons listening or active. What should I do?

Thanks for all the replies

Check which port number is listed in the mysqld.log on the server:

060705 21:34:23 mysqld started
060705 21:34:24 InnoDB: Started; log sequence number 0 43655
060705 21:34:24 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.0.18' socket: '/var/lib/mysql/mysql.sock' port: 3306 SUSE MySQL RPM

I noticed that mine is different. It is possible to have more than one instance of mysqld and have them listen on different ports, as this config example from the manual indicates:
From your remote host, enter "telnet <serverhost> <mysqlport>"

If your host has access, and you have the port number right, then you should get some garbage back.

Does your remote host have priviledges to access the database?

Are both your remote host and the mysql server on a LAN, or are you connecting over the internet? If it's the latter, you should be using a firewall and accessing the database over a secure VPN or an SSH tunnel.

I looked in rpm.pbone.net. It looks look your documentation is in
/usr/share/doc/MySQL50-common-5.0.2/manual-split/manual_Introduction.html
There is a Post Script version
/usr/share/doc/MySQL50-common-5.0.2/manual.ps

Read through the /etc/my.cnf file. Here is a segment:
Note the port value in this global configuration file and whether skip-networking is commented out or not.

Thanks. I'm getting somewhere I think. I looked at the log file and this is what I see:

Note that port 0 is used. So in the my.cnf I added the entry port = 3306. I restarted the mysqld and yet when I checked the log file it is still using port 0. Could it be possible that mysqld is messed up?

telnet localhost 3306 isn't working as well as telnet <remotehost> 3306 either.

The server and the clients are on a local area network.

Here is the content of the my.cnf file:

Did you restart mysql after changing /etc/my.cfg?
Also, did you find the manual. The post installation section is very important.
Right after installing, there are a couple database tables that don't have passwords.
The manual even details how to access the database through ssh, and how to work with
certificates. If the server is visible from the internet, this will be very important.

Nope can't figure it out yet. Oh my. If I do a netstat -ln | grep mysql here is what I get:

Which means that MySQLd is listening alright. The documentation isn't helpful but I've found an online documentation from the MySQL website that look's like quite a read. I'll see what I can find in there. Hmm...

I'm having basically the same problem.
"ERROR 2003 (HY000): Can't connect to MySQL server on 192.168.xxx.xxx"

If I figure anything out, I'll let you know.

Well that was quick, I got mine working.. lol

In my.cnf, there's a value called bind-address (mine's right below the skip-networking line). By default it was set to 127.0.0.1. When I changed it to 192.168.xxx.xxx, it worked.

Does anybody know if there's a better way to do this? I don't want to go running around changing config files if I change the computer's network IP.

Yeah I'm familiar with those things. Have been there. I tried those. And I can't still make remote connections.

There is a better way. Just remove the bind-address (or comment it out). You don't have to set it to any remote address. That way any remote host can connect to your server not just the IP you set, unless that's what you want. Besides, I'm not sure though, if you set the bind-address to a remote computer, that may affect connection locally. Good for you though. You solved your problem.

Obviously my problem goes beyond changing the my.cnf file. Because as I said I've done that. I removed bind-address, I removed skip-networking but remote comp can't still connect. Yeah, I restarted mysqld after changing the settings. :(

Update: I've discovered that the default used by mysql in mandriva uses the --skip-networking option regardless of the settings in the my.cnf file. That is the reason why I can't connect to the server from other machines.

I discovered this when I issue this command:

ps -efH | grep mysql

The output I get is:
I can't figure out how to change this default. Can anyone help?

I don't know, maybe --skip-networking=FALSE? Possibly it was originally configured with --skip-networking and you have to recompile.

Okay. I finally solved it. After going through the system, I discovered an undocumented(?) configuration setting found at /etc/sysconfig/mysqld file. And sure enough there it was:

When I removed the --skip-networking option and restarted mysqld, I finally made a successful connection from a remote machine to the server.

I hope that this will be of help to someone who had the same problem as I did. :)

DUDE! THANK YOU SO MUCH! I have been scouring the net for this! I could not figure out why the heck I could not connect to mysql remotely. I tried EVERYTHING (opening ports, re-install mysql, etc).

Really, THANK YOU!

There's good info in this thread but unfortunately it did not help me. Reason being is that I am hosting on godaddy.com and they don't allow you to connect to your MySQL DB remotely.

http://help.godaddy.com/article.php?...4&topic_id=67&

So can anyone recommend me a good hosting service that does allow remote DB access?

I'm having the same problem.

I have reviewed all the previous messages and we're OK. (no skip networking, default port 3360, no firewall, local connections work noproblem.)

Were are running :

Red Hat Enterprise Linux ES release 3 (Taroon Update 8)
Kernel 2.4.21-47.ELsmp on an i686

Your MySQL connection id is 13 to server version: 5.0.24-standard-log

When I connect via mysql -h option I get:

$ mysql -unbps2 -pnbps2 nbps2 -h machine.domain.com
ERROR 2003 (HY000): Can't connect to MySQL server on 'machine.domain.com' (111)

When I add the port number to the command line I get:

$ mysql -unbps2 -pnbps2 nbps2 -h machine.domain.com -P3306
ERROR 1045 (28000): Access denied for user 'nbps2'@'machine' (using password: YES)

We have recently upgraded to MySQL 5 from 3.23.58.

Thanks for any help.

Greg

All,

I solved the "Cannot connect" problem. I needed to edit the /etc/services file. I'm not sure what it's used for, but there were lines in it changing the default port:

mysql 1034/tcp # MySQL
mysql 1034/udp # MySQL

From what I gather it seems as though the client checks it if no port is specified on the command line.

On to access denied.

Greg

OK, I got the "access denied" issue solved.

From the research I've done, the reverse DNS lookup is not returning a string which matches the hostname. Since the reverse look up resolves to "LocalMachine.Domain.com" and the hostname is "LocalMachine", they don't match. "LocalMachine.Domain.com" doesn't even match the host '%'.

The key is in the error message If you try to connect:
mysql -unbps2 -pnbps2 -h RemoteMachineName.domain.com
and get the error message:
ERROR 1045 (28000): Access denied for user 'nbps2'@'LocalMachineName' (using password: YES)

you see that the server you tried to connect to is different from the servername in the error message.

Editing the etc/my.cnf file with:

[mysqld]
skip-name-resolve

fixes the problem. It also speeds up the DB interaction.

Why now and not with MySQL 3 (from which we migrated)? I can only assume MySQL 5 is stricter.

Note: Yes; I know '%' is unsecure and adding skip-name-resolve is even more unsecure. I think we're OK since all DB access is done via canned queries in PHP apps.

Greg

Huh? Just because your PHP application is the only thing you access MySQL with doesn't mean it's the only way anyone can access MySQL. If you bind it to your public network interface, anyone can access it. Also, are you certain your PHP apps are written absolutely tightly and are not vulnerable to SQL injections? You could be one skiddie away from a trashed database.

Listen: There is a reason why later version of MySQL and recent Linux distributions lock it down so much by default: It was previously very unsafe and very easy to exploit! Essentially what you are all doing is removing all the security and making it exploitable again.

It's already been said once in this thread, but here's the solution: Use a VPN or SSH tunnel to access to box, then access MySQL locally without needing to bind it to external network interfaces. Yes, this will even allow you to access it with the GUI tools from your desktop. You just need to setup a port-forwarded ssh connection to your database server and tell your GUI client to connect to the local port on your machine (the local side of the ssh port-forward).

Stop trying to defeat security that is there for a good reason. You should be working hard to find a secure alternative to what you're trying to do, instead of working heard to defeat the security.