mysql remove connect

[Cendent]

Trying to use MySQL workbench on my dev server. Every time I try connecting I get:
Can't connect to MySQL server on '192.168.1.101'



I'm using user josh with password testing.
I set up the user with:
CREATE USER 'josh'@'192.168.1.100' IDENTIFIED BY 'testing';
and gave permisions:
GRANT ALL PRIVILEGES ON *.* TO 'josh'@'192.168.1.100';


my /etc/my.cnf looks like this:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
bind-address = 127.0.0.1

# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0

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


Checked port using: lsof -i4 -P | grep -i mysql

mysqld 29584 mysql 10u IPv4 1900338 TCP dev.domain:3306 (LISTEN)

Opened up the port using:

iptables -A INPUT -i eth0 -p tcp --destination-port 3306 -j ACCEPT

restarted mysqld, iptables.

Any suggestions?

Thanks,
Josh

[linuxaria]

You are listening on 127.0.0.1 right ?
What's the otuput of "telnet 127.0.0.1 3306" ?

Your error is Can't connect to MySQL server on '192.168.1.101', but than you need to listen there, or on any IP you have configured on the server.

Bye

[Cendent]

Thanks for the reply.

When I telnet 127.0.0.1 3306 I get:

Trying 127.0.0.1...
Connected to dev.domain (127.0.0.1).
Escape character is '^]'.
4
5.0.77dpOw,qx'V,ZAvQo]bWl;~,
Connection closed by foreign host.


Still new not sure what that means.

[linuxaria]

That means that your Mysql is answering at that address, 127.0.0.1 port 3306, that's the private address that every PC has.

If you have the program that need to access Mysql on the same machine use that address (like an apache + cgi that run on the same mysql machine).

If you need to query the mysql server from another computer you need to edit your /etc/my.cnf, and change bind-address = 127.0.0.1.
You can put there your network IP (192.168.1.101 ?) or nothing that should means that Mysql listen on any available IP

Let me know if this work

[Cendent]

hey thanks,
I'm still getting the same error, but when I try:

telnet 192.168.1.101 3306
Trying 192.168.1.101...
Connected to 192.168.1.101 (192.168.1.101).
Escape character is '^]'.
FHost '192.168.1.101' is not allowed to connect to this MySQL serverConnection closed by foreign host.

Looks like its listening correctly now:
grep -i mysql
mysqld 3792 mysql 10u IPv4 2028677 TCP 192.168.1.101:3306 (LISTEN)


Do I need to add something to my my.cnf file?

Thanks,
Josh

[linuxaria]

Try to check this thread
mysql is down when remote telnet to 3306.

[Cendent]

Almost got it . When I telnet 192.168.1.101 3306 will get:
Trying 192.168.1.101...
Connected to 192.168.1.101 (192.168.1.101).
Escape character is '^]'.
4
5.0.77.#`=OU>t,NiGdCa@[(UR}Connection closed by foreign host.

But when I'm trying to connect through the MySQL workbench still getting the same error Can't connect to MySQL server on 192.168.1.101...


here the user I'm trying to login with:
Host: 192.168.%
User: josh
Password: 340bff3dr4076cdd
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: N
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0


Any ideas?

Thanks for your help so far it defiantly appreciated

[linuxaria]

Some more info please

You are trying to connect from box 1 to box 2 where the mysql is running ?
Can you try a mysql cli client connection and post the result ?
Cry How To - Connect to MySQL

Can you check also your mysql log and post what's logged there when you try the connection ?

I feel we are close to the solution

[Cendent]

Yea I'm trying to connect from computer 1 to computer 2 and access mysql db. Both are on the same network. ip for computer with the mysql db is 192.168.1.101, the ip of the one I'm trying to connect from is 192.168.1.100


When I try connecting with cry I get:
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.1.100' (110)

Here is the output from mysqld.log:


100819 23:55:29 mysqld started
100819 23:55:29 InnoDB: Started; log sequence number 0 43665
100819 23:55:29 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.0.77' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution

[linuxaria]

Ok, some check you can do:

1) Check that in your my.cnf you have commented this option:

[mysqld]
...
skip_networking
...

Otherwise Mysql wil laccept only local connection.

2) Check again firewallls, both on mysql server (must have 3306 incoming open) and on the client (must have outgoing ports open), if you have some doubt (and if it's possbile) try to turn off both firewalls and do some test

Follow this check list:
Error2003-CantConnectToMySQLServer - MySQL Forge Wiki

Next if its still not working some more test

i'll call box 1 (mysql server)
Box 2 (the other server)

For every test please post teh command you use to conenct and the results:

Test 1:
Connect to your mysql server from box1 using the Socket

Test 2
Connect to your mysql server from box1 using the Public IP

Test 3
telnet from box2 to box 1 port 3306

test 4
Connect to your mysql server from box2 using the Public IP