Unable to connect

I have mysql databases on our server at a remote location. I need to connect to these databases and maintain them from different locations. HeidiSQL looks to be the perfect tool, however, I cannot seem to connect.
There are a few questions I need answered:

The server is using Linux and has a login password for root. There are different passwords for the databases. Which password is required?

Can I connect directly to the server databases with HeidiSQL through the IP address from a different location?


Thanks,
Bob

have the same problem

HeidiSQL looks to be the perfect tool, however, I cannot seem to connect.

Please provide all error messages you got.

The server is using Linux and has a login password for root. There are different passwords for the databases. Which password is required?

You have to use the password which was set in the mysql server. By default on a fresh install of the mysql-server you normally have only a user "root", which can only connect from localhost to localhost. So if you want remote access you have to setup a new user that can connect from other hosts. If you need some further help on that please tell us.

Can I connect directly to the server databases with HeidiSQL through the IP address from a different location?

Sure, that's one of HeidiSQL's power-features 8)

I think this should be sticky :-)

I've explained this a couple of times already on the forum, but i'll do it just one more time and in detail so it should cover pretty much every new answer on this subject

When connecting to a remote database you need to keep in mind the following things:

MySQL has it's own permissions/rights management. When you connect to a mysql-server, it will want to know your username and password and - if you want to connect to a specific database - what database you want to connect to.
First of all, it will look up your username and password to see if you have permission at all to connect and then it will check whether you have permissions to connect to that specific database. (this is not exactly what happens, but more or less). Btw, all these permissions are stored in the users table in the mysql-database (the database with the name mysql)
As an extra security measure every GRANT (permission) also has information about where the user is allowed to connect from. So, the combination of your username, password and database might be correct, but if it is specified that you may only connect from the localhost (127.0.0.1) and you connect from outside, you will still be denied access.
Most hosting providers will have set up there server this way! However, some of them wont mind to make an exception if you have a static IP address.

But that's not the whole story. The mysql-database will run on a server which will (hopefully) also have some security precautions itself, like a firewall. So, even if the combination of your username, password and database is correct and you are allowed to connect from your remote IP - if the firewall doesn't allow connections from outside to port 3306 (this port is usually used for the mysql database), you will still get nowhere.

So, when you have connection problems, this is what you should do:

[list:8d9dffbe84]
* check that your username and password are correct and that you have specified the right database
* If you have a hosting provider: call them and ask if
[list:8d9dffbe84]
* they allow connections to the database port (usually 3306) from outside (also check that it is indeed 3306)
* it is allowed to connect with your username from outside. If not:
[list:8d9dffbe84]
*check if you have a static ip-address (if you don't know, call you internet provider)
* tell your hosting provider that you have a static ip address (if that is the case) and beg them to change the permissions accordingly
* (or if you don't have it, beg them open it for your domain (this is also possible), or just open it for any remote connection)
[/list:u:8d9dffbe84]
[/list:u:8d9dffbe84]
* If you have access to the database server yourself:
[list:8d9dffbe84]
* connect to the mysql database (USE mysql) and have a look at the user table (SELECT * FROM user)
* if you need to set a special permission for a static ip, this is how you do it: GRANT ALL ON databasename.* TO username@ip-address IDENTIFIED BY 'password' (all lowercase words are variables you have to fill in yourself). This will give you all the permissions on all the tables in that database. see also the grant syntax in the manual. And don't forget to do FLUSH PRIVILEGES afterwards, or the new permissions won't be loaded!
[/list:u:8d9dffbe84][/list:u:8d9dffbe84]

I think this should be sticky :-)

Very good point! Just looking for some button or option where to do so... Can somebody point me where to make posts sticky?

And thanks for the good explanations, Simkin!

Just got it: Simply edit the very first post of the thread and there select "Sticky" from the three radio options. :idea:

For anyone who administrates their own remote server, if you're still having trouble connecting after siMKin's instructions, your firewall is probably blocking you. If you use iptables, you can add something like this:

-A INPUT -i eth0 -p tcp --dport 3306 -s XX.XX.XX.XX -j ACCEPT

where XX.XX.XX.XX is your local IP. And as long as eth0 is your interface and 3306 your MySQL port. Of course be careful playing with your firewall!

Glad I found this as well. It's a darn sight more pleasing that using phpMyAdmin or a console. Cheers to the devs.

Reviving this V-E-R-Y old thread. Running Heidisql on Win7. Connecting to my Centos server. When disabling the firewall, I can connect to the mySQL server. When IPtables is up, I can not access the database. I added both inbound and outbound rules to my firewall, but no luck. My settings:

Table: filter
Chain INPUT (policy ACCEPT)
num target prot opt source destination
1 RH-Firewall-1-INPUT all -- 0.0.0.0/0 0.0.0.0/0
2 DROP all -- 64.xxx.xx.xx 0.0.0.0/0

Chain FORWARD (policy ACCEPT)
num target prot opt source destination
1 RH-Firewall-1-INPUT all -- 0.0.0.0/0 0.0.0.0/0

Chain OUTPUT (policy ACCEPT)
num target prot opt source destination
1 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 tcp spt:3306

Chain RH-Firewall-1-INPUT (2 references)
num target prot opt source destination
1 ACCEPT all -- 0.0.0.0/0 0.0.0.0/0
2 ACCEPT icmp -- 0.0.0.0/0 0.0.0.0/0 icmp type 255
3 ACCEPT esp -- 0.0.0.0/0 0.0.0.0/0
4 ACCEPT ah -- 0.0.0.0/0 0.0.0.0/0
5 ACCEPT udp -- 0.0.0.0/0 xxx.0.0.xxx udp dpt:5353
6 ACCEPT udp -- 0.0.0.0/0 0.0.0.0/0 udp dpt:631
7 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 tcp dpt:631
8 ACCEPT all -- 0.0.0.0/0 0.0.0.0/0 state RELATED,ESTABLISHED
9 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 state NEW tcp dpt:20
10 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 state NEW tcp dpt:21
11 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 state NEW tcp dpt:22
12 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 state NEW tcp dpt:25
13 ACCEPT udp -- 0.0.0.0/0 0.0.0.0/0 state NEW udp dpt:53
14 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 state NEW tcp dpt:53
15 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 state NEW tcp dpt:110
16 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 state NEW tcp dpt:143
17 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 state NEW tcp dpt:80
18 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 state NEW tcp dpt:443
19 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 state NEW tcp dpt:8888
20 REJECT all -- 0.0.0.0/0 0.0.0.0/0 reject-with icmp-host-prohibited
21 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 tcp dpt:3306

What is blocking the mySQL traffic ?

I have a different sintax in my configuration, which works fine for local LAN addresses:

-A INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT
-A INPUT -p tcp -m tcp --dport 3306 -s 192.168.0.0/16 -j ACCEPT

Hope this helps.