distal-attribute
distal-attribute
distal-attribute
distal-attribute

Unable to connect

Star posted 8 years ago in Creating a connection
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
navai posted 8 years ago
have the same problem
ansgar posted 8 years ago

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



Please provide all error messages you got.

Star wrote: 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.

Star wrote: 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)
siMKin posted 8 years ago
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]
ansgar posted 8 years ago

siMKin wrote: 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!

ansgar posted 8 years ago
Just got it: Simply edit the very first post of the thread and there select "Sticky" from the three radio options. :idea:
njmattes posted 5 years ago
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.

Please login to leave a reply, or register at first.