Need help. remote access to my MySQL database.

[EM @MAJ.com]

This is the first I will try to manage a MySQL database remotely, so I have no idea.

I have LAMPP setup, Plesk is my management software which I find out with limitation to open large queries through phpmyadmin. I previously used cPanel and no problem with it.

I want to use a third party MySQL database software to connect remotely to my database, so I can perform quick table queries, monitoring and backup.

Questions:
How to connect remotely to my MySQL Database, I already configured the port in firewall. But still can't connect remotely. I can login to MySQL through SSH.

What do I need to configure in my Linux/Plesk/MySQL so I can connect remotely?

Thanks in advance.
EM @ KING.NET

 

[mvl]

First check if you can make a connection. If you have a Linux client machine you can do this by trying:

telnet [your.mysqlserver.hostname.or.ip] 3306

assuming that you have a standard MySql instance running on port 3306.

If you can't connect check if the option 'skip-networking' in your MySQL config is off. If you skip networking you will not be able to connect from a remote host.

Then you should check if your firewall allows access on port 3306.

If everything else fails, but you can make an ssh connection to you server, you can do port forwarding and bypass the firewall:

Download putty (I like the Putty-tray version)

Now configure a connection with port forwarding as follows:

1. fill in the MySql server hostname or ip address
2.click open the connection->ssh->tunnels categroy (left-hand panel)
3.choose a source port, if you are NOT running a MySQL server on your pc, just use 3306
4.for the remote port choose 'localhost:3306'
5.choose the 'remote' radio button
6.click the 'add' button

Now go back to the 'session' category in the left-hand panel and type a name to remember this connection in the text field 'Saved sessions'. Now press the 'save' button.
Now as long as you have an ssh connection to your remote host, your MySql client can be configured to 'localhost' port 3306 as your MySql host and Putty will automagically forward it to the remote database at port 3306.

 

[katherine]

You will need to add permissions for the user. By default users may only connect though localhost.
First step is to verify that you can connect to mySQL remotely. If the user does not have permission to connect mySQL will tell you.

 

[mvl]

You will need to add permissions for the user. By default users may only connect though localhost.
First step is to verify that you can connect to mySQL remotely. If the user does not have permission to connect mySQL will tell you.

That is true. My assumption was that you were unable to connect to MySql from a remote client. But if you are able to connect but unable to authenticate you should GRANT permissions to the remote user (username@'your.hostname.or.ip' or username@'%' for connections from any remote host). Don't forget to FLUSH PRIVILEGES after setting the correct permissions.

 

[EM @MAJ.com]

I think this is the missing link. Will you please give me step by step instruction on how to add permission to the user "admin"?

I can connect locally, checked the ports 3306 open using network query tool (http://www.netstumble.com/whois-history-check/networkquery.html)

Thank you everyone, almost there.
EM @ KING.NET

---------- Post added at 09:37 AM ---------- Previous post was at 09:31 AM ----------

To grant user "admin" at 123.123.123.123 (remote my IP)

mysql> flush privileges;
mysql> grant all on *.* to 'admin'@'%';
mysql> flush privileges;
mysql> exit

I'm using the wildcard % to allow all host.
is this correct?

 

[mvl]

You only have to flush the privileges after setting them. The permissions that you grant or revoke will not be used until you flush the privileges or restart the MySql server.
Although the syntax is correct (it will work) I consider setting privileges for remote hosts with a public ip insecure and I prefer tunneling or port forwarding over ssh combined with local permissions instead.

 

[EM @MAJ.com]

Can you give me an example on how to do this task?

Thanks in advance,
EM @ KING.NET

 

[mvl]

For the port forwarding you can read my first reply. I don't have the time now, but maybe later today I will write down a little tutorial.

 

[EM @MAJ.com]

Thank you. I will try the port forwarding.

 

[cerno]

There is a great free program called eskuel that has tons of mysql functionality and is super easy to install and use. It operates as a website, not ssh.

 

[south]

http://www.toadsoft.com/toadmysql/Overview.htm

Freeware. Excellent. Can connect through ssh to remote mysql. I use it (almost) daily.

 

[EM @MAJ.com]

I need something that I can connect through SSH so I don't have to make the necessary permission changes for MySQL users access. I tried MVL suggestion but I can't make it work, maybe a detailed step by step will help me.

Thank you in advance.
Em @ king.net

 

[south]

I need something that I can connect through SSH so I don't have to make the necessary permission changes for MySQL users access. I tried MVL suggestion but I can't make it work, maybe a detailed step by step will help me.

Thank you in advance.
Em @ king.net

You just need to be able to work on a single mysql database at a time, correct? And this is for you to work on in, not for a (php) script to access it remotely, so basically just modify/create tables, insert data, etc? If so, try the link I posted. It does this, including making the connection through ssh built into the program. It really sounds like what you need, unless I'm reading it wrong.

 

[EM @MAJ.com]

I can use the tool to download my MySQL data too? I will try it.

Thank you,
EM

 

[south]

I can use the tool to download my MySQL data too?

And much much more

If you want to be able to easily change database permissions, take quick backups, etc, you also might consider installing webmin on the server if you are able. If so, I would suggest you change the default port, and set the allowed IP addresses in the webmin configuration.

Cheers!