Can't connect remotely to MySQL...

gain · 07-05-2006, 03:07 AM

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.

jschiwal · 07-05-2006, 03:24 AM

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.

jayakrishnan · 07-05-2006, 03:28 AM

What error message are u getting?

timmeke · 07-05-2006, 04:11 AM

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

gain · 07-05-2006, 08:55 PM

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

jschiwal · 07-05-2006, 10:42 PM

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:

Code:

# This file should probably be in your home dir (~/.my.cnf)
# or /etc/my.cnf
# Version 2.1 by Jani Tolonen
[mysqld_multi]
mysqld = /usr/local/bin/mysqld_safe
mysqladmin = /usr/local/bin/mysqladmin
user = multi_admin
password = multipass
[mysqld2]
socket = /tmp/mysql.sock2
port = 3307
pid-file = /usr/local/mysql/var2/hostname.pid2
datadir = /usr/local/mysql/var2
language = /usr/local/share/mysql/english
user = john
[mysqld3]
socket = /tmp/mysql.sock3
port = 3308
pid-file = /usr/local/mysql/var3/hostname.pid3
datadir = /usr/local/mysql/var3
language = /usr/local/share/mysql/swedish
user = monty

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:

Code:

# The MySQL server
[mysqld]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking

Note the port value in this global configuration file and whether skip-networking is commented out or not.

gain · 07-05-2006, 11:37 PM

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

Quote:

060706 12:34:47 mysqld started
060706 12:34:47 InnoDB: Started; log sequence number 0 43654
/usr/sbin/mysqld: ready for connections.
Version: '4.1.11' socket: '/var/lib/mysql/mysql.sock' port: 0 Source distribution

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:

Quote:

[mysqld]
datadir=/var/lib/mysql
port = 3306
socket=/var/lib/mysql/mysql.sock

# Default to using old password format for compatibility with old and
# shorter password hash.
# Reference: http://dev.mysql.com/doc/mysql/en/Password_hashing.html
old_passwords=1

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
err-log=/var/log/mysqld/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

jschiwal · 07-06-2006, 03:21 AM

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.

gain · 07-11-2006, 11:43 PM

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

Quote:

# netstat -ln | grep mysql
unix 2 [ ACC ] STREAM LISTENING 10776 /var/lib/mysql/mysql.sock

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...

ShatteredArm · 07-14-2006, 11:22 PM

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.

ShatteredArm · 07-14-2006, 11:26 PM

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.

gain · 07-17-2006, 09:51 PM

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

Quote:

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.

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.

gain · 08-07-2006, 09:32 PM

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:

Quote:

root 17605 1 0 09:07 pts/0 00:00:00 /bin/sh /usr/bin/mysqld_safe --defaults-file=/etc/my.cnf --skip-networking --pid-file=/var/run/mysqld/mysqld.pid

mysql 17644 17605 1 09:07 pts/0 00:00:23 /usr/sbin/mysqld --defaults-file=/etc/my.cnf --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock --skip-networking

I can't figure out how to change this default. Can anyone help?

ShatteredArm · 08-07-2006, 10:10 PM

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

gain · 08-10-2006, 12:45 AM

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:

Quote:

# (oe) Remove --skip-networking to enable network access from
# non local clients. Access from localhost will still work.
MYSQLD_OPTIONS="--skip-networking"

# (oe) set TMPDIR and TMP environment variables
TMPDIR="${datadir}/.tmp"
TMP="${TMPDIR}"

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.