Enable remote access to MySQL on VPS

Recently a friend of mine wanted to setup mysql on his VPS and having read on some blogs was successful in setting up the database on his VPS, but when it came to setting  up his local connection he faced the ire of the error popups.

Its not rocket science to setup mysql server and make the connection accessible over remote machines, e.g our local laptops or desktops.

I just taught him how to do it and here you go.

I did it for him on his Ubuntu 14.04 VPS and  herw what I did.

Edit  /etc/mysql/my.cnf

Change the line bind-address=xx.xx.xx.xx to something like

bind-address=0.0.0.0.  this will make it to listen to all address.

 

First do not restart mysql, there are some more things to do .

Login to your mysql using the command

mysql -u root -p

Now grant your  ‘temp’ user all access

GRANT ALL ON mydb.* TO myuser@’%’ IDENTIFIED BY ‘mypass’;

 

Now flush the hosts list on your mysql logins by using another admin command as below

mysqladmin -uroot -p flush-hosts

 

Well its now time to restart the service by the command

‘service mysql restart’

Once you are done, you can easily connect to the instance from wherever you want.

Note : Never use Grant without a password, its dangerous.