How do i connect to remote mysql server by Navicat?

vidda22
vidda22 used Ask the Experts™
on
Hi guys! in a code area I pasted body of my.conf that is in my remote server on linux machine with OS fedora 8 redhat.
MySQL works fine when I login as root and change my database. Unfortunately when I try to connect via Navicat that is installed on my windows XP home computer I get message as follows:
2003 - Can't connect to MySQL server on [Ip address] (10065)

I try to connect to port 3306

I don't know if that has anything in common, as I am beginner in linux, and I didn't set up remote machine, but I also have Linux Firewall when I click it it displays me page like this:

               Linux Firewall
Rules file /etc/sysconfig/iptables
Incoming packets (INPUT)
Select all. | Invert selection.
          Action          Condition          Move          Add  
      Accept       If protocol is UDP and destination port is 11235:11236             
      Run chain RH-Firewall-1-INPUT       Always             
Select all. | Invert selection.
            
Forwarded packets (FORWARD)
Select all. | Invert selection.
          Action          Condition          Move          Add  
      Reject       Always             
Select all. | Invert selection.
            
Outgoing packets (OUTPUT)
There are no rules defined for this chain.
            
Chain RH-Firewall-1-INPUT
Select all. | Invert selection.
          Action          Condition          Move          Add  
      Accept       If input interface is lo             
      Accept       If protocol is ICMP and ICMP type is any             
      Accept       If protocol is 50             
      Accept       If protocol is 51             
      Accept       If protocol is UDP and destination is 224.0.0.251 and destination port is 5353             
      Accept       If protocol is UDP and destination port is 631             
      Accept       If protocol is TCP and destination port is 631             
      Accept       If state of connection is ESTABLISHED,RELATED             
      Accept       If protocol is TCP and destination port is 22 and state of connection is NEW             
      Accept       If protocol is TCP and destination port is 80 and state of connection is NEW             
      Accept       If protocol is TCP and destination port is 21 and state of connection is NEW             
      Accept       If protocol is TCP and destination port is 443 and state of connection is NEW             
      Accept       If protocol is TCP and destination port is 25 and state of connection is NEW             
      Accept       If protocol is TCP and destination port is 53 and state of connection is NEW             
      Accept       If protocol is UDP and destination port is 53 and state of connection is NEW             
      Accept       If protocol is TCP and destination port is 10000 and state of connection is NEW             
      Reject       Always             
Body of my.conf
[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
 
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

1. Try to check is the network is OK. Ping from your XP box to Linux box

2. Try to check is port 3306 available from your XP box.

C:\>telnet [linux_IP] 3306

You should get a blank screen if the port is available.

3. Default install of MySQL is not allows TCP connection from another host, except localhost. So you must set each user to be allowed connect from your XP Box. For example to allow root user connection from your XP Box to certain database :

mysql> GRANT ALL PRIVILEGES ON DATABASE_NAME.*  TO root@IP_OF_XPBOX IDENTIFIED BY 'password' WITH GRANT OPTION;

Hope help

Author

Commented:
This was so helpful! Thank You!!!
Unfortunatelly problem is still there. I executed the command as you recommended...

After executing this command- C:\>telnet [linux_IP] 3306 from my local XP I get this message:
Connecting to [MyLinux_ipAddress]... Could not open connection to the host, on port 3306: Connect failed.

Does it mean firewall stops it all?

Author

Commented:
And another question is when I type this command to mysql:
GRANT ALL PRIVILEGES ON DATABASE_NAME.*  TO root@IP_OF_XPBOX IDENTIFIED BY 'password' WITH GRANT OPTION;

What do I type in as IP_OF_XPBOX if my ip changes nearly every day?
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Dear vidda22.

It's right

"could not open connection to the host, on port 3306: Connect failed."

may be caused by firewall that stops your connection from XP Box.


Try to clear the INPUT chain rules in your Linux IPTables (#iptables -F), and then try to telnet to port 3306 again. If it helps, reconfigure your IPTables later for security reason.

The command GRANT ALL PRIVILEGES ON DATABASE_NAME.*  TO root@IP_OF_XPBOX IDENTIFIED BY 'password' WITH GRANT OPTION is execute in the mysql prompt after logged in as root in your local Linux box.

If you plan to change your IP of XP Box, change the IP_OF_XPBOX with % :
GRANT ALL PRIVILEGES ON DATABASE_NAME.*  TO root@%  IDENTIFIED BY 'password' WITH GRANT OPTION;

It mean the root user can login from any host now. But we should think it about security reason later.

Regards


Didit

Author

Commented:
You are really good, and I will use this sql query. I understand, that beacouse of security reason it would be good to set it up for my IP, but since it's dynamic IP what can I do...
Thanks for that anyway!

I tried and I took off all the firewall rulles and conditions, but when I telnet this port it still shows the same error -
"could not open connection to the host, on port 3306: Connect failed."

Any Ideas please?!
If I don't pass the port to telnet it gives me the same response is it normal?
Many thanks!
In the dynamic IP environment, you can allow any host or range of host from DHCP server in your environment. But don't allow root connection, create a limited userid, e.g can only SELECT, UPDATE INSERT, DELETE (learn more about MySQL)

Are you sure that your connection to Linux Box is OK?, try to ping from XP Box

C:\>ping [Linux_Box_IP], should got a reply. Also try to telnet localhost 3306 in your Linux Box, to make sure your MySQL Server has been listening on port 3306.

If you don't pass the port to telnet, it will be default telnet port (23). Usually, default Linux installation not includes telnet service, so you got same error.

Regards


Didit

Author

Commented:
Thank You so much for helping me! You are really great!!

So yeah from my XP box when I ping the IP address of Linux Box  I get full score (0% loss 4 sent, 4 received)

When I "telneted" my Linux that's what I got (I tried localhost 3306 and localhost):

> telnet localhost 3306
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.
Connection closed by foreign host.
> telnet localhost
telnet: connect to address 127.0.0.1: Connection refused
Trying 127.0.0.1...

Commented:
You stated "remote" linux server.
Is this a shared hosting server?
Do you have a dedicated IP?

If you have a HOSTING Company like 1and1 or Hostmonster or Hosting24, you have to check their FAQ area to see if they allow remote MySQL.

ALSO, when you login as root --- are you ON the remote server?

If YES, then you need to change the access on the MySQL so that you can connect remotely.

If you have webmin installed on your remote server, it will make life much easier to configure.

I use Navicat all the time from my Vista desktop and remotely from one of my XP desktops and it works very well if the MySQL server is configured properly --- so install webmin on your remote server --- as you stated that you have ROOT access and this will help you a lot on configuring and maintaining your server.

http://www.webmin.com they have file for your Fedora!
Dear vidda22, thanks for your appreciate.

> telnet localhost 3306
>Trying 127.0.0.1...
>Connected to localhost.
>Escape character is '^]'.
>Connection closed by foreign host.

Yes, it mean your MySQL Server already listen at localhost port 3306.


> telnet localhost
>telnet: connect to address 127.0.0.1: Connection refused
>Trying 127.0.0.1...

It's no problem, that's no telnet service (port 23) in your Linux Box.

I think the key is in your Linux Box firewall. Try to display the rules :

#iptables -L

What are the active rules?


Didit

Author

Commented:
Didit Thanks again!
What goes around comes around, your help will come back to you :)
You are a real expert. Even after closing question!

I created an extra user as You recommended and it worked fine. I spotted at the very and, that root user could login only from localhost :D

Thank You buddy and take care!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial