Can't connect using MySQL query browser 2

[sweetlew]

I'm having a little problem connecting remotely to my MySql database. First a little background on my setup. All machines are on my home LAN behind a router. Linux Machine; Ubuntu Linux with MySql installed and running without a problem, command line. Windows XP machine with MySql query browser client installed.

The problem comes in with trying to connect the XP machine to the Linux box to access the sql tables. It mainly says it can't connect to my database/schema. I know the database is fine(or at least it seems) because I can SSH over to it and run queries without a problem. I've tried using the IP address, the machine name to connect but nothing works. I've investigated the .sock files, ports and for some reason I can't get a good connection with the Windows client software to my database.

Please Help!!!!! Any suggestions would be helplful. Thanks

 

[dalchri]

1. Can you ping the IP address of the MySql machine from the XP machine? If not, there is a problem with your TCP/IP network setup.

2. Can you ping the machine name of the MySql machine from the XP machine? If not, there is a problem with your DNS/WINS/lmhosts/SAMBA setup.

3. Can you telnet to port 3306 on the MySql machine from the XP machine? For example:

telnet 192.168.0.1 3306

This should result in a response like this:
<
4.1.7-standard
#;>cZwi*,?3bs=uW36T&_-

Connection to host lost.

And NOT like this:
Could not open connection to the host, on port 3306: Con
nect failed

If you can't telnet, there is a firewall issue either on the XP box or the MySql box.

That should do it.

 

[sweetlew]

Yes, I can ping to the box with no problem.

Yes, I can ping with the machine name.



No I could not Telnet, I tried telnet(ing) to the box from XP and got a: Could not open connection to the host, on port 3306: Connect failed....

As a matter of fact, I get the same message when I telnet while I'm on the Linux box.

I have a software firewall on box my XP desktop and laptop.

 

[sweetlew]

What should I look for in ways of firewall issues? Thanks

 

[azzazzello]

you are saying that you cannot even telnet to port 3306 when on the local machine (the one running mysql)? If that's the case, than that is where the problem is - not your XP or laptop. First, check that you have mysql running with networking on. Do something like netstat -a | grep mysql (provided that grep 3306 /etc/services yields mysql*). If you see a line returned, then it's on the network. In that case, check your /etc/sysconfig/iptables file to see if it lets connection to port 3306 through. If you need help with that, post the iptables info here

 

[sweetlew]

Yeah, it must be something like that as my apache2 webserver works just fine.

 

[sweetlew]

Here's the result of doing a IPTABLES -L on my linux box:

Chain INPUT (policy ACCEPT)
target prot opt source destination

Chain FORWARD (policy ACCEPT)
target prot opt source destination

Chain OUTPUT (policy ACCEPT)
target prot opt source destination

 

[azzazzello]

ok, looks like you have no iptable rules, so that's not a problem. Do

grep /etc/services 3306

That should come back with service name for mysql daemon (usually it is *mysql*). Say it IS mysql. Then do

netstat -a | grep mysql (or whatever is spit out by previous grep)

What is the output of that command?

 

[azzazzello]

If you are still having a problem, post your my.cnf file here (usually /etc/my.cnf).

 

[sweetlew]

Here is the result of a grep on mysql:

unix 2 [ ACC ] STREAM LISTENING 5587 /var/run/mysqld/mysqld.sock

here is a copy of my.cnf file:

SSH Secure Shell 3.2.9 (Build 283)
Copyright (c) 2000-2003 SSH Communications Security Corp -

http://www.ssh.com/

This copy of SSH Secure Shell is a non-commercial version.
This version does not include PKI and PKCS #11 functionality.


Linux markubuntu 2.6.8.1-3-386 #1 Tue Oct 12 12:41:57 BST 2004 i686 GNU/Linux

The programs included with the Ubuntu system are free software;
the exact distribution terms for each program are described in the
individual files in /usr/share/doc/*/copyright.

Ubuntu comes with ABSOLUTELY NO WARRANTY, to the extent permitted by
applicable law.
You have mail.


boo@markubuntu:~ $ cd /etc
boo@markubuntu:/etc $ cd services
-bash: cd: services: Not a directory
boo@markubuntu:/etc $ cd ..
boo@markubuntu:/ $ grep /etc/services 3306
grep: 3306: No such file or directory
boo@markubuntu:/ $ grep /etc/ 3306
grep: 3306: No such file or directory
boo@markubuntu:/ $ services
-bash: services: command not found
boo@markubuntu:/ $ netstat -a | grep mysql
unix 2 [ ACC ] STREAM LISTENING 5587 /var/run/mysqld/mysqld.sock
boo@markubuntu:/ $ netstat -a | grep mysql
unix 2 [ ACC ] STREAM LISTENING 5587 /var/run/mysqld/mysqld.sock
boo@markubuntu:/ $ cd /etc
boo@markubuntu:/etc $ cd mysql
boo@markubuntu:/etc/mysql $ ls
debian.cnf my.cnf
boo@markubuntu:/etc/mysql $ nano my.cnf
GNU nano 1.2.3 File: my.cnf Modified





#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "/var/lib/mysql/my.cnf" to set server-specific options or
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
[client]
#password = my_password
port = 3306
socket = /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
err-log = /var/log/mysql/mysql.err
socket = /var/run/mysqld/mysqld.sock

[mysqld]


boo@markubuntu:/etc/mysql $ ls
debian.cnf my.cnf
boo@markubuntu:/etc/mysql $ nano my.cnf
GNU nano 1.2.3 File: my.cnf

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "/var/lib/mysql/my.cnf" to set server-specific options or
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
[client]
#password = my_password
port = 3306
socket = /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
err-log = /var/log/mysql/mysql.err
socket = /var/run/mysqld/mysqld.sock

[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306

Thanks for the help!!!!!!

 

[azzazzello]

aha! well there is the problem - the server is not running on port 3306. The only thing you have running is a sock listener, which is how you are able to locally connect. Did you post your entire my.cnf file? I just fired up a Xandros system (also a Debian clone) and its my.cnf file looks very similar to yours, including comments. BY DEFAULT it has this line in it

#
# The skip-networkin option will no longer be set via debconf menu.
# You have to manually change it if you want networking i.e. the server
# listening on port 3306. The default is "disable" - for security reasons.
skip-networking

look for that line - if it in there, remove it and restart server

 

[sweetlew]

I think I'm getting close, now I get an error that says;

Host '192.168.x.x' is not allowed to connect to this MySql server.

 

[azzazzello]

ok, now go to the mysql prompt, and from there do this

mysql> GRANT ALL PRIVILEGES ON yourdatabase.* TO 'user'@'192.168.x.x'
    -> IDENTIFIED BY 'secretpassword';

and read this page in general to understand how and what GRANT does

http://dev.mysql.com/doc/mysql/en/grant.html

 

[sweetlew]

Thanks a lot!!!!!!!!!!!!!!!!!!!!!!
That worked!!!!!!!!!!!!!!!