Hello to all. Few days back I was asked to connect to remote MySQL Server without actually logging in to shell account at Server i.e without using telnet,ssh etc. If you too face a similar issue then follow simple steps given below-:
Install MySQL Client on client machine:
You need MySQL client on client machine to directly connect to MySQL server. For that find appropriate client package according to Operating System of client machine. If you also have MySQL Server installed on client machine then you don’t need to install MySQL client.
Open Server configuration file:
Login to the Server using shell or any other means. Then open your MySQL my.cnf file. If you have installed MySQL server binaries then this file is most likely to find at /etc/my.cnf or /etc/mysql/my.cnf. But if you have compiled MySQL from source then find this file under your MySQL install prefix. Also, note that you need root privileges to edit this file.
Edit Server configuration file:
Now, under MySql configuratin settings look for line starting at “bind-address” such as:
bind-address = 127.0.0.1
Change the address value to the IP address of the Server and save the file.
Restart MySQL Server:
Now restart your MySQL Server. For example: On many Linux environments this command will do it.
$sudo /etc/init.d/mysql restart
MySQL Server now listens requests coming from IP interface so remote clients requests are now received by Server.
Edit user settings:
Now edit the settings of user you want to connect to Server as. Easy way is to configure user using Privileges Tab of PhpMyAdmin. Under host field select ANY to bind from any IP address or give particular IP of your client if you don’t want to connect to Server from anywhere of the world except from Server itself and your client pc. It is advised to not remove previous entry of the user (entry for localhost). Also, it is advised to set a different password of good strength to connect to Server from outside for Security reasons. This is big advantage of MySQL that you can choose different passwords for same user depending on the host from which you are connecting.
Now, flush privileges, if you have edited user from commandline. PhpMyAdmin will automatically do it.
Thats it. Now, try connecting to MySQL Server from client machine with following command:
$mysql -h ServerIP -u username -p
Replace ServerIP and username with yours in above command. If you see MySQL prompt (mysql>) that means you have successfully configured the Server. If you are getting error such as:
ERROR 1045 (28000): Access denied for user 'username'@'hostnamw' (using password: YES)
Then you have not configured/edited the user properly. Other source of Errors is Firewall, be sure that MySQL Port (3306 by default) is opened in Firewall.